The SQL Server master database contains many dynamic views that contain information about the current state of the SQL Server. One such view is dm_os_workers, which lists all active threads run by SQL Server and information about each thread. Of particular interest are the error columns:
One or more of the following bit columns will return 1 if there is anything is going wrong with a thread:
- is_sick
- is_in_cc_exception
- is_fatal_exception
- is_inside_catch
One limitation of this view is that it does not retain any history. If you want to keep a history of threads, you can create a Database to hold that history and the following SQL to copy the results of dm_os_workers to a table in that database. The following SQL copies the dm_os_workers view results to the dbo.ThreadsHistory table in the Instrumentation database.
IF NOT EXISTS
(SELECT *
FROM Instrumentation.sys.objects
WHERE object_id = OBJECT_ID(N'[Instrumentation].[dbo].[ThreadsHistory]')
AND type IN (N'U'))
BEGIN
SELECT
GETDATE() AS TimeLogged,
worker_address,
status,
is_preemptive,
is_fiber,
is_sick,
is_in_cc_exception,
is_fatal_exception,
is_inside_catch,
is_in_polling_io_completion_routine,
context_switch_count,
pending_io_count,
pending_io_byte_count,
pending_io_byte_average,
wait_started_ms_ticks,
wait_resumed_ms_ticks,
task_bound_ms_ticks,
worker_created_ms_ticks,
exception_num,
exception_severity,
exception_address,
locale,
affinity,
state,
start_quantum,
end_quantum,
last_wait_type,
return_code,
quantum_used,
max_quantum,
boost_count,
tasks_processed_count,
fiber_address,
task_address,
memory_object_address,
thread_address,
signal_worker_address,
scheduler_address,
processor_group
INTO [Instrumentation].[dbo].[ThreadsHistory]
FROM sys.dm_os_workers
WHERE 1=0
END
DECLARE @TimeNow AS DATETIME
SELECT @TimeNow = GETDATE()
INSERT INTO Instrumentation.dbo.ThreadsHistory
(
TimeLogged,
worker_address,
status,
is_preemptive,
is_fiber,
is_sick,
is_in_cc_exception,
is_fatal_exception,
is_inside_catch,
is_in_polling_io_completion_routine,
context_switch_count,
pending_io_count,
pending_io_byte_count,
pending_io_byte_average,
wait_started_ms_ticks,
wait_resumed_ms_ticks,
task_bound_ms_ticks,
worker_created_ms_ticks,
exception_num,
exception_severity,
exception_address,
locale,
affinity,
state,
start_quantum,
end_quantum,
last_wait_type,
return_code,
quantum_used,
max_quantum,
boost_count,
tasks_processed_count,
fiber_address,
task_address,
memory_object_address,
thread_address,
signal_worker_address,
scheduler_address,
processor_group
)
(
SELECT
@TimeNow,
worker_address,
status,
is_preemptive,
is_fiber,
is_sick,
is_in_cc_exception,
is_fatal_exception,
is_inside_catch,
is_in_polling_io_completion_routine,
context_switch_count,
pending_io_count,
pending_io_byte_count,
pending_io_byte_average,
wait_started_ms_ticks,
wait_resumed_ms_ticks,
task_bound_ms_ticks,
worker_created_ms_ticks,
exception_num,
exception_severity,
exception_address,
locale,
affinity,
state,
start_quantum,
end_quantum,
last_wait_type,
return_code,
quantum_used,
max_quantum,
boost_count,
tasks_processed_count,
fiber_address,
task_address,
memory_object_address,
thread_address,
signal_worker_address,
scheduler_address,
processor_group
FROM sys.dm_os_workers
)
You can use SQL Agent to schedule a job that runs this every 60 seconds (or however frequently you want) to keep a history of the threads being generated by SQL. This history can tell you if threads are generating exception and if thread counts are increasing.
Steve Latsch contributed to this article.