That Conference 2016, Kalahari Resort, Lake Delton, WI
Tell SQL Server Profiler to Take a Hike – Jes Borland

Day 1, 8 Aug 2016

Disclaimer: This post contains my own thoughts and notes based on attending That Conference 2016 presentations. Some content maps directly to what was originally presented. Other content is paraphrased or represents my own thoughts and opinions and should not be construed as reflecting the opinion of the speakers.

Executive Summary

• SQL Server Profiler has been deprecated
• We should start using Extended Events (XE)

1998

• SQL Server 7.0, introduced trace
• Profiler was GUI for trace

Profiler

• 2012–deprecated
• Replaced by Extended Events

Extended Events

• Started out bad, but much more usable now

Why use

• More thorough
• Less resource intensive
  • Profiler hits system hard, can bring server to its knees
• Covers new features
  • Can no longer use profiler for some features
  • Same # events in profiler forever; but always adding XE
• Azure
  • Can’t use Profiler, but can use XE

Where data is gathered

• Post-processing vs. Pre-processing
• Start session, Gather ino, Output
• Profiler
  • Filters after gathering information
• XE
  • Stuff filtered before going into buffer

Common Profiler uses

• Main uses
  • Capture queries executed, their performance
  • Capture deadlocks
  • Capture errors and warnings
• Can use Extended Events for all the same things

Capturing all queries with Extended Events

• Management | Extended Events
• Sessions–already running sessions
• system_health always running
• Rt-click: New Session… (don’t use wizard)
• Can start when server starts
• Events
  • degree_of_parallelism – help capture every event on an object
  • sp_statement_completed, sql_statement_completed,
• Must click Configure to pick database
• Events have fields (properties)
  • e.g. cpu_time
  • Clicking on event, you see properties
• Global fields
  • Properties that all events have in common
  • Common: client_app_name, database_name, query_hash, sql_text
• Filter / Predicate
  • Select all and then filter applies to everything
  • e.g. database_name
  • duration > 5 sec
• Targets
  • ring_buffer
    • Uses memory
    • Set # items
  • event_file
    • Saves to directory on server

Viewing data

• New guy under sessions
• Rt-click | Start
• Expand, see targets
• Rt-click | View Target Data
• Choose Columns
  • Pick what you want to see
• Sort
  • Don’t do on >1,000 rows
• Can view data
  • Export to XEL (XML), CSV

Can script out the session

• Readable text that looks similar to TSQL

XEL file

• One copy per session, per run

sa permissions?

• Don’t need
• Profiler: Need ALTER TRACE
• XE 20012+: ALTER ANY EVENT SESSION
• XE is a bit more secure–can capture events but not necessarily read

Start/stop at particular time

• Use SQL Server Agent

Existing traces

• Can convert to XE sessions
• Can find equivalents
• Mapping