Introduction
SQL
Server Profiler is a powerful tool that is available with SQL Server since a
long time; however, it has mostly been utilized by DBAs for the analysis and troubleshooting the SQL server performance. SQL Server Profiler
can perform various significant functions such as tracing what is running under
the SQL Server Engine, and finding out how queries are resolved internally and
what scripts are running to accomplish any T-SQL command. Profiler captures
only SQL server events.
The
major functions this tool can perform have been listed below:
1. Creating
trace
2. Watching
trace
3. Storing
trace
4. Replaying
trace
SQL Trace: Inside
the database engine, SQL Server provides an event
subsystem called SQL Trace that is based on an external application programming
interface (API). This external API enables us to call SQL Trace by using a
variety of parameters that defines events and column of data to capture.
Traces
allow us to track the specific actions performed against a SQL Server
databases.
Starting the Profiler
It is mandatory for the
user to have “system admin” rights to start the profiler.
Login permissions to run
the SQL Server Profiler without granting the login the
Server Role of “sysadmin :-
We need
ALTER TRACE permissions at the server level. Just be aware that this is a
SERVER level permission so any user we grant this to can trace ANY database.
Use master
Go
Grant Alter Trace to Login
It can
be started using the following methods.
1.
Going to Start >> All Programs >> Microsoft SQL Server 2008
>> Performance Tools >> SQL Server Profiler
2. From SQL Server Management Studio >> Go to Tools >> SQL Server Profiler
3. For
SQL Server 2008, type profiler in the command prompt.
4. For
SQL Server 2005, type profiler90 in the command prompt.
Collecting Data
Once
the profiler is started, connect it to any database. A profiler is not
restricted to connecting only to the local database. With appropriate
authentication and system admin role, it can connect to any database and
capture the data.
Once
the profiler is started click on Menu >> File >> New Trace.
This
will display a login prompt very similar to SQL Server Database Engine. Connect
with the appropriate username and password.
It
will display the ‘Trace Properties’ screen. On this screen you can enter the
trace name. You will notice that trace provider name, type and versions are
pre-populated and cannot be altered. These are set based on which SQL Server
instance you are connected with.
It has two selection
tabs:
·
General:
It is used for general
setting for Trace Database Engine.
·
Event: It is used to add or remove some selected
event for monitor.
It is divided into
four sections.
Part 1: In this section, you have to just specify the
name of your trace, Trace provider name and server name are predefined and
based upon your SQL Server.
Part 2: It is the template section. You can choose
different type of Templates based upon your requirements. It is the
configuration for trace. By default, it is "Standard (Default)"
templates. Other
templates are T-SQL, T-SQL Duration, T-SQL Reply, T-SQL SPs. You can create your own custom Templates as
well.
Part 3: This section is related to save your trace either as File (.trc)
or in a database as table.
While clicking on Save to file check box, File save
dialog box should open and you can save that file (with .trc extension). In the
Set maximum file size check box, specify a maximum file size for the trace.
When the file size reaches this maximum, trace events are no longer recorded in
this file.
If you select Enable file rollover the following occurs:
The file rollover
option causes SQL Server to close the current file and create a new file when
the maximum file size is reached. If the name assigned to a new rollover file is
already used by an existing file, the existing file is overwritten unless it is
read-only. The file rollover option is enabled by default when you are saving
trace data to a file. With the file rollover option on, the trace continues
until it is stopped by some other means. To stop the trace after you have
reached the file size limit, disable the file rollover option.
If you check the "Save
to Table", it will connect with your server and ask you to which
database you want to save that trace table information.
Part 4: You can stop your trace on a particular time.
Check the "Enable trace stop time" checkbox and give the time
at which you want to stop track, SQL Server will automatically stop trace on
that time.
Now Move To "Event
Section" Tab.
What is an Event?
An Event is an action
or operation that is performed in your SQL Server Database Engine.
Some examples of
Events are:
o Transact-SQL SELECT, INSERT, UPDATE, and
DELETE statements.
o User login and logout
o Execution of Stored procedures
o Operation with cursor
SQL Server profiler is
able to trace all of these events and all these events are categories on a
particular Event class.
What is an Event Class?
Event class is a type
of event that can be traced.
Some examples are:
·
SQL: BatchCompleted
·
SQL: Batch Starting
Lock: Acquired
· Lock: Released
·
Audit Login
·
Audit Logout
·
Now you can select
events from this screen:
Column Filter: is something like customization. Just click
on the "Column Filter Button". In this section, you can specify some
conditions (like or not like).
Note:
Using filters can eliminate all the background processes that issue queries to
the system databases. Filters also enable us to isolate our monitoring to a
particular database or database-related activity for a single application.
Organize Column: By clicking on "Organize Column" button, we
can change the sequence of order of selected events. This does not change the
internal storage order for the trace data.
Now Click on the
"Run" Button, then below Trace window will come:
Using
these windows, we will get the detailed time duration of a query and all other
events information that we have selected.
You can save this
result and use it in future. Or you can extract a particular query from the
trace, just right click and click on "Extract Event Data". And
save this as a SQL Script.
Starting, Pausing, and Stopping a Trace:
After a trace is running, we can control it from within
Profiler. In the middle of the toolbar are buttons to start, pause, and stop a
trace.
When we click Pause, the data gathering is suspended at the
server level. Any events that occur while the trace is paused are not captured.
Stopping a trace closes the trace session. Although we can
then restart the trace, the data capture is reset, and all previous data is
discarded. Thus, we should stop a trace only when we are finished capturing all
the data we need.
Running a Trace for specific databases
In
order to run trace against some specific database take the below steps:
1. Go to the Trace
properties and click on Event Selection
2. Now click on column filter in Event selection
window
3. Edit Filter dialog box
opens where select database Name option
4. Then go to like Filter
and Database name must be
‘%database-Name%’
Replay Trace in SQL Server
Profiler
SQL Server profiler
has a Reply facility which has the ability to save a trace and replay it later.
When profiler replays a trace, it spawns
multiple execution threads that use the same security context as the original
execution.The start time, end time, and system process ID (SPID) column enable
profiler to re-create the exact interleaving of queries that occurred on the
original system.
0 comments: