| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-04-17 : 10:39:33
|
| Billy writes "If I use NOLOCKS on my select statements; and I am not worried about dirty reads, then is it faster (under all circumstances)?Billy" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-04-17 : 10:41:15
|
| It's not always faster, it simply keeps the query from being blocked by uncommitted transactions. It's usually more a case between getting your data or not at all. |
 |
|
|
LinuxLars
Starting Member
9 Posts |
Posted - 2006-04-18 : 07:51:50
|
| Yes, it's faster because SQLServer will bypass locking in the query. You will see uncommitted rows, but if that's OK with you, it is advisable for performance. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-18 : 08:12:30
|
I can't think of m/any circumstances where I'm happy to show records to the user that might be "wrong" and them make life changing decisions based on what they saw  Kristen |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-04-18 : 10:08:03
|
quote:
Originally posted by LinuxLars
Yes, it's faster because SQLServer will bypass locking in the query. You will see uncommitted rows, but if that's OK with you, it is advisable for performance.
That is a very bad practice to get into.The default of read/committed is best 99.99% of the time, unless you have a poorly designed application.CODO ERGO SUM |
|
|
|
nosepicker
Constraint Violating Yak Guru
366 Posts |
Posted - 2006-04-18 : 18:23:50
|
From Joe Chang in an article on sql-server-performance.com: quote:
Now it turns out SQL Server 2000 has limited ability to read from disk in a table scan operation. Without hints, SQL Server 2000 table scan operation in most systems will generate between 300-400MB/sec in disk reads. With the NOLOCK hint, the table scan might generate between 700-800MB/sec in disk traffic. If the configured storage system has less sequential capacity than this, a table scan can saturate the disk system, and cause a substantial degradation in transaction driven disk IO. If the storage system can handle both the sequential table scan and transaction random IO load, then there may be little to no degradation in transaction throughput, depending on the availability of CPU and other resources.
The rest of the article is about system and storage configuration w/resp to SQL Server in general. It's a good read:[url]http://www.sql-server-performance.com/jc_system_storage_configuration.asp[/url] |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-04-18 : 23:53:25
|
quote:
Originally posted by nosepicker
From Joe Chang in an article on sql-server-performance.com: quote:
Now it turns out SQL Server 2000 has limited ability to read from disk in a table scan operation.
Perhaps some effort should be put into avoiding the table scan in the first place... |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-04-19 : 09:03:09
|
oh indexes, oh indexes!!our favorite little constructbe it clustered (YES)or non clustered (YES)seeks is what we want!!Go with the flow & have fun! Else fight the flow  Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"] |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-04-19 : 09:30:52
|
| Ogden Nash as database developer... |
|
|
|
mmarovic
Aged Yak Warrior
518 Posts |
Posted - 2006-04-19 : 09:45:21
|
quote:
Originally posted by blindman
quote:
Originally posted by nosepicker
From Joe Chang in an article on sql-server-performance.com: quote:
Now it turns out SQL Server 2000 has limited ability to read from disk in a table scan operation.
Perhaps some effort should be put into avoiding the table scan in the first place...
I guess that the same applies to index scan and even index range scan. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-04-19 : 09:55:03
|
| I'll echo Kristen and MJV's opinions, if your database relies on NOLOCK hints for performance you have a serious problem. And even if that throughput benefit extended to index scans, you'd lose all of it to the associated bookmark lookups, or the query optimizer would choose to do a table scan anyway. |
|
|
|
|
Is NOLOCKS faster