Module 8: Tracing SQL Server Activity
1 of 35
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Module Contents:
Lesson 1: Lesson 2: Lab:
Module While monitoring of a database the activity occurring
performance analysis of problems and
identify ways This module capture information
procedures to troubleshoot and
optimize SQL
Objectives After completing • Trace activity • Use captured
26/09/2016 15:49
Module 8: Tracing SQL Server Activity
2 of 35
Lesson
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Activity
Application workloads
a sequence
of events that
events is a
valuable tool replaying workloads
purposes, and for test systems
or to test application
Lesson After completing • Use SQL • Describe commonly-used • Describe how • Work with • View trace • Use SQL
SQL Server
26/09/2016 15:49
Module 8: Tracing SQL Server Activity
3 of 35
SQL Server
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
view details of
events in a SQL
to SQL
Server and stores
captures data
when events of information ( again select which run SQL Server
definition. A variety but you can each time you templates that you
can use or modify. Options for When a SQL in the SQL Server
graphical grid send the
captured event Capture to Capturing to
Profiler
traces. When
The default
file extension
MB which
can be too small
between 500
MB or 5,000 When the allocated
the previous
filename with
called file
rollover and
enable file
rollover in the large maximum move the captured
work with a is a need to cannot work
with larger files. Capturing SQL Server programming Profiler program
SQL Trace SQL Server those rows
to the specified
26/09/2016 15:49
Module 8: Tracing SQL Server Activity
4 of 35
Note: monitored
SQL Server
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
being
data displayed
in the graphical
Trace Events
The information action in an instance
occurrence of an which are
listed as data
classes. The
information in
events.
Event SQL:BatchCompleted
BatchCompleted is first started details of the
SQL:StmtCompleted
RPC:Completed
retrieve details of
execution. similar to the
26/09/2016 15:49
Module 8: Tracing SQL Server Activity
5 of 35
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Event it contains procedure. You can procedure uses by
Audit Login/Audit
activity can be
Deadlock Graph
from Deadlock Graph event The details are SQL Server
Trace Columns
Data columns
columns in the
trace output SQL Server
writes values to
all the possible
Reads, Writes,
Duration, and
at the time
of the event.
Note:
based on 26/09/2016 15:49
Module 8: Tracing SQL Server Activity
6 of 35
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
column
You can define
should minimize
the number of
trace. You can
also organize One of the more but the values TextData column
function. by default, Completed event, the procedure.
Filtering Columns You can set filters
important to ensure
that you are only
Effective use
of filters helps
overwhelming
the server with
contained in the
trace to reduce
faster to analyze.
The filters that
column to the
trace. For example, Deadlock Graph
and capture the DatabaseName
column is not Text-based columns example, you which would
Note: activity DatabaseName than those typically
characters. For Adventure%, “Adventure”.
trace less portable server will
Trace Templates
26/09/2016 15:49
Module 8: Tracing SQL Server Activity
7 of 35
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
SQL Server
configure the
event classes
example, helps
you to create
connection
information.
starting point
for additional You can use
event classes
and data columns
you can run a
trace that records
the existing
templates to You can create interface—starting
graphical as a
template.
Viewing
26/09/2016 15:49
Module 8: Tracing SQL Server Activity
8 of 35
When you write
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
and are
somewhat difficult SQL Server provides
trace files:
• You can open
output for
analysis. SQL • You can query
files. This approach
enables you
used to
import data
the files into a
table is particularly
data, as you
can add an
the captured
data. You
The following Using fn_trace_gettable
SELECT * default);
26/09/2016 15:49
Module 8: Tracing SQL Server Activity
9 of 35
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Demonstration: In this demonstration, • Use SQL • Run a trace
Demonstration Use SQL Server 1.
Ensure and log
are running, the
password 2.
In the D:
3.
Start SQL
database engine
instance 4.
On the
5.
When SQL
instance
using Windows 6.
7.
In the Trace
o
Trace
o
Use
o
Save
In the Trace
properties:
events and
columns 8.
Select Show
Then clear
26/09/2016 15:49
Module 8: Tracing SQL Server Activity
10 of 35
Show all
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
selected are
shown. 9.
Select Show
StmtCompleted
event. 10. Click the box, expand
Filter dialog all columns
so that
Run a Trace 1.
In the Trace
2.
Observe
3.
Switch
file in the
D:\Demofiles\Mod08
the
AdventureWorks 4.
While the
observe the
activity. 5.
When the
click Stop
Trace. 6.
In the trace,
the
Transact-SQL 7.
Keep SQL
next
demonstration.
SQL Trace
26/09/2016 15:49
Module 8: Tracing SQL Server Activity
11 of 35
SQL Server options you choose, SQL Trace is minimizing the
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
depending upon the being traced. when Server
Profiler uses SQL Trace is
using
system stored
Trace
facility in SQL Traces run in application using learned about
traces. file or an information you directly
applicable to At first, implementing calls to define Profiler to create changes need
stored procedure SQL Server very few creates—this
generally involves SQL Trace It is important
Profiler and 26/09/2016 15:49
Module 8: Tracing SQL Server Activity
12 of 35
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
in which scenarios • You need Profiler provides
SQL Server tracing
activity. • SQL Trace client system (
Profiler runs on a using the SQL
Trace procedures. • SQL Trace
SQL Server
Profiler can • SQL Trace
large traces
that would
Server Profiler
is more commonly
analysis, or
capturing
Note: as scripting creates through
Traces do not to be run constantly,
the same option the events
a trace needs launch it, and then
mark the stored
Demonstration: In this demonstration, • Export a trace • Configure
Demonstration 26/09/2016 15:49
Module 8: Tracing SQL Server Activity
13 of 35
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Export a Trace 1.
Ensure
2.
In SQL Export
3.
Save the
point to - 2014. \Demofiles\Mod08 folder, and
click OK 4.
Keep SQL
Configure and 1.
In SQL D:\Demofiles\Mod08
the in the
previous 2.
View the
sp_trace_create,
replace 3.
Click Execute
4.
Switch
5.
When the
6.
In the StopTrace. DECLARE
7.
Select the status to
query. \Demofiles\Mod08 folder. necessary, modify the previously. Setting the trace trace
definition 8.
Select the
review the
traced events. 9.
Close the
changes so
that only
26/09/2016 15:49
Module 8: Tracing SQL Server Activity
14 of 35
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
10. Keep SQL
Lesson After you have trace to analyze and optimize
can use the troubleshoot problems can use traces,
and these are
Lesson After completing • Replay traces. • Use the Database
trace.
• Combine • Use a trace
Replaying
You can replay
the workload
26/09/2016 15:49
Module 8: Tracing SQL Server Activity
15 of 35
on a SQL Server to a system or You can also
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
considering making layout changes. problem.
Replaying For a trace to that you include options in the
You can ensure set certain to replay the
trace on and The replay does captured on,
events were particularly applies
to objects such Replaying SQL Server for functional
may be useful realistic,
scaled-out conditions.
Server that
enables you
scalable solution
that better simulates The Distributed • Administration
use to start,
monitor, and • Replay controller
actions of the
replay clients. • Replay clients
service to
replay the • Target server
clients replay
the traces.
26/09/2016 15:49
Module 8: Tracing SQL Server Activity
16 of 35
Distributed Replay clients, and the
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
controller, the contains
configuration • Controller The controller default, only
use. By information and
warning messages. • Client configuration The client
the folders
in which to • Administration The administration system session
to include time during the
trace. • Replay configuration
The replay configuration connection pooling,
whether to use elements to
control the number
Additional contents, http://technet.
their
The Database
26/09/2016 15:49
Module 8: Tracing SQL Server Activity
17 of 35
You can use indexing and improve database addition to optimizing
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
existing recommendations for how to structures. In Advisor can
recommend new
Engine Tuning
Advisor also
amount of time
the tuning algorithms
utility—enabling you to take
advantage of Workloads The Database
workloads
run against one
traces
captured by your databases,
workload on improving
system performance. A workload
you want to
tune. The workload
trace file
generated by You can also
SQL Profiler. Engine Tuning
Advisor to analyze Recommendations
26/09/2016 15:49
Module 8: Tracing SQL Server Activity
18 of 35
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
The recommendations
suggested
changes to the
depending
on the tuning
recommendations appear as
a set of Transact-SQL
can view
the Transact-SQL
implement
the recommended
Note: especially centered partial
The Database you to customize
consideration, perform is based on
that enable recommendations should
be made. Running the
long time,
particularly on
configure
Database Engine
return the
results it has You can also
with whether
or not you wish Exploratory You can also involves a combination with the Database enables you structures, such hypothetical
analysis, which exploratory analysis feature. This physical design specifying without
incurring the You can create
and then
26/09/2016 15:49
Module 8: Tracing SQL Server Activity
19 of 35
use it for analysis. configuration.
Demonstration:
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
the current command-line interface.
Advisor
In this demonstration, • Configure • Generate recommendations. • Validate recommendations.
Demonstration Configure a 1.
Ensure
2.
In SQL
3.
When the
Advisor. database
engine 4.
In the Database
5.
Under Workload \Mod08\Demo
Tuning Demo. \Demofiles SQL Server
Profiler 6.
In the Database
7.
In the Select 71 of 71
AdventureWorks. and note that the
following
o
Product
o
ProductCategory
26/09/2016 15:49
Module 8: Tracing SQL Server Activity
20 of 35
8.
o
ProductSubcategory
o
SalesOrderDetail
o
SalesOrderHeader
On the Advanced
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
recommendations. Then click possible, and
click OK
Generate Recommendations 1.
In the Database
2.
When the
. index
recommendations 3.
On the
select
Statement 4.
View the Statement
Recommended processor
uses to 5.
On the as DTA
recommendations script click OK when
notified 6.
Close the
Validate Recommendations 1.
In SQL
Query.sql
script, taking 2.
On the breakdown
displays a query.
26/09/2016 15:49
Module 8: Tracing SQL Server Activity
21 of 35
3.
Note that
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
index that would
improve
the left side
of the query
is displayed
in a tooltip. 4.
In SQL saved from
script you \Demofiles\Mod08 folder.
Then click 5.
Switch
taking care once
again not 6.
On the
7.
Note that the mouse
index. Then hold and view the
Estimated
Combining
SQL Server
SQL Server
database engine,
performance
metrics that measure
two sources
of information,
system
resource utilization,
or plan server
26/09/2016 15:49
Module 8: Tracing SQL Server Activity
22 of 35
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
capacity. Using SQL Server
counters you
want to correlate
alongside the
trace in the SQL
an event in
the trace window,
SQL Server
Profiler indicates
event.
To correlate StartTime and click Import
contains the File menu, select the
System Monitor
Demonstration:
Performance Data
In this demonstration, • Correlate
Demonstration Correlate a 1.
Ensure
2.
In the D:
the log file in
Performance 3.
View the
close
Performance 4.
In SQL and view
\Demofiles\Mod08 folder column match
those in 5.
In SQL
Then open
the AWCounters.
26/09/2016 15:49
Module 8: Tracing SQL Server Activity
23 of 35
6.
In the Performance
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
(which
selects 7.
Click the
event in the
trace that
that was
executed 8.
Keep SQL
Troubleshooting
Many of the multiple users
concurrency issues as simultaneously.
SQL Server uses
in
consistent data,
another.
Locking in Before a database such as a row, attempts to modify element. Locks
element, transaction that on the data uses shared
locks for reading
that no-one
changes it. It
read or write
the potential
the
26/09/2016 15:49
Module 8: Tracing SQL Server Activity
24 of 35
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
transaction has No transaction
has already
been granted
lock mode
that does conflict,
first lock is
released. For UPDATE SELECT operations, transaction isolation
transaction. For upon the completes are
released, regardless Locking is crucial only occur when
system. Problems a similar
way because Blocking Blocking is what process has locked.
another issue, except
when it is excessive. You can monitor Dynamic Management SQL Server
by running template in troubleshoot excessive
blocking. Deadlocks Deadlock errors otherwise the when two transactions
intervene; occurs requests a lock on
the other transaction’s • Task 1 holds
26/09/2016 15:49
Module 8: Tracing SQL Server Activity
25 of 35
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
• Task 2 holds • Task 1 requests
2 releases
the shared • Task 2 requests
1 releases
the shared • Each task
A deadlock can
concurrently in the
same database.
optimizer
processes a complex How SQL Server SQL Server
SQL Server
does the following: • Chooses a deadlock priority.
the highest Server rolls back
the transaction • Rolls back • Notifies the • Allows the
Note: 1205, the application
You can monitor
number is found,
There are several
deadlock events
Demonstration: 26/09/2016 15:49
Module 8: Tracing SQL Server Activity
26 of 35
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
In this demonstration, • Capture a
• View a deadlock
Demonstration Capture a Trace 1.
Ensure
2.
In SQL
the
MIA-SQL 3.
In the Trace
Then in the
Use the 4.
On the click the
template. Then Filter dialog
box, expand 5.
Click Run
6.
While the
Deadlock.cmd. This
will open 7.
When both
File menu,
click Stop
View a Deadlock 1.
In SQL
and select it.
2.
In the bottom
occurred and
one process
26/09/2016 15:49
Module 8: Tracing SQL Server Activity
27 of 35
3.
On the Extract
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
and click the
D:\Demofiles\Mod08 4.
Close SQL Deadlocks_1.
the view
deadlock 5.
Hold the were executing
statements that they process used a
transaction Sales.SpecialOffer
records in the
opposite 6.
Close SQL
Lab: Tracing Scenario You are a database the InternetSales
responsibility for database to
support the application
Objectives After completing • Capture activity • Use the Database • Capture activity
Lab Setup Estimated Time:
26/09/2016 15:49
Module 8: Tracing SQL Server Activity
28 of 35
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Virtual machine: User name: ADVENTUREWORKS Password: Pa$$w0rd
Exercise
Scenario You have identified details of the
want to capture use the
captured trace The main tasks 1.
Prepare
2.
Create
3.
Capture
Task 1: Prepare
1.
Ensure then log
running, and password
Pa$$w0rd 2.
Run Setup.
Task 2: Create
1.
Use SQL
2.
Name the
instance. \Labfiles
\Lab08\Starter\InternetSales 3.
Base the
event.
26/09/2016 15:49
Module 8: Tracing SQL Server Activity
29 of 35
4.
Add the
5.
Filter the
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
InternetSales.
Task 3: Capture
1.
Run the
2.
While the \Starter
\Labfiles\Lab08 approximately
three minutes. 3.
Observe
4.
Stop the
5.
View the
to identify
Transact-SQL
Result: After Profiler.
Exercise
Server
Recommendations
Scenario You want to
InternetSales
database to improve The main tasks 1.
Create
2.
Generate
3.
Validate
Task 1: Create
26/09/2016 15:49
Module 8: Tracing SQL Server Activity
30 of 35
1.
Start the
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
engine
instance. 2.
Create a previously.
captured database and tune all
tables in 3.
Configure
possible.
Task 2: Generate
1.
In the Database
2.
When analysis
Transact-SQL
script in
Task 3: Validate
1.
In the Database
identify the
most frequently 2.
View the Recommended
frequently
used. 3.
Copy the SQL Server
and then use InternetSales
database 4.
Display
missing
indexes 5.
Open the
Advisor and run
it to create 6.
Return to
again, and
note any
Result: After Tuning Advisor,
Engine
26/09/2016 15:49
Module 8: Tracing SQL Server Activity
31 of 35
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Exercise
Scenario You have noticed performance on the server.
server, the impact stored procedures
to lessen the The main tasks 1.
Export
2.
Run the
3.
View the
Task 1: Export
1.
In SQL Server
previously as a
trace definition 2.
Save the
Task 2: Run
1.
Open the
script to save
the trace 2.
Run the
3.
While the \Starter
\Labfiles\Lab08 approximately
three minutes. 4.
While the following
uses the trace). Do not
execute DECLARE
26/09/2016 15:49
Module 8: Tracing SQL Server Activity
32 of 35
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
EXEC EXEC GO
5.
When the
previous step to
stop the
Task 3: View
1.
In SQL Server
retrieve the text
data, start
SELECT TextData, FROM fn_trace_gettable(
default)
WHERE EventClass
Result: After
Review Review Question(
Module In this module,
trace SQL
Server activity,
analyze
performance,
Best Practice: practices: • Use
following best purposes.
• Use
26/09/2016 15:49
Module 8: Tracing SQL Server Activity
33 of 35
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
• Use • Import • Use workload
the overall queries.
Review Question(
26/09/2016 15:49
Module 8: Tracing SQL Server Activity
34 of 35
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
26/09/2016 15:49
Module 8: Tracing SQL Server Activity
35 of 35
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
26/09/2016 15:49
