SQL Server Profiler

What is Profiler?

A graphical tool used to capture SQL Server Engine’s events.

Major functions

• Creating Trace
•  Watching Trace
•  Storing trace as Tables or Trace Files
•  Replaying Trace

SQL Profiler Uses

• Monitor the performance of an instance of SQL Server
• Debug Transact-SQL statements and stored procedures
• Identify slow-executing queries
• Test SQL statements and stored procedures in the development phase of a project by single-stepping through statements to confirm that the code works as expected
• Troubleshoot problems in SQL Server by capturing events on a production system and replaying them on a test system
• Audit and review activity that occurred on an instance of SQL Server to review any of the auditing events, including the success and failure of a login attempt and the success and failure of permissions in accessing statements and objects

Profiler Terminology

• Event:

          An action within an instance of SQL Server Database Engine         

          Examples:

1. Running any T-SQL or performing any operation in the SSMS that is related to database

2. Running stored procedures, and creating jobs

• Event Class:

           A type of event that can be traced and a column in a trace describes the event type.

           Examples:

                         1. SP:Starting and RPC:Completed.

• Event Category:

          Group of events are called an event category

           Examples:

                 1. Stored Procedure, Locks and errors and warnings

                          2. Store Procedures -> SP: Starting; SP: Completed; SP: StmtStarting; SP: StmtCompleted

• Data Column:

          Data column contains value of an event class.

          Examples:

                        1. StartTime, EndTime, Duration, SPID, DatabaseID, ObjectID

• Filter:

          Create seductiveness in data that are collected in trace.

          Filtration Reasons:

                            1. Profiler is too much expensive for the production server’s performance

                             2. Profiler capture too much data and making it extremely difficult to analyse later

• Template:

           Default configuration pattern for trace.Can be saved, imported and exported between instances

           Examples:

                         1. Standard template -> Captures all SP and T-SQL batches