Wait type PAGEIOLATCH_SH
-
Being new to performance tuning in SQL Server, I am curious as to the PAGEIOLATCH_SH, is there a way to clear these with out having to reboot the server.
As you can see by the data below, the drop in numbers relates to a reboot of the server. We do have 6 remote locations that are on VPN at this time, I also have 8 other remotes on MPSL.
Any help or knowledge I can gain will improve my life.
'dt' 'wait_type' 'waiting_tasks_count' 'wait_time_ms' 'max_wait_time_ms' 'signal_wait_time_ms' 7/5/2007 0:00 PAGEIOLATCH_SH 577278 4943515 2453 21921 7/5/2007 2:00 PAGEIOLATCH_SH 578262 4946015 2453 21921 7/5/2007 4:00 PAGEIOLATCH_SH 578750 4948437 2453 21937 7/5/2007 6:00 PAGEIOLATCH_SH 579097 4950406 2453 21937 7/5/2007 8:00 PAGEIOLATCH_SH 588603 5063703 2453 23015 7/5/2007 10:00 PAGEIOLATCH_SH 5218 43640 515 62 7/5/2007 12:00 PAGEIOLATCH_SH 7862 74796 546 140 7/5/2007 14:00 PAGEIOLATCH_SH 10004 107640 656 171 7/5/2007 16:00 PAGEIOLATCH_SH 10898 117281 656 171 7/5/2007 18:00 PAGEIOLATCH_SH 12428 126312 656 171 7/5/2007 20:00 PAGEIOLATCH_SH 12663 129921 812 187 7/5/2007 22:00 PAGEIOLATCH_SH 12692 133140 812 187 Thanks
Greg
-
Well I just gained more knowledge on my own, sys.dm_os_wait_stats, the stats keep adding up until you run the following:
DBCC
SQLPERF ('sys.dm_os_wait_stats', CLEAR);
Now my monitoring will gather stats stored every two hours so that I will get a better representation of the true statistics.
I love it when I teach myself
-
PAGEIOLATCH_SH simply means that the SPID is waiting for some page to be brought into memory (from database files on disk).
Investigate if your IO is a bottleneck. or see if you can tune your queries to reduce IO.
-
If you want a good idea of how many latch waits you're getting, use Performance Monitor. The counters you want are Latches:Average latch wait time, Latches: Latch waits/sec
The average wait time should be low. Latches are supposed to be light-weight, short duration synchronisation devices. If the latch waits are high, monitor your physical disk stats. sec/read and sec/write
Your sec/write should be under 10 ms
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply