How to find out what are the waiting tasks with MSSQL 2005

  • How fast is your SQL Server?
  • How fast is your application?

Everybody has their own measure. The only one that really count is the relationship between the server, the application, and the users.

How many tasks are waiting? If there are too many, then either:

  1. The MS SQL Server is too slow and/or
  2. The application is too slow and/or
  3. The database it too large and/or
  4. There are too many users

Number of tasks waiting with MSSQL 2005

MSSQL keeps track of anything relating to the waiting tasks in the table: sys.dm_os_waiting_tasks.

SELECT COUNT(*) AS 'Number of waiting tasks'
FROM sys.dm_os_waiting_tasks;
go
Number of waiting tasks
-----------------------
11

(1 row(s) affected)

Number of threads used with MSSQL 2005

SELECT COUNT(*) AS 'Number of threads'
FROM sys.dm_os_waiting_tasks
WHERE wait_type <> 'THREADPOOL';
go
Number of threads
-----------------
11

(1 row(s) affected)

Type of waiting tasks with MSSQL 2005

This will give the type of load on your server and which area to start investigating.

SELECT CAST(wait_type AS VARCHAR(30)) AS 'Waiting task',
       COUNT (*) AS 'Number of waiting tasks'
FROM sys.dm_os_waiting_tasks
GROUP BY wait_type
ORDER BY 'Number of waiting tasks' DESC;
go
Waiting task                   Number of waiting tasks
------------------------------ -----------------------
BROKER_TRANSMITTER             2
LAZYWRITER_SLEEP               1
BROKER_EVENTHANDLER            1
CHECKPOINT_QUEUE               1
KSOURCE_WAKEUP                 1
SQLTRACE_BUFFER_FLUSH          1
OLEDB                          1
LOGMGR_QUEUE                   1
ONDEMAND_TASK_QUEUE            1
REQUEST_FOR_DEADLOCK_SEARCH    1

(10 row(s) affected)

Resource Bottlenecks with MSSQL 2005

SELECT resource_address AS 'Resource Bottleneck',
       COUNT (*) AS '# of bottlenecks'
FROM sys.dm_os_waiting_tasks
WHERE resource_address <> 0
GROUP BY resource_address
ORDER BY '# of bottlenecks' DESC;
go
Resource Bottleneck # of bottlenecks
------------------- ----------------
0x027DF6C8          1
0x23C800AC          1
0x027E71F0          1
0x027E87E0          1

(4 row(s) affected)

IO Bottlenecks with MSSQL 2005

  • Set the wait_duration_ms threshold to what you think is right. Start with 20 and see what happens.
SELECT waiting_task_address AS 'Task address',
       session_id AS 'Session',
       exec_context_id AS 'Context',
       wait_duration_ms AS 'Wait in millsec',
       CAST(wait_type AS VARCHAR(30)) AS 'Type',
       resource_address AS 'Resource address',
       blocking_task_address AS 'Blocking task',
       blocking_session_id AS 'Blocking session',
       CAST(resource_description AS VARCHAR(30)) AS 'Resource'
FROM sys.dm_os_waiting_tasks
WHERE wait_duration_ms > 20 AND
      wait_type LIKE '%PAGEIOLATCH%';
go
Task address       Session Context     Wait in millsec      Type                           Resource address   Blocking task      Blocking session Resource
------------------ ------- ----------- -------------------- ------------------------------ ------------------ ------------------ ---------------- ------------------------------

(0 row(s) affected)

In this case, there is no IO bottleneck! Better not be any, this is NOT a production server.