Measuring SQL Server Performance

I had a customer with a reported slow SQL Server, they run a MRP application and I have several customers with the same SQL database so I had a good bench mark to check against.

I used two tools for this Process Monitor and the now defunct Filemon, both from SysInternals.

I used Process Monitor to look at the disk I/O reads and writes and noted that the customer with the performance issues had disk I/O reads of 88 million and writes of 44 million. I then used Filemon to view the disk read/writes on the SQL Server database disk and I could see up to 400 disk transactions per second.

I compared this to the customer with no problems and they returned 16 million reads and 16 million writes with 15 disk transactions per second using Filemon.

Although there is more to this type of investigation such as the RAID array type, disk spindle speeds, nature of the MRP use and age of the physical servers, I found that this was a good method of getting a good overview of the disk usage and if the disk I/O is the bottleneck on your SQL Server when this type of issue arises with performance.