tag:blogger.com,1999:blog-71293672411079726972024-03-06T00:15:10.255+02:00SQL Server MusingsAnonymoushttp://www.blogger.com/profile/15769604002687268779noreply@blogger.comBlogger9125tag:blogger.com,1999:blog-7129367241107972697.post-3686216945716556882014-11-07T09:04:00.001+02:002014-11-07T09:04:17.557+02:00<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhX7k8TldfmV39hn50gBQnQxzJcFx2WyJKlsCqWryNtzV0Yn58lvjp6Ec-xWylNPmD-fMwjJxkynsEHU0ro8_E0XeKmABwf8YtinH06WkieNIEhsOWqGGMLPScRo-k59CgZFzNAP1THYcE/s1600/CertChallenge_354x356.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhX7k8TldfmV39hn50gBQnQxzJcFx2WyJKlsCqWryNtzV0Yn58lvjp6Ec-xWylNPmD-fMwjJxkynsEHU0ro8_E0XeKmABwf8YtinH06WkieNIEhsOWqGGMLPScRo-k59CgZFzNAP1THYcE/s1600/CertChallenge_354x356.jpg" height="320" width="318" /></a></div>
<br />
<div>
#CertifyMe</div>
<div>
<br /></div>
<div>
<a href="http://borntolearn.mslearn.net/certchallenge/" rel="nofollow" target="_blank">http://borntolearn.mslearn.net/certchallenge/</a></div>
Anonymoushttp://www.blogger.com/profile/15769604002687268779noreply@blogger.com0tag:blogger.com,1999:blog-7129367241107972697.post-30384718037498015192014-07-08T12:10:00.004+02:002014-07-08T12:10:46.048+02:00SQL Server will not start. There is not enough disk space for tempdb.<div class="MsoNormal">
<span style="font-family: Arial, Helvetica, sans-serif;">With a large tempdb data file located on C:\ the service
would not start. The following message was in the log file.<o:p></o:p></span></div>
<div class="MsoNormal">
<i><span style="color: #1f4e79;"><span style="font-family: Arial, Helvetica, sans-serif;">Could not create tempdb. You
may not have enough disk space available. Free additional disk space by
deleting other files on the tempdb drive and then restart SQL Server. Check for
additional errors in the event log that may indicate why the tempdb files could
not be initialized.<o:p></o:p></span></span></i></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Arial, Helvetica, sans-serif;">Unfortunately there was no way to free up the required space
and the only way to start SQL would be to move the tempdb file location. Catch
22, we need to start SQL to move the files.<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Arial, Helvetica, sans-serif;">The way to accomplish this is to start SQL using trace flag
3608. This trace flag tells SQL not to start up any databases except master.
From a command prompt:<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="color: #1f4e79;"><span style="font-family: Arial, Helvetica, sans-serif;">net start MSSQLSERVER /f /t3608<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: Arial, Helvetica, sans-serif;">
/f: Minimal configuration mode<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Arial, Helvetica, sans-serif;">
/t: Start SQL Server with the specified trace flag<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Arial, Helvetica, sans-serif;">Once SQL Server is running you can connect with SQLCMD using
a dedicated admin connection (DAC) from the command promt.<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="color: #1f4e79;"><span style="font-family: Arial, Helvetica, sans-serif;">sqlcmd -A -S MyServer -q"ALTER
DATABASE tempdb MODIFY FILE (NAME = 'tempdev', FILENAME =
'H:\MSSQL\DATA\tempdb.mdf', SIZE = 2048MB, FILEGROWTH = 2048MB);"<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="color: #1f4e79;"><span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></span></div>
<div class="MsoNormal">
<span style="color: #1f4e79;"><span style="font-family: Arial, Helvetica, sans-serif;">sqlcmd -A -S MyServer -q"ALTER DATABASE tempdb MODIFY FILE (NAME = 'templog', FILENAME =
'G:\MSSQL\DATA\templog.ldf', SIZE = 1024MB, FILEGROWTH = 1024MB);"<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: Arial, Helvetica, sans-serif;">
-A: Use DAC<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Arial, Helvetica, sans-serif;">
-S: Server Name<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Arial, Helvetica, sans-serif;">
-q: Command<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<br />
<div class="MsoNormal">
<span style="font-family: Arial, Helvetica, sans-serif;">Now stop and restart SQL Server and Bob’s your aunty.<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
Anonymoushttp://www.blogger.com/profile/15769604002687268779noreply@blogger.com0tag:blogger.com,1999:blog-7129367241107972697.post-82270969504960749762013-09-02T08:33:00.000+02:002013-09-02T08:33:16.754+02:00SQL Saturday #244 Johannesburg 2013<span style="font-family: Verdana, sans-serif;">SQL Saturday #244 is fast approaching and I will be speaking about indexes. This will be a beginner level session aimed at helping you understand what indexes are, how to use them and what to look out for. Don't miss it. </span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;">I am also looking forward to hearing Gail Shaw discussing database corruption. The schedule is online at <a href="http://www.sqlsaturday.com/244/schedule.aspx">http://www.sqlsaturday.com/244/schedule.aspx</a>.</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;">See you there!</span><br />
<br />Anonymoushttp://www.blogger.com/profile/15769604002687268779noreply@blogger.com0Microsoft-26.044290920891314 28.018698692321777-26.046074420891312 28.016177192321777 -26.042507420891315 28.021220192321778tag:blogger.com,1999:blog-7129367241107972697.post-33734826581635911002013-05-22T11:42:00.000+02:002013-05-22T11:43:20.510+02:00Code Horrors<span style="font-family: "Calibri","sans-serif";">I just stumbled across a
strange piece of code, albeit a small one. I thought I would create a blog post
to start collecting these gems.</span><o:p></o:p><br />
<o:p></o:p><br />
<u><span style="font-family: "Calibri","sans-serif";">Item 1</span></u><o:p></o:p><br />
<o:p></o:p><br />
<span style="color: navy; font-family: Consolas; font-size: 10pt;">SELECT<o:p></o:p></span><br />
<span style="color: maroon; font-family: Consolas; font-size: 10pt;"><span style="mso-spacerun: yes;"> </span>@RowIndex</span><span style="font-family: Consolas; font-size: 10pt;"> = <span style="color: maroon;">RowID</span></span><span style="font-family: "Calibri","sans-serif";"><o:p></o:p></span><br />
<span style="color: navy; font-family: Consolas; font-size: 10pt;">FROM<o:p></o:p></span><br />
<span style="color: navy; font-family: Consolas; font-size: 10pt;"><span style="mso-spacerun: yes;"> </span></span><span style="color: maroon; font-family: Consolas; font-size: 10pt;">CBT</span><span style="font-family: "Calibri","sans-serif";"><o:p></o:p></span><br />
<span style="color: navy; font-family: Consolas; font-size: 10pt;">WHERE<o:p></o:p></span><br />
<span style="color: navy; font-family: Consolas; font-size: 10pt;"><span style="mso-spacerun: yes;"> </span></span><span style="color: maroon; font-family: Consolas; font-size: 10pt;">RowID</span><span style="font-family: Consolas; font-size: 10pt;"> = 1;<o:p></o:p></span><br />
<span style="font-family: "Calibri","sans-serif";"><o:p><span style="font-family: Times New Roman;"> </span></o:p></span><br />
<span style="font-family: "Calibri","sans-serif";">which actually means</span><o:p></o:p><br />
<o:p></o:p><br />
<span style="color: navy; font-family: Consolas; font-size: 10pt;">SELECT </span><span style="color: maroon; font-family: Consolas; font-size: 10pt;">@RowIndex</span><span style="font-family: Consolas; font-size: 10pt;"> = 1;</span><o:p></o:p><br />
<a name='more'></a><o:p></o:p><br />
<u><span style="font-family: "Calibri","sans-serif";">Item 2</span></u><o:p></o:p><br />
<o:p></o:p><br />
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: purple; font-family: Consolas; font-size: 9.5pt;">UPDATE</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="font-family: Consolas; font-size: 9.5pt;"><span style="mso-spacerun: yes;"> </span><span style="color: maroon;">#Test</span><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: navy; font-family: Consolas; font-size: 9.5pt;">SET</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="font-family: Consolas; font-size: 9.5pt;"><span style="mso-spacerun: yes;"> </span><span style="color: maroon;">Ranking</span> = NULL;<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="font-family: Consolas; font-size: 9.5pt;"><o:p><span style="font-family: Times New Roman; font-size: small;"> </span></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: navy; font-family: Consolas; font-size: 9.5pt;">DECLARE<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: navy; font-family: Consolas; font-size: 9.5pt;"><span style="mso-spacerun: yes;">
</span></span><span style="color: maroon; font-family: Consolas; font-size: 9.5pt;">@ID</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: navy;">INT</span>,<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: maroon; font-family: Consolas; font-size: 9.5pt;"><span style="mso-spacerun: yes;">
</span>@MemberID</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: navy;">INT</span>,<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: maroon; font-family: Consolas; font-size: 9.5pt;"><span style="mso-spacerun: yes;">
</span>@PrevMemberID</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: navy;">INT</span> = -1,<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: maroon; font-family: Consolas; font-size: 9.5pt;"><span style="mso-spacerun: yes;">
</span>@Ranking</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: navy;">INT</span> = 1;<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="font-family: Consolas; font-size: 9.5pt;"><o:p><span style="font-family: Times New Roman; font-size: small;"> </span></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: navy; font-family: Consolas; font-size: 9.5pt;">WHILE</span><span style="font-family: Consolas; font-size: 9.5pt;"> 1 = 1<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: navy; font-family: Consolas; font-size: 9.5pt;">BEGIN</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="font-family: Consolas; font-size: 9.5pt;"><span style="mso-spacerun: yes;"> </span><span style="color: navy;">SELECT</span> <span style="color: navy;">TOP</span> 1<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="font-family: Consolas; font-size: 9.5pt;"><span style="mso-spacerun: yes;"> </span><span style="color: maroon;">@ID</span> = <span style="color: maroon;">ID</span>,<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="font-family: Consolas; font-size: 9.5pt;"><span style="mso-spacerun: yes;"> </span><span style="color: maroon;">@MemberID</span> = <span style="color: maroon;">MemberID</span><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="font-family: Consolas; font-size: 9.5pt;"><span style="mso-spacerun: yes;"> </span><span style="color: navy;">FROM</span><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="font-family: Consolas; font-size: 9.5pt;"><span style="mso-spacerun: yes;"> </span><span style="color: maroon;">#Test</span><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="font-family: Consolas; font-size: 9.5pt;"><span style="mso-spacerun: yes;"> </span><span style="color: navy;">WHERE</span><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="font-family: Consolas; font-size: 9.5pt;"><span style="mso-spacerun: yes;"> </span><span style="color: maroon;">Ranking</span> IS NULL<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: navy; font-family: Consolas; font-size: 9.5pt;"><span style="mso-spacerun: yes;">
</span>ORDER</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: navy;">BY<o:p></o:p></span></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="font-family: Consolas; font-size: 9.5pt;"><span style="mso-spacerun: yes;"> </span><span style="color: maroon;">MemberID</span>,<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="font-family: Consolas; font-size: 9.5pt;"><span style="mso-spacerun: yes;"> </span><span style="color: maroon;">Score</span> <span style="color: navy;">DESC</span>;<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="font-family: Consolas; font-size: 9.5pt;"><o:p><span style="font-family: Times New Roman; font-size: small;"> </span></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: navy; font-family: Consolas; font-size: 9.5pt;"><span style="mso-spacerun: yes;">
</span>IF</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: purple;">@@ROWCOUNT</span> = 0<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: navy; font-family: Consolas; font-size: 9.5pt;"><span style="mso-spacerun: yes;">
</span><span style="mso-spacerun: yes;"> </span>BREAK</span><span style="font-family: Consolas; font-size: 9.5pt;">;<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="font-family: Consolas; font-size: 9.5pt;"><o:p><span style="font-family: Times New Roman; font-size: small;"> </span></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: navy; font-family: Consolas; font-size: 9.5pt;"><span style="mso-spacerun: yes;">
</span>IF</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: maroon;">@PrevMemberID</span> <> <span style="color: maroon;">@MemberID</span><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: navy; font-family: Consolas; font-size: 9.5pt;"><span style="mso-spacerun: yes;">
</span><span style="mso-spacerun: yes;"> </span>SET</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: maroon;">@Ranking</span>
= 1;<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="font-family: Consolas; font-size: 9.5pt;"><o:p><span style="font-family: Times New Roman; font-size: small;"> </span></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: navy; font-family: Consolas; font-size: 9.5pt;"><span style="mso-spacerun: yes;">
</span></span><span style="color: purple; font-family: Consolas; font-size: 9.5pt;">UPDATE</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: navy; font-family: Consolas; font-size: 9.5pt;"><span style="mso-spacerun: yes;">
</span><span style="mso-spacerun: yes;"> </span></span><span style="color: maroon; font-family: Consolas; font-size: 9.5pt;">#Test</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: navy; font-family: Consolas; font-size: 9.5pt;"><span style="mso-spacerun: yes;">
</span>SET</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: navy; font-family: Consolas; font-size: 9.5pt;"><span style="mso-spacerun: yes;">
</span><span style="mso-spacerun: yes;"> </span></span><span style="color: maroon; font-family: Consolas; font-size: 9.5pt;">Ranking</span><span style="font-family: Consolas; font-size: 9.5pt;"> = <span style="color: maroon;">@Ranking</span><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: navy; font-family: Consolas; font-size: 9.5pt;"><span style="mso-spacerun: yes;">
</span>WHERE</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: navy; font-family: Consolas; font-size: 9.5pt;"><span style="mso-spacerun: yes;">
</span><span style="mso-spacerun: yes;"> </span></span><span style="color: maroon; font-family: Consolas; font-size: 9.5pt;">ID</span><span style="font-family: Consolas; font-size: 9.5pt;"> = <span style="color: maroon;">@ID</span>;<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="font-family: Consolas; font-size: 9.5pt;"><o:p><span style="font-family: Times New Roman; font-size: small;"> </span></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: navy; font-family: Consolas; font-size: 9.5pt;"><span style="mso-spacerun: yes;">
</span>SET</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: maroon;">@PrevMemberID</span> = <span style="color: maroon;">@MemberID</span>;<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="font-family: Consolas; font-size: 9.5pt;"><o:p><span style="font-family: Times New Roman; font-size: small;"> </span></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: navy; font-family: Consolas; font-size: 9.5pt;"><span style="mso-spacerun: yes;">
</span>SET</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: maroon;">@Ranking</span> = <span style="color: maroon;">@Ranking</span>
+ 1;<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: navy; 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: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="font-family: Consolas; font-size: 9.5pt;"><o:p><span style="font-family: Times New Roman; font-size: small;"> </span></o:p></span></div>
<span style="font-family: "Calibri","sans-serif";">which actually means</span><o:p></o:p><br />
<o:p> </o:p><br />
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: navy; font-family: Consolas; font-size: 9.5pt;">WITH</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: maroon;">CTE</span> <span style="color: navy;">AS </span>(<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: navy; font-family: Consolas; font-size: 9.5pt;"><span style="mso-spacerun: yes;">
</span>SELECT</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: navy; font-family: Consolas; font-size: 9.5pt;"><span style="mso-spacerun: yes;">
</span><span style="mso-spacerun: yes;"> </span></span><span style="color: maroon; font-family: Consolas; font-size: 9.5pt;">ID</span><span style="font-family: Consolas; font-size: 9.5pt;">,<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: navy; font-family: Consolas; font-size: 9.5pt;"><span style="mso-spacerun: yes;">
</span><span style="mso-spacerun: yes;"> </span></span><span style="color: purple; font-family: Consolas; font-size: 9.5pt;">ROW_NUMBER</span><span style="font-family: Consolas; font-size: 9.5pt;">() <span style="color: navy;">OVER </span>(<span style="color: navy;">PARTITION</span> <span style="color: navy;">BY</span> <span style="color: maroon;">MemberID</span> <span style="color: navy;">ORDER</span> <span style="color: navy;">BY</span> <span style="color: maroon;">Score</span> <span style="color: navy;">DESC</span>) <span style="color: navy;">AS</span> <span style="color: maroon;">Ranking</span><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: navy; font-family: Consolas; font-size: 9.5pt;"><span style="mso-spacerun: yes;">
</span>FROM</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: navy; font-family: Consolas; font-size: 9.5pt;"><span style="mso-spacerun: yes;">
</span><span style="mso-spacerun: yes;"> </span></span><span style="color: maroon; font-family: Consolas; font-size: 9.5pt;">#Test</span><span style="font-family: Consolas; font-size: 9.5pt;">)<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: purple; font-family: Consolas; font-size: 9.5pt;">UPDATE</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: navy; font-family: Consolas; font-size: 9.5pt;"><span style="mso-spacerun: yes;">
</span></span><span style="color: maroon; font-family: Consolas; font-size: 9.5pt;">t</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: navy; font-family: Consolas; font-size: 9.5pt;">SET</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: navy; font-family: Consolas; font-size: 9.5pt;"><span style="mso-spacerun: yes;">
</span></span><span style="color: maroon; font-family: Consolas; font-size: 9.5pt;">Ranking</span><span style="font-family: Consolas; font-size: 9.5pt;"> = <span style="color: maroon;">t</span>.<span style="color: maroon;">Ranking</span><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: navy; font-family: Consolas; font-size: 9.5pt;">FROM</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: navy; font-family: Consolas; font-size: 9.5pt;"><span style="mso-spacerun: yes;">
</span></span><span style="color: maroon; font-family: Consolas; font-size: 9.5pt;">#Test</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: maroon;">t</span><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="font-family: Consolas; font-size: 9.5pt;">INNER JOIN<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: navy; font-family: Consolas; font-size: 9.5pt;"><span style="mso-spacerun: yes;">
</span></span><span style="color: maroon; font-family: Consolas; font-size: 9.5pt;">CTE</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: maroon;">a</span><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: navy; font-family: Consolas; font-size: 9.5pt;"><span style="mso-spacerun: yes;">
</span><span style="mso-spacerun: yes;"> </span>ON</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: maroon;">a</span>.<span style="color: maroon;">ID</span> = <span style="color: maroon;">t</span>.<span style="color: maroon;">ID</span>;<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="font-family: Consolas; font-size: 9.5pt;"><o:p><span style="font-family: Times New Roman; font-size: small;"> </span></o:p></span></div>
<o:p> </o:p>Anonymoushttp://www.blogger.com/profile/15769604002687268779noreply@blogger.com2tag:blogger.com,1999:blog-7129367241107972697.post-599923313572192582013-05-20T12:04:00.000+02:002013-05-20T12:09:57.387+02:00Fixing Database Corruption in a VLDB<span style="font-family: Calibri;">A client of mine phoned me last week with an error message that had appeared in their application. My worst fears were confirmed when I heard the words "logical consistency-based I/O error".</span><br />
<span style="font-family: Calibri;"></span><br />
<span style="font-family: Calibri;">The error message reported was:</span><br />
<span style="color: #660000; font-family: "Helvetica Neue", Arial, Helvetica, sans-serif; font-size: x-small;">SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x31b2521f; actual: 0x4bd50f4c). It occurred during a read of page (3:8191715) in database ID 17 at offset 0x00000f9fdc6000 in file 'C:\MSSQL\Data\Data2.ndf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.</span><br />
<span style="font-family: Arial; font-size: x-small;"></span><br />
<a name='more'></a><br />
<span style="font-family: Calibri;">The first thing to do is identify the database using the database ID presented in the error message, which in my case is 17.</span><br />
<span style="font-family: Calibri;"></span><br />
<span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;">USE</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;">master</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;">;</span></span><br />
<span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;">GO</span> </span></span><br />
<span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;">SELECT</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> *</span></span><br />
<span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;">FROM</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: green; font-family: Consolas; font-size: x-small;"><span style="color: green; font-family: Consolas; font-size: x-small;"><span style="color: green; font-family: Consolas; font-size: x-small;">sys</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;">.</span></span><span style="color: green; font-family: Consolas; font-size: x-small;"><span style="color: green; font-family: Consolas; font-size: x-small;"><span style="color: green; font-family: Consolas; font-size: x-small;">databases</span></span></span><br />
<span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;">WHERE</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: maroon; font-family: Consolas; font-size: x-small;"><span style="color: maroon; font-family: Consolas; font-size: x-small;"><span style="color: maroon; font-family: Consolas; font-size: x-small;">database_id</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> = 17;</span></span></span> </span></span><br />
<br />
<span style="font-family: Calibri;">So my corrupted database is a VLDB and the client will not be happy with the amount of downtime required to perform DBCC CHECKDB. The last full backup was taken almost 18 hours earlier and the client does not want to lose a full day's worth of work. The database is in simple recovery model so page level restores are out of the question. My task was simple - recover the page without losing data and without serious down time.</span><br />
<span style="font-family: Calibri;"></span><br />
<span style="font-family: Calibri;">My solution was actually rather simple, but it requires you to have a good copy of the corrupted data. This can be a recent backup taken before the corruption or, as in my case, a subscriber with a valid copy of the data.</span><br />
<span style="font-family: Calibri;"></span><br />
<span style="font-family: Calibri;">The very first step for me was to backup the corrupted database and restore it onto another server. The purpose of this is to run a full DBCC CHECKDB to identify if we have more corruption somewhere in the database.</span><br />
<span style="font-family: Calibri;"></span><br />
<span style="font-family: Calibri;"><span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;">DBCC</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: maroon; font-family: Consolas; font-size: x-small;"><span style="color: maroon; font-family: Consolas; font-size: x-small;"><span style="color: maroon; font-family: Consolas; font-size: x-small;">CHECKDB</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;">() </span></span><span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;">WITH</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;">NO_INFOMSGS</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;">, </span></span><span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;">ALL_ERRORMSGS;</span></span></span></span><br />
<span style="color: navy; font-family: Consolas; font-size: x-small;"></span><span style="font-family: Calibri;"></span><br />
<span style="font-family: Calibri;">This would take an estimated 16 hours so we can check the results later. I was lucky enough to have a situation where I had no further corruption.</span><br />
<span style="font-family: Calibri;"></span><br />
<span style="font-family: Calibri;">The next step is to identify the object in the error message. The error message states which page is corrupted, in our case (3:8191715). We can use the undocumented command DBCC PAGE to fetch the page header.</span><br />
<span style="font-family: Calibri;"></span><br />
<span style="font-family: Calibri;"><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;">-- Trace to send output to screen</span></span></span></span><br />
<span style="font-family: Calibri;"><span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;">DBCC</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: maroon; font-family: Consolas; font-size: x-small;"><span style="color: maroon; font-family: Consolas; font-size: x-small;"><span style="color: maroon; font-family: Consolas; font-size: x-small;">TRACEON</span></span></span><span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;"> </span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;">(3604)</span></span><br />
<span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;">-- and print the page contents on the screen<br />
-- DBCC PAGE (database_id, file_id, page_id, print option)</span></span></span></span><br />
<span style="font-family: Calibri;"><span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;">DBCC</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;">PAGE </span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;">(17, 3, 8191715, 1);</span></span></span><br />
<br />
<span style="font-family: Calibri;">In the page header you will find Metadata: ObjectID and Metadata: IndexID. It is important to note that if you have any IndexID > 1 then it refers to a secondary index and you can simply drop and recreate the affected index. IndexID 0 refers to a heap and IndexID 1 refers to a clustered index in which case we will need to fix the problem. I was dealing with a clustered index on a large table.</span><br />
<span style="font-family: Calibri;"></span><br />
<span style="font-family: Calibri;">Now we know which table is affected we must first identify any further corrupt pages in our table. We can use DBCC CHECKTABLE to check one table.</span><br />
<span style="font-family: Calibri;"></span><br />
<span style="font-size: x-small;"><span style="font-family: Consolas;"><span style="color: navy;"><span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;">DBCC</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: maroon; font-family: Consolas; font-size: x-small;"><span style="color: maroon; font-family: Consolas; font-size: x-small;"><span style="color: maroon; font-family: Consolas; font-size: x-small;">CHECKTABLE</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;">(</span></span><span style="color: olive; font-family: Consolas; font-size: x-small;"><span style="color: olive; font-family: Consolas; font-size: x-small;"><span style="color: olive; font-family: Consolas; font-size: x-small;">'MyTable'</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;">, </span></span><span style="color: maroon; font-family: Consolas; font-size: x-small;"><span style="color: maroon; font-family: Consolas; font-size: x-small;"><span style="color: maroon; font-family: Consolas; font-size: x-small;">NOINDEX</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;">) </span></span><span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;">WITH</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;">NO_INFOMSGS</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;">, </span></span><span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;">ALL_ERRORMSGS;</span></span></span></span></span></span><br />
<span style="color: navy; font-family: Consolas; font-size: x-small;"></span><span style="font-family: Calibri;"></span><br />
<span style="font-family: Calibri;">In my case I was dealing with only one page. If you have more pages then repeat the next step for each page BEFORE running the repair.</span><br />
<span style="font-family: Calibri;"></span><br />
<span style="font-family: Calibri;">Now we know which pages are corrupt we must find out which rows are on this page. First find out what columns your clustered key is made up of, in my case the column ID. We must now output the contents of the page using print option 3 in DBCC PAGE.</span><br />
<span style="font-family: Calibri;"></span><br />
<span style="font-family: Calibri;"><span style="font-family: Calibri;"><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;">-- Trace to send output to screen</span></span></span></span></span><br />
<span style="font-family: Calibri;"><span style="font-family: Calibri;"><span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;">DBCC</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: maroon; font-family: Consolas; font-size: x-small;"><span style="color: maroon; font-family: Consolas; font-size: x-small;"><span style="color: maroon; font-family: Consolas; font-size: x-small;">TRACEON</span></span></span><span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;"> </span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;">(3604)</span></span><br />
<span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;">-- and print the page contents on the screen<br />
-- DBCC PAGE (database_id, file_id, page_id, print option)</span></span></span></span></span><br />
<span style="font-family: Calibri;"><span style="font-family: Calibri;"><span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;">DBCC</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;">PAGE </span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;">(17, 3, 8191715, 3);</span></span></span></span><br />
<span style="font-family: Calibri;"></span><br />
<span style="font-family: Calibri;">The output of this command will list the page slots, so look for "Slot 0 column 1" after the header. You will find the value of the first ID column, in my case 36240, and at the end of the page look for column 1 in the last Slot number and you will find the largest ID for that page, in my case 36450. Now you know which rows are in the affected page you can fetch those rows from a valid backup or other valid copy.<br />
<br />
<span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: xx-small;"><span style="font-size: x-small;"><span style="color: navy;"><span style="font-family: Consolas; font-size: xx-small;"><span style="font-size: x-small;"><span style="color: navy;">INSERT INTO</span><span style="font-family: Consolas;"><span style="font-family: Consolas;"> </span></span><span style="color: maroon; font-family: Consolas;"><span style="color: maroon; font-family: Consolas;"><span style="color: maroon; font-family: Consolas;">MyTable_BACKUP</span></span></span></span><br />
<span style="font-size: x-small;"><span style="color: navy;">SELECT</span><span style="font-family: Consolas;"><span style="font-family: Consolas;"> * </span></span><span style="color: navy; font-family: Consolas;"><span style="color: navy; font-family: Consolas;"><span style="color: navy; font-family: Consolas;">FROM</span></span></span><span style="font-family: Consolas;"><span style="font-family: Consolas;"> </span></span><span style="color: maroon; font-family: Consolas;"><span style="color: maroon; font-family: Consolas;"><span style="color: maroon; font-family: Consolas;">MyTable </span></span></span></span></span></span></span></span></span></span></span><span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;">WHERE</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: maroon; font-family: Consolas; font-size: x-small;"><span style="color: maroon; font-family: Consolas; font-size: x-small;"><span style="color: maroon; font-family: Consolas; font-size: x-small;">ID</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> BETWEEN 36240 AND 36450;</span></span><br />
<br />
Copy the data into a staging table and run the repair, which will delete the corrupted page from the database file.<br />
<br />
<span style="font-size: x-small;"><span style="font-family: Consolas;"><span style="color: navy;"><span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;">DBCC</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: maroon; font-family: Consolas; font-size: x-small;"><span style="color: maroon; font-family: Consolas; font-size: x-small;"><span style="color: maroon; font-family: Consolas; font-size: x-small;">CHECKTABLE</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;">(</span></span><span style="color: olive; font-family: Consolas; font-size: x-small;"><span style="color: olive; font-family: Consolas; font-size: x-small;"><span style="color: olive; font-family: Consolas; font-size: x-small;">'MyTable'</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;">) </span></span><span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;">WITH</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;"><span style="color: navy; font-family: Consolas; font-size: x-small;">ALLOW_DATA_LOSS;</span></span></span></span></span></span></span><br />
<span style="font-family: Calibri;"><span style="color: navy; font-family: Consolas; font-size: x-small;"></span><br />
Now the page is gone we can simply restore the missing rows from our backup.<br />
<span style="font-family: Consolas; font-size: xx-small;"><br />
<span style="font-family: Consolas; font-size: xx-small;"><span style="font-size: x-small;"><span style="color: navy;">INSERT INTO</span><span style="font-family: Consolas;"><span style="font-family: Consolas;"> </span></span><span style="color: maroon; font-family: Consolas;"><span style="color: maroon; font-family: Consolas;"><span style="color: maroon; font-family: Consolas;">MyTable</span></span></span></span><br />
<span style="font-size: x-small;"><span style="color: navy;">SELECT</span><span style="font-family: Consolas;"><span style="font-family: Consolas;"> * </span></span><span style="color: navy; font-family: Consolas;"><span style="color: navy; font-family: Consolas;"><span style="color: navy; font-family: Consolas;">FROM</span></span></span><span style="font-family: Consolas;"><span style="font-family: Consolas;"> </span></span><span style="color: maroon; font-family: Consolas;"><span style="color: maroon; font-family: Consolas;"><span style="color: maroon; font-family: Consolas;">MyTable_BACKUP;</span></span></span></span></span></span><br />
<span style="font-family: Consolas; font-size: xx-small;"><span style="font-family: Consolas; font-size: xx-small;"><br />
</span></span>Your VLDB is now fixed. <br />
<br />
On a side note, if you have replication as I do then you will need to delete the missing rows from the subscriber before inserting them into the repaired table. You do not need to disable replication while running the repair.</span><br />
<span style="font-family: Calibri;"></span><br />Anonymoushttp://www.blogger.com/profile/15769604002687268779noreply@blogger.com0tag:blogger.com,1999:blog-7129367241107972697.post-4208901613224256992013-02-18T14:22:00.000+02:002013-02-18T14:22:08.213+02:00Heaps Of Trouble<span style="font-family: Calibri;">I recently came across a situation at a client which I
thought I would share with you. I came across this while doing some routine
maintenance and noticed a small table consuming a lot of memory. I mean this
table, which is a smallish lookup table, was consuming almost 430 MB of buffer
space. The thing about lookup tables and the way they are used is that the
entire table is usually read into memory. This is by design but the problem
here is that there should be no way that this table needs 430 MB.<o:p></o:p></span><br />
<br />
<a name='more'></a><br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Calibri;">First I will check the structure of this table. This table is a heap with no indexes at all and the table only contains 3
columns, each of which is a varchar, and they are 1, 15 and 150 characters in
length. This means the maximum possible size of a row is 166 bytes (plus a couple of bytes for overhead) which is not very
big at all.<o:p></o:p></span></div>
<span style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">CREATE</span><span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"> <span style="color: blue;">TABLE</span> Codes<o:p></o:p></span><br />
<span style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><span style="mso-tab-count: 1;"> </span></span><span style="color: grey; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">(</span><span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">Code <span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span>15<span style="color: grey;">)</span> <span style="color: grey;">NOT</span> <span style="color: grey;">NULL,<o:p></o:p></span></span><br />
<span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><span style="mso-tab-count: 1;"> </span>Category <span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span>1<span style="color: grey;">)</span> <span style="color: grey;">NOT</span> <span style="color: grey;">NULL,<o:p></o:p></span></span><br />
<span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><span style="mso-tab-count: 1;"> </span>Condition <span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span>150<span style="color: grey;">)</span> <span style="color: grey;">NOT</span> <span style="color: grey;">NULL);<o:p></o:p></span></span><br />
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Calibri;">OK so how much data is stored in this table? A simple SELECT
COUNT(*) reveals 42,997 rows in this table. Whoa, hang on a second. If we had
42,997 rows all using the maximum possible row size we would have the
following:<o:p></o:p></span></div>
<span style="font-family: Calibri;">42997 * 166 = 7137502 bytes <o:p></o:p></span><br />
<span style="font-family: Calibri;">7137502 / 1024 = 6970 kilobytes<o:p></o:p></span><br />
<span style="font-family: Calibri;">6970 / 1024 = 6.8 megabytes<o:p></o:p></span><br />
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Calibri;">6.8 MB is somewhat different to the 430 MB the table is
currently using. What is going on?<o:p></o:p></span></div>
<span style="font-family: Calibri;">Next I want to have a look at sp_spaceused to see what the
system will report:<o:p></o:p></span><br />
<br />
<div class="MsoNoSpacing" style="margin: 0cm 0cm 0pt;">
<span style="font-family: Calibri;">rows – 42997<o:p></o:p></span></div>
<span style="font-family: Calibri;">reserved – 517 MB<o:p></o:p></span><br />
<span style="font-family: Calibri;">data – 430 MB<o:p></o:p></span><br />
<span style="font-family: Calibri;">index_size – 32 KB<o:p></o:p></span><br />
<span style="font-family: Calibri;">unused – 86 MB<o:p></o:p></span><br />
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Calibri;">Obviously something is terribly wrong. I want to have a look
at index physical stats.<o:p></o:p></span></div>
<span style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">SELECT</span><span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"> index_id<span style="color: grey;">,</span> index_type_desc<span style="color: grey;">,</span>
avg_fragmentation_in_percent<span style="color: grey;">,</span>
avg_page_space_used_in_percent<span style="color: grey;">,</span> page_count<span style="color: grey;">,</span> record_count<o:p></o:p></span><br />
<span style="color: blue; font-family: "Courier New"; font-size: 10pt; line-height: 115%; mso-no-proof: yes;">FROM</span><span style="font-family: "Courier New"; font-size: 10pt; line-height: 115%; mso-no-proof: yes;"> <span style="color: green;">sys</span><span style="color: grey;">.</span><span style="color: green;">dm_db_index_physical_stats</span><span style="color: grey;">(</span><span style="color: magenta;">DB_ID</span><span style="color: grey;">(),</span> <span style="color: magenta;">OBJECT_ID</span><span style="color: grey;">(</span><span style="color: red;">'Codes'</span><span style="color: grey;">),</span> <span style="color: grey;">NULL,</span> <span style="color: grey;">NULL,</span> <span style="color: red;">'DETAILED'</span><span style="color: grey;">)<o:p></o:p></span></span><br />
<br />
<div class="MsoNoSpacing" style="margin: 0cm 0cm 0pt;">
<span style="font-family: Calibri;">avg_fragmentation_in_percent – 30.9%<o:p></o:p></span></div>
<span style="font-family: Calibri;">avg_page_space_used_in_percent – 4.02%<o:p></o:p></span><br />
<span style="font-family: Calibri;">page_count – 55,143<o:p></o:p></span><br />
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Calibri;">Well, well, well. The table storage is not normal at all.
Fragmentation, at 31%, is not too bad but this table has 55,143 pages to store
43,647 rows and on average only 4% of each page has data. In other words we are
using 430MB worth of data pages to store about 6MB worth of data. Not only do
we waste 424MB of disk space, we also waste 424MB of memory and we also have to
read that 430MB from disk to get to our 6MB of data. Any reduction in IO will usually benefit the entire database server.</span>
</div>
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Calibri;">The cause of this problem is documented in Books Online:<o:p></o:p></span></div>
<span style="color: black; font-family: "Segoe UI","sans-serif"; font-size: 9.5pt;"><span style="color: #0b5394;">When
rows are deleted from a heap the Database Engine may use row or page locking
for the operation. As a result, the pages made empty by the delete operation
remain allocated to the heap. When empty pages are not deallocated, the
associated space cannot be reused by other objects in the database. To delete
rows in a heap and deallocate pages, use one of the following methods.<o:p></o:p></span></span><br />
<span style="color: #0b5394;">
<span style="color: black; font-family: Symbol; font-size: 10pt; mso-bidi-font-family: Symbol; mso-bidi-font-size: 9.5pt; mso-fareast-font-family: Symbol;"><span style="mso-list: Ignore;"></span></span></span><br />
<span style="color: #0b5394;"><span style="color: black; font-family: Symbol; font-size: 10pt; mso-bidi-font-family: Symbol; mso-bidi-font-size: 9.5pt; mso-fareast-font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";">
</span></span></span><span style="color: black; font-family: "Segoe UI","sans-serif"; font-size: 9.5pt;"><span style="color: #0b5394;">Specify the TABLOCK hint in the DELETE statement. Using the
TABLOCK hint causes the delete operation to take a shared lock on the table
instead of a row or page lock. This allows the pages to be deallocated</span>.<o:p></o:p></span></span><br />
<span style="color: #0b5394;">
<span style="color: black; font-family: Symbol; font-size: 10pt; mso-bidi-font-family: Symbol; mso-bidi-font-size: 9.5pt; mso-fareast-font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";">
</span></span></span></span><span style="color: #0b5394;"><span style="color: black; font-family: "Segoe UI","sans-serif"; font-size: 9.5pt;"><span style="color: #0b5394;">Use</span><span style="color: #0b5394;"> TRUNCATE TABLE if all rows are to be deleted from the table</span>.<o:p></o:p></span></span><br />
<span style="color: #0b5394;">
<span style="color: black; font-family: Symbol; font-size: 10pt; mso-bidi-font-family: Symbol; mso-bidi-font-size: 9.5pt; mso-fareast-font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";">
</span></span></span></span><span style="color: #0b5394;"><span style="color: black; font-family: "Segoe UI","sans-serif"; font-size: 9.5pt;"><span style="color: #0b5394;">Create a clustered index on the heap before deleting the rows. You
can drop the clustered index after the rows are deleted. This method is more
time consuming than the previous methods and uses more temporary resources</span>.<o:p></o:p></span></span><br />
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Calibri;">Now I know this table gets loaded from another system on a
daily basis. I had a look at the procedure that loads the table and saw the
following:<o:p></o:p></span></div>
<span style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">DELETE</span><span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"> <span style="color: blue;">FROM</span> Codes<span style="color: grey;">;<o:p></o:p></span></span><br />
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">INSERT</span><span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"> <span style="color: blue;">INTO</span> Codes<o:p></o:p></span></div>
<span style="color: blue; font-family: "Courier New"; font-size: 10pt; line-height: 115%; mso-no-proof: yes;">SELECT</span><span style="font-family: "Courier New"; font-size: 10pt; line-height: 115%; mso-no-proof: yes;"> <span style="color: grey;">......<o:p></o:p></span></span><br />
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Calibri;">This table has had pages being allocated to it on a daily
basis and the delete statement ensured that pages would not be de-allocated. </span><span style="font-family: Calibri;">In my particular case I am going to create a clustered index
on this table to remedy the situation and to prevent a reccurance. Let’s see what that has done for our storage.<o:p></o:p></span></div>
<span style="font-family: Calibri;">rows – 42,997<o:p></o:p></span><br />
<span style="font-family: Calibri;">reserved – 3,624 KB<o:p></o:p></span><br />
<span style="font-family: Calibri;">data – 3,408 KB<o:p></o:p></span><br />
<span style="font-family: Calibri;">index_size – 56 KB<o:p></o:p></span><br />
<span style="font-family: Calibri;">unused – 160 KB<o:p></o:p></span><br />
<br />
<div class="MsoNoSpacing" style="margin: 0cm 0cm 0pt;">
<span style="font-family: Calibri;">avg_fragmentation_in_percent – 0.2%<o:p></o:p></span></div>
<span style="font-family: Calibri;">avg_page_space_used_in_percent – 99%<o:p></o:p></span><br />
<span style="font-family: Calibri;">page_count – 426<o:p></o:p></span><br />
<br />
<div class="MsoNoSpacing" style="margin: 0cm 0cm 0pt;">
<span style="font-family: Calibri;">Bang, I’ve just scored 430MB of RAM for free.<o:p></o:p></span></div>
Anonymoushttp://www.blogger.com/profile/15769604002687268779noreply@blogger.com3tag:blogger.com,1999:blog-7129367241107972697.post-58628604178519552292013-02-18T14:18:00.004+02:002013-06-13T09:14:02.279+02:00SQL Server 2012: Restore Fails With Deadlock<span style="font-family: Verdana, sans-serif;">We had an interesting situation at a client where we could not restore a SQL Server 2008 R2 backup onto SQL Server 2012. The restore would fail at 100% with a deadlock.</span><br />
<span style="font-family: Verdana, sans-serif;"></span><br />
<a name='more'></a><br />
<span style="font-family: Verdana, sans-serif;">Here is what we saw:</span><br />
<br />
<span style="color: #444444; font-family: "Courier New", Courier, monospace;">10 percent processed.<br />20 percent processed.<br />30 percent processed.<br />40 percent processed.<br />50 percent processed.<br />60 percent processed.<br />70 percent processed.<br />80 percent processed.<br />90 percent processed.<br />100 percent processed.<br />Processed 2873048 pages for database 'TestRestore', file 'SourceDb' on file 1.<br />Processed 3 pages for database 'TestRestore', file 'SourceDb_log' on file 1.<br />Msg 1205, Level 13, State 51, Line 1<br />Transaction (Process ID 183) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.<br />Msg 3013, Level 16, State 1, Line 1<br />RESTORE DATABASE is terminating abnormally.</span><br />
<br />
<span style="font-family: Verdana, sans-serif;">We then ran a trace to get a deadlock graph. Frustratingly this was what our graph showed:</span><br />
<span style="font-family: Verdana;"></span><br />
<span style="color: #444444; font-family: "Courier New", Courier, monospace;">Failed to initializa deadlock control.<br /> There is an error in the XML document (1,2).<br /> <deadlock-list xmlns=''> was not expected.</span><br />
<span style="color: #444444; font-family: Courier New;"></span><br />
<span style="font-family: Verdana, sans-serif;">However the TextData portion within profiler showed something interesting. Here is a snippet. </span><br />
<span style="font-family: Verdana;"></span><span style="font-family: Verdana;"></span><br />
<span style="color: #444444; font-family: "Courier New", Courier, monospace;">clientapp="Microsoft SQL Server Management Studio - Transact-SQL IntelliSense"</span><br />
<br />
<span style="font-family: Verdana, sans-serif;">So IntelliSense is blocking the restore!</span><br />
<span style="font-family: Verdana, sans-serif;"></span><br />
<span style="font-family: Verdana, sans-serif;">With some help from </span><a href="http://www.sqlservercentral.com/" target="_blank"><span style="font-family: Verdana, sans-serif;">www.sqlservercentral.com</span></a><span style="font-family: Verdana, sans-serif;"> we found it was indeed a bug.</span><br />
<br />
<span style="font-family: Verdana, sans-serif;">Here is the knowledgebase article: </span><a href="http://support.microsoft.com/kb/2725950" target="_blank"><span style="font-family: Verdana, sans-serif;">http://support.microsoft.com/kb/2725950</span></a><span style="font-family: Verdana, sans-serif;"> </span><br />
<span style="font-family: Verdana, sans-serif;"></span><br />
<span style="font-family: Verdana, sans-serif;">And if you are also battling with this bug, here is the cumulative update with the fix: </span><a href="http://support.microsoft.com/kb/2723749" target="_blank"><span style="font-family: Verdana, sans-serif;">http://support.microsoft.com/kb/2723749</span></a><br />
<span style="font-family: Verdana, sans-serif;"></span><br />
<br />Anonymoushttp://www.blogger.com/profile/15769604002687268779noreply@blogger.com1tag:blogger.com,1999:blog-7129367241107972697.post-68139205611396200162013-02-18T14:13:00.001+02:002013-02-18T14:15:14.198+02:00Isolation Levels<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Calibri;">As a follow up to my NOLOCK blog I thought it would be a
good idea to discuss the five different isolation levels in SQL Server. These
are as follows:<o:p></o:p></span></div>
<a name='more'></a><br />
<div class="MsoNoSpacing" style="margin: 0cm 0cm 0pt 36pt; mso-list: l0 level1 lfo1; text-indent: -18pt;">
<span style="font-family: Symbol; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";">
</span></span></span><span style="font-family: Calibri;">Read Uncommitted – Lowest Level<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin: 0cm 0cm 0pt 72pt; mso-list: l0 level2 lfo1; text-indent: -18pt;">
<span style="font-family: "Courier New"; mso-fareast-font-family: "Courier New";"><span style="mso-list: Ignore;">o<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";"> </span></span></span><span style="font-family: Calibri;">Can
read uncommitted modifications<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin: 0cm 0cm 0pt 72pt; mso-list: l0 level2 lfo1; text-indent: -18pt;">
<span style="font-family: "Courier New"; mso-fareast-font-family: "Courier New";"><span style="mso-list: Ignore;">o<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";"> </span></span></span><span style="font-family: Calibri;">Does
not issue shared locks<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin: 0cm 0cm 0pt 72pt; mso-list: l0 level2 lfo1; text-indent: -18pt;">
<span style="font-family: "Courier New"; mso-fareast-font-family: "Courier New";"><span style="mso-list: Ignore;">o<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";"> </span></span></span><span style="font-family: Calibri;">Is
not blocked by exclusive locks<o:p></o:p></span></div>
<br />
<div class="MsoNoSpacing" style="margin: 0cm 0cm 0pt 36pt; mso-list: l0 level1 lfo1; text-indent: -18pt;">
<span style="font-family: Symbol; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";">
</span></span></span><span style="font-family: Calibri;">Read Committed – SQL Server Default<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin: 0cm 0cm 0pt 72pt; mso-list: l0 level2 lfo1; text-indent: -18pt;">
<span style="font-family: "Courier New"; mso-fareast-font-family: "Courier New";"><span style="mso-list: Ignore;">o<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";"> </span></span></span><span style="font-family: Calibri;">Cannot
read uncommitted modifications<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin: 0cm 0cm 0pt 72pt; mso-list: l0 level2 lfo1; text-indent: -18pt;">
<span style="font-family: "Courier New"; mso-fareast-font-family: "Courier New";"><span style="mso-list: Ignore;">o<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";"> </span></span></span><span style="font-family: Calibri;">Issues
shared locks when READ_COMMITTED_SNAPSHOT is set to OFF<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin: 0cm 0cm 0pt 108pt; mso-list: l0 level3 lfo1; text-indent: -18pt;">
<span style="font-family: Wingdings; mso-bidi-font-family: Wingdings; mso-fareast-font-family: Wingdings;"><span style="mso-list: Ignore;">§<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";"> </span></span></span><span style="font-family: Calibri;">Row
locks released before reading next row<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin: 0cm 0cm 0pt 108pt; mso-list: l0 level3 lfo1; text-indent: -18pt;">
<span style="font-family: Wingdings; mso-bidi-font-family: Wingdings; mso-fareast-font-family: Wingdings;"><span style="mso-list: Ignore;">§<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";"> </span></span></span><span style="font-family: Calibri;">Page
locks released when reading next page<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin: 0cm 0cm 0pt 108pt; mso-list: l0 level3 lfo1; text-indent: -18pt;">
<span style="font-family: Wingdings; mso-bidi-font-family: Wingdings; mso-fareast-font-family: Wingdings;"><span style="mso-list: Ignore;">§<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";"> </span></span></span><span style="font-family: Calibri;">Table
locks released at the end of the statement<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin: 0cm 0cm 0pt 72pt; mso-list: l0 level2 lfo1; text-indent: -18pt;">
<span style="font-family: "Courier New"; mso-fareast-font-family: "Courier New";"><span style="mso-list: Ignore;">o<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";"> </span></span></span><span style="font-family: Calibri;">Uses
row versioning when </span><span style="color: black; font-family: "Segoe UI","sans-serif"; font-size: 9.5pt;">READ_COMMITTED_SNAPSHOT is set to ON</span><o:p></o:p></div>
<br />
<div class="MsoNoSpacing" style="margin: 0cm 0cm 0pt 36pt; mso-list: l0 level1 lfo1; text-indent: -18pt;">
<span style="font-family: Symbol; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";">
</span></span></span><span style="font-family: Calibri;">Repeatable Read<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin: 0cm 0cm 0pt 72pt; mso-list: l0 level2 lfo1; text-indent: -18pt;">
<span style="font-family: "Courier New"; mso-fareast-font-family: "Courier New";"><span style="mso-list: Ignore;">o<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";"> </span></span></span><span style="font-family: Calibri;">Cannot
read uncommitted modifications<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin: 0cm 0cm 0pt 72pt; mso-list: l0 level2 lfo1; text-indent: -18pt;">
<span style="font-family: "Courier New"; mso-fareast-font-family: "Courier New";"><span style="mso-list: Ignore;">o<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";"> </span></span></span><span style="font-family: Calibri;">Issues
shared locks on all data read<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin: 0cm 0cm 0pt 108pt; mso-list: l0 level3 lfo1; text-indent: -18pt;">
<span style="font-family: Wingdings; mso-bidi-font-family: Wingdings; mso-fareast-font-family: Wingdings;"><span style="mso-list: Ignore;">§<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";"> </span></span></span><span style="font-family: Calibri;">All
locks are released at the end of the statement<o:p></o:p></span></div>
<br />
<div class="MsoNoSpacing" style="margin: 0cm 0cm 0pt 36pt; mso-list: l0 level1 lfo1; text-indent: -18pt;">
<span style="font-family: Symbol; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";">
</span></span></span><span style="font-family: Calibri;">Snapshot<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin: 0cm 0cm 0pt 72pt; mso-list: l0 level2 lfo1; text-indent: -18pt;">
<span style="font-family: "Courier New"; mso-fareast-font-family: "Courier New";"><span style="mso-list: Ignore;">o<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";"> </span></span></span><span style="font-family: Calibri;">Data
read by any statement in a transaction will be consistent with the data at the
beginning of the transaction<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin: 0cm 0cm 0pt 72pt; mso-list: l0 level2 lfo1; text-indent: -18pt;">
<span style="font-family: "Courier New"; mso-fareast-font-family: "Courier New";"><span style="mso-list: Ignore;">o<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";"> </span></span></span><span style="font-family: Calibri;">Modifications
by other transactions after the start of the transaction are not visible to
statements in the current transaction<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin: 0cm 0cm 0pt 72pt; mso-list: l0 level2 lfo1; text-indent: -18pt;">
<span style="font-family: "Courier New"; mso-fareast-font-family: "Courier New";"><span style="mso-list: Ignore;">o<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";"> </span></span></span><span style="font-family: Calibri;">Does
not issue locks when reading data<o:p></o:p></span></div>
<div class="MsoNoSpacing" style="margin: 0cm 0cm 0pt 72pt; mso-list: l0 level2 lfo1; text-indent: -18pt;">
<span style="font-family: "Courier New"; mso-fareast-font-family: "Courier New";"><span style="mso-list: Ignore;">o<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";"> </span></span></span><span style="font-family: Calibri;">The
ALLOW_SNAPSHOT_ISOLATION database option must be set to ON<o:p></o:p></span></div>
<br />
<div class="MsoListParagraphCxSpFirst" style="margin: 0cm 0cm 0pt 36pt; mso-list: l0 level1 lfo1; text-indent: -18pt;">
<span style="font-family: Symbol; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";">
</span></span></span><span style="font-family: Calibri;">Serializable – Highest Level<o:p></o:p></span></div>
<div class="MsoListParagraphCxSpMiddle" style="margin: 0cm 0cm 0pt 72pt; mso-add-space: auto; mso-list: l0 level2 lfo1; text-indent: -18pt;">
<span style="font-family: "Courier New"; mso-fareast-font-family: "Courier New";"><span style="mso-list: Ignore;">o<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";">
</span></span></span><span style="font-family: Calibri;">Cannot read uncommitted modifications<o:p></o:p></span></div>
<div class="MsoListParagraphCxSpMiddle" style="margin: 0cm 0cm 0pt 72pt; mso-add-space: auto; mso-list: l0 level2 lfo1; text-indent: -18pt;">
<span style="font-family: "Courier New"; mso-fareast-font-family: "Courier New";"><span style="mso-list: Ignore;">o<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";">
</span></span></span><span style="font-family: Calibri;">Other transactions cannot modify data read by
the current transaction until the current transaction completes.<o:p></o:p></span></div>
<div class="MsoListParagraphCxSpLast" style="margin: 0cm 0cm 10pt 72pt; mso-add-space: auto; mso-list: l0 level2 lfo1; text-indent: -18pt;">
<span style="font-family: "Courier New"; mso-fareast-font-family: "Courier New";"><span style="mso-list: Ignore;">o<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";">
</span></span></span><span style="font-family: Calibri;">Range locks are issued and only released at the
end of the transaction.<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<b style="mso-bidi-font-weight: normal;"><u><span style="font-family: Calibri;">What Are Isolation
Levels?<o:p></o:p></span></u></b></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Calibri;">Isolation levels are so named as they describe the degree
that one transaction must be <u>isolated</u> from modifications made by other
transactions. Locking is used by the database engine to enforce data integrity
while we use the isolation level to choose what kind of locking takes places,
how long it takes place and whether or not we select uncommitted data or block
until the modification lock is released. With our highest level of isolation,
serializable, we have the highest level of data integrity at the cost of
concurrency while our lowest level, read uncommitted, offers the highest level
of concurrency at the cost of data integrity. As developers we are striving for
the best balance between data integrity and concurrency. Isolation levels only
affect read operations. Data modification statements always take an exclusive
lock and holds the lock until the transaction completes.<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<b style="mso-bidi-font-weight: normal;"><u><span style="font-family: Calibri;">Concurrency Side Effects<o:p></o:p></span></u></b></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Calibri;">Concurrency side effects occur in five different ways namely
dirty reads, non-repeatable reads, phantom reads, lost updates and missing
& double reads. Missing & double reads (MD) can occur via a key update
or a page split.<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<u><span style="font-family: Calibri;">Dirty Reads<o:p></o:p></span></u></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Calibri;">Dirty reads quite simply mean that one transaction may read
data that has been updated by another transaction but has not yet been
committed. This modification may be rolled back leaving the first transaction
having read incorrect data.<o:p></o:p></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhuyAV1FloLNx02P9ycowQI9wfa_77_2OFfOD_s_jzgwjWQe39LWKAwUzP6annOkQegAclpZKqey-Qc-o8ECcBl4K4k3By86SuTvNsz2aBwOObQ6xAGIt8YQ1Qe2kAS3g9QrzROjbiziEfH/s1600/Dirty+Read.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhuyAV1FloLNx02P9ycowQI9wfa_77_2OFfOD_s_jzgwjWQe39LWKAwUzP6annOkQegAclpZKqey-Qc-o8ECcBl4K4k3By86SuTvNsz2aBwOObQ6xAGIt8YQ1Qe2kAS3g9QrzROjbiziEfH/s1600/Dirty+Read.png" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<u><span style="font-family: Calibri;">Non-repeatable Reads<o:p></o:p></span></u></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Calibri;">A non-repeatable read occurs when the first transaction
issues a read for the same row multiple times and in-between each read request
another transaction has updated that row. This means the first transaction
would read different data for the same row for each read.<o:p></o:p></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgEJOkR8zefniz1ZQoBfTXpEyfxZTOhAgxiDZcQ-vCcjweDQdBVGZWvTKfo2zYuv-vpz7EoEMYHSBy7LTsYYUa8hkJYahdneFa7eh7Ippv6GShQCyJMXVfJvhVMrCjVRYpzpD5sYhC4h5MZ/s1600/Non-repeatable+read.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgEJOkR8zefniz1ZQoBfTXpEyfxZTOhAgxiDZcQ-vCcjweDQdBVGZWvTKfo2zYuv-vpz7EoEMYHSBy7LTsYYUa8hkJYahdneFa7eh7Ippv6GShQCyJMXVfJvhVMrCjVRYpzpD5sYhC4h5MZ/s1600/Non-repeatable+read.png" /></a></div>
<br />
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<u><span style="font-family: Calibri;">Phantom Reads<o:p></o:p></span></u></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Calibri;">A phantom read is when two or more read range requests are
issued by the first transaction and in-between each read request another
transaction has issued an insert or delete against that particular range. This
means the first transaction would read a different number of rows each time.<o:p></o:p></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEinuLI4w1fLXuBN_i09KahJHbRT4VfwpmTKR06AVgd3pNmYBRS8TDtZWC5KY6smjNr4ktwLSxHCarEpqFF7D5idTdQiEry8d93iyImCZj3_DrcGbRElFYBj6lz7PJRMwFvt1XAZcAvH8r1p/s1600/Phantom+Read.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEinuLI4w1fLXuBN_i09KahJHbRT4VfwpmTKR06AVgd3pNmYBRS8TDtZWC5KY6smjNr4ktwLSxHCarEpqFF7D5idTdQiEry8d93iyImCZj3_DrcGbRElFYBj6lz7PJRMwFvt1XAZcAvH8r1p/s1600/Phantom+Read.png" /></a></div>
<br />
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<u><span style="font-family: Calibri;">Lost Updates<o:p></o:p></span></u></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Calibri;">Lost updates occur when two or more transactions are
updating the same row at the same time. The transaction that issues a commit
last will retain its update while the other transactions will have their update
overwritten.<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<u><span style="font-family: Calibri;">Missing & Double Reads (MD) – Updated Key<o:p></o:p></span></u></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Calibri;">This occurs when the first transaction is scanning an index
and another transaction changes the index key column of the row during the read.
The row will appear again if the key change moved the row ahead of the scan
while the row will not appear if the key change moved the row behind the scan.<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<u><span style="font-family: Calibri;">Missing & Double Reads (MD) – Page Split<o:p></o:p></span></u></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Calibri;">This occurs when the first transaction is doing an
allocation order scan and a second transaction issues an insert or update that
causes a page split. The page split could cause an already read page to move
ahead of the scan or an unread page to move behind the scan.<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
</div>
<br />
<table border="0" cellpadding="0" cellspacing="0" class="MsoNormalTable" style="border-collapse: collapse; margin: auto auto auto 4.65pt; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt; mso-yfti-tbllook: 1184; width: 683px;">
<tbody>
<tr style="height: 15pt; mso-yfti-firstrow: yes; mso-yfti-irow: 0;">
<td nowrap="" style="background-color: transparent; border: rgb(0, 0, 0); height: 15pt; padding: 0cm 5.4pt; width: 95pt;" valign="bottom" width="127"></td>
<td colspan="5" nowrap="" style="background-color: transparent; border: 1pt solid windowtext; height: 15pt; mso-border-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 417pt;" valign="bottom" width="556"><div align="center" class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; text-align: center;">
<b><span style="color: black; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-ZA; mso-hansi-font-family: Calibri;"><span style="font-family: Calibri;">Concurrency
Side Effects<o:p></o:p></span></span></b></div>
</td>
</tr>
<tr style="height: 15pt; mso-yfti-irow: 1;">
<td nowrap="" style="background-color: transparent; border: 1pt solid windowtext; height: 15pt; mso-border-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 95pt;" valign="bottom" width="127"><div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<b><span style="color: black; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-ZA; mso-hansi-font-family: Calibri;"><span style="font-family: Calibri;">Isolation Level<o:p></o:p></span></span></b></div>
</td>
<td nowrap="" style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 69.55pt;" valign="bottom" width="93"><div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<b><span style="color: black; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-ZA; mso-hansi-font-family: Calibri;"><span style="font-family: Calibri;">Dirty Reads<o:p></o:p></span></span></b></div>
</td>
<td nowrap="" style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 91.7pt;" valign="bottom" width="122"><div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<b><span style="color: black; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-ZA; mso-hansi-font-family: Calibri;"><span style="font-family: Calibri;">MD - Page Split<o:p></o:p></span></span></b></div>
</td>
<td nowrap="" style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 102.25pt;" valign="bottom" width="136"><div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<b><span style="color: black; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-ZA; mso-hansi-font-family: Calibri;"><span style="font-family: Calibri;">MD - Key Update<o:p></o:p></span></span></b></div>
</td>
<td nowrap="" style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 97.75pt;" valign="bottom" width="130"><div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<b><span style="color: black; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-ZA; mso-hansi-font-family: Calibri;"><span style="font-family: Calibri;">Non-Repeatable<o:p></o:p></span></span></b></div>
</td>
<td nowrap="" style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 55.75pt;" valign="bottom" width="74"><div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<b><span style="color: black; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-ZA; mso-hansi-font-family: Calibri;"><span style="font-family: Calibri;">Phantom<o:p></o:p></span></span></b></div>
</td>
</tr>
<tr style="height: 15pt; mso-yfti-irow: 2;">
<td nowrap="" style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext; border-style: none solid solid; border-width: 0px 1pt 1pt; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 95pt;" valign="bottom" width="127"><div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<b><span style="color: black; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-ZA; mso-hansi-font-family: Calibri;"><span style="font-family: Calibri;">Read uncommitted<o:p></o:p></span></span></b></div>
</td>
<td nowrap="" style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 69.55pt;" valign="bottom" width="93"><div align="center" class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; text-align: center;">
<span style="color: black; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-ZA; mso-hansi-font-family: Calibri;"><span style="font-family: Calibri;">YES<o:p></o:p></span></span></div>
</td>
<td nowrap="" style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 91.7pt;" valign="bottom" width="122"><div align="center" class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; text-align: center;">
<span style="color: black; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-ZA; mso-hansi-font-family: Calibri;"><span style="font-family: Calibri;">YES<o:p></o:p></span></span></div>
</td>
<td nowrap="" style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 102.25pt;" valign="bottom" width="136"><div align="center" class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; text-align: center;">
<span style="color: black; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-ZA; mso-hansi-font-family: Calibri;"><span style="font-family: Calibri;">YES<o:p></o:p></span></span></div>
</td>
<td nowrap="" style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 97.75pt;" valign="bottom" width="130"><div align="center" class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; text-align: center;">
<span style="color: black; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-ZA; mso-hansi-font-family: Calibri;"><span style="font-family: Calibri;">YES<o:p></o:p></span></span></div>
</td>
<td nowrap="" style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 55.75pt;" valign="bottom" width="74"><div align="center" class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; text-align: center;">
<span style="color: black; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-ZA; mso-hansi-font-family: Calibri;"><span style="font-family: Calibri;">YES<o:p></o:p></span></span></div>
</td>
</tr>
<tr style="height: 15pt; mso-yfti-irow: 3;">
<td nowrap="" style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext; border-style: none solid solid; border-width: 0px 1pt 1pt; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 95pt;" valign="bottom" width="127"><div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<b><span style="color: black; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-ZA; mso-hansi-font-family: Calibri;"><span style="font-family: Calibri;">Read committed<o:p></o:p></span></span></b></div>
</td>
<td nowrap="" style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 69.55pt;" valign="bottom" width="93"><div align="center" class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; text-align: center;">
<span style="color: black; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-ZA; mso-hansi-font-family: Calibri;"><span style="font-family: Calibri;">-<o:p></o:p></span></span></div>
</td>
<td nowrap="" style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 91.7pt;" valign="bottom" width="122"><div align="center" class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; text-align: center;">
<span style="color: black; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-ZA; mso-hansi-font-family: Calibri;"><span style="font-family: Calibri;">-<o:p></o:p></span></span></div>
</td>
<td nowrap="" style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 102.25pt;" valign="bottom" width="136"><div align="center" class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; text-align: center;">
<span style="color: black; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-ZA; mso-hansi-font-family: Calibri;"><span style="font-family: Calibri;">YES<o:p></o:p></span></span></div>
</td>
<td nowrap="" style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 97.75pt;" valign="bottom" width="130"><div align="center" class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; text-align: center;">
<span style="color: black; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-ZA; mso-hansi-font-family: Calibri;"><span style="font-family: Calibri;">YES<o:p></o:p></span></span></div>
</td>
<td nowrap="" style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 55.75pt;" valign="bottom" width="74"><div align="center" class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; text-align: center;">
<span style="color: black; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-ZA; mso-hansi-font-family: Calibri;"><span style="font-family: Calibri;">YES<o:p></o:p></span></span></div>
</td>
</tr>
<tr style="height: 15pt; mso-yfti-irow: 4;">
<td nowrap="" style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext; border-style: none solid solid; border-width: 0px 1pt 1pt; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 95pt;" valign="bottom" width="127"><div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<b><span style="color: black; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-ZA; mso-hansi-font-family: Calibri;"><span style="font-family: Calibri;">Repeatable read<o:p></o:p></span></span></b></div>
</td>
<td nowrap="" style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 69.55pt;" valign="bottom" width="93"><div align="center" class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; text-align: center;">
<span style="color: black; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-ZA; mso-hansi-font-family: Calibri;"><span style="font-family: Calibri;">-<o:p></o:p></span></span></div>
</td>
<td nowrap="" style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 91.7pt;" valign="bottom" width="122"><div align="center" class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; text-align: center;">
<span style="color: black; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-ZA; mso-hansi-font-family: Calibri;"><span style="font-family: Calibri;">-<o:p></o:p></span></span></div>
</td>
<td nowrap="" style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 102.25pt;" valign="bottom" width="136"><div align="center" class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; text-align: center;">
<span style="color: black; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-ZA; mso-hansi-font-family: Calibri;"><span style="font-family: Calibri;">-<o:p></o:p></span></span></div>
</td>
<td nowrap="" style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 97.75pt;" valign="bottom" width="130"><div align="center" class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; text-align: center;">
<span style="color: black; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-ZA; mso-hansi-font-family: Calibri;"><span style="font-family: Calibri;">-<o:p></o:p></span></span></div>
</td>
<td nowrap="" style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 55.75pt;" valign="bottom" width="74"><div align="center" class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; text-align: center;">
<span style="color: black; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-ZA; mso-hansi-font-family: Calibri;"><span style="font-family: Calibri;">YES<o:p></o:p></span></span></div>
</td>
</tr>
<tr style="height: 15pt; mso-yfti-irow: 5;">
<td nowrap="" style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext; border-style: none solid solid; border-width: 0px 1pt 1pt; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 95pt;" valign="bottom" width="127"><div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<b><span style="color: black; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-ZA; mso-hansi-font-family: Calibri;"><span style="font-family: Calibri;">Snapshot<o:p></o:p></span></span></b></div>
</td>
<td nowrap="" style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 69.55pt;" valign="bottom" width="93"><div align="center" class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; text-align: center;">
<span style="color: black; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-ZA; mso-hansi-font-family: Calibri;"><span style="font-family: Calibri;">-<o:p></o:p></span></span></div>
</td>
<td nowrap="" style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 91.7pt;" valign="bottom" width="122"><div align="center" class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; text-align: center;">
<span style="color: black; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-ZA; mso-hansi-font-family: Calibri;"><span style="font-family: Calibri;">-<o:p></o:p></span></span></div>
</td>
<td nowrap="" style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 102.25pt;" valign="bottom" width="136"><div align="center" class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; text-align: center;">
<span style="color: black; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-ZA; mso-hansi-font-family: Calibri;"><span style="font-family: Calibri;">-<o:p></o:p></span></span></div>
</td>
<td nowrap="" style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 97.75pt;" valign="bottom" width="130"><div align="center" class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; text-align: center;">
<span style="color: black; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-ZA; mso-hansi-font-family: Calibri;"><span style="font-family: Calibri;">-<o:p></o:p></span></span></div>
</td>
<td nowrap="" style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 55.75pt;" valign="bottom" width="74"><div align="center" class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; text-align: center;">
<span style="color: black; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-ZA; mso-hansi-font-family: Calibri;"><span style="font-family: Calibri;">-<o:p></o:p></span></span></div>
</td>
</tr>
<tr style="height: 15pt; mso-yfti-irow: 6; mso-yfti-lastrow: yes;">
<td nowrap="" style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext; border-style: none solid solid; border-width: 0px 1pt 1pt; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 95pt;" valign="bottom" width="127"><div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<b><span style="color: black; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-ZA; mso-hansi-font-family: Calibri;"><span style="font-family: Calibri;">Serializable<o:p></o:p></span></span></b></div>
</td>
<td nowrap="" style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 69.55pt;" valign="bottom" width="93"><div align="center" class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; text-align: center;">
<span style="color: black; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-ZA; mso-hansi-font-family: Calibri;"><span style="font-family: Calibri;">-<o:p></o:p></span></span></div>
</td>
<td nowrap="" style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 91.7pt;" valign="bottom" width="122"><div align="center" class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; text-align: center;">
<span style="color: black; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-ZA; mso-hansi-font-family: Calibri;"><span style="font-family: Calibri;">-<o:p></o:p></span></span></div>
</td>
<td nowrap="" style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 102.25pt;" valign="bottom" width="136"><div align="center" class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; text-align: center;">
<span style="color: black; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-ZA; mso-hansi-font-family: Calibri;"><span style="font-family: Calibri;">-<o:p></o:p></span></span></div>
</td>
<td nowrap="" style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 97.75pt;" valign="bottom" width="130"><div align="center" class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; text-align: center;">
<span style="color: black; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-ZA; mso-hansi-font-family: Calibri;"><span style="font-family: Calibri;">-<o:p></o:p></span></span></div>
</td>
<td nowrap="" style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 55.75pt;" valign="bottom" width="74"><div align="center" class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; text-align: center;">
<span style="color: black; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-ZA; mso-hansi-font-family: Calibri;"><span style="font-family: Calibri;">-<o:p></o:p></span></span></div>
</td>
</tr>
</tbody></table>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
</div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<b style="mso-bidi-font-weight: normal;"><u><span style="font-family: Calibri;">Setting Isolation
Level<o:p></o:p></span></u></b></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Calibri;">Isolation level is specified at a session level or as a
table hint. When set at a session level it remains in effect until the session
is terminated or the isolation level is set to another level.<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<u><span style="font-family: Calibri;">Session Level<o:p></o:p></span></u></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">SET</span><span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"> <span style="color: blue;">TRANSACTION</span> <span style="color: blue;">ISOLATION</span>
<span style="color: blue;">LEVEL</span> <span style="color: blue;">READ</span> <span style="color: blue;">UNCOMMITTED</span><span style="color: grey;">;<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">SET</span><span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"> <span style="color: blue;">TRANSACTION</span> <span style="color: blue;">ISOLATION</span>
<span style="color: blue;">LEVEL</span> <span style="color: blue;">READ</span> <span style="color: blue;">COMMITTED</span><span style="color: grey;">;<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">SET</span><span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"> <span style="color: blue;">TRANSACTION</span> <span style="color: blue;">ISOLATION</span>
<span style="color: blue;">LEVEL</span> <span style="color: blue;">REPEATABLE</span>
<span style="color: blue;">READ</span><span style="color: grey;">;<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">SET</span><span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"> <span style="color: blue;">TRANSACTION</span> <span style="color: blue;">ISOLATION</span>
<span style="color: blue;">LEVEL</span> <span style="color: blue;">SNAPSHOT</span><span style="color: grey;">;<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">SET</span><span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"> <span style="color: blue;">TRANSACTION</span> <span style="color: blue;">ISOLATION</span>
<span style="color: blue;">LEVEL</span> <span style="color: blue;">SERIALIZABLE</span><span style="color: grey;">;<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
</div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<u><span style="font-family: Calibri;">Table Hint<o:p></o:p></span></u></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="color: green; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">-- Serializable
Hints<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">SELECT</span><span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"> <span style="color: grey;">*</span> <span style="color: blue;">FROM</span> MyTable <span style="color: blue;">WITH </span><span style="color: grey;">(</span><span style="color: blue;">SERIALIZABLE</span><span style="color: grey;">,</span> <span style="color: blue;">HOLDLOCK</span><span style="color: grey;">);<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
</div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="color: green; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">-- Repeatable
Read Hints<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">SELECT</span><span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"> <span style="color: grey;">*</span> <span style="color: blue;">FROM</span> MyTable <span style="color: blue;">WITH </span><span style="color: grey;">(</span><span style="color: blue;">REPEATABLEREAD</span><span style="color: grey;">);<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
</div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="color: green; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">-- Read
Committed Hints<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">SELECT</span><span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"> <span style="color: grey;">*</span> <span style="color: blue;">FROM</span> MyTable <span style="color: blue;">WITH </span><span style="color: grey;">(</span><span style="color: blue;">READCOMMITTED</span><span style="color: grey;">,</span> <span style="color: blue;">READCOMMITTEDLOCK</span><span style="color: grey;">);<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
</div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="color: green; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">-- Read
Uncommitted Hints<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">SELECT</span><span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"> <span style="color: grey;">*</span> <span style="color: blue;">FROM</span> MyTable <span style="color: blue;">WITH </span><span style="color: grey;">(</span><span style="color: blue;">READUNCOMMITTED</span><span style="color: grey;">,</span> <span style="color: blue;">NOLOCK</span><span style="color: grey;">);<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
</div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<u><span style="font-family: Calibri;">Notes<o:p></o:p></span></u></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt; line-height: 115%; mso-no-proof: yes;">WITH </span><span style="color: grey; font-family: "Courier New"; font-size: 10pt; line-height: 115%; mso-no-proof: yes;">(</span><span style="color: blue; font-family: "Courier New"; font-size: 10pt; line-height: 115%; mso-no-proof: yes;">READCOMMITTED</span><span style="color: grey; font-family: "Courier New"; font-size: 10pt; line-height: 115%; mso-no-proof: yes;">)</span><span style="font-family: Calibri;"> will take
locks when the database option READ_COMMITTED_SNAPSHOT is OFF. <span style="mso-spacerun: yes;"> </span>When READ_COMMITTED_SNAPSHOT is ON the read
operation will run under the SNAPSHOT isolation level.<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt; line-height: 115%; mso-no-proof: yes;">WITH </span><span style="color: grey; font-family: "Courier New"; font-size: 10pt; line-height: 115%; mso-no-proof: yes;">(</span><span style="color: blue; font-family: "Courier New"; font-size: 10pt; line-height: 115%; mso-no-proof: yes;">READCOMMITTEDLOCK</span><span style="color: grey; font-family: "Courier New"; font-size: 10pt; line-height: 115%; mso-no-proof: yes;">)</span><span style="font-family: Calibri;">
will take locks regardless of the database option READ_COMMITTED_SNAPSHOT.<o:p></o:p></span></div>
Anonymoushttp://www.blogger.com/profile/15769604002687268779noreply@blogger.com0tag:blogger.com,1999:blog-7129367241107972697.post-45475844758076403452013-02-18T14:09:00.000+02:002013-02-18T14:17:12.034+02:00NOLOCK!<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: Verdana;">I see many developers writing SQL code and using NOLOCK as a "performance tool". I see NOLOCK spread liberally through scripts, applied to every single table as far as the eye can see. This technique is dangerous and I would like to share why.</span><br />
<a name='more'></a><br />
<span style="color: #660000; font-family: Verdana;"><u><strong>Background</strong></u></span><br />
<br />
<span style="font-family: Verdana;">In order to understand the dangers of NOLOCK, I will start by giving a bit of background with regards to SQL Server storage, index structures and page splits.</span><br />
<br />
<br />
<span style="color: #660000; font-family: Verdana;"><u><strong>SQL Server Storage</strong></u></span><br />
<br />
<span style="font-family: Verdana;">The fundamental unit of data storage in SQL Server is the page and pages are numbered contiguously from 0 to n. Disk I/O operations are performed at the page level i.e. SQL Server reads or writes whole data pages at a time. A page is 8Kb with 96 bytes reserved for the header. The header contains metadata about the page, and for the purposes of this article we are interested in m_pageId, m_nextPage and m_prevPage. These page numbers have a file number and a page number separated by a colon, for example 1:2870 is page number 2870 on file 1.</span><br />
<br />
<br />
<span style="font-family: Verdana;"></span><br />
<span style="font-family: Verdana;"></span><br />
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: Verdana;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgUYWsoHNq8NG4xKI5NFyvfEqtvAgrcxaE-Vx0sv2Ou34PS8xVkqEy_z5lTf4xmywtKgjrUJqC3VkKyS5T9xJSXQ3mReuVuNhu2peTHzWBm-H-M0WdMpjsEDx8XKeGlxLVO-p3ZRwpEAi0B/s1600/Page+Structure.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="233" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgUYWsoHNq8NG4xKI5NFyvfEqtvAgrcxaE-Vx0sv2Ou34PS8xVkqEy_z5lTf4xmywtKgjrUJqC3VkKyS5T9xJSXQ3mReuVuNhu2peTHzWBm-H-M0WdMpjsEDx8XKeGlxLVO-p3ZRwpEAi0B/s400/Page+Structure.jpg" width="400" /></a></span></div>
<span style="font-family: Verdana;">
</span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: Verdana;"><br /></span></div>
<span style="font-family: Verdana;">
</span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: Verdana;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEisSJCI8nSlhaRwK__7OtNP6nVAoGfa54gNBhmQz_ZQu68cW-pGLVnyEs1lk6IuBvYKTSkc-gFdVumQ6Gk-YJmm9wkP_je0T2tgOT-63ES2wGGTPWnsREj3suB4OXbzJ9NXTn3qOnumvMYj/s1600/Page+Header.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="215" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEisSJCI8nSlhaRwK__7OtNP6nVAoGfa54gNBhmQz_ZQu68cW-pGLVnyEs1lk6IuBvYKTSkc-gFdVumQ6Gk-YJmm9wkP_je0T2tgOT-63ES2wGGTPWnsREj3suB4OXbzJ9NXTn3qOnumvMYj/s640/Page+Header.jpg" width="640" /></a></span></div>
<span style="font-family: Verdana;">
</span>
<br />
<div>
<span style="font-family: Verdana;">
<strong><u><span style="color: #660000;">SQL Server Index Structure</span></u></strong><br />
<br />
Indexes store data on a page in order of the key columns of the index. If, for example, we created an index on an ID column with a type of INT the index would look something like this.<br />
<br />
</span></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: Verdana;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhP5jKViH9yZOhc4Z1W3iRPZ4nIKcbyOw2NUr9ZbvVMKy5aMPfawbbs7BwuCtmdBSur9HKQVBY3u0H1NMQzQtVX0AVb0VTaq5GrG7f-x-_8h3uzZ-MR9af1QBLQd-3EC-xneTn7NIpdgfNL/s1600/Index+Structure.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="242" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhP5jKViH9yZOhc4Z1W3iRPZ4nIKcbyOw2NUr9ZbvVMKy5aMPfawbbs7BwuCtmdBSur9HKQVBY3u0H1NMQzQtVX0AVb0VTaq5GrG7f-x-_8h3uzZ-MR9af1QBLQd-3EC-xneTn7NIpdgfNL/s320/Index+Structure.jpg" width="320" /></a></span></div>
<span style="font-family: Verdana;">
</span><span style="font-family: Verdana;"></span><br />
<span style="font-family: Verdana;"><div>
<strong><u><span style="color: #660000;">Page Splits</span></u></strong><br />
<br />
Page splits occur when data is inserted or updated and the target page for the operation does not have enough room for the new data. In this case a new page is created and the page headers (next page & prev page) are updated accordingly. This leads to index fragmentation where the physical order of the pages no longer represents the logical order of the index.<br />
<br />
For example if we inserted a new row in our example above with an ID of 11 there would not be enough space on the page and we would end up with a split which looks something like this.<br /><br />
</div>
</span><div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgZ7ebZNoYmoECpht_oCoqPG5RlYQtMyfdwvodm-YG7KMqyYrJTyc-bxzS3wTNXcRiqELgKNE3gEIrLTBKz0kmyeKwp-DPkypmLbbxcvg_h-rqqGqiEZBUs08aurOaoDGKpykU87uIfIJf0/s1600/Insert+Split.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="187" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgZ7ebZNoYmoECpht_oCoqPG5RlYQtMyfdwvodm-YG7KMqyYrJTyc-bxzS3wTNXcRiqELgKNE3gEIrLTBKz0kmyeKwp-DPkypmLbbxcvg_h-rqqGqiEZBUs08aurOaoDGKpykU87uIfIJf0/s400/Insert+Split.jpg" width="400" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-family: Verdana, sans-serif;">And similarly if we ran an update statement on the row with ID 7 and increased the size of the row (perhaps updating CompanyName from 'Belay' to 'EOH Microsoft Services') we would get another page split.</span></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-family: Verdana, sans-serif;"><br /></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEghZUAdCsHbMxnJsOQaeeJVlBCeUEDC6s2wZ-wbWNjWbizrP-Iuhefkb5bJKJGP9kLRCWRJOaYp4E7Zm5kGsEOfrKHpoMLveDg_5aveFo3l-c1YqzpQ_Vrp8Q9UkLPAQKza77Ta3g0qHNho/s1600/Update+Split.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="227" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEghZUAdCsHbMxnJsOQaeeJVlBCeUEDC6s2wZ-wbWNjWbizrP-Iuhefkb5bJKJGP9kLRCWRJOaYp4E7Zm5kGsEOfrKHpoMLveDg_5aveFo3l-c1YqzpQ_Vrp8Q9UkLPAQKza77Ta3g0qHNho/s640/Update+Split.jpg" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-family: Verdana, sans-serif;"><br /></span></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-family: Verdana, sans-serif;">Notice how the physical order does not represent the logical order.</span></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-family: Verdana, sans-serif;"><br /></span> </div>
<div class="separator" style="clear: both; text-align: left;">
<span style="color: #660000; font-family: Verdana, sans-serif;"><b><u>Reading Data</u></b></span></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-family: Verdana, sans-serif;"></span> </div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-family: Verdana, sans-serif;">When data is read from the pages the order of the read is influenced by the use of NOLOCK. When a read without NOLOCK is issued the read occurs in logical order while a read with NOLOCK occurs in physical order. Please note: this order is influenced by other factors outside the scope of this article so the actual order may vary on a busy system but the reading data with and without NOLOCK is vastly different.</span></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-family: Verdana, sans-serif;"><br /></span></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-family: Verdana, sans-serif;"><u>Without NOLOCK</u></span></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-family: Verdana, sans-serif;"><br /></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjjeul6Bln-1l4kzdZKpr22qnN6Fc6ULV6SdroYYhvTdqembc7J27OgvvToHDYKcHN3P_p4KFc4Y4b_vxifed8mEn3i9wYLSvPES8MvOHH417_uaT4c9ZszQQzm_ERRzd8LshAyutR5SNPo/s1600/Logical+Scan.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="387" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjjeul6Bln-1l4kzdZKpr22qnN6Fc6ULV6SdroYYhvTdqembc7J27OgvvToHDYKcHN3P_p4KFc4Y4b_vxifed8mEn3i9wYLSvPES8MvOHH417_uaT4c9ZszQQzm_ERRzd8LshAyutR5SNPo/s640/Logical+Scan.jpg" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-family: Verdana, sans-serif;"><u><br /></u></span></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-family: Verdana, sans-serif;"><u>With NOLOCK</u></span></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-family: Verdana, sans-serif;"><br /></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiHtCWtu_UwfZ4aW_i87x-JRGvPX6hRKaNWgVTaOpd_FKj1sLuLwCzyLZYd76CcwQEOe_UE7tOSPUWx3bOgMzdOjepQ_AGTG_f7hJk4N3-SO5emkQpM2fwd1Cg6nF9wSlOnkGX0RXuFRdW_/s1600/Allocation+Order+Scan.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="292" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiHtCWtu_UwfZ4aW_i87x-JRGvPX6hRKaNWgVTaOpd_FKj1sLuLwCzyLZYd76CcwQEOe_UE7tOSPUWx3bOgMzdOjepQ_AGTG_f7hJk4N3-SO5emkQpM2fwd1Cg6nF9wSlOnkGX0RXuFRdW_/s640/Allocation+Order+Scan.jpg" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-family: Verdana, sans-serif;"><br /></span></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-family: Verdana, sans-serif;">We can demonstrate this with the following code:</span></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-family: Verdana, sans-serif;"></span><br /></div>
<code style="font-size: 12px;"><span style="color: blue;">USE </span><span style="color: black;">AdventureWorks</span><span style="color: grey;">;</span></code><br />
<code style="font-size: 12px;"><span style="color: black;">GO</span></code><br />
<code style="font-size: 12px;"><span style="color: black;"><br /></span></code><code style="font-size: 12px;"><span style="color: blue;">SELECT TOP </span><span style="color: black;">5</span></code><br />
<code style="font-size: 12px;"><span style="color: black;"> SalesOrderID</span><span style="color: grey;">,</span></code><br />
<code style="font-size: 12px;"><span style="color: black;"> SalesOrderDetailID</span><span style="color: grey;">,</span></code><br />
<code style="font-size: 12px;"> <span style="color: black;">OrderQty</span></code><br />
<code style="font-size: 12px;"><span style="color: blue;">FROM </span><span style="color: black;">Sales.SalesOrderDetail</span><span style="color: grey;">;</span></code><br />
<br />
<code style="font-size: 12px;"><span style="color: blue;"><code style="font-size: 12px;"><span style="color: blue;"><code style="font-size: 12px;"><span style="color: blue;"><code style="font-size: 12px;"><span style="color: blue;"><span style="color: blue;">SELECT TOP </span><span style="color: black;">5</span></span></code><br />
<code style="font-size: 12px;"><span style="color: blue;"><span style="color: black;"> SalesOrderID</span><span style="color: grey;">,</span></span></code><br />
<code style="font-size: 12px;"><span style="color: blue;"><span style="color: grey;"></span></span></code><code style="font-size: 12px;"><span style="color: blue;"><span style="color: black;"> SalesOrderDetailID</span><span style="color: grey;">,</span></span></code><br />
<code style="font-size: 12px;"><span style="color: blue;"><span style="color: grey;"></span></span></code><code style="font-size: 12px;"><span style="color: blue;"><span style="color: black;"> OrderQty</span></span></code><br />
<code style="font-size: 12px;"><span style="color: blue;"><span style="color: black;"></span></span></code><code style="font-size: 12px;"><span style="color: blue;"><span style="color: blue;">FROM </span><span style="color: black;">Sales.SalesOrderDetail </span><span style="color: blue;">WITH </span><span style="color: grey;">(</span><span style="color: black;">NOLOCK</span><span style="color: grey;">);</span></span></code></span></code></span></code></span></code><br />
<code style="font-size: 12px;"><span style="color: blue;"><code style="font-size: 12px;"><span style="color: blue;"><code style="font-size: 12px;"><span style="color: grey;"><code style="font-size: 12px;"></code></span><br /></code></span></code></span><span style="font-family: Verdana, sans-serif;">which produces the following results on my system</span></code><br />
<br />
<span style="font-family: Verdana, sans-serif;"></span><br />
<span style="font-family: Verdana, sans-serif;"></span><br />
<span style="font-family: Verdana, sans-serif;"><div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjKnfi4hfRxJBSSKYOSbxiGCK7y1cCUZgxuWao9Rfm9o0ryk7mX42kqHqZ49FkpyXs8PBeQPmqF6KdiNprfbTLOEDsxlANMtCcDuxZnkiMplFE56q7bW4YU6wn07RGo7RTrn3f8FFfql6bZ/s1600/Scan+Order.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjKnfi4hfRxJBSSKYOSbxiGCK7y1cCUZgxuWao9Rfm9o0ryk7mX42kqHqZ49FkpyXs8PBeQPmqF6KdiNprfbTLOEDsxlANMtCcDuxZnkiMplFE56q7bW4YU6wn07RGo7RTrn3f8FFfql6bZ/s1600/Scan+Order.jpg" /></a></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<u><b><span style="color: #660000;">The Dangers Of NOLOCK</span></b></u></div>
<div>
<br /></div>
<div>
<div>
The danger comes in when your read is happening at the same time as a page split. Due to you not issuing any locks while reading data an update or insert may occur while SQL Server is busy with your read. These updates and inserts may cause a page split on one of the pages that has just been read or is about to be read. If this page split causes a new page in front of or behind the read operation you may end up reading the same rows twice or you may miss rows completely. Data read with a NOLOCK is therefore possibly an estimate.</div>
</div>
<div>
<br /></div>
<div>
Allow me to demonstrate this.</div>
<div>
<br /></div>
<div>
We first setup a table to test this. If we build a clustered index on an UNIQUEIDENTIFIER (which is a GUID) we can simulate many page splits.</div>
<div>
<br /></div>
<div>
<div>
<span style="color: blue;"><span style="font-size: x-small;"><span style="font-family: Courier New;"><span style="color: black;"><span style="font-family: Times New Roman; font-size: small;">
</span></span></span></span></span><br />
<span style="color: blue;"><span style="font-size: x-small;"><span style="font-family: Courier New;"><span style="color: black;"></span></span></span></span><br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="color: blue;"><span style="font-size: x-small;"><span style="font-family: Courier New;"><span style="color: black;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">IF</span><span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"> <span style="color: grey;">EXISTS(</span><span style="color: blue;">SELECT</span> <span style="color: grey;">*</span> <span style="color: blue;">FROM</span> <span style="color: green;">sys</span><span style="color: grey;">.</span><span style="color: green;">tables</span> <span style="color: blue;">WHERE</span> name <span style="color: grey;">=</span> <span style="color: red;">'AccountEntry'</span><span style="color: grey;">)<o:p></o:p></span></span></span></span></span></span></div>
<span style="color: blue;"><span style="font-size: x-small;"><span style="font-family: Courier New;"><span style="color: black;">
<span style="font-family: Times New Roman; font-size: small;">
</span><span style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">BEGIN<o:p></o:p></span><br />
<span style="font-family: Times New Roman; font-size: small;">
</span><span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><span style="mso-tab-count: 1;"> </span><span style="color: blue;">DROP</span> <span style="color: blue;">TABLE</span>
AccountEntry<span style="color: grey;">;<o:p></o:p></span></span><br />
<span style="font-family: Times New Roman; font-size: small;">
</span><span style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">END</span><span style="color: grey; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">;</span></span></span></span></span></div>
<div>
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">GO</span></div>
<div>
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;"></span> </div>
<span style="color: blue;"><span style="font-size: x-small;"><span style="font-family: Courier New;"><span style="color: black;">
<span style="font-family: Times New Roman; font-size: small;">
</span></span><span style="color: blue;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">CREATE
TABLE </span><span style="color: black; font-family: "Courier New"; font-size: 10pt;">AccountEntry </span></span><br />
</span></span></span><span style="color: blue;"><span style="font-size: x-small;"><span style="font-family: Courier New;"><span style="color: blue;"><span style="color: grey; font-family: "Courier New"; font-size: 10pt;">(</span><span style="color: black; font-family: "Courier New"; font-size: 10pt;">ID </span><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">UNIQUEIDENTIFIER PRIMARY KEY DEFAULT </span><span style="color: grey; font-family: "Courier New"; font-size: 10pt;">(</span><span style="color: magenta; font-family: "Courier New"; font-size: 10pt;">NEWID</span><span style="color: grey; font-family: "Courier New"; font-size: 10pt;">()),</span></span></span></span></span><br />
<span style="color: blue;"><span style="font-size: x-small;"><span style="font-family: Courier New;"><span style="color: blue;"></span></span></span></span><br />
<span style="color: blue;"><span style="font-size: x-small;"><span style="font-family: Courier New;"><span style="color: blue;"></span></span></span></span><br />
<span style="color: blue;"><span style="font-size: x-small;"><span style="font-family: Courier New;"><span style="color: blue;"><div>
<span style="color: black; font-family: "Courier New"; font-size: 10pt;"> AccountID </span><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">INT </span><span style="color: grey; font-family: "Courier New"; font-size: 10pt;">NOT NULL,<br />
</span><span style="color: black; font-family: "Courier New"; font-size: 10pt;"> Amount
</span><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">DECIMAL</span><span style="color: grey; font-family: "Courier New"; font-size: 10pt;">(</span><span style="color: black; font-family: "Courier New"; font-size: 10pt;">10</span><span style="color: grey; font-family: "Courier New"; font-size: 10pt;">, </span><span style="color: black; font-family: "Courier New"; font-size: 10pt;">2</span><span style="color: grey; font-family: "Courier New"; font-size: 10pt;">) NOT NULL);</span></div>
<div>
<span style="color: black; font-family: "Courier New"; font-size: 10pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">GO</span></span></div>
</span></span></span></span><br />
<div>
</div>
<div>
We also want to insert 10 rows with an amount of 10 for account number -1. This will give us an account balance of 100 for this particular account.</div>
<br />
<div>
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="color: blue;"><span style="font-size: x-small;"><span style="font-family: Courier New;"><span style="color: blue;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">INSERT</span><span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><span style="color: black;"> </span><span style="color: blue;">INTO</span><span style="color: black;"> AccountEntry</span><span style="color: blue;"> </span><span style="color: grey;">(</span><span style="color: black;">AccountID</span><span style="color: grey;">,</span><span style="color: black;"> Amount</span><span style="color: grey;">)<o:p></o:p></span></span></span></span></span></span></div>
<span style="color: blue;"><span style="font-size: x-small;"><span style="font-family: Courier New;"><span style="color: blue;">
<span style="color: black; font-family: Times New Roman; font-size: small;">
</span><span style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">VALUES </span><span style="color: grey; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">(-</span><span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><span style="color: black;">1</span><span style="color: grey;">,</span><span style="color: black;">10</span><span style="color: grey;">),</span><span style="color: blue;"> </span><span style="color: grey;">(-</span><span style="color: black;">1</span><span style="color: grey;">,</span><span style="color: black;">10</span><span style="color: grey;">),</span><span style="color: blue;"> </span><span style="color: grey;">(-</span><span style="color: black;">1</span><span style="color: grey;">,</span><span style="color: black;">10</span><span style="color: grey;">),</span><span style="color: blue;"> </span><span style="color: grey;">(-</span><span style="color: black;">1</span><span style="color: grey;">,</span><span style="color: black;">10</span><span style="color: grey;">),</span><span style="color: blue;"> </span><span style="color: grey;">(-</span><span style="color: black;">1</span><span style="color: grey;">,</span><span style="color: black;">10</span><span style="color: grey;">),<o:p></o:p></span></span><br />
<span style="color: black; font-family: Times New Roman; font-size: small;">
</span><span style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span></span><span style="color: grey; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">(-</span><span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><span style="color: black;">1</span><span style="color: grey;">,</span><span style="color: black;">10</span><span style="color: grey;">),</span><span style="color: blue;"> </span><span style="color: grey;">(-</span><span style="color: black;">1</span><span style="color: grey;">,</span><span style="color: black;">10</span><span style="color: grey;">),</span><span style="color: blue;"> </span><span style="color: grey;">(-</span><span style="color: black;">1</span><span style="color: grey;">,</span><span style="color: black;">10</span><span style="color: grey;">),</span><span style="color: blue;"> </span><span style="color: grey;">(-</span><span style="color: black;">1</span><span style="color: grey;">,</span><span style="color: black;">10</span><span style="color: grey;">),</span><span style="color: blue;"> </span><span style="color: grey;">(-</span><span style="color: black;">1</span><span style="color: grey;">,</span><span style="color: black;">10</span><span style="color: grey;">);<o:p></o:p></span></span><br />
<span style="color: black; font-family: Times New Roman; font-size: small;">
</span><span style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">GO</span></span></span></span></span></div>
<div>
<span style="color: blue;"><span style="font-family: Courier New;"><span style="font-size: x-small;"><br /></span></span></span>Now we want to generate many inserts into this table which will cause page splits to occur. I'm using a random number for the account ID, but note this random number is guaranteed to be greater than 0. This will ensure we are not changing the balance for our test account. Run the following code in a new window.</div>
</div>
<div>
</div>
<div>
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="color: blue;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">SET</span><span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><span style="color: black;"> </span><span style="color: blue;">NOCOUNT</span><span style="color: black;"> </span><span style="color: blue;">ON</span><span style="color: grey;">;<o:p></o:p></span></span></span></div>
<span style="color: blue;">
<span style="color: black; font-family: Times New Roman;">
</span></span><br />
<span style="color: blue;"></span><br />
<span style="color: blue;"></span><br />
<span style="color: blue;"><div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">WHILE</span><span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><span style="color: black;"> 1 </span><span style="color: grey;">=</span><span style="color: black;"> 1<o:p></o:p></span></span></div>
<div>
<span style="color: black; font-family: Times New Roman;">
</span><span style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">BEGIN<o:p></o:p></span><br />
<span style="color: black; font-family: Times New Roman;">
</span><span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"><span style="color: black;"> </span></span><span style="color: blue;">INSERT</span><span style="color: black;"> </span><span style="color: blue;">INTO</span><span style="color: black;"> AccountEntry</span><span style="color: blue;"> </span><span style="color: grey;">(</span><span style="color: black;">AccountID</span><span style="color: grey;">,</span><span style="color: black;"> Amount</span><span style="color: grey;">)<o:p></o:p></span></span><br />
<span style="color: black; font-family: Times New Roman;">
</span><span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"><span style="color: black;"> </span></span><span style="color: blue;">VALUES </span><span style="color: grey;">(</span><span style="color: magenta;">CAST</span><span style="color: grey;">(</span><span style="color: magenta;">RAND</span><span style="color: grey;">()</span><span style="color: black;"> </span><span style="color: grey;">*</span><span style="color: black;"> 10000 </span><span style="color: blue;">AS</span><span style="color: black;"> </span><span style="color: blue;">INT</span><span style="color: grey;">),</span><span style="color: black;"> 10</span><span style="color: grey;">);<o:p></o:p></span></span><br />
<span style="color: black; font-family: Times New Roman;">
</span><span style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">END</span><span style="color: grey; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">;</span></div>
</span><br />
<div>
Finally, while the code above is running in a separate window, we run the following code. This code runs in a loop and checks the balance of our test account. This balance should always be 100. If we get a balance that is not 100 we print the balance to the screen and increase a counter. I want to see how long it will take to have 5 reads with the incorrect balance.</div>
</div>
<div>
<div>
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">DECLARE</span><span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">
@TotalAmount <span style="color: blue;">DECIMAL</span><span style="color: grey;">(</span>10<span style="color: grey;">,</span> 2<span style="color: grey;">),<o:p></o:p></span></span></div>
<span style="font-family: Times New Roman;">
</span><span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span>@InvalidCount <span style="color: blue;">SMALLINT</span>
<span style="color: grey;">=</span> 0<span style="color: grey;">;<o:p></o:p></span></span><br />
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">WHILE</span><span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">
@InvalidCount <span style="color: grey;"><</span> 5<o:p></o:p></span></div>
<span style="font-family: Times New Roman;">
</span><span style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">BEGIN<o:p></o:p></span><br />
<span style="font-family: Times New Roman;">
</span><span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span><span style="color: blue;">SELECT</span>
@TotalAmount <span style="color: grey;">=</span> <span style="color: magenta;">SUM</span><span style="color: grey;">(</span>Amount<span style="color: grey;">)<o:p></o:p></span></span><br />
<span style="font-family: Times New Roman;">
</span><span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span><span style="color: blue;">FROM</span>
AccountEntry <span style="color: blue;">WITH</span><span style="color: grey;">(</span><span style="color: blue;">NOLOCK</span><span style="color: grey;">)<o:p></o:p></span></span><br />
<span style="font-family: Times New Roman;">
</span><span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span><span style="color: blue;">WHERE</span>
AccountID <span style="color: grey;">=</span> <span style="color: grey;">-</span>1<span style="color: grey;">;<o:p></o:p></span></span><br />
<span style="font-family: Times New Roman;">
</span><span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span><o:p></o:p></span><br />
<span style="font-family: Times New Roman;">
</span><span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span><span style="color: blue;">IF</span>
@TotalAmount <span style="color: grey;"><></span> 100<o:p></o:p></span><br />
<span style="font-family: Times New Roman;">
</span><span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span><span style="color: blue;">BEGIN<o:p></o:p></span></span><br />
<span style="font-family: Times New Roman;">
</span><span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span><span style="color: blue;">PRINT</span>
@TotalAmount<span style="color: grey;">;<o:p></o:p></span></span><br />
<span style="font-family: Times New Roman;">
</span><span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span><span style="color: blue;">SET</span>
@InvalidCount <span style="color: grey;">=</span> @InvalidCount <span style="color: grey;">+</span> 1<span style="color: grey;">;<o:p></o:p></span></span><br />
<span style="font-family: Times New Roman;">
</span><span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span><span style="color: blue;">END</span><span style="color: grey;">;<o:p></o:p></span></span><br />
<span style="font-family: Times New Roman;">
</span><span style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">END</span><span style="color: grey; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">;<o:p></o:p></span></div>
</div>
<div>
And as we can see it is quite easy to select an incorrect balance using NOLOCK.</div>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhnxZfi9aGZ_VVtIAg2ByPqTdSXYGm8byanyIUA38D9c5VdKLNJB26BR2xsYHbaodhvEDMubp5vxwE2BxVRLBrFrb8m93nzaJ_YAORVCdXm3tcldAhLZ8NIEfMZpchel4CFE7O7bYn2MJn4/s1600/Invalid+Results.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhnxZfi9aGZ_VVtIAg2ByPqTdSXYGm8byanyIUA38D9c5VdKLNJB26BR2xsYHbaodhvEDMubp5vxwE2BxVRLBrFrb8m93nzaJ_YAORVCdXm3tcldAhLZ8NIEfMZpchel4CFE7O7bYn2MJn4/s1600/Invalid+Results.jpg" /></a></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<b><u><span style="color: #660000;">How Is NOLOCK Applied</span></u></b></div>
<div>
<br /></div>
<div>
There are 3 ways to apply NOLOCK, 2 of which are table hints and the other applies NOLOCK to your connection.</div>
<div>
<span style="color: blue; font-family: "Courier New"; font-size: 10pt;"></span><br />
<span style="color: blue; font-family: "Courier New"; font-size: 10pt;">SET</span><span style="color: grey; font-family: "Courier New"; font-size: 10pt;"><span style="color: black;"> </span><span style="color: blue;">TRANSACTION</span><span style="color: black;"> </span><span style="color: blue;">ISOLATION</span><span style="color: black;">
</span><span style="color: blue;">LEVEL</span><span style="color: black;"> </span><span style="color: blue;">READ</span><span style="color: black;"> </span><span style="color: blue;">UNCOMMITTED</span><span style="color: grey;">;</span></span></div>
<div>
<span style="color: blue;"><span style="color: grey;"><span style="font-family: Courier New; font-size: x-small;">
<span style="color: black; font-family: Times New Roman; font-size: small;">
</span>
</span></span></span><span style="color: blue;"><span style="color: grey;"><span style="font-family: Courier New; font-size: x-small;"></span></span></span><br />
<span style="color: blue;"><span style="color: grey;"><span style="font-family: Courier New; font-size: x-small;"></span></span></span><br />
<span style="color: blue;"><span style="color: grey;"><span style="font-family: Courier New; font-size: x-small;"></span></span></span><br />
<span style="color: blue;"><span style="color: grey;"><span style="font-family: Courier New; font-size: x-small;"><div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">SELECT</span><span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><span style="color: black;"> </span><span style="color: grey;">*</span><span style="color: black;"> </span><span style="color: blue;">FROM</span><span style="color: black;"> Sales</span><span style="color: grey;">.</span><span style="color: black;">SalesOrderDetail </span><span style="color: blue;">WITH </span><span style="color: grey;">(</span><span style="color: blue;">READUNCOMMITTED</span><span style="color: grey;">);<o:p></o:p></span></span></div>
<span style="color: black; font-family: Times New Roman; font-size: small;">
</span>
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"></span><br />
<span style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;">SELECT</span><span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><span style="color: black;"> </span><span style="color: grey;">*</span><span style="color: black;"> </span><span style="color: blue;">FROM</span><span style="color: black;"> Sales</span><span style="color: grey;">.</span><span style="color: black;">SalesOrderDetail </span><span style="color: blue;">WITH </span><span style="color: grey;">(</span><span style="color: blue;">NOLOCK</span><span style="color: grey;">);</span></span><br />
<span style="font-family: "Courier New"; font-size: 10pt; mso-no-proof: yes;"><span style="color: grey;"><br /></span></span></div>
</span></span></span><b><u><span style="color: #660000;">When Can We Use NOLOCK</span></u></b><br /></div>
<div>
<br /></div>
<div>
<div>
<ul>
<li>Tables that do not change i.e. look-up tables.</li>
<li>Databases that do not change i.e. warehouses and reporting systems that are loaded at set periods.</li>
<li>Data that is acceptable as an estimate i.e. trending graphs</li>
</ul>
<div>
The following two trending graphs have slightly different figures. These differences are not noticeable. </div>
</div>
</div>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjrb86kCX3ktdWcwD0THuA9438BBlF_OFp-x9BzA6JmgI0JSlZCYN33ggyJUHvhffaKFEoBkVdBhGdxqJSv5rLtsKLzKYJEepN9p3Y_iSTFltHJBff1vG2_zh_HA99kC8PFrCUIbyRUylTa/s1600/Graph+1.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="185" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjrb86kCX3ktdWcwD0THuA9438BBlF_OFp-x9BzA6JmgI0JSlZCYN33ggyJUHvhffaKFEoBkVdBhGdxqJSv5rLtsKLzKYJEepN9p3Y_iSTFltHJBff1vG2_zh_HA99kC8PFrCUIbyRUylTa/s400/Graph+1.jpg" width="400" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgdwdwADuIKMPbQykl0a__ApopI10azhwZ-shSRhl6of25jmtbfTLQX5gyQySmrrSryvmfsQ7zegOyFnQNolNVYYedFbvWT8Mkl6wZ2XAhz3O2SpwkyidmKPiGU-xTpGo8KsNPvQ8X0ukIy/s1600/Graph+2.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="181" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgdwdwADuIKMPbQykl0a__ApopI10azhwZ-shSRhl6of25jmtbfTLQX5gyQySmrrSryvmfsQ7zegOyFnQNolNVYYedFbvWT8Mkl6wZ2XAhz3O2SpwkyidmKPiGU-xTpGo8KsNPvQ8X0ukIy/s400/Graph+2.jpg" width="400" /></a></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<b><u><span style="color: #660000;">And Now?</span></u></b></div>
<div>
<br /></div>
<div>
Now that we know why we shouldn't use NOLOCK, what do we do with that slow running, blocking query that NOLOCK magically fixed for us? We optimise of course.</div>
<div>
<br /></div>
<div>
1. Fine tune your code. Maybe it can be simplified or split up.</div>
<div>
2. Read the execution plan(s).</div>
<div>
3. Identify indexes that can be modified to support your query.</div>
<div>
4. Identify indexes that you can create to support your query.</div>
<div>
5. Go back to step 1.</div>
<div>
6. Use an appropriate isolation level.</div>
<div>
<br /></div>
<div>
<br /></div>
</span><br /></div>
Anonymoushttp://www.blogger.com/profile/15769604002687268779noreply@blogger.com1