What:
SQL Server Profiler is a rich interface to create and manage traces and analyze and replay trace results of SQL Trace for monitoring an instance of the Database Engine or Analysis Services. Results can be saved in trace files or table for further investigation.
When:
SQL Server Profiler is
used for activities such as:
·
Stepping through problem
queries to find the cause of the problem.· Finding and diagnosing slow-running queries.
· Capturing the series of Transact-SQL statements that lead to a problem.
· Monitoring the performance of SQL Server to tune workloads.
· Correlating performance counters to diagnose problems.
· Finding problems for table locks and dead lock information
· Group all slow running queries for applications.
· Use results in Tuning Advisor for optimization
· Finding XML and deadlock execution plan to understand where exactly it is occurring.
· Audits record and security-related actions
How:
There are predefined trace templates that allow you to easily configure the event classes that you will most likely need for specific traces.
SQL Server Profiler can trace a variety of
server types. For example you can trace Analysis Services and SQL Server 2005
(and later versions). Therefore, SQL Server Profiler maintains different
templates for different servers, and makes available the specific template that
matches the selected server type.
Templates:
Some templates and there output are as
follows;
|
Template name
|
Template purpose
|
|
SP_Counts
|
Captures stored
procedure execution behavior over time.
|
|
Standard
|
Generic starting point
for creating a trace. Captures all stored procedures and Transact-SQL batches
that are run. Use to monitor general database server activity.
|
|
TSQL
|
Captures all
Transact-SQL statements that are submitted to SQL Server by clients and the
time issued. Use to debug client applications.
|
|
TSQL_Duration
|
Captures all
Transact-SQL statements submitted to SQL Server by clients, their execution
time (in milliseconds), and groups them by duration. Use to identify slow
queries.
|
|
TSQL_Grouped
|
Captures all
Transact-SQL statements submitted to SQL Server and the time they were
issued. Groups information by user or client that submitted the statement.
Use to investigate queries from a particular client or user.
|
|
TSQL_Locks
|
Captures all of the
Transact-SQL statements that are submitted to SQL Server by clients along
with exceptional lock events. Use to troubleshoot deadlocks, lock time-out,
and lock escalation events.
|
|
TSQL_Replay
|
Captures detailed
information about Transact-SQL statements that is required if the trace will
be replayed. Use to perform iterative tuning, such as benchmark testing.
|
|
TSQL_SPs
|
Captures detailed
information about all executing stored procedures. Use to analyze the
component steps of stored procedures. Add the SP:Recompile event if
you suspect that procedures are being recompiled.
|
|
Tuning
|
Captures information
about stored procedures and Transact-SQL batch execution. Use to produce
trace output that Database Engine Tuning Advisor can use as a workload to
tune databases.
|
Step1: I have opend Sql server Profiler from SSMS Sql server management studio-- > tools tab.
Step2: From Pic1 you can view how to create New template
You can save your new template with your own name as I have set name to [My Template] using default sp_lock profiler template. If You want to create your own uncheck existing profiler check box and in Event Selection, select class events of your own requirement.
Step3: Once Template saved, it is listed in other templates as you can see in pic2. There are severl other usefull options for profiler as I have enables all
- You can save profiler results into trace file
- You can save profiler results into database table
- Trace can be stopped at given time
- Trace can be set in table as how many rows you want to store and many others
No comments:
Post a Comment