Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server Latency

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_stats

Author: Jack Vamvas (http://www.sqlserver-dba.com)

Comments

Leave a comment on the original post [www.sqlserver-dba.com, opens in a new window]

Loading comments...