SQL Server Bottlenecks
The guys over at PSS SQL Blog have written a short but really sweet article about how to really detect whether you have a bottleneck with SQL Server implementations.
It gives a few pointers of what to look for if you really start digging including:
- PAGE I/O Waits (select * from sys.dm_os_wait_stats where wait_type like ‘%PAGEIO%’)
- Virtual File Stats (select * from sys.dm_io_virtual_file_stats(-1, -1))
- Stalled I/O Warnings
- Additional disk based performance counters and available hardware utilities
I found this useful recently when SQL Server was virtualised and all other methods of monitoring looked fine (perfmon, vCharter), but couldn’t find the problem.
Tags: Microsoft, SQL Server