Getting Off Of Profiler, A Twelve-Step Program

Wayne Sheffield has a blast from the past, repeating an old T-SQL Tuesday to show how to use Extended Events:

Now that you have this XE session scripted out, it can be easily installed on multiple servers. If you encounter a deadlock problem, you can easily start the XE session and let it run to trap your deadlocks. They will be persisted to a file dedicated for the deadlocks. You can use my Deadlock Shredder script at http://bit.ly/ShredDL to read the deadlocks from the file and shred the deadlock XML into a tabular output.

Note that the default system_health XE session also captures deadlocks. I like to have a dedicated session for just deadlocks. As lightweight as XE is, sometimes it may benefit a server to turn off the system_health session. Additionally, Jonathan Kehayias has a script that will take a running trace and completely script out an XE session for it. This script can be found at https://www.sqlskills.com/blogs/jonathan/converting-sql-trace-to-extended-events-in-sql-server-2012/. Even though this script is available, I like to figure things out for myself so that I can learn what is actually going on.

Extended Events are extremely useful for administrators, typically with a fraction of the overhead cost of  server-side (much less Profiler) traces.

Related Posts

Finding Database Growth Events

Arun Sirpal has an Extended Events session which tracks growth events on a database file: A quick post that is hopefully useful, I wanted a quick way to find the time, size of the database file size change and who caused it. I went down the extended events route using sqlserver.database_file_size_change event. By the way […]

Read More

Stop Profiling

Wayne Sheffield concludes his two-part series on converting a trace to use Extended Events: This query extracts the data from XE file target. It also calculates the end date, and displays both the internal and user-friendly names for the resource_type and mode columns – the internal values are what the trace was returning. For a […]

Read More

Categories

April 2017
MTWTFSS
« Mar May »
 12
3456789
10111213141516
17181920212223
24252627282930