ADMIN

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.

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.