Transcript Session Title

Sofia, Bulgaria | 9-10 October
SQL Server Profiler
Richard Campbell
Who Am I?
● Consultant in development
methodology and high scaling systems
● Microsoft Regional Director
● Partner in PWOP Productions
● Author of Advisor Answers
● www.campbellassociates.ca
Sofia, Bulgaria | 9-10 October
PWOP Productions
● .NET Rocks!
● Internet Audio Talkshow for .NET
Developers
● www.dotnetrocks.com
● dnrTV
● Screencast (see the code!)
● www.dnrtv.com
Sofia, Bulgaria | 9-10 October
Overview
● Understand performance problems
● Answer the question “Why is it slow?”
● Tune up your SQL Server
● SQL Server behavior changes over time
● Understand middleware
● ODBC and OLE DB change your
queries!
Sofia, Bulgaria | 9-10 October
Profiler Terms
● Event
● All data coming and going from SQL
Server is considered an event
● Connections, queries, stored
procedures, cursors, batches, locks,
errors; everything is an event!
● Related events are grouped into Event
Categories (all connection events, etc)
Sofia, Bulgaria | 9-10 October
Profiler Terms
● Data Columns
● Information about events is displayed
as data columns
● The Event Class column identifies the
event
● Different events provided different data
columns (although many are common
across events)
Sofia, Bulgaria | 9-10 October
Profiler Terms
● Trace
● A record of all the events that occur for
a given period in a SQL Server
● You define which events you wish to
trace
● Can be saved to a file for later
examination and analysis
● Some traces can be replayed
Sofia, Bulgaria | 9-10 October
Profiler Terms
● Template
● A file that records what events and data
columns to capture in a particular trace
● Profiler comes with a set of templates
● You can build your own templates to
examine issues in your SQL Server
● There are several templates included
with this session
Sofia, Bulgaria | 9-10 October
Scenarios
● Performance Issues
● Find the slowest running
queries/stored procedures
● Identify most frequently used queries
(the best candidates for performance
tuning)
● Fight back from deadlocks and
blocking
Sofia, Bulgaria | 9-10 October
Scenarios
● Tuning
● What indexes will improve
performance?
● What indexes aren’t being used and
should be removed?
● How efficiently is SQL Server executing
stored procedures and queries?
Sofia, Bulgaria | 9-10 October
Scenarios
● Application Issues
● ODBC and OLE-DB can and will change
your queries to provide features as
needed
● Often this involves a cursor – a HUGE
performance hit
● Without Profiler, you’d have NO WAY to
find out that this was happening
Sofia, Bulgaria | 9-10 October
Other Capabilities
● Replaying Traces
● You can execute the queries from a
trace into SQL Server again
● This provides a method for “proving” a
fix to the database design
● Also provides a means to transfer
changes to a database from one to
another (such as from production to
test)
Sofia, Bulgaria | 9-10 October
Other Capabilities
● Replaying Traces
● You have to capture all data change
related events and data columns for
replaying to be available
● There is a trace included with Profiler
that has the right parameters set for
replaying
Sofia, Bulgaria | 9-10 October
Performance Concerns
● Production Trace Loads
● The more you trace, the more load you
exert on your SQL Server
● Be aware that very generic traces (lots
of events and data columns) can
significantly impact database
performance
● Generic traces create HUGE trace files
(have you got enough drive space)
Sofia, Bulgaria | 9-10 October
Conclusions
● Profiler provides effective means to
understand what’s actually happening
at the SQL Server level
● Use Profiler to identify slow queries
● Use your optimization time carefully by
identifying the worst queries and stored
procedures
Sofia, Bulgaria | 9-10 October
Conclusions
● The Index Tuning Wizard can tell you
what indexes are missing or useless
● SQL Server behavior changes as the
data changes, so use Profiler on an ongoing basis
● Be aware of your application
middleware is up to!
Sofia, Bulgaria | 9-10 October
Questions?
Sofia, Bulgaria | 9-10 October