Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-12-19 : 08:55:19
|
| Venugopal writes "Hi I am using nolock in an sql statment. For result set size of 30000 I am getting good performance gain. But when I tried with a result set of 215000, I am not getting a performance gain. Some time it is worse than with lock. Wht will be the reason ?Is there any range of result where we will get best performance by using nolock.I am using SQL server 2000.regardsVenu" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2001-12-19 : 09:57:37
|
| NOLOCK doesn't guarantee a performance improvement. It's available for situations when there's a lot of activity that holds locks, and lock management would adversely affect SELECT performance. If you don't have a large amount of that kind of activity NOLOCK won't do much.I think that the problem may be more related to the database or the server. The database could be fragemented and causing a lot of disk activity; 30,000 rows will retrieve faster, and the fragmentation may not be noticeable with that number of rows. Also, you should UPDATE STATISTICS on your indexes, and maybe DBCC DBREINDEX your tables.The server may not have enough available RAM to support 215,000 rows in the data cache. If you are SELECTing a lot of columns (50+) this is likely. You can try to increase the amount of RAM SQL Server uses, or add more. |
 |
|
|
venugopal.c
Starting Member
1 Post |
Posted - 2001-12-24 : 03:36:15
|
quote:
Hi Thanks for your immediate reply. I am working for single user scenario with huge volume of data. Can I get a performance gain if I use the lock type as read uncommitted(ir nolock). Could u explain me how SQL server uses the shared lock while fetching data ?When I tried with a 30000 result set the without lock the result is getting 8 times faster than with lock. But when I executed the same query for a result set 215000 I am not getting this performance difference. It is hardly 25% increase only. My test query fetches around 28 fields from 2 tables using a join condition.The tables are indexed on Primary keys.thanks a lotVenu
quote:
NOLOCK doesn't guarantee a performance improvement. It's available for situations when there's a lot of activity that holds locks, and lock management would adversely affect SELECT performance. If you don't have a large amount of that kind of activity NOLOCK won't do much.I think that the problem may be more related to the database or the server. The database could be fragemented and causing a lot of disk activity; 30,000 rows will retrieve faster, and the fragmentation may not be noticeable with that number of rows. Also, you should UPDATE STATISTICS on your indexes, and maybe DBCC DBREINDEX your tables.The server may not have enough available RAM to support 215,000 rows in the data cache. If you are SELECTing a lot of columns (50+) this is likely. You can try to increase the amount of RAM SQL Server uses, or add more.
|
|
|
|
|
|
|
|
|
Using NOLock in Select Statement