07 December,2011 by Jack Vamvas
Latency is the time delay measure in the system. SQL Server can only be as fast as the slowest component in the system.
Database servers are particularly sensitive to disk IO speeds. For example, introducing an SVC system may speed up writes , but will add a cost to Reads.
Latency is about benchmarking certain limitations , such as disk speed , and acknowledging that relative to the speed of light – delays occur. Use Production data or SQL Server test data generation testing tools to benchmark systems.
Database server latency is also about finding the acceptable level of delay. Yes, the IO request may not be 3 ms – but 6 ms may be acceptable to the users.
In a large database server ecosystem – multiple requirements are considered. Balancing budgets, business requirements and system management are factors in finding the acceptable level of latency.
SQL Server since 2005 has offered some useful methods of starting the analysis.
Use these two DMVs to develop a quick overview of where some delays are occurring. Use the information to focus on bottlenecks. There are other ways of measuring Disk IO performance such as SQLIO
sys.dm_io_pending_io_requests - View pending I/O request(s) in SQL Server.
sys.dm_io_virtual_file_stats Returns I/O statistics for Database data and log files. Measure IO transaction rates and sys.dm_io_virtual_file_statsAuthor: Jack Vamvas (http://www.sqlserver-dba.com)