In administering SQL Server databases, the DBA is often presented with the task of identifying resource bottlenecks on the system and the sources of those bottlenecks. One common bottleneck that can impact query performance is disk I/O. In this blog post, we will explore how to identify if any disk I/O bottlenecks are present in SQL Server and how to address them.

Using Extended Events for IO Monitoring

SQL Server Extended Events can be used to find if any queries are experiencing disk I/O waits. By creating an event session and capturing relevant data, DBAs can gain insights into the performance impact of disk I/O on the system.

Here is an example of how to create an event session for IO monitoring:

CREATE EVENT SESSION [XEvent_IO_Tracker] ON SERVER
ADD EVENT sqlos.wait_info(ACTION(sqlserver.session_id,sqlserver.sql_text)
WHERE (([package0].[equal_uint64]([wait_type],(68)) OR [package0].[equal_uint64]([wait_type],(66))
OR [package0].[equal_uint64]([wait_type],(67)) OR [package0].[equal_uint64]([wait_type],(182)))
AND [package0].[greater_than_uint64]([duration],(0)) AND [package0].[equal_uint64]([opcode],(1)) AND [sqlserver].[is_system]=(0)))
ADD TARGET package0.event_file(SET filename=N'C:\Temp\XeventIObottleneck.xel')
WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY=30 SECONDS, MAX_EVENT_SIZE=0 KB, MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=OFF, STARTUP_STATE=OFF);
GO

ALTER EVENT SESSION [XEvent_IO_Tracker] ON SERVER STATE = START;
GO

Once the event session is running, you can verify that it is actively capturing data by executing the following DMV query:

SELECT * FROM sys.dm_xe_sessions WHERE name = 'XEvent_IO_Tracker';

To induce an IO operation and capture the data using the XEvent, you can execute a T-SQL command like the following:

DBCC DROPCLEANBUFFERS
GO

USE AdventureWorks2012
GO

SELECT COUNT_BIG(*) FROM [Production].[ProductInventory] a
CROSS JOIN [Production].[ProductInventory] b
CROSS JOIN [Production].[ProductInventory] c
CROSS JOIN [Production].[ProductInventory] d
WHERE a.Quantity > 300

Once the query completes, you can stop the Extended Event session by executing the following command:

ALTER EVENT SESSION [XEvent_IO_Tracker] ON SERVER  STATE = STOP
GO

By viewing the captured data, you can analyze the disk I/O waits and identify any potential bottlenecks.

Using Diagnostic Manager for SQL Server Monitoring

In addition to using Extended Events, there are other tools available for monitoring SQL Server performance. One such tool is Idera’s Diagnostic Manager.

Some of the features of Diagnostic Manager that can help DBAs in monitoring SQL Server performance include:

• Alerting: The tool provides alerts for various server metrics such as CPU, memory, IO, throughput, and network. This allows DBAs to be notified when any of these resources are peaking or when certain events occur.
• Standard Reporting: Diagnostic Manager offers a set of standard reports that provide insights into server performance. These reports are categorized under headings such as “Server,” “Virtualization,” and “Activity.” They can help DBAs forecast based on existing and historical data and make informed decisions about hardware configuration and capacity planning.
• Rich Dashboard: The tool provides a customizable dashboard that allows DBAs to monitor various performance counters and values. It also allows the creation of additional counters and values for monitoring specific needs.
• Monitoring with Guidance: Diagnostic Manager offers reference range values for SQL Server performance counters, making it easier for DBAs to monitor and track performance. It also allows the import and export of performance counters and ranges from tools like PAL.

With these features, Diagnostic Manager can be a valuable asset for DBAs in monitoring and optimizing SQL Server performance.

Conclusion

Identifying and addressing disk I/O bottlenecks is crucial for optimizing SQL Server performance. By using tools like Extended Events and Diagnostic Manager, DBAs can gain insights into the impact of disk I/O on query performance and take necessary actions to improve overall system performance.

Remember, monitoring and optimizing SQL Server performance is an ongoing process, and it’s important to stay updated with the latest tools and techniques to ensure optimal performance.