Nolock Hint
in [Server Configuration]
|
Prev: cannot obtain provider factory for data provider named 'microsoft.sqlserverce.client3.5'
Next: Can't authenticate in SQL Mgmt Studio
From: RG on 12 Dec 2009 21:20 Does the performance gain stems from the that sql server doesn't need to dirty or in middle of transaction rows? If yes, is that the only reason? Thanks in advance
From: RG on 12 Dec 2009 21:40 I meant to say... Does the performance gain stem from that sql server doesn't need to check for "in middle of transaction" rows? "RG" <nobody(a)nowhere.com> wrote in message news:4B9959AB-96AA-4C2F-9DBA-0701CABEB0DD(a)microsoft.com... > Does the performance gain stems from the that sql server doesn't need to > dirty or in middle of transaction rows? > > If yes, is that the only reason? > > > Thanks in advance
From: Jay on 13 Dec 2009 01:03 I wasn't aware of a performance gain from the use of NO LOCK. I suppose you could consider never blocking due to a lock a gain, but that's not the reason to use NO LOCK. Perhaps if you were clearer. "RG" <nobody(a)nowhere.com> wrote in message news:D4541452-4BFE-417B-93A7-989113E57F67(a)microsoft.com... >I meant to say... > > Does the performance gain stem from that sql server doesn't need to check > for "in middle of transaction" rows? > > > "RG" <nobody(a)nowhere.com> wrote in message > news:4B9959AB-96AA-4C2F-9DBA-0701CABEB0DD(a)microsoft.com... >> Does the performance gain stems from the that sql server doesn't need to >> dirty or in middle of transaction rows? >> >> If yes, is that the only reason? >> >> >> Thanks in advance >
From: Erland Sommarskog on 13 Dec 2009 06:12 RG (nobody(a)nowhere.com) writes: > Does the performance gain stem from that sql server doesn't need to check > for "in middle of transaction" rows? The main gain is that you are not blocked by others. You also gain some by the reduced amount of lock manangement, but that is not the major part. That does not mean that you should use NOLOCK every now and then. Most of the time when you want to avoid blocking, you should probably use some snapshot variation. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: RG on 13 Dec 2009 09:46
This is a great piece of info. What other snapshot variations are there? Thanks, Roman "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9CE07C1F0BDEFYazorman(a)127.0.0.1... > RG (nobody(a)nowhere.com) writes: >> Does the performance gain stem from that sql server doesn't need to check >> for "in middle of transaction" rows? > > The main gain is that you are not blocked by others. You also gain some > by the reduced amount of lock manangement, but that is not the major part. > > That does not mean that you should use NOLOCK every now and then. Most of > the time when you want to avoid blocking, you should probably use some > snapshot variation. > > > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000: > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > |