tag:blogger.com,1999:blog-90757861581220891042024-03-14T11:49:00.184+05:30SQL-Server Blog of Ritesh Shah --Fight the fear of SQL with SQLHub.comThis blog is focusing on Microsoft Technology. Especially SQL-ServerRitesh Shahhttp://www.blogger.com/profile/09562656548324468450noreply@blogger.comBlogger364125tag:blogger.com,1999:blog-9075786158122089104.post-23459412826244089602012-10-16T22:41:00.000+05:302012-10-16T23:04:48.670+05:30blog.extreme-advice.com is new blog home for me<div dir="ltr" style="text-align: left;" trbidi="on">
SQLHub.com is my favorite domain name and I love SQL Server so I started this blog few years back. I still like SQL Server very much but now I am thinking to share my knowledge in SQL Server along with other technology too and that why I have booked new domain <a href="http://www.extreme-advice.com/">http://www.extreme-advice.com/</a> and started new technology blog at <a href="http://blog.extreme-advice.com/">http://blog.extreme-advice.com/.</a><br />
<br />
SQLHub.com will not have my new articles now so I would like to request my blog readers, please update your book mark with my new blog <a href="http://blog.extreme-advice.com/">http://blog.extreme-advice.com/</a>. You can even subscribe to new blog, there is text box given for subscription in <a href="http://blog.extreme-advice.com/">http://blog.extreme-advice.com/</a> at center-right side of the blog.<br />
<br />
You can even drop by face book page of <a href="http://blog.extreme-advice.com/">extreme-advice.com</a>. Have a look at <a href="http://www.facebook.com/XtremeAdvice" target="_blank">Facebook page of Extreme-Advice</a>.<br />
<br />
Enjoy Reading !!!!<br />
<br />
<b>Reference: Ritesh Shah</b><br />
<a href="http://www.extreme-advice.com/" target="_blank" title="Extreme-Advice"><b>http://Extreme-Advice.com</b></a><br />
<a href="http://www.sqlhub.com/"><b>http://www.sqlhub.com</b></a><br />
<b>“</b><a href="http://learn.extreme-advice.com/" title=""Quick Learn". Tips by experts"><b>Quick Learn</b></a><b>“</b><br />
<b>Note: Microsoft Books online is a default reference of all articles</b></div>
<div class="blogger-post-footer">Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah</div>Ritesh Shahhttp://www.blogger.com/profile/09562656548324468450noreply@blogger.com0tag:blogger.com,1999:blog-9075786158122089104.post-14527332251086297312012-10-15T09:36:00.002+05:302012-10-15T09:36:57.826+05:30Extreme-Advice : Analyze IO disk pressure in SQL Server<div dir="ltr" style="text-align: left;" trbidi="on">
Before we move further, have a look at my following article as we are going to use the script given in that article:<br />
<br />
<a data-mce-href="http://blog.extreme-advice.com/2012/10/12/find-disk-performance-with-sys-dm_io_virtual_file_stats-in-sql-server/" href="http://blog.extreme-advice.com/2012/10/12/find-disk-performance-with-sys-dm_io_virtual_file_stats-in-sql-server/">Find Disk performance with sys.Dm_io_virtual_file_stats in SQL Server</a><br />
I have already provided the script to display <a data-mce-href="http://blog.extreme-advice.com/2012/10/12/find-disk-performance-with-sys-dm_io_virtual_file_stats-in-sql-server/" href="http://blog.extreme-advice.com/2012/10/12/find-disk-performance-with-sys-dm_io_virtual_file_stats-in-sql-server/">Disk IO waits</a> in above given article, now we will further enhance that process.<br />
<br />
By
looking at IO Stall given in above article, can’t help us much. We
should have data captured for few hours or may be a few days so that we
can have details like which time/day we get high number of stall, we can
find pattern of IO based on the statistics we capture for few
hours/days. Now question comes into the picture, how can we do it with
help of script given in “<a data-mce-href="http://blog.extreme-advice.com/2012/10/12/find-disk-performance-with-sys-dm_io_virtual_file_stats-in-sql-server/" href="http://blog.extreme-advice.com/2012/10/12/find-disk-performance-with-sys-dm_io_virtual_file_stats-in-sql-server/">Find Disk performance with sys.Dm_io_virtual_file_stats in SQL Server</a>” article?<br />
<br />
Well we have to enhance the script give there. Let us start our journey to capture pattern of IO.<br />
<br />
<a data-mce-href="http://blog.extreme-advice.com/2012/10/14/analyze-io-disk-pressure-in-sql-server/" href="http://blog.extreme-advice.com/2012/10/14/analyze-io-disk-pressure-in-sql-server/">Click here</a> to read complete article.</div>
<div class="blogger-post-footer">Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah</div>Ritesh Shahhttp://www.blogger.com/profile/09562656548324468450noreply@blogger.com0tag:blogger.com,1999:blog-9075786158122089104.post-6573947179219143702012-10-12T09:08:00.000+05:302012-10-12T09:08:13.382+05:30Extreme-Advice : Find Disk performance with sys.Dm_io_virtual_file_stats in SQL Server<div dir="ltr" style="text-align: left;" trbidi="on">
“sys.Dm_io_virtual_file_stats” is one of the excellent DMV in SQL
Server. Generally people used talk about processor, memory and network a
lot but always overlook disk capacity. You can’t read/write more than
the capacity of your disk, no matter how many processor/core you have,
how much RAM you assign to SQL Server or how fast your network is.
<br />
Whenever I have a situation to deal with performance issue of SQL
Server, I never forget to use “sys.Dm_io_virtual_file_stats” DMV. I have
very small script but it can save so many hours which you suppose to
spend in finding a bottleneck of performance in SQL Server whenever disk
is a culprit.<br />
<br />
Each disk has capacity to read and write, if your workload is
generating more read/write then the capacity of your disk, you can’t
achieve optimal performance of other hardware you have.<br />
Here is the script to measure disk IO capacity, have a look at it:<br />
<br />
<a href="http://blog.extreme-advice.com/2012/10/12/find-disk-performance-with-sys-dm_io_virtual_file_stats-in-sql-server/" target="_blank">Click here </a>to read complete article: </div>
<div class="blogger-post-footer">Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah</div>Ritesh Shahhttp://www.blogger.com/profile/09562656548324468450noreply@blogger.com0tag:blogger.com,1999:blog-9075786158122089104.post-59053397373137279112012-10-12T09:05:00.001+05:302012-10-12T09:05:50.481+05:30Extreme-Advice : Performance Counter in Microsoft Windows Server 2008<div dir="ltr" style="text-align: left;" trbidi="on">
Performance Counter in Microsoft Windows Server 2008 :<br />
<br />
As I have promised in my article “<a href="http://blog.extreme-advice.com/2012/10/09/performance-counter-in-sql-server/">Performance Counter in SQL Server</a>“, herewith, I am providing introduction about how to run Performance Counter from Microsoft Windows?<br />
As a Database Administrator or System Administrator, you always have
responsibility to monitor system performance, find bottleneck of
performance which may be related to network, disk, memory, IIS, SQL
Server and much more. Each admin have different responsibility to
monitor different part of system, depends of the domain s/he working
one. These monitoring responsibility can greatly be done with help of
Performance Counter as it cater the needs of DBA, System Admin, IIS
Admin and much more.<br />
<br />
You can create your own “Data Collector Set” in “Performance Counter”
with the counters you need. You have variety of choices to select in
form of counter. some of the important counters are as follows:<br />
<br />
<a href="http://blog.extreme-advice.com/2012/10/11/performance-counter-in-microsoft-windows-server-2008/" target="_blank">Click Here</a> to read complete article. </div>
<div class="blogger-post-footer">Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah</div>Ritesh Shahhttp://www.blogger.com/profile/09562656548324468450noreply@blogger.com0tag:blogger.com,1999:blog-9075786158122089104.post-39350686321521100022012-10-09T13:47:00.001+05:302012-10-09T13:47:30.040+05:30Extreme-Advice : Performance Counter in SQL Server<div dir="ltr" style="text-align: left;" trbidi="on">
Performance Counter always helps DBA as well as system administrator
to keep watch on performance of Operating System as well as SQL Server.
There are thousands of performance counter offered by Windows but I
mostly interested in some of the important performance counter related
to SQL Server and hence I have created one view which I can use directly
from SSMS or from one of web page for monitoring I have.
<br />
Keeping a watch on performance, especially in business hours , is one
of the mandatory task for any DBA or system administrator. Herewith, I
am sharing the TSQL script which can be helpful to any DBA or system
administrator who wants to monitor performance of SQL Server &
Database along with some of the crucial information about buffer &
memory usage, lock & dead lock details and compilation-recompilation
& caching.<br />
<br />
You can run performance counter from GUI in Windows as well as from
DMVs of SQL Server (Given in this article). Performance counter from GUI
in windows give lot more counters then we are provided in DMV of SQL
Server. Windows’ performance counter intend to keep an eye on every
aspect of Windows not only SQL Server but DMV provided in SQL Server
will have only those performance counter which are important and
necessary to keep a watch on performance of SQL Server instance and its
databases. I will cover Performance Counter from Windows GUI sometime
later.<br />
<br />
<a href="http://blog.extreme-advice.com/2012/10/09/performance-counter-in-sql-server/" target="_blank">Click here</a> to read complete article. </div>
<div class="blogger-post-footer">Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah</div>Ritesh Shahhttp://www.blogger.com/profile/09562656548324468450noreply@blogger.com1tag:blogger.com,1999:blog-9075786158122089104.post-53724170442302931532012-10-08T16:18:00.000+05:302012-10-08T16:18:47.336+05:30Extreme-Advice : HIPAA or EPA compliance SQL Server Database<div dir="ltr" style="text-align: left;" trbidi="on">
If you look around in IT market, you will find so many software
product available for different type of laboratory like environment,
pharmaceutical and many more. You will also find so many software
product available for health care, hospitals, insurance and doctors. I
have observed many times that while developing all these application,
many of the US government agency rules are ignored. If you are selling
your product in USA, you shouldn’t ignore the rules & regulation
provided by agencies. EPA (Environmental Protection Agency) and HIPAA
(Health Insurance Portability and Accountability Act) are two of the
example of those agency who controls environmental laboratories and
health care company respectively.<br />
<br />
I have personally observed , in my few of the past consultation
project , that Software company, many times, ignore rules given by these
kind of agencies while developing product. This ignorance or lack of
knowledge will fit you in critical situation along with the user of this
software product. Not only HIPAA or EPA but almost each government
agencies always enforce security for database. They will not allow any
security breach in database. There may be different level of security
and rules given by different agencies but my intention is to define some
generic advice to make the database & SQL Server security little
more tighten. According to me the security advice are given in this
article should be regardless of which software product you are
using/developing or which government agency is going to audit your
environment. You can apply even more security then given in this article
but these advice should be implemented first to make sure that your
server is not exposed to security breaches.<br />
<br />
<a href="http://blog.extreme-advice.com/2012/10/08/hipaa-or-epa-compliance-sql-server-architecture/" target="_blank">Click here</a> to read completed </div>
<div class="blogger-post-footer">Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah</div>Ritesh Shahhttp://www.blogger.com/profile/09562656548324468450noreply@blogger.com0tag:blogger.com,1999:blog-9075786158122089104.post-51241946361979173352012-10-05T16:20:00.003+05:302012-10-05T16:20:50.297+05:30Extreme-Advice : Find database restore date in SQL Server<div dir="ltr" style="text-align: left;" trbidi="on">
Last week I had a situation to check & confirm some data in one
of the master table with current live database with two-week old data so
obviously I have to restore two-week or older backup somewhere and
check the data of the master table from restored database to current
live database.<br />
<br />
We have one development server and we used to restore live database
there as and when needed so I wanted to check when was the live database
restored in development environment last time. If database was restored
in last 15 days, I have to take one of the two-week old backup and
restore it in development environment. If development database wasn’t
restored in last two weeks, I can directly check and compare data of
master table and save my few hours. So I have developed one TSQL script
which can give me the history of my database restore.<br />
<br />
I have used some system table of MSDB database in SQL Server because
backup/restore history of databases resides in MSDB database. Here is
the list of system table resides in MSDB database which I have used to
find database restore date in SQL Server along with its official
definition given by Microsoft in BOL.<br />
<br />
<a href="http://blog.extreme-advice.com/2012/10/05/find-database-restore-date-in-sql-server/" target="_blank">Click Here</a> to read full article. </div>
<div class="blogger-post-footer">Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah</div>Ritesh Shahhttp://www.blogger.com/profile/09562656548324468450noreply@blogger.com0tag:blogger.com,1999:blog-9075786158122089104.post-49844286364241830462012-10-04T13:04:00.003+05:302012-10-04T13:04:38.229+05:30Extreme-Advice: Setup development environment for Windows Azure<div dir="ltr" style="text-align: left;" trbidi="on">
After explaining <a href="http://blog.extreme-advice.com/2012/09/28/what-is-cloud-computing/">what is cloud computing?</a> and <a href="http://blog.extreme-advice.com/2012/10/01/understanding-microsoft-cloud-and-its-essential-part/">essential part of Windows Azure</a>
in last few days, many people asked me how they can setup development
environment for Windows Azure and how they can test or deploy the
application they have created. I got overwhelming response, in form of
messages, from the Facebook page of “<a href="http://www.facebook.com/XtremeAdvice">Extreme-Advice</a>” after keeping above two cloud articles there.<br />
<br />
Well, there are many different tools and kits provided by Microsoft
to setup your development environment for Windows Azure. Some of the
tools are coming in one bundle at the same time you can install it
individually as per your requirement. Most of the tools given here,
would comes under one bundle itself, now a days , even I have mentioned
few of the important part separately just to demonstrate which exact
tools comes in use for which purpose.<br />
<br />
Here, I will be providing with few of the mandatory tools which you will need to develop Windows Azure application.<br />
<br />
<a href="http://blog.extreme-advice.com/2012/10/04/setup-development-environment-for-windows-azure/" target="_blank">Click here</a> to read complete article. </div>
<div class="blogger-post-footer">Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah</div>Ritesh Shahhttp://www.blogger.com/profile/09562656548324468450noreply@blogger.com0tag:blogger.com,1999:blog-9075786158122089104.post-79463045118441496002012-10-03T15:50:00.003+05:302012-10-03T15:50:45.352+05:30Extreme-Advice.com: Calculate Table & Schema size in SQL Server<div dir="ltr" style="text-align: left;" trbidi="on">
Keeping watch on database size, drive size, table size, index size
etc. are few of the primary duty of DBA. I have already written few
articles to cover this type of subject, for example:<br />
<br />
<a href="http://blog.extreme-advice.com/2009/07/08/keep-watch-on-your-disk-space-in-sql-server-20082005/">Keep watch on your disk size</a><br />
<a href="http://blog.extreme-advice.com/2009/07/10/keep-watch-on-your-database-file-size-in-sql-server-20082005/">Keep watch on your database size</a><br />
<a href="http://blog.extreme-advice.com/2009/06/30/calculate-total-size-of-clustered-and-non-clustered-index-for-database-in-sql-server-20082005/">Calculate size of index</a><br />
<br />
Today I am going to introduce two more TSQL Script which covers the
size of each Table and size of each Schema in your SQL Server database. I
used to have log of table size for each date so that I can compare the
growth of table for future disk & other resource planning. It is
really very mandatory for DBA to know the actual size of each table
along with total number or rows available in that table, in order to
make operation smooth and successful.<br />
<br />
<b><a href="http://blog.extreme-advice.com/2012/10/03/calculate-table-schema-size-in-sql-server/" target="_blank">Click Here</a></b> to read full article. </div>
<div class="blogger-post-footer">Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah</div>Ritesh Shahhttp://www.blogger.com/profile/09562656548324468450noreply@blogger.com0tag:blogger.com,1999:blog-9075786158122089104.post-58551001617897127632012-10-02T07:23:00.001+05:302012-10-02T07:23:13.608+05:30Understanding Microsoft Cloud and its essential part<div dir="ltr" style="text-align: left;" trbidi="on">
After giving brief <a data-mce-href="http://blog.extreme-advice.com/2012/09/28/what-is-cloud-computing/" href="http://blog.extreme-advice.com/2012/09/28/what-is-cloud-computing/">introduction of Cloud </a>few
days back I thought to give some light on more details of Microsoft
Windows Azure. Microsoft Windows Cloud is created by so many different
services and out of which, following are the some of the important
services which makes Windows Cloud more fascinating.<br />
<br />
Read full article, <a href="http://blog.extreme-advice.com/2012/10/01/understanding-microsoft-cloud-and-its-essential-part/" target="_blank">click here</a>. </div>
<div class="blogger-post-footer">Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah</div>Ritesh Shahhttp://www.blogger.com/profile/09562656548324468450noreply@blogger.com0tag:blogger.com,1999:blog-9075786158122089104.post-51171225551627535522012-09-28T17:51:00.005+05:302012-09-28T17:51:54.077+05:30Extreme-Advice: What is Cloud Computing ?<div dir="ltr" style="text-align: left;" trbidi="on">
Well, I have received the question “What is SQL Azure?” many times in an
email and in IT events I go. I generally write blog about SQL Server
but I have not written anything about SQL Azure so far. Before I
actually answer What the SQL Azure is, I have to explain a small bit
about “Cloud” first.<br />
<br />
To read complete article, <a href="http://blog.extreme-advice.com/2012/09/28/what-is-cloud-computing/" target="_blank"><b>click here</b></a>.<br />
<br />
<strong>Reference: Ritesh Shah</strong><br />
<br />
<a href="http://www.extreme-advice.com/" target="_blank" title="Extreme-Advice"><strong>http://Extreme-Advice.com</strong></a><br />
<a href="http://www.sqlhub.com/"><strong>http://www.sqlhub.com</strong></a><br />
<strong>“</strong><a href="http://learn.extreme-advice.com/" title=""Quick Learn". Tips by experts"><strong>Quick Learn</strong></a><strong>“</strong><br />
<br />
<strong>Note: Microsoft Books online is a default reference of all articles but examples and explanations </strong></div>
<div class="blogger-post-footer">Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah</div>Ritesh Shahhttp://www.blogger.com/profile/09562656548324468450noreply@blogger.com0tag:blogger.com,1999:blog-9075786158122089104.post-69569980926677237732012-09-25T16:28:00.000+05:302012-09-25T16:28:27.974+05:30Extreme-Advice.com: Find Blocking in SQL Server and use it to send an alert<div dir="ltr" style="text-align: left;" trbidi="on">
As a DBA, I am always interested to find whether blocking is going
on, in my environment or not? How long blocking exists? Which are the
queries/SPs creating blocking etc.<br />
<br />
Blocking generally occurs when one SQL Server connection (user
process or application process) places a lock on a table or a number of
rows and a second connection attempts to read or modify the data under
the lock by first connection. Depending on the type of the lock, this
can cause the second connection to wait until the first connection
releases its lock. A blocked connection waits indefinitely for the
blocking connection to release its lock.<br />
<br />
<a href="http://blog.extreme-advice.com/2012/09/25/find-blocking-in-sql-server-and-use-it-to-send-an-alert/" target="_blank">Read complete article here</a>. </div>
<div class="blogger-post-footer">Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah</div>Ritesh Shahhttp://www.blogger.com/profile/09562656548324468450noreply@blogger.com0tag:blogger.com,1999:blog-9075786158122089104.post-35756501168999751992012-09-25T16:27:00.002+05:302012-09-25T16:27:37.256+05:30Extreme-Advice: Write process in TRASACTION LOG (.LDF) file in SQL Server<div dir="ltr" style="text-align: left;" trbidi="on">
I have written one article about <strong><a data-mce-href="http://blog.extreme-advice.com/2012/09/18/transaction-log-file-ldf-in-sql-server-along-with-acid-property-and-recover-mode-of-database/" href="http://blog.extreme-advice.com/2012/09/18/transaction-log-file-ldf-in-sql-server-along-with-acid-property-and-recover-mode-of-database/">Transaction Log File (.LDF)</a></strong> yesterday, today I am presenting one more article on the same subject.<br />
<br />
There
is a myth going on in the mind of many developers that only DML
statement like INSERT/ UPDATE & DELETE are being written in the log
file but the truth is that SQL Server writes DDL operation like CREATE
TABLE or CREATE INDEX etc. in the log file too.<br />
<br />
<a href="http://blog.extreme-advice.com/2012/09/20/write-process-in-trasaction-log-ldf-file-in-sql-server/" target="_blank">Read complete article here</a>:<br />
</div>
<div class="blogger-post-footer">Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah</div>Ritesh Shahhttp://www.blogger.com/profile/09562656548324468450noreply@blogger.com0tag:blogger.com,1999:blog-9075786158122089104.post-67080307881896771452012-09-25T16:25:00.002+05:302012-09-25T16:25:30.959+05:30Extreme-Advice: Transaction Log File (.LDF) in SQL Server along with ACID property and recover mode of database<div dir="ltr" style="text-align: left;" trbidi="on">
SQL Server databases created with two types of file:<br />
Data File (File with extension .MDF/.NDF)<br />
Log File (File with extension .LDF)<br />
<br />
Data
File can be one or more than one but physical log file will always
remain one for each database. You have control over the size of log file
but you can’t have more than one physical log file. There are so
many mysteries and myth roaming around log file in developer’s mind so I
thought to take an initiative to break this ice.<br />
<br />
<a href="http://blog.extreme-advice.com/2012/09/18/transaction-log-file-ldf-in-sql-server-along-with-acid-property-and-recover-mode-of-database/" target="_blank">Read complete article here:</a> <br />
<strong></strong></div>
<div class="blogger-post-footer">Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah</div>Ritesh Shahhttp://www.blogger.com/profile/09562656548324468450noreply@blogger.com0tag:blogger.com,1999:blog-9075786158122089104.post-52468747742126133182012-09-25T16:21:00.000+05:302012-09-25T16:21:34.995+05:30Extreme-Advice: Find locked table name with duration and count in SQL Server<div dir="ltr" style="text-align: left;" trbidi="on">
Finding LOCK table in SQL Server is one of essential task of DBA.
Locked table for long time in SQL Server adversary affect on performance
of the application, especially in pick business hours.<br />
<br />
I have one
SQL Server VIEW which help us to find all the table which are being
locked currently along with duration since it is being locked.<br />
<br />
<a href="http://blog.extreme-advice.com/2012/09/17/find-locked-table-name-with-duration-and-count-in-sql-server/" target="_blank">Please read complete article here</a>: </div>
<div class="blogger-post-footer">Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah</div>Ritesh Shahhttp://www.blogger.com/profile/09562656548324468450noreply@blogger.com0tag:blogger.com,1999:blog-9075786158122089104.post-70190211590045461792012-09-14T14:40:00.000+05:302012-09-14T14:40:04.004+05:30use of sys.dm_exec_requests and sys.dm_exec_sessions<div dir="ltr" style="text-align: left;" trbidi="on">
As a DBA, it is mandatory to keep an eye on your database and server
too, especially during the business hour. SQL Server providing very
helpful Dynamic Management View to help in this situation. Dynamic
Management View (DMV) are as follow:<br />
<br />
sys.dm_exec_requests<br />
sys.dm_exec_sessions<br />
<br />
Get the script of the SQL Server view, click on the below given link:<br />
<br />
<a href="http://blog.extreme-advice.com/2012/09/14/find-currently-running-sessionprocess-in-sql-server-databaseinstance/">http://blog.extreme-advice.com/2012/09/14/find-currently-running-sessionprocess-in-sql-server-databaseinstance/</a><br />
<br />
<br />
<div>
<strong>Reference: Ritesh Shah</strong></div>
<div>
<strong><a href="http://www.extreme-advice.com/" target="_blank" title="Extreme-Advice">http://Extreme-Advice.com</a></strong></div>
<div>
<a href="http://www.sqlhub.com/"><strong>http://www.sqlhub.com</strong></a><strong><br />
Note: Microsoft Books online is a default reference of all articles but examples and explanations </strong></div>
</div>
<div class="blogger-post-footer">Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah</div>Ritesh Shahhttp://www.blogger.com/profile/09562656548324468450noreply@blogger.com0tag:blogger.com,1999:blog-9075786158122089104.post-29055741294124710522012-09-03T11:20:00.000+05:302012-09-03T11:20:32.414+05:30SQLHub News: Interview of Ritesh Shah is published on PACKT website<div dir="ltr" style="text-align: left;" trbidi="on">
<!--[if gte mso 9]><xml>
<w:WordDocument>
<w:View>Normal</w:View>
<w:Zoom>0</w:Zoom>
<w:TrackMoves/>
<w:TrackFormatting/>
<w:PunctuationKerning/>
<w:ValidateAgainstSchemas/>
<w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid>
<w:IgnoreMixedContent>false</w:IgnoreMixedContent>
<w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText>
<w:DoNotPromoteQF/>
<w:LidThemeOther>EN-US</w:LidThemeOther>
<w:LidThemeAsian>X-NONE</w:LidThemeAsian>
<w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript>
<w:Compatibility>
<w:BreakWrappedTables/>
<w:SnapToGridInCell/>
<w:WrapTextWithPunct/>
<w:UseAsianBreakRules/>
<w:DontGrowAutofit/>
<w:SplitPgBreakAndParaMark/>
<w:DontVertAlignCellWithSp/>
<w:DontBreakConstrainedForcedTables/>
<w:DontVertAlignInTxbx/>
<w:Word11KerningPairs/>
<w:CachedColBalance/>
</w:Compatibility>
<w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel>
<m:mathPr>
<m:mathFont m:val="Cambria Math"/>
<m:brkBin m:val="before"/>
<m:brkBinSub m:val="--"/>
<m:smallFrac m:val="off"/>
<m:dispDef/>
<m:lMargin m:val="0"/>
<m:rMargin m:val="0"/>
<m:defJc m:val="centerGroup"/>
<m:wrapIndent m:val="1440"/>
<m:intLim m:val="subSup"/>
<m:naryLim m:val="undOvr"/>
</m:mathPr></w:WordDocument>
</xml><![endif]--><br />
<!--[if gte mso 9]><xml>
<w:LatentStyles DefLockedState="false" DefUnhideWhenUsed="true"
DefSemiHidden="true" DefQFormat="false" DefPriority="99"
LatentStyleCount="267">
<w:LsdException Locked="false" Priority="0" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Normal"/>
<w:LsdException Locked="false" Priority="9" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="heading 1"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 2"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 3"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 4"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 5"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 6"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 7"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 8"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 9"/>
<w:LsdException Locked="false" Priority="39" Name="toc 1"/>
<w:LsdException Locked="false" Priority="39" Name="toc 2"/>
<w:LsdException Locked="false" Priority="39" Name="toc 3"/>
<w:LsdException Locked="false" Priority="39" Name="toc 4"/>
<w:LsdException Locked="false" Priority="39" Name="toc 5"/>
<w:LsdException Locked="false" Priority="39" Name="toc 6"/>
<w:LsdException Locked="false" Priority="39" Name="toc 7"/>
<w:LsdException Locked="false" Priority="39" Name="toc 8"/>
<w:LsdException Locked="false" Priority="39" Name="toc 9"/>
<w:LsdException Locked="false" Priority="35" QFormat="true" Name="caption"/>
<w:LsdException Locked="false" Priority="10" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Title"/>
<w:LsdException Locked="false" Priority="1" Name="Default Paragraph Font"/>
<w:LsdException Locked="false" Priority="11" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtitle"/>
<w:LsdException Locked="false" Priority="22" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Strong"/>
<w:LsdException Locked="false" Priority="20" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Emphasis"/>
<w:LsdException Locked="false" Priority="59" SemiHidden="false"
UnhideWhenUsed="false" Name="Table Grid"/>
<w:LsdException Locked="false" UnhideWhenUsed="false" Name="Placeholder Text"/>
<w:LsdException Locked="false" Priority="1" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="No Spacing"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 1"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 1"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 1"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 1"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 1"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 1"/>
<w:LsdException Locked="false" UnhideWhenUsed="false" Name="Revision"/>
<w:LsdException Locked="false" Priority="34" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="List Paragraph"/>
<w:LsdException Locked="false" Priority="29" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Quote"/>
<w:LsdException Locked="false" Priority="30" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Quote"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 1"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 1"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 1"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 1"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 1"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 1"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 1"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 1"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 2"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 2"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 2"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 2"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 2"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 2"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 2"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 2"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 2"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 2"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 2"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 2"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 2"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 2"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 3"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 3"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 3"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 3"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 3"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 3"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 3"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 3"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 3"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 3"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 3"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 3"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 3"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 3"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 4"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 4"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 4"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 4"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 4"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 4"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 4"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 4"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 4"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 4"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 4"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 4"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 4"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 4"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 5"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 5"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 5"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 5"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 5"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 5"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 5"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 5"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 5"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 5"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 5"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 5"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 5"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 5"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 6"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 6"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 6"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 6"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 6"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 6"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 6"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 6"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 6"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 6"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 6"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 6"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 6"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 6"/>
<w:LsdException Locked="false" Priority="19" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtle Emphasis"/>
<w:LsdException Locked="false" Priority="21" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Emphasis"/>
<w:LsdException Locked="false" Priority="31" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtle Reference"/>
<w:LsdException Locked="false" Priority="32" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Reference"/>
<w:LsdException Locked="false" Priority="33" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Book Title"/>
<w:LsdException Locked="false" Priority="37" Name="Bibliography"/>
<w:LsdException Locked="false" Priority="39" QFormat="true" Name="TOC Heading"/>
</w:LatentStyles>
</xml><![endif]--><!--[if gte mso 10]>
<style>
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-qformat:yes;
mso-style-parent:"";
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin-top:0in;
mso-para-margin-right:0in;
mso-para-margin-bottom:10.0pt;
mso-para-margin-left:0in;
line-height:115%;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-fareast-font-family:"Times New Roman";
mso-fareast-theme-font:minor-fareast;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:"Times New Roman";
mso-bidi-theme-font:minor-bidi;}
</style>
<![endif]-->
<br />
<div class="MsoNormal">
There is a big difference between writing a book and writing
a blog. I understand this when I have started writing a book “<a href="http://www.amazon.co.uk/dp/1849685746/?tag=packtpubli-21">SQL Server 2012
Performance Tuning Cookbook</a>”. </div>
<div class="MsoNormal">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-DIaEGpACoa4/TCxvRIAjPyI/AAAAAAAAAMo/IZKPaZPecpQ/s1600/3.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><br /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-q9Uaj56NPhQ/T5JNha-wC8I/AAAAAAAAAXo/0PEarIdjwuM/s1600/5740EN_MockupCover_Cookbook.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="200" src="http://2.bp.blogspot.com/-q9Uaj56NPhQ/T5JNha-wC8I/AAAAAAAAAXo/0PEarIdjwuM/s200/5740EN_MockupCover_Cookbook.jpg" width="161" /></a></div>
<div class="MsoNormal">
As soon as PACKT proposed me to write the book on this subject,
I have started deciding the topics to cover in this book with my co-author Bihag
Thaker. It was a big challenge to decide the content of this book because
performance tuning is a big topic and covering all aspects under the one book
is little difficult but finally we have managed the book in three following
parts.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
1.)<span style="mso-spacerun: yes;"> </span>Performance
Monitoring</div>
<div class="MsoNormal">
2.) Performance Tuning</div>
<div class="MsoNormal">
3.) Performance Management</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Each topic covered the practical recipe for hands-on
experience of the readers. <b style="mso-bidi-font-weight: normal;"><a href="http://authors.packtpub.com/content/interview-ritesh-shah-co-author-microsoft-sql-server-2012-performance-tuning-cookbook">PACKT</a></b>
has conducted my interview regarding my journey of this book writing process. Further
reading the interview, please <b style="mso-bidi-font-weight: normal;"><a href="http://authors.packtpub.com/content/interview-ritesh-shah-co-author-microsoft-sql-server-2012-performance-tuning-cookbook">click
here</a></b>.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<b><span style="color: #333333;">Reference: Ritesh Shah</span></b></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<a href="http://www.sqlhub.com/"><b style="mso-bidi-font-weight: normal;">http://www.sqlhub.com</b></a><b style="mso-bidi-font-weight: normal;"><span style="color: #333333;"><br />
Note: Microsoft Books online is a default reference of all articles but
examples and explanations prepared by Ritesh Shah, founder of </span></b><a href="http://www.sqlhub.com/"><b style="mso-bidi-font-weight: normal;">http://www.SQLHub.com</b></a></div>
<div class="MsoNormal">
<b style="mso-bidi-font-weight: normal;"><span style="color: red;">Ask me any SQL Server related question at my “<a href="http://beyondrelational.com/ask/ritesh/default.aspx">ASK Profile</a>”</span></b></div>
</div>
<div class="blogger-post-footer">Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah</div>Ritesh Shahhttp://www.blogger.com/profile/09562656548324468450noreply@blogger.com0tag:blogger.com,1999:blog-9075786158122089104.post-27400549523767992112012-07-28T23:01:00.002+05:302012-07-28T23:01:28.062+05:30Implementing physical Database Structure<div dir="ltr" style="text-align: left;" trbidi="on">
<!--[if gte mso 9]><xml>
<o:OfficeDocumentSettings>
<o:AllowPNG/>
</o:OfficeDocumentSettings>
</xml><![endif]--><!--[if gte mso 9]><xml>
<w:WordDocument>
<w:View>Normal</w:View>
<w:Zoom>0</w:Zoom>
<w:TrackMoves/>
<w:TrackFormatting/>
<w:PunctuationKerning/>
<w:ValidateAgainstSchemas/>
<w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid>
<w:IgnoreMixedContent>false</w:IgnoreMixedContent>
<w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText>
<w:DoNotPromoteQF/>
<w:LidThemeOther>EN-US</w:LidThemeOther>
<w:LidThemeAsian>X-NONE</w:LidThemeAsian>
<w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript>
<w:Compatibility>
<w:BreakWrappedTables/>
<w:SnapToGridInCell/>
<w:WrapTextWithPunct/>
<w:UseAsianBreakRules/>
<w:DontGrowAutofit/>
<w:SplitPgBreakAndParaMark/>
<w:EnableOpenTypeKerning/>
<w:DontFlipMirrorIndents/>
<w:OverrideTableStyleHps/>
</w:Compatibility>
<m:mathPr>
<m:mathFont m:val="Cambria Math"/>
<m:brkBin m:val="before"/>
<m:brkBinSub m:val="--"/>
<m:smallFrac m:val="off"/>
<m:dispDef/>
<m:lMargin m:val="0"/>
<m:rMargin m:val="0"/>
<m:defJc m:val="centerGroup"/>
<m:wrapIndent m:val="1440"/>
<m:intLim m:val="subSup"/>
<m:naryLim m:val="undOvr"/>
</m:mathPr></w:WordDocument>
</xml><![endif]--><!--[if gte mso 9]><xml>
<w:LatentStyles DefLockedState="false" DefUnhideWhenUsed="true"
DefSemiHidden="true" DefQFormat="false" DefPriority="99"
LatentStyleCount="267">
<w:LsdException Locked="false" Priority="0" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Normal"/>
<w:LsdException Locked="false" Priority="9" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="heading 1"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 2"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 3"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 4"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 5"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 6"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 7"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 8"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 9"/>
<w:LsdException Locked="false" Priority="39" Name="toc 1"/>
<w:LsdException Locked="false" Priority="39" Name="toc 2"/>
<w:LsdException Locked="false" Priority="39" Name="toc 3"/>
<w:LsdException Locked="false" Priority="39" Name="toc 4"/>
<w:LsdException Locked="false" Priority="39" Name="toc 5"/>
<w:LsdException Locked="false" Priority="39" Name="toc 6"/>
<w:LsdException Locked="false" Priority="39" Name="toc 7"/>
<w:LsdException Locked="false" Priority="39" Name="toc 8"/>
<w:LsdException Locked="false" Priority="39" Name="toc 9"/>
<w:LsdException Locked="false" Priority="35" QFormat="true" Name="caption"/>
<w:LsdException Locked="false" Priority="10" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Title"/>
<w:LsdException Locked="false" Priority="1" Name="Default Paragraph Font"/>
<w:LsdException Locked="false" Priority="11" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtitle"/>
<w:LsdException Locked="false" Priority="22" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Strong"/>
<w:LsdException Locked="false" Priority="20" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Emphasis"/>
<w:LsdException Locked="false" Priority="59" SemiHidden="false"
UnhideWhenUsed="false" Name="Table Grid"/>
<w:LsdException Locked="false" UnhideWhenUsed="false" Name="Placeholder Text"/>
<w:LsdException Locked="false" Priority="1" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="No Spacing"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 1"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 1"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 1"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 1"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 1"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 1"/>
<w:LsdException Locked="false" UnhideWhenUsed="false" Name="Revision"/>
<w:LsdException Locked="false" Priority="34" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="List Paragraph"/>
<w:LsdException Locked="false" Priority="29" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Quote"/>
<w:LsdException Locked="false" Priority="30" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Quote"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 1"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 1"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 1"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 1"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 1"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 1"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 1"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 1"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 2"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 2"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 2"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 2"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 2"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 2"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 2"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 2"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 2"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 2"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 2"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 2"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 2"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 2"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 3"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 3"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 3"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 3"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 3"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 3"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 3"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 3"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 3"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 3"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 3"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 3"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 3"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 3"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 4"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 4"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 4"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 4"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 4"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 4"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 4"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 4"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 4"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 4"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 4"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 4"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 4"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 4"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 5"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 5"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 5"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 5"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 5"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 5"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 5"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 5"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 5"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 5"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 5"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 5"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 5"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 5"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 6"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 6"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 6"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 6"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 6"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 6"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 6"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 6"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 6"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 6"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 6"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 6"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 6"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 6"/>
<w:LsdException Locked="false" Priority="19" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtle Emphasis"/>
<w:LsdException Locked="false" Priority="21" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Emphasis"/>
<w:LsdException Locked="false" Priority="31" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtle Reference"/>
<w:LsdException Locked="false" Priority="32" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Reference"/>
<w:LsdException Locked="false" Priority="33" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Book Title"/>
<w:LsdException Locked="false" Priority="37" Name="Bibliography"/>
<w:LsdException Locked="false" Priority="39" QFormat="true" Name="TOC Heading"/>
</w:LatentStyles>
</xml><![endif]--><!--[if gte mso 10]>
<style>
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-parent:"";
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin:0in;
mso-para-margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:10.0pt;
font-family:"Calibri","sans-serif";}
</style>
<![endif]-->
<br />
<div class="MsoNormal">
Our book “<b style="mso-bidi-font-weight: normal;"><a href="http://www.packtpub.com/microsoft-sql-server-2012-performance-tuning-cookbook/book">SQL
Server 2012 Performance Tuning cookbook</a></b>” is published this week. This
book is based on the concept of performance tuning in SQL Server 2012. We have
one chapter “<b style="mso-bidi-font-weight: normal;">Implementing physical
Database Structure</b>” in our book “<b style="mso-bidi-font-weight: normal;"><a href="http://www.packtpub.com/microsoft-sql-server-2012-performance-tuning-cookbook/book">SQL
Server 2012 Performance Tuning cookbook</a></b>”. Following article is
completely based on the chapter we have in our book “<b style="mso-bidi-font-weight: normal;"><a href="http://www.packtpub.com/microsoft-sql-server-2012-performance-tuning-cookbook/book">SQL
Server 2012 Performance Tuning cookbook</a></b>”.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Database structure is one of the important factor which
affects performance of your database. Your database performance heavily depends
on how you have physically placed your database objects and how you have
configured your disk subsystem. Designing the physical layout of your database
correctly is the key factor to improve the performance of your database queries
and thus the performance of your database. However, the correct decision on a
physical design structure of the database depends on the available hardware
resources that you might have. This includes the number of processors, RAM, and
storage resources, such as how many, disks or RAID controllers you might have
in your database environment. The best thing while designing physical layout of
the database is to have multiple physical disks for your database. If you
configure your database in such a way that it spreads across multiple disks, it
can benefit from parallel I/O operations.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
The following are some of the decisions that influence your
database performance:</div>
<ul type="disc">
<li class="MsoNormal" style="line-height: normal; mso-list: l0 level1 lfo1; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Where do you place data files? </span></li>
<li class="MsoNormal" style="line-height: normal; mso-list: l0 level1 lfo1; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Where do you place log files? </span></li>
<li class="MsoNormal" style="line-height: normal; mso-list: l0 level1 lfo1; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Where do you place large objects? </span></li>
<li class="MsoNormal" style="line-height: normal; mso-list: l0 level1 lfo1; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Where do you place indexes? </span></li>
<li class="MsoNormal" style="line-height: normal; mso-list: l0 level1 lfo1; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Where do you place the tempdb database? </span></li>
</ul>
<div class="MsoNormal">
You can control the physical location of database objects
within the database by using files and filegroups.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
To read complete article kindly <b style="mso-bidi-font-weight: normal;"><span style="font-size: 14.0pt; line-height: 115%; mso-bidi-font-size: 11.0pt;"><a href="http://www.packtpub.com/article/sql-server-2012-implementing-physical-database-strusture">click
here</a></span></b>.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<b><span style="color: #333333;">Reference: Ritesh Shah</span></b></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<a href="http://www.sqlhub.com/"><b style="mso-bidi-font-weight: normal;">http://www.sqlhub.com</b></a><b style="mso-bidi-font-weight: normal;"><span style="color: #333333;"><br />
Note: Microsoft Books online is a default reference of all articles but
examples and explanations prepared by Ritesh Shah, founder of </span></b><a href="http://www.sqlhub.com/"><b style="mso-bidi-font-weight: normal;">http://www.SQLHub.com</b></a></div>
<div class="MsoNormal">
<b style="mso-bidi-font-weight: normal;"><span style="color: red;">Ask me any SQL Server related question at my “<a href="http://beyondrelational.com/ask/ritesh/default.aspx">ASK Profile</a>”</span></b></div>
</div><div class="blogger-post-footer">Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah</div>Ritesh Shahhttp://www.blogger.com/profile/09562656548324468450noreply@blogger.com0tag:blogger.com,1999:blog-9075786158122089104.post-74890089317976611792012-07-27T07:07:00.003+05:302012-07-29T14:07:49.253+05:30350 Article on the blog and SQL Server 2012 book release<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<div class="MsoNormal">
I have completed 350 articles on this blog and apart from
that my book “SQL Server 2012 Performance Tuning Cookbook” has released
yesterday officially.</div>
<div class="MsoNormal">
</div>
<div class="MsoNormal">
<br />
I would like express my deepest gratitude to all my blog
readers and online SQL Server community which always inspire me to work hard and
returning back to the community.</div>
<div class="MsoNormal">
</div>
<div class="MsoNormal">
<br />
In today’s blogpost I am not going to discuss any technical stuff
but I would like express my truthful thanks to few person who has always played
an important role in my life. Though I have expressed my thankfulness to them in the “Acknowledgement”
section of my book, I can’t stop my temptation to express my gratitude blogpost.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<a href="http://www.packtpub.com/microsoft-sql-server-2012-performance-tuning-cookbook/book0EN_MockupCover_Cookbook.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;" target="_blank"><img border="0" src="http://4.bp.blogspot.com/-kY7hKzYosyg/UBF8zsajfQI/AAAAAAAAA1o/hm0RBCci7I8/s1600/5740EN_MockupCover_Cookbook.jpg" /></a> I would sincerely like to thank Packt Publishing, for
showing their confidence in me and providing the invaluable opportunity of
being a part of this book. Individuals at Packt whom I am deeply grateful to,
are Kedar Bhat, Sai Gamare, Madhuri Das, Ashwin Shetty, Apoorva Bolar, and
Dhwani Devater. They have been very co-operative and supportive at all the
stages of this book. I am extremely thankful to Michael Zilberstein (<a href="http://sqlblog.com/blogs/michael_zilberstein/default.aspx">Blog</a>) and
Maria Zakourdaev (<a href="http://sqlblog.com/blogs/maria_zakourdaev/default.aspx" target="_blank">Blog</a>, <a href="https://twitter.com/mariazakourdaev">Twitter</a>) and Satya (<a href="http://sqlserver-qa.net/" target="_blank">Blog</a>,<a href="https://twitter.com/sqlmaster" target="_blank">Twitter</a>) the technical
reviewers, for their excellent work of getting the accuracy of the technical
details of the book in perfect shape.</div>
<div class="MsoNormal">
</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
I wouldn't have been able to author this book alone, so
thanks should go to Mr. Bihag Thaker, as well, as he agreed to co-author this
book with me and has worked even harder on it than I have myself.</div>
<div class="MsoNormal">
</div>
<div class="MsoNormal">
<br />
Two individuals to whom I am indebted and whose disciple I
have always been are Mr. Pinal Dave(<a href="http://blog.sqlauthority.com/">Blog</a>,
<a href="https://twitter.com/pinaldave/">Twitter</a>) and Mr. Jacob Sebastian (<a href="http://beyondrelational.com/modules/2/blogs/28/jacobs-blog.aspx?tab=posts&bs=43">Blog</a>,
<a href="https://twitter.com/jacobsebastian">Twitter</a>). I have learnt a lot
from them, and they are the reason I'm part of the IT community today.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
1.) <b style="mso-bidi-font-weight: normal;">Pinal Dave</b>,
who blogs at SQLAuthority.com and is an author of several SQL Server books.
Currently, he is working as a Technology Evangelist at Microsoft.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
2.) <b style="mso-bidi-font-weight: normal;">Jacob Sebastian</b>,
who blogs at BeyondRelational.com and is
a SQL Server MVP, book author, well-known speaker in SQL Server technology, and
much more.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Without my family support, a task such as writing a book
would not have been achievable. I would like to heartily thank my parents, Mr. Ashwinkumar
Shah and Mrs. Divya Shah. It is because of them that I exist, and I cherish
their blessings, which are always with me. I would also like to thank my
one-and-a-half-year-old son, Teerth, who used to often start crying at
midnight, because of which I would lose my sleep and, not being able to get it
back so started researching more on the subjects that helped me write this
book. Last but not least, I would like to thank my wife, Alka Shah!</div>
<div class="MsoNormal">
</div>
<div class="MsoNormal">
<br />
Finally I would appreciate help and support of all my
friends who has directly and indirectly helped me a lot.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;">
<b><span style="color: #333333; font-family: "Times New Roman","serif"; font-size: 12pt;">Reference: Ritesh Shah</span></b><span style="font-family: "Times New Roman","serif"; font-size: 12pt;"></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: "Times New Roman","serif"; font-size: 12pt;"><a href="http://www.sqlhub.com/"><b style="mso-bidi-font-weight: normal;">http://www.sqlhub.com</b></a><b style="mso-bidi-font-weight: normal;"><span style="color: #333333;"><br />
Note: Microsoft Books online is a default reference of all articles but
examples and explanations prepared by Ritesh Shah, founder of </span></b><a href="http://www.sqlhub.com/"><b style="mso-bidi-font-weight: normal;">http://www.SQLHub.com</b></a></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;">
<b style="mso-bidi-font-weight: normal;"><span style="color: red; font-family: "Times New Roman","serif"; font-size: 12pt;">Ask me any SQL Server related question at my “<a href="http://beyondrelational.com/ask/ritesh/default.aspx">ASK Profile</a>”</span></b><span style="font-family: "Times New Roman","serif"; font-size: 12pt;"></span></div>
</div><div class="blogger-post-footer">Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah</div>Ritesh Shahhttp://www.blogger.com/profile/09562656548324468450noreply@blogger.com7tag:blogger.com,1999:blog-9075786158122089104.post-20729783371239912122012-07-25T07:40:00.000+05:302012-07-25T07:45:25.444+05:30Find SQL Server job ran on specific date with its status<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<div class="MsoNormal">
It is very mandatory for SQL Server DBA to keep an eye on
the SQL Server Job and take an immediate action if job gets failed. There are
several ways of doing this. I will be showing one query which will return all
jobs ran on the specific date along with its status like whether it was failed
or succeed.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
In order to gain information regarding JOBs, we have to
query following list of system tables in MSDB database because all SQL Server
jobs are stored in MSDB database only.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoListParagraphCxSpFirst" style="margin-left: .75in; mso-add-space: auto; mso-list: l0 level1 lfo1; text-indent: -.25in;">
1.)<span style="font: 7pt "Times New Roman";">
</span> <span style="font-family: "Courier New"; font-size: 10pt; line-height: 115%;">Sysjobhistory</span></div>
<div class="MsoListParagraphCxSpMiddle" style="margin-left: .75in; mso-add-space: auto; mso-list: l0 level1 lfo1; text-indent: -.25in;">
2.)<span style="font: 7pt "Times New Roman";">
</span><span style="font-family: "Courier New"; font-size: 10pt; line-height: 115%;">Sysjobs</span></div>
<div class="MsoListParagraphCxSpLast" style="margin-left: .75in; mso-add-space: auto; mso-list: l0 level1 lfo1; text-indent: -.25in;">
3.)<span style="font: 7pt "Times New Roman";">
</span><span style="font-family: "Courier New"; font-size: 10pt; line-height: 115%;">sysjobsteps</span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Here is the query which will return the desired result:</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt;">USE</span><span style="font-family: "Courier New"; font-size: 10pt;"> MSDB</span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt;">GO</span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt;">DECLARE</span><span style="font-family: "Courier New"; font-size: 10pt;"> @Today <span style="color: blue;">AS</span> <span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span>8<span style="color: grey;">)</span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt;">SET</span><span style="font-family: "Courier New"; font-size: 10pt;"> @Today <span style="color: grey;">=</span> <span style="color: magenta;">CONVERT</span><span style="color: grey;">(</span><span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span>8<span style="color: grey;">),</span><span style="color: magenta;">GETDATE</span><span style="color: grey;">(),</span>112<span style="color: grey;">)</span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt;">SELECT</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: grey;">*</span> <span style="color: blue;">FROM </span><span style="color: grey;">(</span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: blue;">SELECT</span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: grey;"><br /></span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10pt;"> SysJob<span style="color: grey;">.</span>name<span style="color: grey;">,</span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: blue;">CASE</span> <span style="color: blue;">WHEN</span>
</span><span style="font-family: "Courier New"; font-size: 10pt;">JobHist<span style="color: grey;">.</span></span><span style="font-family: "Courier New"; font-size: 10pt;">run_status <span style="color: grey;">=</span>1</span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: blue;">THEN</span> <span style="color: red;">'Success'</span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: blue;">WHEN</span> </span><span style="font-family: "Courier New"; font-size: 10pt;">JobHist<span style="color: grey;">.</span></span><span style="font-family: "Courier New"; font-size: 10pt;">run_status <span style="color: grey;">=</span>0</span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: blue;">THEN</span> <span style="color: red;">'Fail'</span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: blue;">END</span> <span style="color: blue;">AS</span> JobRunStatus<span style="color: grey;">,</span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10pt;"> JobHist<span style="color: grey;">.</span>run_date<span style="color: grey;">,</span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10pt;"> JobHist<span style="color: grey;">.</span>run_time<span style="color: grey;">,</span> </span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10pt;"> JobHist<span style="color: grey;">.</span>run_duration <span style="color: blue;">AS</span> RunDuration<span style="color: grey;">,</span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10pt;"> JobStep<span style="color: grey;">.</span>step_id<span style="color: grey;">,</span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10pt;"> JobStep<span style="color: grey;">.</span>command,</span><span style="font-family: "Courier New"; font-size: 10pt;"><span style="color: magenta;">ROW_NUMBER</span><span style="color: grey;">()</span> <span style="color: blue;">OVER</span><span style="color: grey;">(</span><span style="color: blue;">PARTITION</span> <span style="color: blue;">BY</span> SysJob<span style="color: grey;">.</span>name<span style="color: grey;">,</span>JobStep<span style="color: grey;">.</span>step_id <span style="color: blue;">ORDER</span> <span style="color: blue;">BY</span> run_time <span style="color: blue;">DESC</span><span style="color: grey;">)</span> <span style="color: blue;">AS</span> NumberOfExecution</span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: blue;">FROM</span> </span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10pt;"> dbo<span style="color: grey;">.</span>sysjobhistory <span style="color: blue;">AS</span> jobHist</span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: grey;">INNER</span> <span style="color: grey;">JOIN</span>
</span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10pt;"> dbo<span style="color: grey;">.</span>sysjobs <span style="color: blue;">AS</span> SysJob </span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: blue;">ON</span> </span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10pt;"> JobHist<span style="color: grey;">.</span>job_id <span style="color: grey;">=</span> SysJob<span style="color: grey;">.</span>job_id</span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: grey;">INNER</span> <span style="color: grey;">JOIN</span>
</span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10pt;"> dbo<span style="color: grey;">.</span>sysjobsteps <span style="color: blue;">AS</span> JobStep</span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: blue;">ON</span> </span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt;"> </span><span style="color: grey; font-family: "Courier New"; font-size: 10pt;">(</span><span style="font-family: "Courier New"; font-size: 10pt;">JobStep<span style="color: grey;">.</span>job_id <span style="color: grey;">=</span> SysJob<span style="color: grey;">.</span>job_id<span style="color: grey;">)</span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: blue;">WHERE</span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10pt;"> JobHist<span style="color: grey;">.</span>run_date <span style="color: grey;">=</span> @Today</span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: grey; font-family: "Courier New"; font-size: 10pt;">)</span><span style="font-family: "Courier New"; font-size: 10pt;"> </span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt;">AS</span><span style="font-family: "Courier New"; font-size: 10pt;"> T </span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt;">WHERE</span><span style="font-family: "Courier New"; font-size: 10pt;"> </span></div>
<div class="MsoNormal">
<span style="font-family: "Courier New"; font-size: 10pt; line-height: 115%;"> NumberOfExecution<span style="color: grey;">=</span>1</span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
As a DBA, I keep this script handy as I never know when I
will need this. We already have monitoring over each and every jobs and failure
of any jobs will be notified to me via email though this has become time saving
for me so many times.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
One tip I would like to give especially when I am talking
about JOB is, keep regular backup policy for MSDB database too as all the JOBs
are being stored here and while restoring SQL Server from any disaster, we need
to create all the JOBs again and MSDB database backup become life savior in
that case.</div>
<div class="MsoNormal">
I have explained the importance of backup in one of my past
article, if you are interested to read<b style="mso-bidi-font-weight: normal;">, <a href="http://www.sqlhub.com/2011/08/do-i-need-backup-of-my-database.html">click
here</a></b>.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Happy Scripting!!!!</div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;">
<b><span style="color: #333333; font-family: "Times New Roman","serif"; font-size: 12pt;">Reference: Ritesh Shah</span></b><span style="font-family: "Times New Roman","serif"; font-size: 12pt;"></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: "Times New Roman","serif"; font-size: 12pt;"><a href="http://www.sqlhub.com/"><b style="mso-bidi-font-weight: normal;">http://www.sqlhub.com</b></a><b style="mso-bidi-font-weight: normal;"><span style="color: #333333;"><br />
Note: Microsoft Books online is a default reference of all articles but
examples and explanations prepared by Ritesh Shah, founder of </span></b><a href="http://www.sqlhub.com/"><b style="mso-bidi-font-weight: normal;">http://www.SQLHub.com</b></a></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;">
<b style="mso-bidi-font-weight: normal;"><span style="color: red; font-family: "Times New Roman","serif"; font-size: 12pt;">Ask me any SQL Server related question at my “<a href="http://beyondrelational.com/ask/ritesh/default.aspx">ASK Profile</a>”</span></b><span style="font-family: "Times New Roman","serif"; font-size: 12pt;"></span></div>
</div><div class="blogger-post-footer">Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah</div>Ritesh Shahhttp://www.blogger.com/profile/09562656548324468450noreply@blogger.com0tag:blogger.com,1999:blog-9075786158122089104.post-44637285187131349862012-07-18T08:36:00.004+05:302012-07-18T08:36:47.229+05:30TableDiff utility to compare data between two databases<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 2;">
<b><span lang="EN-GB" style="font-family: 'Times New Roman', serif; font-size: 18pt;">Introduction</span></b></div>
<div class="MsoNormal" style="line-height: normal;">
<br /></div>
<div class="MsoNormal">
As a database professional, we might come across in
situation where we need to compare data row by row or column wise between two
tables which either resides in same database or in same instance or may be in
different instance in different server. </div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
What do you in this situation?</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
1.) Do you write down script of your own?</div>
<div class="MsoNormal">
2.) Do you use any third party software?</div>
<div class="MsoNormal">
3.) Do you use “TableDiff” utility comes with SQL Server
itself?</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Third option, out of all of the above, seems good to me as
we neither need to invent the zero again by writing down the script by our own nor
we need to pay extra money to compare data.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
“TableDiff” is one of the wonderful and oldest utility
provided by Microsoft. It works fine with SQL Server 2000 to the latest SQL
Server edition. However, I am providing you the script and example from my SQL
Server 2008 instance.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<b><span lang="EN-GB" style="font-family: 'Times New Roman', serif; font-size: 18pt;">Getting Ready</span></b> </div>
<h2>
<span lang="EN-GB"></span></h2>
<div class="MsoNormal">
Before you move forward, you need to find out two tables
whose data you wanted to compare. It might be in publisher/subscriber in
replication, it might be in two different databases you are using for scale out
or may be anywhere else.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
If you don’t have this situation at the moment in your
environment, don’t worry, I will be giving a script to raise the scenario to
test “TableDiff” utility.</div>
<h2>
<span lang="EN-GB"><br /></span></h2>
<h2>
<b><span lang="EN-GB" style="font-family: 'Times New Roman', serif; font-size: 18pt;">How to do it...</span></b></h2>
<h2>
<span lang="EN-GB"></span></h2>
<h2>
</h2>
<div class="MsoListParagraphCxSpFirst" style="margin-left: .75in; mso-add-space: auto; mso-list: l0 level1 lfo1; text-indent: -.25in;">
1.)<span style="font: normal normal normal 7pt/normal 'Times New Roman';">
</span>Open New Query window in you SQL Server</div>
<div class="MsoListParagraphCxSpFirst" style="margin-left: 0.75in; text-indent: -0.25in;">
<br /></div>
<div class="MsoListParagraphCxSpLast" style="margin-left: .75in; mso-add-space: auto; mso-list: l0 level1 lfo1; text-indent: -.25in;">
2.)<span style="font: normal normal normal 7pt/normal 'Times New Roman';">
</span>Create two different database by using following
script:</div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">USE</span><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">master</span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">GO</span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">CREATE</span><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">DATABASE</span>
TableDiffDb1</span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">GO</span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">CREATE</span><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">DATABASE</span>
TableDiffDb2</span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">GO</span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;">
<br /></div>
<div class="MsoListParagraphCxSpFirst" style="margin-left: .75in; mso-add-space: auto;">
<br /></div>
<div class="MsoListParagraphCxSpLast" style="margin-left: .75in; mso-add-space: auto; mso-list: l0 level1 lfo1; text-indent: -.25in;">
3.)<span style="font: normal normal normal 7pt/normal 'Times New Roman';">
</span>Create a sample table in “TableDiffDB1” database
with following script</div>
<div class="MsoListParagraphCxSpLast" style="margin-left: 0.75in; text-indent: -0.25in;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .25in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">USE</span><span style="font-family: 'Courier New'; font-size: 10pt;">
TableDiffDb1</span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .25in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">GO</span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .25in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .25in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: green; font-family: 'Courier New'; font-size: 10pt;">--if orders table is already there. you can
delete it than create new one with name "Orders"</span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .25in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">IF</span><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: magenta;">OBJECT_ID</span><span style="color: grey;">(</span><span style="color: red;">'orders'</span><span style="color: grey;">,</span> <span style="color: red;">'U'</span><span style="color: grey;">)</span> <span style="color: grey;">IS</span> <span style="color: grey;">NOT</span> <span style="color: grey;">NULL</span> <span style="color: blue;">BEGIN</span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .25in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">DROP</span> <span style="color: blue;">TABLE</span> orders</span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .25in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">END</span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .25in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">GO</span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .25in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .25in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: green; font-family: 'Courier New'; font-size: 10pt;">--creating table</span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .25in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">CREATE</span><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">TABLE</span>
orders<span style="color: blue;"> </span><span style="color: grey;">(</span>OrderID
<span style="color: blue;">INT</span> <span style="color: blue;">IDENTITY</span><span style="color: grey;">,</span> OrderDate <span style="color: blue;">DATETIME</span><span style="color: grey;">,</span> Amount <span style="color: blue;">MONEY</span><span style="color: grey;">,</span> Refno <span style="color: blue;">INT</span><span style="color: grey;">)</span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .25in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">GO</span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .25in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .25in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: green; font-family: 'Courier New'; font-size: 10pt;">--inserting 1000 sample rows into table</span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .25in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">INSERT</span><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">INTO</span>
orders<span style="color: blue;"> </span><span style="color: grey;">(</span>OrderDate<span style="color: grey;">,</span> Amount<span style="color: grey;">,</span> Refno<span style="color: grey;">)</span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .25in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">SELECT</span><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">TOP</span>
1000</span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .25in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: magenta;">DATEADD</span><span style="color: grey;">(</span><span style="color: blue;">minute</span><span style="color: grey;">,</span> <span style="color: magenta;">ABS</span><span style="color: grey;">(</span>a<span style="color: grey;">.</span><span style="color: magenta;">object_id</span> <span style="color: grey;">%</span> 50000 <span style="color: grey;">),</span> <span style="color: magenta;">CAST</span><span style="color: grey;">(</span><span style="color: red;">'2010-02-01'</span> <span style="color: blue;">AS</span> <span style="color: blue;">DATETIME</span><span style="color: grey;">)),</span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .25in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: magenta;">ABS</span><span style="color: grey;">(</span>a<span style="color: grey;">.</span><span style="color: magenta;">object_id</span> <span style="color: grey;">%</span> 10<span style="color: grey;">),</span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .25in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: magenta;">CAST</span><span style="color: grey;">(</span><span style="color: magenta;">ABS</span><span style="color: grey;">(</span>a<span style="color: grey;">.</span><span style="color: magenta;">object_id</span><span style="color: grey;">)</span> <span style="color: blue;">AS</span> <span style="color: blue;">VARCHAR</span><span style="color: grey;">)</span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .25in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">FROM</span><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: green;">sys</span><span style="color: grey;">.</span><span style="color: green;">all_objects</span> a</span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .25in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: grey; font-family: 'Courier New'; font-size: 10pt;">CROSS</span><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: grey;">JOIN</span>
<span style="color: green;">sys</span><span style="color: grey;">.</span><span style="color: green;">all_objects</span> b</span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .25in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">GO</span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .25in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;">
<br /></div>
<div class="MsoListParagraph" style="margin-left: .75in; mso-add-space: auto; mso-list: l0 level1 lfo1; text-indent: -.25in;">
4.)<span style="font: normal normal normal 7pt/normal 'Times New Roman';">
</span>Creating “Orders” table in second database by
copying 900 records (out of total 1000 records) from “Orders” table from “TableDiffDB1”
database by using following script.</div>
<div class="MsoListParagraph" style="margin-left: 0.75in; text-indent: -0.25in;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .25in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">USE</span><span style="font-family: 'Courier New'; font-size: 10pt;"> TableDiffDb2</span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .25in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">GO</span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .25in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .25in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: green; font-family: 'Courier New'; font-size: 10pt;">--if orders table is already there. you can
delete it than create new one with name "Orders"</span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .25in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">IF</span><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: magenta;">OBJECT_ID</span><span style="color: grey;">(</span><span style="color: red;">'orders'</span><span style="color: grey;">,</span> <span style="color: red;">'U'</span><span style="color: grey;">)</span> <span style="color: grey;">IS</span> <span style="color: grey;">NOT</span> <span style="color: grey;">NULL</span> <span style="color: blue;">BEGIN</span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .25in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">DROP</span> <span style="color: blue;">TABLE</span> orders</span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .25in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">END</span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .25in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">GO</span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .25in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .25in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: green; font-family: 'Courier New'; font-size: 10pt;">--creating table</span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .25in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">CREATE</span><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">TABLE</span>
orders<span style="color: blue;"> </span><span style="color: grey;">(</span>OrderID
<span style="color: blue;">INT</span> <span style="color: blue;">IDENTITY</span><span style="color: grey;">,</span> OrderDate <span style="color: blue;">DATETIME</span><span style="color: grey;">,</span> Amount <span style="color: blue;">MONEY</span><span style="color: grey;">,</span> Refno <span style="color: blue;">INT</span><span style="color: grey;">)</span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .25in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">GO</span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .25in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .25in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: green; font-family: 'Courier New'; font-size: 10pt;">--inserting 900 sample rows into table from
TableDiffDb1 database's Orders table</span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .25in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">INSERT</span><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">INTO</span>
orders<span style="color: blue;"> </span><span style="color: grey;">(</span>OrderDate<span style="color: grey;">,</span> Amount<span style="color: grey;">,</span> Refno<span style="color: grey;">)</span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .25in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">SELECT</span><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">TOP</span>
900 OrderDate<span style="color: grey;">,</span>Amount<span style="color: grey;">,</span>Refno
<span style="color: blue;">FROM</span> TableDiffDb1<span style="color: grey;">.</span>dbo<span style="color: grey;">.</span>orders</span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .25in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;">
<br /></div>
<div class="MsoListParagraph" style="margin-left: .75in; mso-add-space: auto; mso-list: l0 level1 lfo1; text-indent: -.25in;">
5.)<span style="font: normal normal normal 7pt/normal 'Times New Roman';">
</span>Now use following command to see the difference
between two tables.</div>
<div class="MsoNormal" style="margin-left: .5in;">
<br /></div>
<div class="MsoNormal" style="margin-left: 0.5in;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt; line-height: 115%;">exec</span><span style="font-family: 'Courier New'; font-size: 10pt; line-height: 115%;"> <span style="color: blue;">master</span><span style="color: grey;">..</span><span style="color: maroon;">xp_cmdshell</span><span style="color: blue;"> </span><span style="color: red;">'tablediff -sourceserver [RITESH-SHAH\MSSQL2008]
-sourcedatabase TableDiffDb1 -sourcetable Orders -destinationserver
[RITESH-SHAH\MSSQL2008] -destinationdatabase TableDiffDb2 -destinationtable
Orders -et Difference -f D:\OrdersDifference.sql'</span></span></div>
<div class="MsoNormal" style="margin-left: 0.5in;">
<br /></div>
<span style="font-family: Calibri, sans-serif; font-size: 11pt;">Replace your server instance name in “<b style="mso-bidi-font-weight: normal;">SourceServer</b>” and “<b style="mso-bidi-font-weight: normal;">destinationServer</b>” parameter in above given command and you will
get one .SQL file in D drive. Running that SQL file will insert all missing
records in “Orders” table of “TableDiffDb2” database as it shows you the list
of all missing records there.</span><br />
<h2>
<span lang="EN-GB"></span></h2>
<h2>
<b><span lang="EN-GB" style="font-family: 'Times New Roman', serif; font-size: 18pt;">There's more...</span></b></h2>
<div class="MsoNormal">
I would like to draw your attention to some of the facts
which can help you if you don’t find “TableDiff” working in your environment.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Remember that “TableDiff.exe” file resides in installation directory of SQL
Server by default which is “C:\Program Files\Microsoft SQL Server\100\COM” in
my case. So, there is chance that “TableDiff”
command is not accessible via DOS prompt, you have to set path for “TableDiff”
in “ServerVariable”. </div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
You can reach “ServerVariable” by “<b style="mso-bidi-font-weight: normal;">MY Computer Properties > Advanced System Settings > Advanced >
Environment Variables > System Variables > PATH</b>” </div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<b>If you find any path
under “PATH” in “ServerVariable”, you can put “;” (semicolon) after that path
and can add your path for “TableDiff”</b>.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Generally people tend to use “TableDiff” from DOS prompt
itself or via .bat (batch file) file but I have used “<b style="mso-bidi-font-weight: normal;">xp_cmdshell</b>” extended stored procedure to show the use of command
right from SQL Server but there may be a chance that “<b style="mso-bidi-font-weight: normal;">xp_cmdshell</b>” <b style="mso-bidi-font-weight: normal;">is disable in
your environment</b>. If your security constraint allows, you can enable “xp_cmdshell”.
For more details about the steps,<b style="mso-bidi-font-weight: normal;"> <a href="http://www.sqlhub.com/2009/05/enable-xpcmdshell-in-sql-server-2008.html">click
here</a></b>.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<b><span style="color: #333333;">Reference: Ritesh Shah</span></b></div>
<div class="MsoNormal">
<a href="http://www.sqlhub.com/"><b style="mso-bidi-font-weight: normal;">http://www.sqlhub.com</b></a><b style="mso-bidi-font-weight: normal;"><span style="color: #333333;"><br />
</span></b></div>
<div class="MsoNormal">
<b><span style="color: #333333;">Note: Microsoft Books online
is a default reference of all articles but examples and explanations prepared
by Ritesh Shah, founder of </span></b><a href="http://www.sqlhub.com/"><b style="mso-bidi-font-weight: normal;">http://www.SQLHub.com</b></a></div>
<div class="MsoNormal">
<b><span style="color: red;">Ask me any SQL Server related
question at my “</span></b><a href="http://beyondrelational.com/ask/ritesh/default.aspx"><b style="mso-bidi-font-weight: normal;">ASK Profile</b></a><b style="mso-bidi-font-weight: normal;"><span style="color: red;">”</span></b></div>
</div>
<div class="blogger-post-footer">Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah</div>Ritesh Shahhttp://www.blogger.com/profile/09562656548324468450noreply@blogger.com0tag:blogger.com,1999:blog-9075786158122089104.post-26281941681380497492012-06-06T14:59:00.002+05:302012-06-06T14:59:37.422+05:30Training – useful or useless?<div dir="ltr" style="text-align: left;" trbidi="on">
<!--[if gte mso 9]><xml>
<w:WordDocument>
<w:View>Normal</w:View>
<w:Zoom>0</w:Zoom>
<w:TrackMoves/>
<w:TrackFormatting/>
<w:PunctuationKerning/>
<w:ValidateAgainstSchemas/>
<w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid>
<w:IgnoreMixedContent>false</w:IgnoreMixedContent>
<w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText>
<w:DoNotPromoteQF/>
<w:LidThemeOther>EN-US</w:LidThemeOther>
<w:LidThemeAsian>X-NONE</w:LidThemeAsian>
<w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript>
<w:Compatibility>
<w:BreakWrappedTables/>
<w:SnapToGridInCell/>
<w:WrapTextWithPunct/>
<w:UseAsianBreakRules/>
<w:DontGrowAutofit/>
<w:SplitPgBreakAndParaMark/>
<w:DontVertAlignCellWithSp/>
<w:DontBreakConstrainedForcedTables/>
<w:DontVertAlignInTxbx/>
<w:Word11KerningPairs/>
<w:CachedColBalance/>
</w:Compatibility>
<w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel>
<m:mathPr>
<m:mathFont m:val="Cambria Math"/>
<m:brkBin m:val="before"/>
<m:brkBinSub m:val="--"/>
<m:smallFrac m:val="off"/>
<m:dispDef/>
<m:lMargin m:val="0"/>
<m:rMargin m:val="0"/>
<m:defJc m:val="centerGroup"/>
<m:wrapIndent m:val="1440"/>
<m:intLim m:val="subSup"/>
<m:naryLim m:val="undOvr"/>
</m:mathPr></w:WordDocument>
</xml><![endif]--><!--[if gte mso 9]><xml>
<w:LatentStyles DefLockedState="false" DefUnhideWhenUsed="true"
DefSemiHidden="true" DefQFormat="false" DefPriority="99"
LatentStyleCount="267">
<w:LsdException Locked="false" Priority="0" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Normal"/>
<w:LsdException Locked="false" Priority="9" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="heading 1"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 2"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 3"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 4"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 5"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 6"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 7"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 8"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 9"/>
<w:LsdException Locked="false" Priority="39" Name="toc 1"/>
<w:LsdException Locked="false" Priority="39" Name="toc 2"/>
<w:LsdException Locked="false" Priority="39" Name="toc 3"/>
<w:LsdException Locked="false" Priority="39" Name="toc 4"/>
<w:LsdException Locked="false" Priority="39" Name="toc 5"/>
<w:LsdException Locked="false" Priority="39" Name="toc 6"/>
<w:LsdException Locked="false" Priority="39" Name="toc 7"/>
<w:LsdException Locked="false" Priority="39" Name="toc 8"/>
<w:LsdException Locked="false" Priority="39" Name="toc 9"/>
<w:LsdException Locked="false" Priority="35" QFormat="true" Name="caption"/>
<w:LsdException Locked="false" Priority="10" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Title"/>
<w:LsdException Locked="false" Priority="1" Name="Default Paragraph Font"/>
<w:LsdException Locked="false" Priority="11" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtitle"/>
<w:LsdException Locked="false" Priority="22" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Strong"/>
<w:LsdException Locked="false" Priority="20" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Emphasis"/>
<w:LsdException Locked="false" Priority="59" SemiHidden="false"
UnhideWhenUsed="false" Name="Table Grid"/>
<w:LsdException Locked="false" UnhideWhenUsed="false" Name="Placeholder Text"/>
<w:LsdException Locked="false" Priority="1" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="No Spacing"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 1"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 1"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 1"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 1"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 1"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 1"/>
<w:LsdException Locked="false" UnhideWhenUsed="false" Name="Revision"/>
<w:LsdException Locked="false" Priority="34" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="List Paragraph"/>
<w:LsdException Locked="false" Priority="29" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Quote"/>
<w:LsdException Locked="false" Priority="30" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Quote"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 1"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 1"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 1"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 1"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 1"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 1"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 1"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 1"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 2"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 2"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 2"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 2"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 2"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 2"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 2"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 2"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 2"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 2"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 2"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 2"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 2"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 2"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 3"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 3"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 3"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 3"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 3"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 3"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 3"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 3"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 3"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 3"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 3"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 3"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 3"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 3"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 4"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 4"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 4"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 4"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 4"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 4"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 4"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 4"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 4"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 4"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 4"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 4"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 4"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 4"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 5"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 5"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 5"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 5"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 5"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 5"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 5"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 5"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 5"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 5"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 5"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 5"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 5"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 5"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 6"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 6"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 6"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 6"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 6"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 6"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 6"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 6"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 6"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 6"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 6"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 6"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 6"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 6"/>
<w:LsdException Locked="false" Priority="19" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtle Emphasis"/>
<w:LsdException Locked="false" Priority="21" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Emphasis"/>
<w:LsdException Locked="false" Priority="31" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtle Reference"/>
<w:LsdException Locked="false" Priority="32" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Reference"/>
<w:LsdException Locked="false" Priority="33" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Book Title"/>
<w:LsdException Locked="false" Priority="37" Name="Bibliography"/>
<w:LsdException Locked="false" Priority="39" QFormat="true" Name="TOC Heading"/>
</w:LatentStyles>
</xml><![endif]--><!--[if gte mso 10]>
<style>
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-qformat:yes;
mso-style-parent:"";
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin-top:0in;
mso-para-margin-right:0in;
mso-para-margin-bottom:10.0pt;
mso-para-margin-left:0in;
line-height:115%;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-fareast-font-family:"Times New Roman";
mso-fareast-theme-font:minor-fareast;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:"Times New Roman";
mso-bidi-theme-font:minor-bidi;}
</style>
<![endif]-->
<br />
<div class="MsoNormal">
My very good friends and SQL Server icon Mr. Pinal Dave
wrote <a href="http://blog.sqlauthority.com/2012/05/28/developer-training-importance-and-significance-part-1/">5
articles</a> on this wonderful topic “Training”. I often see debate among developer
whether training is useful or useless but according to my personal opinion,
Training plays very important role in the life of professional so let us not
discussed whether it is really useful or useless as Pinal has already had so
many details in his 5 articles and I am sure it will change your attitude
towards training if you are not so much positive towards training.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Training enhances skill level of professional. Though a
benefit of training is intangible but investing in training benefits both, employee
as well as organization.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
In today’s age there is rapid technology innovations comes
into the market and making the employee trained for those innovations
ultimately benefits company as well as employee both in order to stand in
market with cut edge technology.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Learning & Training are the processes, any human follows
lifetime. <span style="mso-spacerun: yes;"> </span>At age of toddler, we are
being trained from family regarding how to eat, how to walk, how to talk etc.
In the kindergarten we are having social training for our transition from home
to school then schooling and college and so on……….</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
In short, training is a never ending process in life and always try
to learn from each and every person you meet in life. Here is the list of 5 articles
written by Pinal Dave, I highly recommend reading it!!! believe me those are really
awesome articles:</div>
<div class="MsoNormal">
<br /></div>
<strong><a href="http://blog.sqlauthority.com/2012/05/28/developer-training-importance-and-significance-part-1/" target="_blank">Developer Training – Importance and Significance – Part 1</a></strong>
<br />
<strong><a href="http://blog.sqlauthority.com/2012/05/29/developer-training-employee-morals-and-ethics-part-2/" target="_blank">Developer Training – Employee Morals and Ethics – Part 2</a></strong>
<br />
<strong><a href="http://blog.sqlauthority.com/2012/05/30/developer-training-difficult-questions-and-alternative-perspective-part-3/" target="_blank">Developer Training – Difficult Questions and Alternative
Perspective – Part 3</a></strong> <br />
<strong><a href="http://blog.sqlauthority.com/2012/05/31/developer-training-various-options-for-maximum-benefit-part-4/" target="_blank">Developer Training – Various Options for Developer Training –
Part 4</a></strong> <br />
<strong><a href="http://blog.sqlauthority.com/2012/06/01/developer-training-a-conclusive-summary-part-5/" target="_blank">Developer Training – A Conclusive Summary- Part 5</a></strong><br />
<div class="MsoNormal" style="line-height: 19.2pt;">
<br /></div>
<div class="MsoNormal" style="line-height: 19.2pt;">
<b><span style="color: #333333;">Reference:
Ritesh Shah</span></b></div>
<div class="MsoNormal" style="line-height: normal;">
<br /></div>
<div class="MsoNormal" style="line-height: normal;">
<span style="color: #333333;"><a href="http://www.sqlhub.com/"><b><span style="color: #5588aa; text-decoration: none;">http://www.sqlhub.com</span></b></a></span><b><span style="color: #333333; font-size: 10.0pt;"><br />
Note: Microsoft Books online is a default reference of all articles but
examples and explanations prepared by Ritesh Shah, founder of </span></b><span style="color: #333333;"><a href="http://www.sqlhub.com/"><b><span style="color: #5588aa; text-decoration: none; text-underline: none;">http://www.SQLHub.com</span></b></a></span></div>
</div><div class="blogger-post-footer">Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah</div>Ritesh Shahhttp://www.blogger.com/profile/09562656548324468450noreply@blogger.com0tag:blogger.com,1999:blog-9075786158122089104.post-18326261345206020122012-05-05T13:05:00.000+05:302012-05-05T13:12:02.115+05:30“Max degree of parallelism” or “MAXDOP”, good guy or bad guy?<div dir="ltr" style="text-align: left;" trbidi="on">
<style>
<!--
/* Font Definitions */
@font-face
{font-family:Wingdings;
panose-1:5 0 0 0 0 0 0 0 0 0;
mso-font-charset:2;
mso-generic-font-family:auto;
mso-font-pitch:variable;
mso-font-signature:0 268435456 0 0 -2147483648 0;}
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;
mso-font-charset:0;
mso-generic-font-family:roman;
mso-font-pitch:variable;
mso-font-signature:-1610611985 1107304683 0 0 159 0;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;
mso-font-charset:0;
mso-generic-font-family:swiss;
mso-font-pitch:variable;
mso-font-signature:-1610611985 1073750139 0 0 159 0;}
@font-face
{font-family:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;
mso-font-charset:0;
mso-generic-font-family:modern;
mso-font-pitch:fixed;
mso-font-signature:-1610611985 1073750091 0 0 159 0;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{mso-style-unhide:no;
mso-style-qformat:yes;
mso-style-parent:"";
margin-top:0cm;
margin-right:0cm;
margin-bottom:10.0pt;
margin-left:0cm;
line-height:115%;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-fareast-font-family:Calibri;
mso-bidi-font-family:"Times New Roman";
mso-fareast-language:EN-US;}
h1
{mso-style-name:"Heading 1\,Heading 1 \[PACKT\]";
mso-style-unhide:no;
mso-style-qformat:yes;
mso-style-parent:"";
mso-style-link:"Heading 1 Char";
mso-style-next:"Normal \[PACKT\]";
margin-top:20.0pt;
margin-right:0cm;
margin-bottom:3.0pt;
margin-left:0cm;
mso-pagination:widow-orphan;
page-break-after:avoid;
mso-outline-level:1;
background:#1F497D;
border:none;
mso-border-alt:solid windowtext .5pt;
padding:0cm;
mso-padding-alt:4.0pt 4.0pt 4.0pt 4.0pt;
font-size:16.0pt;
font-family:"Arial","sans-serif";
mso-fareast-font-family:"Times New Roman";
color:white;
mso-font-kerning:16.0pt;
mso-ansi-language:EN-GB;
mso-fareast-language:EN-US;
mso-bidi-font-weight:normal;
mso-bidi-font-style:italic;}
h2
{mso-style-name:"Heading 2\,Heading 2 \[PACKT\]";
mso-style-unhide:no;
mso-style-qformat:yes;
mso-style-parent:"";
mso-style-link:"Heading 2 Char";
mso-style-next:"Normal \[PACKT\]";
margin-top:16.0pt;
margin-right:0cm;
margin-bottom:3.0pt;
margin-left:0cm;
mso-pagination:widow-orphan;
page-break-after:avoid;
mso-outline-level:2;
font-size:14.0pt;
font-family:"Arial","sans-serif";
mso-fareast-font-family:"Times New Roman";
color:#333399;
mso-ansi-language:EN-GB;
mso-fareast-language:EN-US;
mso-bidi-font-style:italic;}
span.Heading1Char
{mso-style-name:"Heading 1 Char";
mso-style-unhide:no;
mso-style-locked:yes;
mso-style-link:"Heading 1\,Heading 1 \[PACKT\]";
mso-ansi-font-size:16.0pt;
mso-bidi-font-size:16.0pt;
font-family:"Arial","sans-serif";
mso-ascii-font-family:Arial;
mso-fareast-font-family:"Times New Roman";
mso-hansi-font-family:Arial;
mso-bidi-font-family:Arial;
color:white;
background:#1F497D;
mso-font-kerning:16.0pt;
mso-ansi-language:EN-GB;
mso-fareast-language:EN-US;
font-weight:bold;
mso-bidi-font-weight:normal;
mso-bidi-font-style:italic;}
span.Heading2Char
{mso-style-name:"Heading 2 Char";
mso-style-unhide:no;
mso-style-locked:yes;
mso-style-link:"Heading 2\,Heading 2 \[PACKT\]";
mso-ansi-font-size:14.0pt;
mso-bidi-font-size:14.0pt;
font-family:"Arial","sans-serif";
mso-ascii-font-family:Arial;
mso-fareast-font-family:"Times New Roman";
mso-hansi-font-family:Arial;
mso-bidi-font-family:Arial;
color:#333399;
mso-ansi-language:EN-GB;
mso-fareast-language:EN-US;
font-weight:bold;
mso-bidi-font-style:italic;}
p.ChapterTitlePACKT, li.ChapterTitlePACKT, div.ChapterTitlePACKT
{mso-style-name:"Chapter Title \[PACKT\]";
mso-style-unhide:no;
mso-style-parent:"";
mso-style-next:"Normal \[PACKT\]";
margin-top:0cm;
margin-right:0cm;
margin-bottom:42.0pt;
margin-left:0cm;
text-align:right;
mso-pagination:widow-orphan;
font-size:28.0pt;
mso-bidi-font-size:16.0pt;
font-family:"Arial","sans-serif";
mso-fareast-font-family:"Times New Roman";
color:black;
mso-font-kerning:16.0pt;
mso-ansi-language:EN-GB;
mso-fareast-language:EN-US;
mso-bidi-font-weight:bold;}
p.BulletPACKT, li.BulletPACKT, div.BulletPACKT
{mso-style-name:"Bullet \[PACKT\]";
mso-style-unhide:no;
mso-style-parent:"Normal \[PACKT\]";
margin-top:0cm;
margin-right:18.0pt;
margin-bottom:3.0pt;
margin-left:0cm;
text-indent:0cm;
mso-pagination:widow-orphan;
mso-list:l3 level1 lfo1;
mso-hyphenate:none;
tab-stops:18.0pt;
font-size:11.0pt;
mso-bidi-font-size:12.0pt;
font-family:"Calibri","sans-serif";
mso-fareast-font-family:"Times New Roman";
mso-bidi-font-family:"Times New Roman";
color:purple;
mso-ansi-language:EN-US;
mso-fareast-language:EN-US;}
p.NormalPACKT, li.NormalPACKT, div.NormalPACKT
{mso-style-name:"Normal \[PACKT\]";
mso-style-unhide:no;
mso-style-parent:"";
mso-style-link:"Normal \[PACKT\] Char";
margin-top:0cm;
margin-right:0cm;
margin-bottom:6.0pt;
margin-left:0cm;
mso-pagination:widow-orphan;
font-size:11.0pt;
mso-bidi-font-size:12.0pt;
font-family:"Calibri","sans-serif";
mso-fareast-font-family:"Times New Roman";
mso-bidi-font-family:"Times New Roman";
mso-ansi-language:EN-US;
mso-fareast-language:EN-US;}
span.NormalPACKTChar
{mso-style-name:"Normal \[PACKT\] Char";
mso-style-unhide:no;
mso-style-locked:yes;
mso-style-link:"Normal \[PACKT\]";
mso-ansi-font-size:11.0pt;
mso-bidi-font-size:12.0pt;
font-family:"Times New Roman","serif";
mso-fareast-font-family:"Times New Roman";
mso-ansi-language:EN-US;
mso-fareast-language:EN-US;}
span.SpellE
{mso-style-name:"";
mso-spl-e:yes;}
span.GramE
{mso-style-name:"";
mso-gram-e:yes;}
.MsoChpDefault
{mso-style-type:export-only;
mso-default-props:yes;
mso-ascii-font-family:Calibri;
mso-fareast-font-family:Calibri;
mso-hansi-font-family:Calibri;}
@page Section1
{size:595.3pt 841.9pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;
mso-header-margin:35.4pt;
mso-footer-margin:35.4pt;
mso-paper-source:0;}
div.Section1
{page:Section1;}
/* List Definitions */
@list l0
{mso-list-id:344863004;
mso-list-type:hybrid;
mso-list-template-ids:1577252908 1074331649 1074331651 1074331653 1074331649 1074331651 1074331653 1074331649 1074331651 1074331653;}
@list l0:level1
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:Symbol;}
@list l1
{mso-list-id:641040119;
mso-list-type:hybrid;
mso-list-template-ids:1990613932 1074331663 1074331673 1074331675 1074331663 1074331673 1074331675 1074331663 1074331673 1074331675;}
@list l1:level1
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l2
{mso-list-id:1501046828;
mso-list-type:hybrid;
mso-list-template-ids:-1856706300 1074331649 1074331651 1074331653 1074331649 1074331651 1074331653 1074331649 1074331651 1074331653;}
@list l2:level1
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:Symbol;}
@list l3
{mso-list-id:1724213397;
mso-list-type:hybrid;
mso-list-template-ids:493095766 236218882 67698691 67698693 67698689 67698691 67698693 67698689 67698691 67698693;}
@list l3:level1
{mso-level-number-format:bullet;
mso-level-style-link:"Bullet \[PACKT\]";
mso-level-text:\F0B7;
mso-level-tab-stop:36.0pt;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:Symbol;
color:windowtext;}
ol
{margin-bottom:0cm;}
ul
{margin-bottom:0cm;}
-->
</style>
<br />
<div class="Section1">
<h2>
<span lang="EN-GB">Introduction</span></h2>
<div class="MsoNormal">
<span lang="EN-GB">Yesterday I
have written article for <a href="http://www.sqlhub.com/2012/05/nolock-hint-in-sql-server-good-guy-or.html">NOLOCK
in good guy or bad guy</a> series. Today I am presenting one topic on the same
subject which is “max degree of parallelism”. This is also one of the topics
which can raise the big debate on. Now a days people used to have powerful
servers with multiple processor for processing their database request and they
tend to believe that more processor can perform well always to process the
query which is a myth but in reality more processor can performs well for some
query but perform really worst for others too so rather then setting up a fix
figure to process each query, I believe to leave the decision on SQL Server as
SQL Server 2012 has such a smart engine which can decide whether to go for
parallelism or not.<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB">As long as
my personal practise concern, I used to use this option at very last resort as
most of the query performance can be done via following:<o:p></o:p></span></div>
<ul style="margin-top: 0cm;" type="disc">
<li class="MsoNormal" style="mso-list: l0 level1 lfo3;"><span lang="EN-GB">Proper Index<o:p></o:p></span></li>
<li class="MsoNormal" style="mso-list: l0 level1 lfo3;"><span lang="EN-GB">Properly written query which can use index<o:p></o:p></span></li>
<li class="MsoNormal" style="mso-list: l0 level1 lfo3;"><span lang="EN-GB">Updated statistics<o:p></o:p></span></li>
</ul>
<h2>
<span lang="EN-GB">Getting ready</span></h2>
<div class="NormalPACKT">
<span lang="EN-GB">I am
going to perform this example in my SQL Server 2012 RTM version but it may work
as it is in SQL Server 2005 / 2008 too.<o:p></o:p></span></div>
<div class="NormalPACKT">
<span lang="EN-GB">Deciding the
processor for the query could be done via following two popular ways:<o:p></o:p></span></div>
<div class="NormalPACKT" style="margin-left: 36.0pt; mso-list: l2 level1 lfo4; text-indent: -18.0pt;">
<span lang="EN-GB" style="font-family: Symbol;">·<span style="font: 7pt "Times New Roman";">
</span></span><span lang="EN-GB">Set
server wide value with option “max degree of parallelism” via “<span class="SpellE">sp_configure</span>” stored procedure<o:p></o:p></span></div>
<div class="NormalPACKT" style="margin-left: 36.0pt; mso-list: l2 level1 lfo4; text-indent: -18.0pt;">
<span lang="EN-GB" style="font-family: Symbol;">·<span style="font: 7pt "Times New Roman";">
</span></span><span lang="EN-GB">Use
the MAXDOP hint<o:p></o:p></span></div>
<div class="NormalPACKT">
<span lang="EN-GB">In this
article I will show both ways in coming sections.<o:p></o:p></span></div>
<div class="NormalPACKT">
<span lang="EN-GB">We are
going to use “orders” table in this article, you can get the script to generate
that table from previous article, <a href="http://www.sqlhub.com/2012/05/nolock-hint-in-sql-server-good-guy-or.html">click
here</a>.<o:p></o:p></span></div>
<h2>
<span lang="EN-GB">How to do it...</span></h2>
<div class="NormalPACKT" style="margin-left: 36.0pt; mso-list: l1 level1 lfo2; text-indent: -18.0pt;">
<span lang="EN-GB">1.<span style="font: 7pt "Times New Roman";">
</span></span><span lang="EN-GB">After
connecting to SQL Server Management Studio (SSMS), open new query window.<o:p></o:p></span></div>
<div class="NormalPACKT" style="margin-left: 36.0pt; mso-list: l1 level1 lfo2; text-indent: -18.0pt;">
<span lang="EN-GB">2.<span style="font: 7pt "Times New Roman";">
</span></span><span lang="EN-GB">Set
the 0 for “max degree of parallelism” in “<span class="SpellE">sp_configure</span>”
system stored procedure with following T-SQL:<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: green; font-family: Consolas; font-size: 9.5pt;">--enable
advanced option in <span class="SpellE">sp_configure</span> procedure</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">EXEC</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span class="SpellE"><span style="color: maroon;">sp_configure</span></span><span style="color: blue;"> </span><span style="color: red;">'show advanced option'</span><span class="GramE"><span style="color: grey;">,</span>1</span><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">RECONFIGURE</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">WITH</span> <span style="color: blue;">OVERRIDE</span><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">GO</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: green; font-family: Consolas; font-size: 9.5pt;">--setting
0 for max degree of parallelism<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: green; font-family: Consolas; font-size: 9.5pt;">--0 is
the default value so even by executing <o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: green; font-family: Consolas; font-size: 9.5pt;">--following
command, we are not making any change</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span class="SpellE"><span style="color: maroon; font-family: Consolas; font-size: 9.5pt;">sp_configure</span></span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: red;">'max degree of parallelism'</span><span style="color: grey;">,</span>
0<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">RECONFIGURE</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">WITH</span> <span style="color: blue;">OVERRIDE</span><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">GO</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; mso-list: l1 level1 lfo2; text-autospace: none; text-indent: -18.0pt;">
<span style="font-family: Consolas; font-size: 9.5pt;">3.<span style="font: 7pt "Times New Roman";"> </span></span><span style="font-family: Consolas; font-size: 9.5pt;">Before we test any query let us clear cache and
buffer so nothing will be used from saved execution plan. <b style="mso-bidi-font-weight: normal;">I highly recommend using following command in development or testing
server only. Clearing cache and buffer in production server may give you slow
performance temporarily</b>.<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">DBCC</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: teal;">FREEPROCCACHE</span><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">DBCC</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: teal;">DROPCLEANBUFFERS<o:p></o:p></span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; mso-list: l1 level1 lfo2; text-autospace: none; text-indent: -18.0pt;">
<span style="font-family: Consolas; font-size: 9.5pt;">4.<span style="font: 7pt "Times New Roman";"> </span></span><span style="font-family: Consolas; font-size: 9.5pt;">Now specifying parallelism at query level with
MAXDOP hint by executing following T-SQL<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span class="GramE"><span style="color: blue; font-family: Consolas; font-size: 9.5pt;">set</span></span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">statistics</span> <span style="color: blue;">IO</span> <span style="color: blue;">on</span><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span class="GramE"><span style="color: blue; font-family: Consolas; font-size: 9.5pt;">set</span></span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">statistics</span> <span style="color: blue;">time</span> <span style="color: blue;">on</span><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span class="GramE"><span style="color: blue; font-family: Consolas; font-size: 9.5pt;">select</span></span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: grey;">*</span> <span style="color: blue;">from</span> <span style="color: teal;">orders</span> <span style="color: blue;">option </span><span style="color: grey;">(</span><span style="color: blue;">MAXDOP</span> 1<span style="color: grey;">)</span><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span class="GramE"><span style="color: blue; font-family: Consolas; font-size: 9.5pt;">set</span></span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">statistics</span> <span style="color: blue;">time</span> <span style="color: blue;">off</span><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span class="GramE"><span style="color: blue; font-family: Consolas; font-size: 9.5pt;">set</span></span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">statistics</span> <span style="color: blue;">IO</span> <span style="color: blue;">off</span><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span class="GramE"><span style="color: blue; font-family: Consolas; font-size: 9.5pt;">set</span></span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">statistics</span> <span style="color: blue;">IO</span> <span style="color: blue;">on</span><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span class="GramE"><span style="color: blue; font-family: Consolas; font-size: 9.5pt;">set</span></span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">statistics</span> <span style="color: blue;">time</span> <span style="color: blue;">on</span><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span class="GramE"><span style="color: blue; font-family: Consolas; font-size: 9.5pt;">select</span></span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: grey;">*</span> <span style="color: blue;">from</span> <span style="color: teal;">orders</span> <span style="color: blue;">option </span><span style="color: grey;">(</span><span style="color: blue;">MAXDOP</span> 2<span style="color: grey;">)</span><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span class="GramE"><span style="color: blue; font-family: Consolas; font-size: 9.5pt;">set</span></span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">statistics</span> <span style="color: blue;">time</span> <span style="color: blue;">off</span><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span class="GramE"><span style="color: blue; font-family: Consolas; font-size: 9.5pt;">set</span></span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">statistics</span> <span style="color: blue;">IO</span> <span style="color: blue;">off<o:p></o:p></span></span></div>
<div class="NormalPACKT">
<br /></div>
<div class="NormalPACKT" style="margin-left: 36.0pt; mso-list: l1 level1 lfo2; text-indent: -18.0pt;">
<span lang="EN-GB">5.<span style="font: 7pt "Times New Roman";">
</span></span><span lang="EN-GB">Since
we had “Statistics IO” and “Statistics Time” enabled while executing both of
the above SELECT query. We can get results of SET commands in “Message” tab
besides “Results” panel. Here is the result I am getting in my testing server,
you might get different result.<o:p></o:p></span></div>
<div class="NormalPACKT">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<b style="mso-bidi-font-weight: normal;"><span style="font-family: "Courier New"; font-size: 12pt;">(100000
row(s) affected)<o:p></o:p></span></b></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span class="GramE"><b style="mso-bidi-font-weight: normal;"><span style="font-family: "Courier New"; font-size: 12pt;">Table 'orders'.</span></b></span><b style="mso-bidi-font-weight: normal;"><span style="font-family: "Courier New"; font-size: 12pt;">
Scan count 1, logical reads 409, <span class="GramE">physical</span> reads 0,
read-ahead reads 414, lob logical reads 0, lob physical reads 0, lob read-ahead
reads 0.<o:p></o:p></span></b></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<b style="mso-bidi-font-weight: normal;"><span style="font-family: "Courier New"; font-size: 12pt;"> SQL Server Execution Times:<o:p></o:p></span></b></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<b style="mso-bidi-font-weight: normal;"><span style="font-family: "Courier New"; font-size: 12pt;"> <span style="color: red;">CPU time = 16 ms</span><span class="GramE">, elapsed</span> time = 586 ms.<o:p></o:p></span></b></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<b style="mso-bidi-font-weight: normal;"><span style="font-family: "Courier New"; font-size: 12pt;">(100000
row(s) affected)<o:p></o:p></span></b></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span class="GramE"><b style="mso-bidi-font-weight: normal;"><span style="font-family: "Courier New"; font-size: 12pt;">Table 'orders'.</span></b></span><b style="mso-bidi-font-weight: normal;"><span style="font-family: "Courier New"; font-size: 12pt;">
Scan count 1, logical reads 409, <span class="GramE">physical</span> reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead
reads 0.<o:p></o:p></span></b></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<b style="mso-bidi-font-weight: normal;"><span style="font-family: "Courier New"; font-size: 12pt;"> SQL Server Execution Times:<o:p></o:p></span></b></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<b style="mso-bidi-font-weight: normal;"><span style="font-family: "Courier New"; font-size: 12pt;"> <span style="color: red;">CPU time = 62 ms</span><span class="GramE">, elapsed</span> time = 575 ms.<o:p></o:p></span></b></div>
<div class="NormalPACKT">
<br /></div>
<div class="NormalPACKT">
<br /></div>
<h2>
<span lang="EN-GB">How it works...</span></h2>
<div class="NormalPACKT">
<span lang="EN-GB">Setting
value 0 (zero) either in MAXDOP or for “max degree of parallelism” indicate
that SQL Server has choice to decide whether to use multiple processor for the
query or not. Apart from 0, any number specified more then 0 indicate that,
maximum <span class="SpellE">upto</span> that number of processor could be used
by processor.<o:p></o:p></span></div>
<div class="NormalPACKT">
<span lang="EN-GB">We have
used MAXDOP 1 for first SELECT query and MAXDOP 2 for second SELECT query and
you can see results of “STATISTICS TIME” that one processor has done the better
job by consuming 16ms in CPU time as against 62ms CPU time in second try where
we had MAXDOP 2 which is indeed a big difference.<o:p></o:p></span></div>
<div class="NormalPACKT">
<span lang="EN-GB">CPU time
shows the time it has taken to process the query.<o:p></o:p></span></div>
<h2>
<span lang="EN-GB">Conclusion</span></h2>
<div class="MsoNormal">
If there is no other alternative, then and then go for
selecting the processor for your query otherwise let SQL Server take decision
by its own. </div>
<div class="MsoNormal" style="line-height: 19.2pt; margin-bottom: .0001pt; margin-bottom: 0cm;">
<b><span style="color: #333333; font-family: "Times New Roman","serif"; font-size: 12pt;">Reference: Ritesh Shah</span></b><span style="font-family: "Times New Roman","serif"; font-size: 12pt;"><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="color: #333333; font-family: "Times New Roman","serif"; font-size: 12pt; line-height: 115%;"><a href="http://www.sqlhub.com/"><b><span style="color: #5588aa; text-decoration: none;">http://www.sqlhub.com</span></b></a></span><b><span style="color: #333333; font-family: "Times New Roman","serif"; font-size: 10pt; line-height: 115%;"><br />
</span></b><b><span style="color: #333333; font-family: "Times New Roman","serif"; font-size: 10pt; line-height: 115%;">Note: Microsoft Books online is a default
reference of all articles but examples and explanations prepared by Ritesh
Shah, founder of </span></b><span style="color: #333333; font-family: "Times New Roman","serif"; font-size: 12pt; line-height: 115%;"><a href="http://www.sqlhub.com/"><b><span style="color: #5588aa; text-decoration: none;">http://www.SQLHub.com</span></b></a></span></div>
<div class="MsoNormal">
<br /></div>
</div>
</div><div class="blogger-post-footer">Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah</div>Ritesh Shahhttp://www.blogger.com/profile/09562656548324468450noreply@blogger.com0tag:blogger.com,1999:blog-9075786158122089104.post-82479122146753009872012-05-04T16:43:00.000+05:302012-05-04T17:14:54.330+05:30NOLOCK hint in SQL Server, Good guy or bad guy?<div dir="ltr" style="text-align: left;" trbidi="on">
<link href="NOLOCK%20hint%20in%20SQL%20Server_files/filelist.xml" rel="File-List"></link>
<link href="NOLOCK%20hint%20in%20SQL%20Server_files/editdata.mso" rel="Edit-Time-Data"></link>
<link href="NOLOCK%20hint%20in%20SQL%20Server_files/themedata.thmx" rel="themeData"></link>
<link href="NOLOCK%20hint%20in%20SQL%20Server_files/colorschememapping.xml" rel="colorSchemeMapping"></link>
<style>
<!--
/* Font Definitions */
@font-face
{font-family:Wingdings;
panose-1:5 0 0 0 0 0 0 0 0 0;
mso-font-charset:2;
mso-generic-font-family:auto;
mso-font-pitch:variable;
mso-font-signature:0 268435456 0 0 -2147483648 0;}
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;
mso-font-charset:0;
mso-generic-font-family:roman;
mso-font-pitch:variable;
mso-font-signature:-1610611985 1107304683 0 0 159 0;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;
mso-font-charset:0;
mso-generic-font-family:swiss;
mso-font-pitch:variable;
mso-font-signature:-1610611985 1073750139 0 0 159 0;}
@font-face
{font-family:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;
mso-font-charset:0;
mso-generic-font-family:modern;
mso-font-pitch:fixed;
mso-font-signature:-1610611985 1073750091 0 0 159 0;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{mso-style-unhide:no;
mso-style-qformat:yes;
mso-style-parent:"";
margin-top:0cm;
margin-right:0cm;
margin-bottom:10.0pt;
margin-left:0cm;
line-height:115%;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-fareast-font-family:Calibri;
mso-bidi-font-family:"Times New Roman";
mso-fareast-language:EN-US;}
h1
{mso-style-name:"Heading 1\,Heading 1 \[PACKT\]";
mso-style-unhide:no;
mso-style-qformat:yes;
mso-style-parent:"";
mso-style-link:"Heading 1 Char";
mso-style-next:"Normal \[PACKT\]";
margin-top:20.0pt;
margin-right:0cm;
margin-bottom:3.0pt;
margin-left:0cm;
mso-pagination:widow-orphan;
page-break-after:avoid;
mso-outline-level:1;
background:#1F497D;
border:none;
mso-border-alt:solid windowtext .5pt;
padding:0cm;
mso-padding-alt:4.0pt 4.0pt 4.0pt 4.0pt;
font-size:16.0pt;
font-family:"Arial","sans-serif";
mso-fareast-font-family:"Times New Roman";
color:white;
mso-font-kerning:16.0pt;
mso-ansi-language:EN-GB;
mso-fareast-language:EN-US;
mso-bidi-font-weight:normal;
mso-bidi-font-style:italic;}
h2
{mso-style-name:"Heading 2\,Heading 2 \[PACKT\]";
mso-style-unhide:no;
mso-style-qformat:yes;
mso-style-parent:"";
mso-style-link:"Heading 2 Char";
mso-style-next:"Normal \[PACKT\]";
margin-top:16.0pt;
margin-right:0cm;
margin-bottom:3.0pt;
margin-left:0cm;
mso-pagination:widow-orphan;
page-break-after:avoid;
mso-outline-level:2;
font-size:14.0pt;
font-family:"Arial","sans-serif";
mso-fareast-font-family:"Times New Roman";
color:#333399;
mso-ansi-language:EN-GB;
mso-fareast-language:EN-US;
mso-bidi-font-style:italic;}
h3
{mso-style-name:"Heading 3\,Heading 3 \[PACKT\]";
mso-style-unhide:no;
mso-style-qformat:yes;
mso-style-parent:"";
mso-style-link:"Heading 3 Char\,Heading 3 \[PACKT\] Char";
mso-style-next:"Normal \[PACKT\]";
margin-top:12.0pt;
margin-right:0cm;
margin-bottom:3.0pt;
margin-left:0cm;
mso-pagination:widow-orphan;
page-break-after:avoid;
mso-outline-level:3;
border:none;
mso-border-top-alt:solid windowtext .5pt;
mso-border-left-alt:solid windowtext .5pt;
padding:0cm;
mso-padding-alt:2.0pt 0cm 0cm 4.0pt;
font-size:13.0pt;
font-family:"Arial","sans-serif";
mso-fareast-font-family:"Times New Roman";
color:#3366FF;
mso-ansi-language:EN-GB;
mso-fareast-language:EN-US;
mso-bidi-font-weight:normal;
mso-bidi-font-style:italic;}
span.Heading1Char
{mso-style-name:"Heading 1 Char";
mso-style-unhide:no;
mso-style-locked:yes;
mso-style-link:"Heading 1\,Heading 1 \[PACKT\]";
mso-ansi-font-size:16.0pt;
mso-bidi-font-size:16.0pt;
font-family:"Arial","sans-serif";
mso-ascii-font-family:Arial;
mso-fareast-font-family:"Times New Roman";
mso-hansi-font-family:Arial;
mso-bidi-font-family:Arial;
color:white;
background:#1F497D;
mso-font-kerning:16.0pt;
mso-ansi-language:EN-GB;
mso-fareast-language:EN-US;
mso-bidi-language:AR-SA;
font-weight:bold;
mso-bidi-font-weight:normal;
mso-bidi-font-style:italic;}
span.Heading2Char
{mso-style-name:"Heading 2 Char";
mso-style-unhide:no;
mso-style-locked:yes;
mso-style-link:"Heading 2\,Heading 2 \[PACKT\]";
mso-ansi-font-size:14.0pt;
mso-bidi-font-size:14.0pt;
font-family:"Arial","sans-serif";
mso-ascii-font-family:Arial;
mso-fareast-font-family:"Times New Roman";
mso-hansi-font-family:Arial;
mso-bidi-font-family:Arial;
color:#333399;
mso-ansi-language:EN-GB;
mso-fareast-language:EN-US;
mso-bidi-language:AR-SA;
font-weight:bold;
mso-bidi-font-style:italic;}
span.Heading3Char
{mso-style-name:"Heading 3 Char\,Heading 3 \[PACKT\] Char";
mso-style-unhide:no;
mso-style-locked:yes;
mso-style-link:"Heading 3\,Heading 3 \[PACKT\]";
mso-ansi-font-size:13.0pt;
mso-bidi-font-size:13.0pt;
font-family:"Arial","sans-serif";
mso-ascii-font-family:Arial;
mso-fareast-font-family:"Times New Roman";
mso-hansi-font-family:Arial;
mso-bidi-font-family:Arial;
color:#3366FF;
mso-ansi-language:EN-GB;
mso-fareast-language:EN-US;
mso-bidi-language:AR-SA;
font-weight:bold;
mso-bidi-font-weight:normal;
mso-bidi-font-style:italic;}
p.ChapterTitlePACKT, li.ChapterTitlePACKT, div.ChapterTitlePACKT
{mso-style-name:"Chapter Title \[PACKT\]";
mso-style-unhide:no;
mso-style-parent:"";
mso-style-next:"Normal \[PACKT\]";
margin-top:0cm;
margin-right:0cm;
margin-bottom:42.0pt;
margin-left:0cm;
text-align:right;
mso-pagination:widow-orphan;
font-size:28.0pt;
mso-bidi-font-size:16.0pt;
font-family:"Arial","sans-serif";
mso-fareast-font-family:"Times New Roman";
color:black;
mso-font-kerning:16.0pt;
mso-ansi-language:EN-GB;
mso-fareast-language:EN-US;
mso-bidi-font-weight:bold;}
p.BulletPACKT, li.BulletPACKT, div.BulletPACKT
{mso-style-name:"Bullet \[PACKT\]";
mso-style-unhide:no;
mso-style-parent:"Normal \[PACKT\]";
margin-top:0cm;
margin-right:18.0pt;
margin-bottom:3.0pt;
margin-left:0cm;
text-indent:0cm;
mso-pagination:widow-orphan;
mso-list:l2 level1 lfo1;
mso-hyphenate:none;
tab-stops:18.0pt;
font-size:11.0pt;
mso-bidi-font-size:12.0pt;
font-family:"Calibri","sans-serif";
mso-fareast-font-family:"Times New Roman";
mso-bidi-font-family:"Times New Roman";
color:purple;
mso-ansi-language:EN-US;
mso-fareast-language:EN-US;}
p.InformationBoxPACKT, li.InformationBoxPACKT, div.InformationBoxPACKT
{mso-style-name:"Information Box \[PACKT\]";
mso-style-unhide:no;
mso-style-parent:"Normal \[PACKT\]";
mso-style-link:"Information Box \[PACKT\] Char";
mso-style-next:"Normal \[PACKT\]";
margin-top:9.0pt;
margin-right:18.0pt;
margin-bottom:9.0pt;
margin-left:18.0pt;
mso-pagination:widow-orphan;
mso-hyphenate:none;
background:white;
border:none;
mso-border-alt:solid black .75pt;
padding:0cm;
mso-padding-alt:6.0pt 6.0pt 9.0pt 6.0pt;
font-size:11.0pt;
mso-bidi-font-size:12.0pt;
font-family:"Calibri","sans-serif";
mso-fareast-font-family:"Times New Roman";
mso-bidi-font-family:"Times New Roman";
mso-ansi-language:EN-US;
mso-fareast-language:EN-US;}
p.ChapterNumberPACKT, li.ChapterNumberPACKT, div.ChapterNumberPACKT
{mso-style-name:"Chapter Number \[PACKT\]";
mso-style-unhide:no;
mso-style-parent:"";
mso-style-next:"Chapter Title \[PACKT\]";
margin:0cm;
margin-bottom:.0001pt;
text-align:right;
mso-pagination:widow-orphan;
font-size:60.0pt;
mso-bidi-font-size:16.0pt;
font-family:"Arial","sans-serif";
mso-fareast-font-family:"Times New Roman";
color:black;
mso-font-kerning:16.0pt;
mso-ansi-language:EN-GB;
mso-fareast-language:EN-US;
font-weight:bold;}
p.NormalPACKT, li.NormalPACKT, div.NormalPACKT
{mso-style-name:"Normal \[PACKT\]";
mso-style-unhide:no;
mso-style-parent:"";
mso-style-link:"Normal \[PACKT\] Char";
margin-top:0cm;
margin-right:0cm;
margin-bottom:6.0pt;
margin-left:0cm;
mso-pagination:widow-orphan;
font-size:11.0pt;
mso-bidi-font-size:12.0pt;
font-family:"Calibri","sans-serif";
mso-fareast-font-family:"Times New Roman";
mso-bidi-font-family:"Times New Roman";
mso-ansi-language:EN-US;
mso-fareast-language:EN-US;}
span.NormalPACKTChar
{mso-style-name:"Normal \[PACKT\] Char";
mso-style-unhide:no;
mso-style-locked:yes;
mso-style-link:"Normal \[PACKT\]";
mso-ansi-font-size:11.0pt;
mso-bidi-font-size:12.0pt;
font-family:"Times New Roman","serif";
mso-fareast-font-family:"Times New Roman";
mso-ansi-language:EN-US;
mso-fareast-language:EN-US;
mso-bidi-language:AR-SA;}
span.InformationBoxPACKTChar
{mso-style-name:"Information Box \[PACKT\] Char";
mso-style-unhide:no;
mso-style-locked:yes;
mso-style-parent:"Normal \[PACKT\] Char";
mso-style-link:"Information Box \[PACKT\]";
mso-ansi-font-size:11.0pt;
mso-bidi-font-size:12.0pt;
font-family:"Times New Roman","serif";
mso-fareast-font-family:"Times New Roman";
background:white;
mso-ansi-language:EN-US;
mso-fareast-language:EN-US;
mso-bidi-language:AR-SA;}
.MsoChpDefault
{mso-style-type:export-only;
mso-default-props:yes;
font-size:10.0pt;
mso-ansi-font-size:10.0pt;
mso-bidi-font-size:10.0pt;
mso-ascii-font-family:Calibri;
mso-fareast-font-family:Calibri;
mso-hansi-font-family:Calibri;}
@page Section1
{size:595.3pt 841.9pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;
mso-header-margin:35.4pt;
mso-footer-margin:35.4pt;
mso-paper-source:0;}
div.Section1
{page:Section1;}
/* List Definitions */
@list l0
{mso-list-id:641040119;
mso-list-type:hybrid;
mso-list-template-ids:251030766 1074331663 1074331673 1074331675 1074331663 1074331673 1074331675 1074331663 1074331673 1074331675;}
@list l0:level1
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l1
{mso-list-id:1024940464;
mso-list-type:hybrid;
mso-list-template-ids:391942938 1074331663 1074331673 1074331675 1074331663 1074331673 1074331675 1074331663 1074331673 1074331675;}
@list l1:level1
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l2
{mso-list-id:1724213397;
mso-list-type:hybrid;
mso-list-template-ids:493095766 236218882 67698691 67698693 67698689 67698691 67698693 67698689 67698691 67698693;}
@list l2:level1
{mso-level-number-format:bullet;
mso-level-style-link:"Bullet \[PACKT\]";
mso-level-text:\F0B7;
mso-level-tab-stop:36.0pt;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:Symbol;
color:windowtext;}
ol
{margin-bottom:0cm;}
ul
{margin-bottom:0cm;}
-->
</style>
<br />
<div class="Section1">
<h2>
<span lang="EN-GB">Introduction</span></h2>
<div class="MsoNormal">
<span lang="EN-GB">It is quite
often in production environment that multiple application and/or users try to
access same resource at the same time which may create locking and blocking
issues. It is tricky to deal with this situation as there is no rocket science
which suits for all situations. It is all depends on situation because in some
production environment, it is not possible to resolve blocking issues easily
over the night.<o:p></o:p></span></div>
<div class="MsoNormal">
<span lang="EN-GB">The main
root cause for locking is, we have long running transaction which keeps your
object locked and meanwhile any request comes to access the same object, has to
wait until the current transaction complete its operation. Best choice should
be to minimize the transaction length so that it releases the lock quickly and
other request doesn’t need wait due to lock but unfortunately it is possible to
solve it easily in ALL environments.<o:p></o:p></span></div>
<h2>
<span lang="EN-GB">Getting ready</span></h2>
<div class="NormalPACKT">
<span lang="EN-GB">I am
going to perform this example in my SQL Server 2012 RTM version but it may work
as it is in SQL Server 2005 / 2008 too.<o:p></o:p></span></div>
<div class="NormalPACKT">
<span lang="EN-GB">Connect
to your SQL Server and open three query window. We will call these query
windows with following name:<o:p></o:p></span></div>
<div class="BulletPACKT" style="margin-left: 0cm; text-indent: 0cm;">
<span lang="EN-US" style="color: windowtext; font-family: Symbol;">·<span style="font: 7pt "Times New Roman";">
</span></span><span lang="EN-US">Win1</span></div>
<div class="BulletPACKT" style="margin-left: 0cm; text-indent: 0cm;">
<span lang="EN-US" style="color: windowtext; font-family: Symbol;">·<span style="font: 7pt "Times New Roman";">
</span></span><span lang="EN-US">Win2</span></div>
<div class="BulletPACKT" style="margin-left: 0cm; text-indent: 0cm;">
<span lang="EN-US" style="color: windowtext; font-family: Symbol;">·<span style="font: 7pt "Times New Roman";">
</span></span><span lang="EN-US">Win3</span></div>
<h2>
<span lang="EN-GB">How to do it...</span></h2>
<div class="NormalPACKT" style="margin-left: 36.0pt; mso-list: l0 level1 lfo2; text-indent: -18.0pt;">
<span lang="EN-GB">1.<span style="font: 7pt "Times New Roman";">
</span></span><span lang="EN-GB">After
connecting to SQL Server Management Studio (SSMS), open Win1<o:p></o:p></span></div>
<div class="NormalPACKT" style="margin-left: 36.0pt; mso-list: l0 level1 lfo2; text-indent: -18.0pt;">
<span lang="EN-GB">2.<span style="font: 7pt "Times New Roman";">
</span></span><span lang="EN-GB">Create
one sample database, named SQLHub and create one table with sample rows with
following T-SQL script:<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">create</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">database</span> <span style="color: teal;">SQLHub</span><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">GO</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">USE</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: teal;">SQLHub</span><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">GO</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: green; font-family: Consolas; font-size: 9.5pt;">--if
orders table is already there. you can delete it than create new one with name
"Orders"</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">IF</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: magenta;">OBJECT_ID</span><span style="color: grey;">(</span><span style="color: red;">'orders'</span><span style="color: grey;">,</span> <span style="color: red;">'U'</span><span style="color: grey;">)</span> <span style="color: grey;">IS</span> <span style="color: grey;">NOT</span> <span style="color: grey;">NULL</span> <span style="color: blue;">BEGIN</span><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">DROP</span> <span style="color: blue;">TABLE</span> <span style="color: teal;">orders</span><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">END</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">GO</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: green; font-family: Consolas; font-size: 9.5pt;">--creating
table</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">CREATE</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">TABLE</span> <span style="color: teal;">orders</span><span style="color: blue;"> </span><span style="color: grey;">(</span><span style="color: teal;">OrderID</span> <span style="color: blue;">INT</span> <span style="color: blue;">IDENTITY</span><span style="color: grey;">,</span> <span style="color: teal;">OrderDate</span> <span style="color: blue;">DATETIME</span><span style="color: grey;">,</span> <span style="color: teal;">Amount</span> <span style="color: blue;">MONEY</span><span style="color: grey;">,</span> <span style="color: teal;">Refno</span> <span style="color: blue;">INT</span><span style="color: grey;">)</span><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">GO</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: green; font-family: Consolas; font-size: 9.5pt;">--inserting
100000 sample rows into table </span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">INSERT</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">INTO</span> <span style="color: teal;">orders</span><span style="color: blue;"> </span><span style="color: grey;">(</span><span style="color: teal;">OrderDate</span><span style="color: grey;">,</span> <span style="color: teal;">Amount</span><span style="color: grey;">,</span> <span style="color: teal;">Refno</span><span style="color: grey;">)</span><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">SELECT</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">TOP</span> 100000<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: magenta;">DATEADD</span><span style="color: grey;">(</span><span style="color: blue;">minute</span><span style="color: grey;">,</span> <span style="color: magenta;">ABS</span><span style="color: grey;">(</span><span style="color: teal;">a</span><span style="color: grey;">.</span><span style="color: magenta;">object_id</span> <span style="color: grey;">%</span> 50000 <span style="color: grey;">),</span> <span style="color: magenta;">CAST</span><span style="color: grey;">(</span><span style="color: red;">'2010-02-01'</span> <span style="color: blue;">AS</span> <span style="color: blue;">DATETIME</span><span style="color: grey;">)),</span><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: magenta;">ABS</span><span style="color: grey;">(</span><span style="color: teal;">a</span><span style="color: grey;">.</span><span style="color: magenta;">object_id</span> <span style="color: grey;">%</span> 10<span style="color: grey;">),</span><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: magenta;">CAST</span><span style="color: grey;">(</span><span style="color: magenta;">ABS</span><span style="color: grey;">(</span><span style="color: teal;">a</span><span style="color: grey;">.</span><span style="color: magenta;">object_id</span><span style="color: grey;">)</span> <span style="color: blue;">AS</span> <span style="color: blue;">VARCHAR</span><span style="color: grey;">)</span><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">FROM</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: green;">sys</span><span style="color: grey;">.</span><span style="color: green;">all_objects</span> <span style="color: teal;">a</span><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: grey; font-family: Consolas; font-size: 9.5pt;">CROSS</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: grey;">JOIN</span> <span style="color: green;">sys</span><span style="color: grey;">.</span><span style="color: green;">all_objects</span> <span style="color: teal;">b</span><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">GO</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 72.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; mso-list: l0 level1 lfo2; text-autospace: none; text-indent: -18.0pt;">
<span style="font-family: Consolas; font-size: 9.5pt;">3.<span style="font: 7pt "Times New Roman";"> </span></span><span style="font-family: Consolas; font-size: 9.5pt;">Execute one UPDATE statement in Win1 with BEGIN
TRANSACTION. Note that we don’t have COMMIT or ROLLBACK after the UPDATE
Statement at the moment. ROLLBACK is commented so it won’t be executed.<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">BEGIN</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">TRANSACTION</span><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">UPDATE</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: teal;">Orders</span> <span style="color: blue;">SET</span> <span style="color: teal;">Amount</span><span style="color: grey;">=</span>5.00 <span style="color: blue;">WHERE</span> <span style="color: teal;">OrderID</span><span style="color: grey;"><</span>10<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: green; font-family: Consolas; font-size: 9.5pt;">--ROLLBACK</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; mso-list: l0 level1 lfo2; text-autospace: none; text-indent: -18.0pt;">
<span style="font-family: Consolas; font-size: 9.5pt;">4.<span style="font: 7pt "Times New Roman";"> </span></span><span style="font-family: Consolas; font-size: 9.5pt;">In Win2, try to execute following SELECT statement:<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; mso-layout-grid-align: none; text-autospace: none; text-indent: 36.0pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">select</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: grey;">*</span> <span style="color: blue;">from</span> <span style="color: teal;">Orders</span> <span style="color: blue;">Where</span> <span style="color: teal;">orderID</span><span style="color: grey;"><=</span>15<o:p></o:p></span></div>
<div class="NormalPACKT">
<br /></div>
<div class="NormalPACKT" style="margin-left: 36.0pt; mso-list: l0 level1 lfo2; text-indent: -18.0pt;">
<span lang="EN-GB">5.<span style="font: 7pt "Times New Roman";">
</span></span><span lang="EN-GB">You
will observe that SELECT statement wouldn’t return any results so in WIN3, try
to execute following T-SQL and know what is going on behind the screen:<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: maroon; font-family: Consolas; font-size: 9.5pt;">sp_who2</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">go<o:p></o:p></span></div>
<div class="NormalPACKT">
<br /></div>
<div class="NormalPACKT" style="margin-left: 36.0pt; mso-list: l0 level1 lfo2; text-indent: -18.0pt;">
<span lang="EN-GB">6.<span style="font: 7pt "Times New Roman";">
</span></span><span lang="EN-GB">We
will get many rows in the result set from the above command but we have to look
at last two rows of SQLHub database as per given in following screen capture:<o:p></o:p></span></div>
<div class="NormalPACKT">
<span lang="EN-GB"><o:p></o:p></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-FKoBIKv4g5Y/T6O5MmWNHUI/AAAAAAAAAYA/qG24ndXo8Yg/s1600/1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="11" src="http://4.bp.blogspot.com/-FKoBIKv4g5Y/T6O5MmWNHUI/AAAAAAAAAYA/qG24ndXo8Yg/s320/1.png" width="320" /></a></div>
<div class="NormalPACKT">
<br /></div>
<div class="NormalPACKT" style="margin-left: 36.0pt; mso-list: l0 level1 lfo2; text-indent: -18.0pt;">
<span lang="EN-GB">7.<span style="font: 7pt "Times New Roman";">
</span></span><span lang="EN-GB">We
can see that out UPDATE statement runs with SPID 54 and SELECT statement runs
with SPID 55. SELECT query is blocked by SPID 54 given in row no.2 column no.5
in screen capture. Now I have two ways. Either I issue COMMIT / ROLLBACK
command which is not a good idea to interrupt UPDATE or cancel SELECT query in
SSMS which I will do now.<o:p></o:p></span></div>
<div class="NormalPACKT" style="margin-left: 36.0pt; mso-list: l0 level1 lfo2; text-indent: -18.0pt;">
<span lang="EN-GB">8.<span style="font: 7pt "Times New Roman";">
</span></span><span lang="EN-GB">Since
we have cancelled SELECT query, we will not execute same SELECT statement with
NOLOCK hint.<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">select</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: grey;">*</span> <span style="color: blue;">from</span> <span style="color: teal;">Orders</span> <span style="color: blue;">WITH </span><span style="color: grey;">(</span><span style="color: blue;">NOLOCK</span><span style="color: grey;">)</span> <span style="color: blue;">Where</span> <span style="color: teal;">orderID</span><span style="color: grey;"><=</span>15<o:p></o:p></span></div>
<div class="NormalPACKT" style="margin-left: 36.0pt;">
<br /></div>
<div class="NormalPACKT" style="margin-left: 36.0pt; mso-list: l0 level1 lfo2; text-indent: -18.0pt;">
<span lang="EN-GB">9.<span style="font: 7pt "Times New Roman";">
</span></span><span lang="EN-GB">Here
is the result return by above query.<o:p></o:p></span></div>
<div class="NormalPACKT">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-i1zCnE8BxwQ/T6O5NhK7W5I/AAAAAAAAAYI/JJms5heDwKU/s1600/2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="297" src="http://4.bp.blogspot.com/-i1zCnE8BxwQ/T6O5NhK7W5I/AAAAAAAAAYI/JJms5heDwKU/s320/2.png" width="320" /></a></div>
<div class="NormalPACKT">
<br /></div>
<div class="NormalPACKT" style="margin-left: 36.0pt; mso-list: l0 level1 lfo2; text-indent: -18.0pt;">
<span lang="EN-GB">10.<span style="font: 7pt "Times New Roman";">
</span></span><span lang="EN-GB">Go
to Win 1 and execute “ROLLBACK” statement.<o:p></o:p></span></div>
<div class="NormalPACKT" style="margin-left: 36.0pt; mso-list: l0 level1 lfo2; text-indent: -18.0pt;">
<span lang="EN-GB">11.<span style="font: 7pt "Times New Roman";">
</span></span><span lang="EN-GB">Go
back to Win 2 and execute SELECT statement with or without “NOLOCK”. For eg:<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; mso-layout-grid-align: none; text-autospace: none; text-indent: 36.0pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">select</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: grey;">*</span> <span style="color: blue;">from</span> <span style="color: teal;">Orders</span> <span style="color: blue;">Where</span> <span style="color: teal;">orderID</span><span style="color: grey;"><=</span>15<o:p></o:p></span></div>
<div class="NormalPACKT">
<br /></div>
<div class="NormalPACKT" style="margin-left: 36.0pt; mso-list: l0 level1 lfo2; text-indent: -18.0pt;">
<span lang="EN-GB">12.<span style="font: 7pt "Times New Roman";">
</span></span><span lang="EN-GB">Here
is the screen shot of result return by above query:<o:p></o:p></span></div>
<div class="NormalPACKT">
<span lang="EN-GB"><o:p></o:p></span></div>
<div class="NormalPACKT">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-AhxU-fe7o30/T6O5OpHHeGI/AAAAAAAAAYQ/YZqyeYkd-ng/s1600/3.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="291" src="http://2.bp.blogspot.com/-AhxU-fe7o30/T6O5OpHHeGI/AAAAAAAAAYQ/YZqyeYkd-ng/s320/3.png" width="320" /></a></div>
<div class="NormalPACKT">
<span lang="EN-GB"><o:p><br /></o:p></span></div>
<div class="NormalPACKT">
<br /></div>
<h2>
<span lang="EN-GB">How it works...</span></h2>
<div class="NormalPACKT">
<span lang="EN-GB">When we
have executed UPDATE statement in Step no 3 without COMMIT or ROLLBACK, It
updates the records but didn’t release the lock it has acquired on the table so
SELECT query was not able read data and return it.<o:p></o:p></span></div>
<div class="NormalPACKT">
<span lang="EN-GB">How do
you know that whether UPDATE has really updated the values or not? Since our
select query is not giving results. We have executed same select query with
“WITH (NOLOCK)” hint in step no. 8 and we can confirm that values are update
with the screen capture given in step 9. Isn’t it good situation? Yes, may be
as we were not even able to get the result of SELECT statement in step no 4. But
I would say we can’t decide whether it is a good or bad without evaluating the
business need.<o:p></o:p></span></div>
<div class="NormalPACKT">
<span lang="EN-GB">Since
UPDATE statement has updated the value but it is not saved so there is a chance
to ROLLBACK. If you see resultset given in steps no 9, you will see “5.00” in
“Amount” column which may not be a proper value as after ROLLBACK, it comes
back to “2.00” again as per step no. 12.<o:p></o:p></span></div>
<div class="NormalPACKT">
<span lang="EN-GB">Think if
this would be a result of stock exchange, what would happen? Can we run with
dirty read we seen in screen shot in step no.9? No certainly not that is why I
have conveyed that NOLOCK is all depends on the business need and situation.
Use it wisely as it is two sided sword.<o:p></o:p></span></div>
<h2>
<span lang="EN-GB">See also</span></h2>
<div class="NormalPACKT">
<span lang="EN-GB">NOLOCK as
known as READUNCOMMITED concept is somehow related to ISOLATION level. Here are the links of some of my past article
on the subject.<o:p></o:p></span></div>
<div class="NormalPACKT">
<span lang="EN-GB"><a href="http://www.sqlhub.com/2009/03/different-types-of-isolation-levels.html">Different
Types of Isolation Levels - Microsoft SQL Server 2005 – Part 1</a><o:p></o:p></span></div>
<div class="NormalPACKT">
<span lang="EN-GB"><a href="http://www.sqlhub.com/2009/03/different-types-of-isolation-levels_15.html">Different
Types of Isolation Levels - Microsoft SQL Server 2005 – Part 2</a><o:p></o:p></span></div>
<div class="NormalPACKT">
<span lang="EN-GB"><a href="http://www.sqlhub.com/2009/03/different-types-of-isolation-levels_5757.html">Different
Types of Isolation Levels - Microsoft SQL Server 2005 – Part 3</a><o:p></o:p></span></div>
<div class="MsoNormal">
<a href="http://www.sqlhub.com/2009/07/find-table-being-locked-in-sql-server.html" target="_blank">Find locked table in SQL Server</a>.</div>
<div class="MsoNormal">
Happy Reading!!!</div>
<div class="MsoNormal">
</div>
<div class="MsoNormal">
<b><span style="color: #333333;">Reference: Ritesh Shah</span></b></div>
<div class="MsoNormal">
<a href="http://www.sqlhub.com/"><b style="mso-bidi-font-weight: normal;">http://www.sqlhub.com</b></a><b style="mso-bidi-font-weight: normal;"><span style="color: #333333;"><br /> </span></b></div>
<div class="MsoNormal">
<b><span style="color: #333333;">Note: Microsoft Books online is a default reference of all articles but
examples and explanations prepared by Ritesh Shah, founder of </span></b><a href="http://www.sqlhub.com/"><b style="mso-bidi-font-weight: normal;">http://www.SQLHub.com</b></a></div>
<div class="MsoNormal">
</div>
<div class="MsoNormal">
<b><span style="color: red;">Ask me any SQL Server related question at my “</span></b><a href="http://beyondrelational.com/ask/ritesh/default.aspx"><b style="mso-bidi-font-weight: normal;">ASK Profile</b></a><b style="mso-bidi-font-weight: normal;"><span style="color: red;">”</span></b></div>
</div>
</div><div class="blogger-post-footer">Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah</div>Ritesh Shahhttp://www.blogger.com/profile/09562656548324468450noreply@blogger.com0tag:blogger.com,1999:blog-9075786158122089104.post-7323677686842341782012-04-21T11:39:00.000+05:302012-04-23T17:17:48.437+05:30SQL Server 2012 Performance Tuning Cookbook<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<div class="MsoNormal">
</div>
<div class="MsoNormal">
We
are leaving in the world of data now a day. RDBMS systems make it easy to
manage data properly with validations and integrity even it needs expertise to
keep database operation smooth so that you can get performance benefit. Everybody
hates to wait for receiving information. No matter, whether they are waiting
for response from internet, database, report or anything else. </div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
It is a duty of database personnel in the company to make
users of the database or software happy by not keeping them waiting for the
information they require. In a business world, sometime, late information can
create disaster for the company. This is the time when Performance Tuning in
database comes into the picture.</div>
<div class="MsoNormal">
</div>
<div class="MsoNormal">
<a href="http://2.bp.blogspot.com/-q9Uaj56NPhQ/T5JNha-wC8I/AAAAAAAAAXo/0PEarIdjwuM/s1600/5740EN_MockupCover_Cookbook.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img alt="SQL Server 2012 Performance Tuning Cookbook" border="0" height="320" src="http://2.bp.blogspot.com/-q9Uaj56NPhQ/T5JNha-wC8I/AAAAAAAAAXo/0PEarIdjwuM/s320/5740EN_MockupCover_Cookbook.jpg" title="SQL Server 2012 Performance Tuning Cookbook" width="257" /></a><br />
Basically I have started my career as a Developer 10 years
back and I used to work mostly with .NET technology for web & windows
application. Later on I have shifted my interest towards database technology to
maintain performance of my applications as even today, many companies do not
afford expert database developer or administrator so they expect that .NET
developer manage the show in that situation. </div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
While working with database and performance tuning, I
learned a lot by self learning or from the online community. Finally I decided
to share my knowledge with the community in proper format. This is the wish
which lead me towards writing a performance tuning book name “<a href="http://www.packtpub.com/microsoft-sql-server-2012-performance-tuning-cookbook/book"><b style="mso-bidi-font-weight: normal;"><i style="mso-bidi-font-style: normal;">SQL
Server 2012 Performance Tuning Cookbook</i></b></a>” which will be available from June 2012..</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
After thinking to write something about performance tuning,
<b><a href="http://www.sqlhub.com/" target="_blank">me</a></b> and my co-author <b><a href="http://www.mssqlblog.com/" target="_blank">Bihag Thaker</a></b> had a long discussion about the architecture
of the book and we finally decided to divide the book in three main topic.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<b style="mso-bidi-font-weight: normal;">Performance
Monitoring</b>: This supposes to be the first step when you think to tune
anything in the database or in SQL Server. Without monitoring, you will not be
able find the bottleneck which is creating hazard for performance.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<b>Performance Tuning</b>:
Once you find the bottleneck, it is time to overcome that. You have to find
different ways to eliminate that bottleneck and make performance faster. We are
having many different chapters which are discussing this topic.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<b>Performance
Management</b>: “Prevention is better than cure” this is what I believe so it
is better to know some common mistakes which may lead to your system towards
slow performance. If you know these, you can manage it even before it starts
creating an issue for you.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
I have always received overwhelming response and welcome
from the online community in blog and from the forums I am active in. I am
expecting the same response from the community for my book.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Happy Reading!!!</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<b><span style="color: #333333;">Reference: Ritesh Shah</span></b></div>
<div class="MsoNormal">
<a href="http://www.sqlhub.com/"><b style="mso-bidi-font-weight: normal;">http://www.sqlhub.com</b></a><b style="mso-bidi-font-weight: normal;"><span style="color: #333333;"><br /> </span></b></div>
<div class="MsoNormal">
<b><span style="color: #333333;">Note: Microsoft Books online is a default reference of all articles but
examples and explanations prepared by Ritesh Shah, founder of </span></b><a href="http://www.sqlhub.com/"><b style="mso-bidi-font-weight: normal;">http://www.SQLHub.com</b></a></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<b><span style="color: red;">Ask me any SQL Server related question at my “</span></b><a href="http://beyondrelational.com/ask/ritesh/default.aspx"><b style="mso-bidi-font-weight: normal;">ASK Profile</b></a><b style="mso-bidi-font-weight: normal;"><span style="color: red;">”</span></b></div>
<div class="MsoNormal">
<br /></div>
</div><div class="blogger-post-footer">Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah</div>Ritesh Shahhttp://www.blogger.com/profile/09562656548324468450noreply@blogger.com0