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 |
|
lilsamp
Starting Member
3 Posts |
 Posted - 2011-11-10 : 17:32:52
|
| I'm running SQL Server 2005 Enterprise Edition SP2 on Windows 2003 R2 2X8 core with 256GB of RAMUsually, PageLatch_SH and PageLatch_EX waittimes are in the 20-30 millisecond range. Twice now, all of a sudden, PageLatch_SH has gone from 30000 ms to 252000 ms over 20 minutes and PageLatch_EX 25000 to 82000 and then they both dropped rapidly to their normal levels and the issue went away.During that time, there was a large amount of "rolling" blocking occurring in tempdb. That means that everytime I ran sp_who2'active', blocking was occurring, but the same spid was not blocking anymore. Another spid was.Obviously, during this incident my waiting tasks ( in Activity Monitor) increased from 20-30 to up over 300. Users were impacted. I'm trying to figure out what type of activity would cause PageLatch_SH to increase drastically and then go away. We don't run bulk insert queries, so I'm a little confused on this one. The definiton of the wait type "Caused when a task is waiting on a latch for a buffer that is not in an I/O request" What else could cause this issue outside of I/O? |
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-11-10 : 23:04:34
|
| Maybe a large table scan.Could you identify the exact query using profiler when the PageLatch_SH goes up ?PBUH |
 |
|
|
lilsamp
Starting Member
3 Posts |
Posted - 2011-11-11 : 10:39:36
|
| There were traces going, so I digging through that data right now. |
|
|
|
|
|
|
Random PageLatch_SH WaitTypes Increase