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.