In this post :
-Introduction
-Where SQL Server Profiler
-SQL Server Profiler Keywords
-When using SQL Server Profiler
-Using SQL Server Profiler
-About Next step
Introduction
SQL Server Profiler is a tool that
let administrator to monitoring an instance of database engine or analysis
services. The Result of monitoring is data saved in file (trace file) or stored
in database (database table) to analyze it later .
Where SQL Server Profiler
·
From start > All Programs > Microsoft SQL Server 2008 R2 > Performance
Tools > SQL Server Profiler
or
·
login to SQL server database engine
o
Select SQL Server Profiler from Tools menu
SQL Server Profiler Keywords
Event
it's about specific action fired
in an instance of SQL Server. for example :
- Login connections, failures, and
disconnections.
- The start or end of a stored
procedure.
- Transact-SQL SELECT, INSERT,
UPDATE, and DELETE statements.
EventClass
is a type of event that can be
traced .it's contains all the data that can be reported by an event .It's
determines the type of data that can be collected . for example :
- SQL:BatchCompleted
- Audit Login
- Audit Logout
EventCategory
it's a category classification for
an event.
DataColumn
It's attribute of an event class .
for example Lock:Acquired event class, the BinaryData data column contains the value of the
locked page ID or row.
Template
is a selected collection of
configuration for a trace. you can make a template with your selection of
specific events and data columns.
Trace
is a process of recording data
about specific events and data columns. SQL Server let to make a trace by SQL
Server Profiler or using System Stored Procedures.
Filter
It's a way to filter the result of
tracing . trace need to be filtered if you need a thin trace file that contains
what you need only .
When using SQL Server Profiler.
Microsoft says, it's good to use SQL Server
Profiler when one of the following case needed .
·
Find the
worst-performing queries.
·
Identify
the cause of a deadlock.
·
Monitor
stored procedure performance.
·
Audit
SQL Server activity.
·
Monitoring
Transact-SQL activity per user.
·
Collect
a representative sample of events for stress testing.
·
Collect
a sample of events for tuning the physical database design by using Database
Engine Tuning Advisor.
Using SQL Server Profiler
- Open SQL Server Profiler.
- Select New Trace from File
menu
- Connect to your instance that
you want to trace .
- The trace properties window,
will appear.
try to insert data in the
following fields in the General Tab:
·
Trace name : The name
of your trace
·
Use the template : Select
the template that your want to use. in this example leave the default
selection.
·
check in the Save to file or Save to table or both
to let profiler to know how your tracing data will be store.
in the
example : check in both option .
·
if you check Save to file option
you will need to specify file name and location .
·
if you check Save to table option you will need to specify your
server and authentication that you want tracing data to store it .
o
The Destination Table form will be appear .
§ Try to specify the
following :
·
Database : the database the host your tracing data .
·
Schema : the location of
your table inside your database selected .
·
Table : the name of the
table that you want to store your tracing data .
in
this example, type MyTable_Tbl in the Table field .
- the Enable trace stop time checkbox enable to end the tracing
in the specific date and time .
- Select the next tab, Events Selection tab. in this tab all
events and attributes that can be traced by SQL Server Profiler.
Note that, there are default selection events . it's not all events. the show
all events and show all columns checkbox can display all
events with its attributes that available .
Note also the following :
1- Button Column filters ... to filter the result of
tracing . for example it's applicable to filter the result of tracing only in
the specific database name .
2- Button Organize Columns ... to sort columns in a certain way .
- After selecting all events wanted in tracing, Press Run button
.
- The new window will appear .
it's a window of tracing . SQL Server Profiler now trace the activity of
SQL Server in the selected events and store the data of tracing in the My_Trace
file and MyTable_Tbl table that is found in the My_Test_DB
database .
by clicking on any row in the grid of tracing, the details of the event
will appear in the bottom of grid .
- SQL Server Profiler can pause the activity of tracing or stop it by
the buttons in the toolbox of SQL server Profiler.
- After a little time, Press Stop
Button . the tracing will stop now and the task of SQL Server Profiler finish
now also .
- all Traced data now stored in the file trace and table database .
(File Trace)
(Structure of table trace)
(Data stored in the Trace table)
About Next step
Database Engine Tuning Advisor is a performance tool that
let administrator to analyze and view recommendations about specific issues. this tool need to data to be analyze, this data
can be prepared from SQL Server Profiler .
Follow the following steps, in
order to analyze and view recommendation bout traced data .
- From Start > Microsoft
SQL Server 2008 R2 > Performance Tools > Database Engine
Tuning Advisor
- Try connect to SQL Server
- In the Workload section,
select File option or Table option depending on the result of SQL
server Profiler .
- from Database for workload
analysis option, select the database that will do the analysis of workload
.
- Then, simply select databases
and tables to analyze it .
- from tool bar, select Start Analysis
button. the Progress tab will appear.
- After Progress tab
finish, the Recommendations and Reports tabs will appear.
and
there are a set of reports in Tuning
Reports Section
