Home » Wait Stats

Wait Stats

sql server DBA 999

ASYNC_NETWORK_IO Waits SQL Server 2005 2008 2012 2014 2016

ASYNC_NETWORK_IO Waits SQL Server 2005 2008 2012 2014 2016

ASYNC_NETWORK_IO Wait type in SQL 2005 2008 2012 2014 or 2016 are associated with either a Network Performance issues in the Environment or a the client application is not processing results quickly enough from SQL Server.

As always, if you are One of the DBA’s who check Top Waits Stats on SQL Servers to get a insight on what is SQL Server waiting on actually for this performance issues. Below few tips on troubleshooting ASYNC_NETWORK_IO Waits SQL Server

The Top Waits Stats rollup query is used to find the Top Waits SQL Server is waiting on from last restart of SQL … Read the rest

sql server DBA 999

CXPACKET Waits and High CPU Usage Bottleneck SQL Server 2008 2012 2014 2016

CXPACKET Waits and High CPU Usage Bottleneck SQL Server 2008 2012 2014 2016

When you suspect SQL Server is waiting on CPU and CPU is constantly spiked on 90-100% or SQL server high cpu usage query then it would wise to check the Top Waits Stats on SQL Server to see if the Top Waits is on “CXPACKET” by using Top Waits Rollup Query here.

Most of the time, DBA needs to find the culprit queries which are using high CPU and try to optimize it them by designing proper indexes or by rewriting the SQL more efficiently. SQL Server High CPU Usage query below can be very useful.

The queries useful in troubleshooting CPU Bottleneck issues, which are used

Read the rest
sql server DBA 999

The SQL Server Wait Types with their descriptions

The SQL Server Wait Types with their descriptions

Wait Statistics in SQL Server indicate sql server performance in relation to different resources like CPU, Disk usage, Memory etc..

SQL Server Activity monitor waiting tasks is another important place to observe what SQL Server is waiting on. Activity monitor has different sections like Processes, Resource Waits, Recent Expensive Queries and Data IO Stats.

“Wait_Type, Area, Usage, Version” Description Action
“ASYNC_IO_COMPLETION
I/O
Resource
SQL 2005-2008”
Used to indicate a worker is waiting on a asynchronous I/O operation to complete not associated with database pages Since this is used for various reason you need to find out what query or task is associated with the wait. Two examples of where this wait type
Read the rest
sql server DBA 999

To check where the connections are made to a database

To check where the connections are made to database the below queries are have been very helpful.


-- Get a count of SQL connections by IP address
SELECT ec.client_net_address, es.[program_name],
es.[host_name], es.login_name,
COUNT(ec.session_id) AS [connection count]
FROM sys.dm_exec_sessions AS es
INNER JOIN sys.dm_exec_connections AS ec
ON es.session_id = ec.session_id
GROUP BY ec.client_net_address, es.[program_name], es.[host_name], es.login_name
ORDER BY ec.client_net_address, es.[program_name];

-- Get a count of SQL connections by login_name
SELECT login_name, COUNT(session_id) AS [session_count]
FROM sys.dm_exec_sessions
GROUP BY login_name
ORDER BY login_name;

The above queries can be used in conjuction with Top Waits Stats rollup query here to get the best understanding whats SQL Server is waiting on and where the connections are coming to the SQL Server with aggregated connections … Read the rest

sql server DBA 999

SQL Server Disk I/O Specific issues

If you suspect that your SQL Server instance is having Disk I/O issues, these queries can help furnish proof that you are under I/O pressure, and they can help pinpoint what is causing the problem.
Before you run determine that you have I/O issues, please verify that by running Top Wait Stats rollup query.


-- Always look at Avg Disk Sec/Read and Avg Disk Sec/Write in PerfMon for each Physical Disk

-- Check for IO Bottlenecks (run multiple times, look for values above zero)
SELECT cpu_id, pending_disk_io_count
FROM sys.dm_os_schedulers
WHERE [status] = 'VISIBLE ONLINE';

-- Look at average for all schedulers (run multiple times, look for values above zero)
SELECT AVG(pending_disk_io_count) AS [AvgPendingDiskIOCount]
FROM sys.dm_os_schedulers
WHERE [status] = 'VISIBLE ONLINE';… Read the rest

sql server DBA 999

Top Waits Stats rollup query

The query which can be used to identify Top Wait Stats is given below. It is used to Isolate top waits for server instance since last restart.
This query has been really helpfull in identifying various issues as I/O issues, CPU Bottlenecks etc.
Top Waits Stats rollup query

-- Isolate top waits for server instance since last restart
WITH Waits AS
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK'
,'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE'
,'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP'
,'CLR_MANUAL_EVENT','CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT'
,'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN'))
SELECT W1.wait_type,
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS … Read the rest