THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Extended Events

Sampling SQL server batch activity

Recently I was troubleshooting a performance issue on an internal tracking workload and needed to collect some very low level events over a period of 3-4 hours.  During analysis of the data I found that a common pattern I was using was to find a batch with a duration that was longer than average and follow all the events it produced. 

This pattern got me thinking that I was discarding a substantial amount of event data that had been collected, and that it would be great to be able to reduce the collection overhead on the server if I could still get all activity from some batches.

In the past I’ve used a sampling technique based on the counter predicate to build a baseline of overall activity (see Mikes post here).  This isn’t exactly what I want though as there would certainly be events from a particular batch that wouldn’t pass the predicate.  What I need is a way to identify streams of work and select say one in ten of them to watch, and sql server provides just such a mechanism: session_id.  Session_id is a server assigned integer that is bound to a connection at login and lasts until logout.  So by combining the session_id predicate source and the divides_by_uint64 predicate comparator we can limit collection, and still get all the events in batches for investigation.

CREATE EVENT SESSION session_10_percent ON SERVER

ADD EVENT sqlserver.sql_statement_starting(

    WHERE (package0.divides_by_uint64(sqlserver.session_id,10))),

ADD EVENT sqlos.wait_info (

       WHERE (package0.divides_by_uint64(sqlserver.session_id,10))),

ADD EVENT sqlos.wait_info_external (

       WHERE (package0.divides_by_uint64(sqlserver.session_id,10))),

ADD EVENT sqlserver.sql_statement_completed(

    WHERE (package0.divides_by_uint64(sqlserver.session_id,10)))

ADD TARGET ring_buffer

WITH (MAX_DISPATCH_LATENCY=30 SECONDS,TRACK_CAUSALITY=ON)

GO

 

There we go; event collection is reduced while still providing enough information to find the root of the problem. 

By the way the performance issue turned out to be an IO issue, and the session definition above was more than enough to show long waits on PAGEIOLATCH*.

 

 

 

 

Published Thursday, February 10, 2011 5:25 PM by extended_events

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About extended_events

The SQL Server Extended Events team at Microsoft is responsible for the care and feeding of the Extended Events infrastructure. Extended Events was introduced in SQL Server 2008 and supports generic eventing within SQL Server. Currently Extended Events is used for diagnostic tracing and as one of the technologies underlying SQL Server Audit.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement