Tracking wait statistics per T-SQL statement

Question

I am creating an application that tracks some specific performance-related statistics from SQL Server for every query executed on the server. I can get most of the information through SQL Trace or more specifically, using the StatementCompleted and BatchCompleted event classes.

Using SQL Trace with the above events gives me everything except wait stats. I know I can get the wait statistics separately from the two Dynamic Management Views and Functions sys.dm_os_wait_stats and sys.dm_os_waiting_tasks.

I need to find a way to somehow club these two information together so that I can determine the wait stats on a per query basis. For example, I need to be able to answer a question like: How long did query X had to wait for an IO or CPU resource before it could begin execution?

I cannot answer this question accurately because SQL Trace does not give me this information. Is there anyway to obtain wait stats from SQL Server for every statement that is being executed on the server?

Martin Smith · Accepted Answer · 2011-11-14 12:25:14Z

3

If you are on SQL Server 2008 you can use extended events to capture details of the sqlos.wait_info event filtered on the spid of interest.

See SQLSkills: Capturing wait stats for a single operation for example code.

answered Nov 14, 2011 at 12:25

Martin Smith

457k97 gold badges777 silver badges886 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rahul Ravindran Over a year ago

This is exactly what I was looking for. Although, it is a bit expensive to use to track every query. Thanks for the quick response!

Collectives™ on Stack Overflow

Tracking wait statistics per T-SQL statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related