SQL Server 2005 DMV - quick information to find resource allocation & DDL bottleneck

Initially I preferred to put this blog post within Performance tuning blog section here, but as it relates to the TSQL script thought this is best place to go.

Anyways, if you have a performance problem the foremost option is to find whether the server resources are utilized properly or not, this is where SQL Server migth encounter the allocation bottleneck in terms of memory & query exeuction.

The following DMV will get the information about about the waits encountered by threads that are in execution. You can use this view to diagnose performance issues with SQL Server and also with specific queries and batches, by using the TSQL below you can identify the allocation bottleneck:

SELECT session_id, wait_duration_ms, resource_description

FROM sys.dm_os_waiting_tasks

WHERE wait_type LIKE 'PAGE%LATCH_%' AND resource_description like '2:%'

As you can see the above query will get information for TEMPDB that represents any page in TEMPDB acorss any file. Refer to Kalen Delaney's Inside SQL Server 2005: Query Tuning and Optimization book for  more information.

Second part of the subject refers about identifying the DDL bottlenecks, which is similar to finding the allocation contention by getting the current worker threads that are waiting by using the above TSQL.