Module 17 Tracing Access to SQL Server 2008 R2

1. Module 17 Tracing Access to SQL Server 2008 R2

2. Module Overview • Capturing Activity using SQL Server Profiler • Improving Performance with the Database Engine Tuning Advisor • Working with Tracing Options

3. Lesson 1: Capturing Activity using SQL Server Profiler • Overview of SQL Server Profiler • Available Tracing Output Options • Commonly used Trace Events • Commonly used Trace Columns • Filtering Traces • Working with Trace Templates • Demonstration 1A: Capturing Activity using SQL Server Profiler

4. Overview of SQL Server Profiler • Based on the SQL Trace programming interface • Used in many scenarios such as debugging, performance monitoring, deadlock monitoring, and many others • Replay functionality for stress testing SQL Server Profiler is a tool used to trace activity against SQL Server.

5. Available Tracing Output Options • Profiler traces can be saved in tables and files • For file output, ensure that you configure: • Maximum file size (default of 5MB is almost always too small) • Enable file rollover (if appropriate)

6. Commonly used Trace Events Events: The occurrence of an action within SQL Server Categories: Groups of related trace events

7. Commonly used Trace Columns • Avoid capturing too many columns, to minimize trace size • Not all events provide data in every column • Columns can be used to group event data Trace columns represent data (often attributes) that can be captured when an event occurs.

8. Filtering Traces • Avoid capturing too many events, to minimize: • Trace size • Performance impact • Complexity during analysis • Filter is only applied when the filtered column is supported by the selected event

9. Working with Trace Templates • SQL Server provides a number of standard templates • Users can save trace templates from existing trace configurations Trace templates are predefined (or user-defined) sets of event classes and trace columns.

10. Demonstration 1A: Capturing Activity using SQL Server Profiler In this demonstration, you will see: • How to create and run a trace using SQL Server Profiler • How to use a trace to capture SQL Server activity • How to save the trace to a file

11. Lesson 2: Improving Performance with the Database Engine Tuning Advisor • Overview of Performance Tuning • Available Options for Performance Tuning • Introduction to the Database Engine Tuning Advisor • Database Engine Tuning Advisor Options • Demonstration 2A: Using the Database Engine Tuning Advisor

12. Overview of Performance Tuning

13. Available Options for Performance Tuning • Database Engine Tuning Advisor • SSMS • Activity Monitor • Standard Reports • Custom Reports • Dynamic Management Objects • SQL Server Data Collection • SQL Server Profiler • SQL Trace • SQL Server Extended Events • Reliability and Performance Monitor

14. Database Engine Tuning Advisor • Used to suggest index and statistics changes for improving performance • Processes workloads captured by SQL Server Profiler as traces Workload Database Engine Tuning Advisor Reports and Recommendations Database and Database Objects

15. Database Engine Tuning Advisor Options • Limit the time for analysis • Analysis can take a long time for large schemas and workloads • Determine the types of recommendations that should be returned

16. Demonstration 2A: Using the Database Engine Tuning Advisor In this demonstration you will see how to use the Database Engine Tuning Advisor to generate indexing and partitioning recommendations for a query

17. Lesson 3: Working with Tracing Options • Overview of SQL Trace • SQL Trace vs. SQL Server Profiler • Demonstration 3A: SQL Trace • Retrieving Trace Output • Replaying Traces • Default Trace • Combining Traces with Reliability and Performance Monitor Logs • Demonstration 3B: Combining Traces with Reliability and Performance Monitor Logs

18. Overview of SQL Trace SQL Trace is a programming interface that is based on system stored procedures and is used for capturing activity against SQL Server. • SQL Server Profiler can be used to script SQL traces • Events can be sent to files or SMO objects • SQL Server Profiler uses SMO to create and consume traces

19. SQL Trace vs. SQL Server Profiler SQL Trace SQL Server Profiler Use via a Graphical tool Utilizes SQL Trace Write to files or database tables Used for: Debugging on test systems Short term analysis Small traces • Defined through procedures • Runs directly within the database engine • Writes events to files or SMO • Used for: • Long term monitoring • Performance-critical traces • Large traces

20. Demonstration 3A: SQL Trace • In this demonstration, you will see: • How to script a trace using SQL Server Profiler • How to start a SQL Trace • How to viewing a SQL Trace using SQL Server Profiler

21. Retrieving Trace Output • SQL Server Traces are written to files • Traces can be analyzed by: • Opening the trace in SQL Server Profiler • Importing the trace into a SQL Server table CREATETABLEdbo.tracetable ( TextData varchar(max)NULL, BinaryData varbinary(max)NULL, ... ); INSERTINTOdbo.tracetable SELECT*FROMfn_trace_gettable('L:\Traces\adworks.trc',default);

22. Replaying Traces • Multithreaded playback engine to reproduce activity • Validate configuration changes • Test performance changes • Specific Events and Columns must be present in trace file • Use TSQL_Replay trace template

23. Default Trace • Traces mostly configuration changes for troubleshooting • Written to log directory of SQL Server • Uses up to five 20MB trace rollover files • Is on by default • Can be configured using sp_configure: EXECsp_configure'show advanced options', 1; RECONFIGURE; EXECsp_configure'defaulttraceenabled', 1; RECONFIGURE; EXECsp_configure'show advanced options', 0; RECONFIGURE;

24. Combining Traces with Performance Monitor Logs • Correlate Performance Monitor with SQL Trace events in SQL Server Profiler

25. Demonstration 3B: Combining Traces with Performance Monitor Logs • In this demonstration, you will see how to combine a SQL Server trace with a performance monitor log.

26. Lab 17: Tracing Access to SQL Server • Exercise 1: Capture a trace using SQL Server Profiler • Exercise 2: Analyze a trace using Database Engine Tuning Advisor • Challenge Exercise 3: Configure SQL Trace (Only if time permits) Logon information Estimated time: 45minutes

27. Lab Scenario The developers for the new marketing application are concerned about the performance of their queries. When the developers were testing the application they were working with small amounts of data and performance was acceptable. The developers are unsure that they have created appropriate indexes to support the application. You will use SQL Server Profiler to capture traces of application execution. You will then analyze the traces using the Database Engine Tuning Advisor. If you have time, you will configure traces using the SQL Trace system stored procedures.

28. Lab Review • In what situations would you use SQL Trace rather than SQL Server Profiler? • How would you limit the contents of your trace to events that are related to a specific database?

29. Module Review and Takeaways • Review Questions • Best Practices