I really enjoyed the NTSSUG meeting on Thursday. Bob Ward (blog, Twitter), a major role model of mine, presented Inside SQL Server Wait Types. As with the SQLskills Immersion Events, I spent the whole time taking copious notes as the priceless information flowed.

While discussing THREADPOOL waits, I missed the name of a helpful DMV for identifying a lack of worker threads. I did hear the name of the relevant column though… “work_queue_count.” Thanks to SQL Server’s catalog views, it was easily to track down the DMV in question, [sys].[dm_os_schedulers]:

SELECT [_Objects].[object_id] AS [ObjectID],
    [_Objects].[name] AS [ObjectName],
    [_Objects].[type_desc] AS [ObjectType],
    [_Columns].[column_id] AS [ColumnID],
    [_Columns].[name] AS [ColumnName]
FROM [master].[sys].[system_objects] AS [_Objects]
INNER JOIN [master].[sys].[system_columns] AS [_Columns]
ON ([_Objects].[object_id] = [_Columns].[object_id])
WHERE (
    ([_Objects].[name] LIKE N'dm_%')
    AND
    ([_Objects].[type] IN ('IF', 'TF', 'V'))
    AND
    ([_Objects].[schema_id] = Schema_ID('sys'))
    AND
    ([_Objects].[is_ms_shipped] = 1)
    AND
    ([_Columns].[name] LIKE N'%work%queue%count%')
)
ORDER BY [_Objects].[name] ASC,
    [_Columns].[name] ASC;

This type of query is also useful when searching for gems in SQL Server’s meta data:

SELECT [_Objects].[object_id] AS [ObjectID],
    [_Objects].[name] AS [ObjectName],
    [_Objects].[type_desc] AS [ObjectType],
    [_Columns].[column_id] AS [ColumnID],
    [_Columns].[name] AS [ColumnName]
FROM [master].[sys].[system_objects] AS [_Objects]
INNER JOIN [master].[sys].[system_columns] AS [_Columns]
ON ([_Objects].[object_id] = [_Columns].[object_id])
WHERE (
    ([_Objects].[type] IN ('IF', 'TF', 'V'))
    AND
    ([_Objects].[schema_id] = Schema_ID('sys'))
    AND
    ([_Objects].[is_ms_shipped] = 1)
)
ORDER BY [_Objects].[name] ASC,
    [_Columns].[column_id] ASC;