Module 7: Monitoring SQL Server 2014
1 of 29
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Module Contents:
Lesson 1: Lesson 2: Lesson 3: Lab:
Module A large amount activity in databases in resource utilization you can use to explains how
monitoring identify changes functionality This module dynamic
management
Objectives After completing • Describe considerations • Use dynamic • Use Performance
26/09/2016 15:48
Module 7: Monitoring SQL Server 2014
2 of 29
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Lesson
Server
SQL Server
subsystems and
potential workloads,
how database
applications
Server
database solution,
workloads
that the solution
plan
hardware capacity
Lesson After completing • Describe considerations • Describe tools • Use Activity
SQL Server
As a DBA, you your organization are using server
services in those workloads and diagnose
26/09/2016 15:48
Module 7: Monitoring SQL Server 2014
3 of 29
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
the causes of Why Monitor? Important reasons • Diagnosing slow performance that the response request and often the problem
complain of usually mean submitting a acceptable, though throughput of the
system (the simultaneously).
resolving these
problems.
optimized by
creating indexes
or you
might find
cope with
demand (reducing • Detecting the same data, requests to
applications access cause some occasionally result in
deadlock where
current activity,
you can identify
resolve the
problem if
can then
troubleshoot
for
accomplishing • Identifying provisioned, hardware database workloads resource utilization
is initially required processes, and the to monitor consolidate
workloads
Guidelines When planning
guidelines:
26/09/2016 15:48
Module 7: Monitoring SQL Server 2014
4 of 29
• Understand requirements, demand between solution, you to monitor,
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
different of trends in database relevant metrics to the
business. • Establish
before
monitoring
without
something
unlikely to help
you identify approach values for performance baseline in
better baseline experience metric to its investigation to try to
diagnose the • Monitor regularly arise, it is
problem to to identify
any trends
With this
approach,
they become
critical.
Monitoring
26/09/2016 15:48
Module 7: Monitoring SQL Server 2014
5 of 29
SQL Server provides tuning. Each
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
monitoring and combine several of
them to achieve
Tool Activity Monitor
Studio that activity in the
Dynamic Management
internal SQL
Performance Monitor
can use to record over a period of of chart and
SQL Server Profiler
use to record in a SQL Server use the trace as a tool has been and is
SQL Trace
Database Engine
Distributed Replay
programming
tuning indexes and
across a
SQL Server Extended SQL Server Data
storing, and SQL Server
SQL Server Utility
monitoring server specific collection
Microsoft System
management solution
26/09/2016 15:48
Module 7: Monitoring SQL Server 2014
6 of 29
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Tool performance and services.
Activity
Activity Monitor
information
about processes,
You can use it
to investigate
SERVER
STATE permission To start Activity
Activity
Monitor. Activity • The Overview
waiting tasks,
database I/O, • The Processes databases,
logins, blocking
other processes. • The Resource
resources
and information
26/09/2016 15:48
Module 7: Monitoring SQL Server 2014
7 of 29
• The Data
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
in use and
their recent • The Recent expensive queries in
You can filter
most right-click the query.
which you
want to view
Demonstration: In this demonstration, • View server • Troubleshoot
Demonstration View Server 1.
Ensure
are running,
and log
the
password 2.
In the D:
3.
Start SQL
database engine
instance 4.
In Object
click Activity
Monitor 5.
In Activity
background
activity
26/09/2016 15:48
Module 7: Monitoring SQL Server 2014
8 of 29
6.
Expand
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
the SQL
Server 7.
Click the
to show only
processes
you may
need to 8.
Remove
9.
Expand
waiting on
resources. 10. Expand (you may 11.
Expand
I/O activity displayed). that have
consumed
Troubleshoot 1.
With Activity D:\Demofiles\Mod07
2.
In SQL AdventureWorks
expand Product and click
Select 3.
In status
executing.
Another 4.
In the Activity
column to
show processes 5.
In the Blocked
process that is
blocking 6.
Remove
the blocking
process
26/09/2016 15:48
Module 7: Monitoring SQL Server 2014
9 of 29
7.
Note the
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
value of 1
indicates 8.
Right-click that is causing
Transact-SQL code but not
committed 9.
Click Kill
seconds, the
Processes 10. Close the
1000 rows
from Production. 11.
Close the
Studio open for
the next
Lesson SQL Server provides Dynamic management insights directly
Functions activity. DMFs) provide are useful for
monitoring.
Lesson After completing • Describe SQL • View dynamic
Overview
Functions
26/09/2016 15:48
Module 7: Monitoring SQL Server 2014
10 of 29
In earlier versions monitor the internal using extended stored procedures
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
tools to monitoring by extended Poorly-written
programs that
SQL Server.
SQL Server
into the
inner operation Some of the
procedures. objects have
been created
Note: database that return
DMVs and DMFs server instance,
persisted in the virtual objects is restarted.
health of a DMV and
DMF: • Server-scoped
objects, users
require VIEW
26/09/2016 15:48
Module 7: Monitoring SQL Server 2014
11 of 29
• Database-scoped
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
these
objects, users
All DMVs and are defined in There are a great information.
dm_%. They databases. system following table:
Category sys.dm_exec_%
query execution. currently
sys.dm_os_% performance counters
sys.dm_tran_% transactions. sys.dm_io_% for each
sys.dm_db_% database has
In addition to
information
about specific
AlwaysOn
Availability Groups,
Viewing
Views
26/09/2016 15:48
Module 7: Monitoring SQL Server 2014
12 of 29
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Both DMVs
System Views
node for any
master
database.
Note: Transact-SQL
in names.
There are two • Objects that • Objects that
Objects That Most DMVs The following sys.dm_os_waiting_tasks
waiting for longer
than three seconds: Returning Real-Time
26/09/2016 15:48
Module 7: Monitoring SQL Server 2014
13 of 29
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
SELECT s. t.wait_duration_ms FROM INNER ON t. WHERE AND
In many cases,
Note: The task resource to be scheduled tuning
lock.
waiting list. requested where it waits useful when the system.
Objects That The second type For example, long any task
often and how started.
Returning Historical
SELECT * ORDER
Another useful
, which
returns information
Demonstration:
Views
26/09/2016 15:48
Module 7: Monitoring SQL Server 2014
14 of 29
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
In this demonstration, • View SQL • View storage • View query
Demonstration View SQL Server 1.
If you did MIA-DC,
20462C20462C-MIA-SQL as
ADVENTUREWORKS\Student D:\Demofiles\Mod07 2.
If SQL
to the
MIA-SQL 3.
In SQL
\Demofiles
\Mod07 4.
Highlight
information,
and then 5.
Review
SQL Server
and SQL 6.
Highlight
information,
and then 7.
Review MSSQLSERVER in the value_data startup_type
corresponding values for the dynamic
management
26/09/2016 15:48
Module 7: Monitoring SQL Server 2014
15 of 29
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
View Storage 1.
Select the retrieves
noting that it as well as
the sys. 2.
Click Execute instance,
databases in the hosted.
View Query 1.
Highlight
and then
click Execute 2.
Highlight this code
noting that dm_exec_sql_text DMF
to return 3.
Click Execute
queries,
noting 4.
Highlight
, and then
click Execute 5.
Re-highlight
stats, and then
click Execute 6.
In the results,
Transact-SQL
statement 7.
Re-highlight
query, and then
click Execute 8.
Re-highlight
stats, and then
click Execute 9.
Review
has an
execution_count
26/09/2016 15:48
Module 7: Monitoring SQL Server 2014
16 of 29
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
10. Close SQL
Lesson SQL Server
you can use to
investigate both
also expose a
set of performance-related objects and counters
Monitor. These entire server.
Lesson After completing • Describe Performance • Work with • Work with
Introduction
Windows Performance previously disparate
several sources of
26/09/2016 15:48
Module 7: Monitoring SQL Server 2014
17 of 29
information in
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
information
you need to diagnose Windows Performance SQL Server runs level, as well
Because the server engine might
be caused by Objects, Counters Performance metrics measure The specific • Objects. Objects
Typically, these subsystems. hierarchy. Processor,
Memory,
Windows,
and server
relate to
application-specific • Counters monitor. For
you can counter,
which measures • Instances when monitoring measurements
example, capture counters provide
a _Total instance,
When describing
For
example, the
object is
described as
_Total instance,
or if an object
example
Processor: % Charts and Performance
you have 26/09/2016 15:48
Module 7: Monitoring SQL Server 2014
18 of 29
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
captured. These • A line chart. • A histogram • A text-based
When viewing below the chart counter within
in a list recorded for that counter to make it
easier to identify You export data
SQL Server
Many applications
SQL Server
exposes a large
convention:
Object Name SQLServer:
