1 / 17

SQL Server Profiler

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. PWOP Productions. .NET Rocks!

yepa
Download Presentation

SQL Server Profiler

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Server Profiler Richard Campbell

  2. 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

  3. PWOP Productions • .NET Rocks! • Internet Audio Talkshow for .NET Developers • www.dotnetrocks.com • dnrTV • Screencast (see the code!) • www.dnrtv.com

  4. 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!

  5. 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)

  6. 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)

  7. 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

  8. 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

  9. 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

  10. 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?

  11. 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

  12. 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)

  13. 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

  14. 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)

  15. 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

  16. 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 on-going basis • Be aware of your application middleware is up to!

  17. Questions?

More Related