Query tuning, optimization and troubleshooting are all possible by the ability to view what's going on inside SQL Server | ISQL Users Group May 2009 Tracing and Profiling SQL Server Over the next 1 hour: SQL Trace Architecture and Terminology Security and Permissions Profiler Server side Tracing Saving and Replaying Traces Querying Server-Side Trace Metadata Introduction Query tuning, optimization and troubleshooting are all possible by the ability to view what's going on inside SQL Server SQL Trace provides a real-time view into what’s going on inside the database engine and at a very granular level SQL Trace Architecture and Terminology SQL Trace is an SQL Server database engine technology SQL Server Profiler is a .NET application that uses system stored procedures exposing the functionality of SQL Trace 2000 requires membership of the sysadmin server role 2005 ALTER trace permissions is more flexible – granted to a | ISQL Users Group May 2009 Tracing and Profiling SQL Server Over the next 1 hour: SQL Trace Architecture and Terminology Security and Permissions Profiler Server side Tracing Saving and Replaying Traces Querying Server-Side Trace Metadata Introduction Query tuning, optimization and troubleshooting are all possible by the ability to view what's going on inside SQL Server SQL Trace provides a real-time view into what’s going on inside the database engine and at a very granular level SQL Trace Architecture and Terminology SQL Trace is an SQL Server database engine technology SQL Server Profiler is a .NET application that uses system stored procedures exposing the functionality of SQL Trace 2000 requires membership of the sysadmin server role 2005 ALTER trace permissions is more flexible – granted to a login When tracing we monitor for specific events which are generated when various actions occur in the database engine For example, a user login or the execution of a query are each actions that cause events to fire Each event has an associated collection of columns that contain the collected data when the event fires SQL Server uses a model that selectively enables data collection as required Data is never collected until someone asks for it There are around 171 events and 65 columns to select from Around 106 events in 2000 The main component of the SQL Trace architecture is the trace controller which is a shared resource that manages all traces created by any consumer There are various event producers in the database engine for example in the query processor, lock manager, and cache manager Each of these producers is responsible for generating events that relates to certain categories of server activity All producers are disabled by default and therefore generate no data When a .
