# Wednesday, May 16, 2012

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.
Comments are closed.