SQL Server Latency

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_stats

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

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


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on SQL Server Latency

sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer