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 |
|
Sarat
Constraint Violating Yak Guru
265 Posts |
 Posted - 2004-01-14 : 16:25:51
|
| Hi,Few minutes ago there were atleast 8 processes/users queries blocking each other in Production database. I could hardly see which id is blocking which id since it took forever for EM to show current activity screen details. Even QA sp_lock was terribly slow!!I want to know could this be due to 'Allowing Page Locks = False' on some of the tables that were being queried? I never saw anything like this!!How can I avoid such a situation in future? As of now I asked some people to exit and come back in again to resolve the issue. It helped getting rid of all blocking processes.Thanks,Sarat.**To be intoxicated is to feel sophisticated, but not be able to say it.** |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-01-14 : 16:34:03
|
| You should look at sp_who and/or sp_who2 in QA to observer the blocking.What is 'Allowing page locks = False'?When blocking suddenly occurs, it is due to increased activity which can cause hardware problems. If this happens often, then consider hardware upgrades.Tara |
 |
|
|
Sarat
Constraint Violating Yak Guru
265 Posts |
Posted - 2004-01-14 : 17:03:34
|
| I tried sp_who, sp_lock in qa. it was very very slow while returning output. I barely got a glance in EM and saw atleast 8 process ids being blocked.sp_indexoption table_name, 'AllowPageLocks', false is intended to disable page level locking to improve performace - which didn't do the job for me. I just want to know if this has anything to do with blocking that occurred today.**To be intoxicated is to feel sophisticated, but not be able to say it.** |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-01-14 : 17:37:47
|
| From BOL:sp_indexoption Sets option values for user-defined indexes.Note Microsoft® SQL Server™ automatically makes choices of page-, row-, or table-level locking. It is not necessary to set these options manually. sp_indexoption is provided for expert users who know with certainty that a particular type of lock is always appropriate.************Has thorough analysis been performed to see if the type of lock is appropriate? I have never set this option as SQL Server is able to determine which locking level to use. But the blocking problem could just be increased activity which caused degradation in hardware performance.Tara |
|
|
|
Sarat
Constraint Violating Yak Guru
265 Posts |
Posted - 2004-01-14 : 18:34:47
|
| Ya, I went throught that. PS Consultant advised this. All details are in Allow Page Locks folder in this forum. I asked similar question there.Thanks,Sarat.**To be intoxicated is to feel sophisticated, but not be able to say it.** |
|
|
|
|
|
|
|
|
Terrible Record Locking