SQL Server Profiler

MSDN > Documentation > SQL Server > Database Engine > Tools > SQL Server Profiler

Introduction to SQL Server Profiler

SQL Server Profiler is a graphical interface to SQL Server Trace for monitoring and auditing SQL Server, tuning and troubleshooting SQL Server performance, and developing SQL Server applications.

SQL Server Profiler allows you to capture a live stream of events from one or more SQL Server instances. These events can include:

  • Statements submitted to SQL Server
  • Stored procedure executions
  • Login and logout events
  • Errors and warnings
  • Data manipulation language (DML) and data definition language (DDL) statements
  • ...and many more.

You can then analyze these events to understand how users and applications are interacting with your SQL Server instance, identify performance bottlenecks, and diagnose issues.

Key Features and Capabilities

  • Event Monitoring: Capture detailed information about SQL Server activities.
  • Trace Definition: Customize which events, data columns, and filters are included in a trace.
  • Real-time Analysis: View events as they occur.
  • Trace Saving and Replay: Save traces to files or tables for later analysis or replaying events to reproduce issues.
  • Performance Tuning: Identify slow-running queries, resource-intensive operations, and deadlocks.
  • Security Auditing: Track access to sensitive data and monitor for suspicious activity.
  • Application Debugging: Understand the SQL statements generated by your applications.

Getting Started with SQL Server Profiler

To start using SQL Server Profiler:

  1. Open SQL Server Management Studio (SSMS).
  2. Connect to your SQL Server instance.
  3. From the 'Tools' menu, select 'SQL Server Profiler'.
  4. In the 'Connect to Server' dialog, configure your connection and click 'Connect'.
  5. A new Profiler window will open. Click 'New Trace' from the 'File' menu.
  6. Configure the trace properties, including the trace name, events to capture, and filters.
  7. Click 'Run' to start the trace.
Tip: Start with a predefined trace template, such as 'Standard' or 'TSQL', and then customize it as needed. This can save time and ensure you capture essential information.

Trace Configuration

When creating a trace, you'll define several key components:

Events

Events represent specific actions that occur on SQL Server. You can select individual events or use event categories.

Commonly used events include:

  • SQL:BatchStarting and SQL:BatchCompleted
  • SP:StmtStarting and SP:StmtCompleted
  • Audit Login and Audit Logout
  • ErrorLog

Data Columns

Data columns provide details about each captured event. Select the columns that are most relevant to your analysis.

Essential data columns often include:

  • ApplicationName
  • ClientProcessID
  • DatabaseName
  • HostName
  • LoginName
  • NTUserName
  • NTDomainName
  • SPID
  • StartTime
  • TextData (for the SQL statement)
  • Duration
  • CPU
  • Reads
  • Writes

Filters

Filters help reduce the amount of data captured by specifying criteria for events to be included or excluded. This is crucial for performance and managing large traces.

Common filtering criteria:

  • DatabaseName: Filter by specific databases.
  • LoginName: Filter by specific users or applications.
  • ApplicationName: Filter by client application.
  • SPID: Filter by server process ID.
  • Duration: Filter out very fast queries if you're looking for performance issues.

Example Trace Configuration

To diagnose a slow-running query, you might configure a trace as follows:

  • Trace Name: SlowQueryDiagnostic
  • Events: SQL:BatchStarting, SQL:BatchCompleted, SP:StmtStarting, SP:StmtCompleted
  • Data Columns: ApplicationName, LoginName, DatabaseName, SPID, StartTime, Duration, CPU, TextData
  • Filters:
    • Duration Greater than 1000 milliseconds
    • DatabaseName Equals 'YourDatabaseName'

-- Example of a T-SQL statement that might be captured and analyzed
SELECT
    CustomerID,
    CompanyName,
    ContactName
FROM
    Customers
WHERE
    City = 'London';

Best Practices

Important: Running SQL Server Profiler against a production server can introduce overhead. Always be mindful of the impact and use it judiciously.
  • Start Small: Begin with a minimal set of events and data columns.
  • Use Filters Extensively: Filter out noise and focus on the relevant data.
  • Save Traces: Save traces to files or tables for later analysis. This is less intrusive than keeping the Profiler GUI open indefinitely.
  • Use Trace Templates: Leverage built-in templates and modify them.
  • Monitor Profiler's Own Overhead: Be aware that Profiler itself consumes resources.
  • Consider Extended Events: For more modern and less intrusive monitoring, explore SQL Server's Extended Events platform, which offers greater flexibility and lower overhead.