Performance Tuning
1.
DMV - sys.dm_os_wait_stats
2.
DMV -
sys.dm_os_performance_counters
3.
DMV -
sys.dm_io_virtual_file_stats
4.
DMV -
sys.dm_io_pending_io_requests
5.
DMV - sys.dm_exec_query_memory_grants
6.
DMV - sys.dm_exec_sql_text
7.
DMV - sys.dm_exec_query_resource_semaphores
8.
DMV - sys.dm_os_wait_stats
9.
DMV - sys.dm_os_schedulers
10. Extended Proc Sp_who5
11. Performance Monitor - PerfMon
1.DMV - sys.dm_os_wait_stats
ABOUT
Ø DMV - that helps us to understand wait stats
is sys.dm_os_wait_stats.
Ø This DMV gives us all the information that
we need to know regarding wait stats
Ø Specific types of wait times during query
execution can indicate bottlenecks or stall points within the query. Similarly,
high wait times, or wait counts server wide can indicate bottlenecks or hot
spots in interaction query interactions within the server instance
COLUMN DETAILS
wait_type – this is the name of the wait type. There can be three different
kinds of wait types – resource, queue and external.
waiting_tasks_count – this incremental counter is a good indication of
frequent the wait is happening. If this number is very high, it is good
indication for us to investigate that particular wait type. It is quite
possible that the wait time is considerably low, but the frequency of the wait
is much high.
wait_time_ms – this is total wait accumulated for any type of wait.
This is the total wait time and includes singal_wait_time_ms.
max_wait_time_ms – this indicates the maximum wait type
ever occurred for that particular wait type. Using this, one can
estimate the intensity of the wait type in past. Again, it is not
necessary that this max wait time will occur every time; so do not over invest
yourself here.
signal_wait_time_ms – this is the wait time when thread is marked as
runnable and it gets to the running state. If the runnable queue is very long,
you will find that this wait time becomes high.
REFERENCE QUERY
Select * from sys.dm_os_wait_stats order
by wait_time_ms desc
With Percentage
WITH Waits AS
(
SELECT
wait_type,
wait_time_ms /
1000. AS wait_time_s,
100. *
wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
WHERE
wait_type
NOT IN
('CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE',
'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR',
'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT')
) -- filter out additional irrelevant waits
SELECT W1.wait_type,
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2 ON W2.rn <= W1.rn
GROUP BY W1.rn,
W1.wait_type,
W1.wait_time_s,
W1.pct
HAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold;
2.DMV - sys.dm_os_performance_counters
Reference
ABOUT
DMV that returns
one row for each SQL Server performance counter. It’s useful for obtaining information about current performance
counter values, you can collect counter information that you would receive
from PerfMon for the various SQL
Server counters
COLUMN DETAILS
·
Cache Hit Ratio – I
ignore this value, but I still monitor it. I will, ideally, never be the only
DBA on a team again, and everyone seems to think this value is cool.
·
Page Life Exp – My
favorite! When you read a page from disk into memory how many seconds will it
stay there? Just don’t use the outdated “300” rule or your disks will catch on
fire!!!
·
Page Lookups/Sec – How
many pages are read from memory.
·
Page Reads/Sec – How
many pages are read from disk.
·
Page Writes/Sec – How
many pages are written to disk.
·
Lazy Writes/sec – How
many pages are written to disk outside of a checkpoint due to memory pressure.
·
Batch Requests/sec –
How busy is the server?
·
Trans/sec – How busy
is the server?
·
Total Server Memory – How
much memory SQL Server is currently using. Typically ramps up to Target value
and PLE is low as it ramps up since new pages are in memory dropping the
average.
·
Target Server Memory –
How much memory SQL Server is allowed to use. Should be the same as the max
memory setting, but memory pressure can cause this to decrease.
·
Memory Grants Pending
– How many processes aren’t able to get enough memory to run. Should be 0,
always 0, if not then find out why.
·
Deadlocks – How many
deadlocks are we getting. Most apps handle deadlocks gracefully, but they still
lose time doing it. If this number starts going up, start looking into it.
·
SQL Compilations/sec –
This is a hidden performance killer! Some queries can’t be cached so they’re
compiled every time they’re run. I’ve seen this with a query being run once a
second and a big server was running slower than my laptop. It’s normal for
things to compile throughout the day, it’s not normal for this number to be 10x
higher than before that last upgrade.
·
SQL Re-Compilations/sec
– Same goes here. The counters aren’t that much different.
REFERENCE QUERY
-- it must be
> 95 always
SELECT object_name,
counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE
[object_name] LIKE '%Buffer
Manager%'
AND [counter_name] = 'Buffer
cache hit ratio'
3.DMV - sys.dm_io_virtual_file_stats
Reference
ABOUT
Returns I/O
statistics for data and log files. This dynamic management view replaces
the fn_virtualfilestats function.
The sys.dm_io_virtual_file_stats DMV will show an IO Stall
when any wait occurs to access a physical data file.
Virtual file stats
are a great starting point when you want to understand I/O performance for a
SQL Server instance.
The DMV contains
cumulative I/O information for each database file, but the data resets on
instance restart
Need
SQL Server
constantly brings database pages in and out of the buffer pool. This generates
substantial I/O traffic. Similarly, the log records need to be flushed to the
disk before a transaction can be declared committed. And finally, SQL Server
uses TempDB for various purposes such
as to store intermediate results, to sort, to keep row versions and so on. So a
good I/O subsystem is critical to the performance of SQL Server
Perfmon Counter
Set in PerfMon that
captures the Physical Disk counters Avg. Disk Sec/Read and Avg. Disk Sec/Read
for all disks that host database files.
Storage latency can
exist for a variety of reasons, such as:
- SQL
Server has to read too much data as a result of inefficient query plans or
missing indexes
- Too
little memory is allocated to the instance and the same data is read from
disk over and over because it cannot stay in memory
- Implicit
conversions cause index or table scans
- Queries
perform SELECT * when not all columns are required
- Forwarded
record problems in heaps cause additional I/O
- Low
page densities from index fragmentation, page splits, or incorrect fill
factor settings cause additional I/O
COLUMN DETAILS
|
sample_ms
|
Number
of milliseconds since the computer was started. This column can be used to
compare different outputs from this function.
The data type is int for SQL Server 2008 through SQL Server 2014 |
|
num_of_reads
|
Number
of reads issued on the file.
|
|
num_of_bytes_read
|
Total
number of bytes read on this file.
|
|
io_stall_read_ms
|
Total
time, in milliseconds, that the users waited for reads issued on the file.
|
|
num_of_writes
|
Number
of writes made on this file.
|
|
num_of_bytes_written
|
Total
number of bytes written to the file.
|
|
io_stall_write_ms
|
Total
time, in milliseconds, that users waited for writes to be completed on the
file.
|
|
io_stall
|
Total
time, in milliseconds, that users waited for I/O to be completed on the file.
|
|
size_on_disk_bytes
|
Number
of bytes used on the disk for this file. For sparse files, this number is the
actual number of bytes on the disk that are used for database snapshots.
|
REFERENCE QUERY
select DB_NAME(database_id) As DB,* from sys.dm_io_virtual_file_stats(null,null)
Order By num_of_writes Desc
SELECT a.io_stall,
a.io_stall_read_ms, a.io_stall_write_ms, a.num_of_reads,
a.num_of_writes,
--a.sample_ms, a.num_of_bytes_read, a.num_of_bytes_written, a.io_stall_write_ms,
( ( a.size_on_disk_bytes / 1024 ) / 1024.0 ) AS size_on_disk_mb,
db_name(a.database_id) AS dbname,
b.name, a.file_id,
db_file_type = CASE
WHEN a.file_id = 2 THEN 'Log'
ELSE 'Data'
END,
UPPER(SUBSTRING(b.physical_name, 1, 2)) AS disk_location
FROM sys.dm_io_virtual_file_stats (NULL, NULL) a
JOIN sys.master_files b ON a.file_id = b.file_id
AND a.database_id = b.database_id
ORDER BY a.io_stall DESC
a.num_of_writes,
--a.sample_ms, a.num_of_bytes_read, a.num_of_bytes_written, a.io_stall_write_ms,
( ( a.size_on_disk_bytes / 1024 ) / 1024.0 ) AS size_on_disk_mb,
db_name(a.database_id) AS dbname,
b.name, a.file_id,
db_file_type = CASE
WHEN a.file_id = 2 THEN 'Log'
ELSE 'Data'
END,
UPPER(SUBSTRING(b.physical_name, 1, 2)) AS disk_location
FROM sys.dm_io_virtual_file_stats (NULL, NULL) a
JOIN sys.master_files b ON a.file_id = b.file_id
AND a.database_id = b.database_id
ORDER BY a.io_stall DESC
Other Perfmon Counters
You can use the following performance counters to identify
I/O bottlenecks. Note, these AVG values tend to be skewed (to the low side) if
you have an infrequent collection interval. For example, it is hard to tell the
nature of an I/O spike with 60-second snapshots. Also, you should not rely on
one counter to determine a bottleneck; look for multiple counters to cross
check the validity of your findings.
- Physical Disk Object: Avg. Disk Queue Length represents the average number of physical read
and write requests that were queued on the selected physical disk during
the sampling period. If your I/O system is overloaded, more read/write
operations will be waiting. If your disk queue length frequently exceeds a
value of 2 during peak usage of SQL Server, then you might have an I/O
bottleneck.
- Avg. Disk Sec/Read is
the average time, in seconds, of a read of data from the disk. Any number:
- Less than 10 ms - very good
- Between 10 - 20 ms - okay
- Between 20 - 50 ms - slow, needs attention
- Greater than 50 ms - Serious I/O bottleneck
- Avg. Disk Sec/Write is
the average time, in seconds, of a write of data to the disk. Please refer
to the guideline in the previous bullet.
- Physical Disk: %Disk Time is the percentage of elapsed time that the
selected disk drive was busy servicing read or write requests. A general
guideline is that if this value is greater than 50 percent, it represents
an I/O bottleneck.
- Avg. Disk Reads/Sec is
the rate of read operations on the disk. You need to make sure that this
number is less than 85 percent of the disk capacity. The disk access time
increases exponentially beyond 85 percent capacity.
- Avg. Disk Writes/Sec is
the rate of write operations on the disk. Make sure that this number is
less than 85 percent of the disk capacity. The disk access time increases
exponentially beyond 85 percent capacity.
When using above counters, you may need to adjust the
values for RAID configurations using the following formulas.
- Raid 0 --
I/Os per disk = (reads + writes) / number of disks
- Raid 1 --
I/Os per disk = [reads + (2 * writes)] / 2
- Raid 5 --
I/Os per disk = [reads + (4 * writes)] / number of disks
- Raid 10 --
I/Os per disk = [reads + (2 * writes)] / number of disks
For example, you have a RAID-1 system with two physical
disks with the following values of the counters.
- Disk Reads/sec - 80
- Disk Writes/sec - 70
- Avg. Disk Queue Length - 5
In that case, you are encountering (80 + (2 * 70))/2 = 110
I/Os per disk and your disk queue length = 5/2 = 2.5 which indicates a border
line I/O bottleneck."
4.DMV - sys.
dm_io_pending_io_requests
Reference
ABOUT
Returns
a row for each pending I/O request in SQL Server.
COLUMN DETAILS
REFERENCE QUERY
select * from sys.dm_io_pending_io_requests cross
apply sys.dm_io_virtual_file_stats(null,null)
/* The query
below can be used to determine if pending IO is causing work to be queued on
the scheduler. */
SELECT f.database_id, f.[file_id], DB_NAME(f.database_id) AS database_name, f.name AS
logical_file_name, f.type_desc,
CAST
(CASE
-- Handle UNC
paths (e.g. '\\fileserver\DBs\readonly_dw.ndf')
WHEN LEFT (LTRIM (f.physical_name), 2) = '\\'
THEN LEFT (LTRIM (f.physical_name),CHARINDEX('\',LTRIM(f.physical_name),CHARINDEX('\',LTRIM(f.physical_name), 3) + 1) - 1)
-- Handle local
paths (e.g. 'C:\Program Files\...\master.mdf')
WHEN CHARINDEX('\', LTRIM(f.physical_name), 3) > 0
THEN UPPER(LEFT(LTRIM(f.physical_name), CHARINDEX ('\', LTRIM(f.physical_name), 3) - 1))
ELSE f.physical_name
END AS NVARCHAR(255)) AS logical_disk,
fs.io_stall/1000/60 AS io_stall_min,
fs.io_stall_read_ms/1000/60 AS io_stall_read_min,
fs.io_stall_write_ms/1000/60 AS io_stall_write_min,
(fs.io_stall_read_ms / (1.0 + fs.num_of_reads)) AS
avg_read_latency_ms,
(fs.io_stall_write_ms / (1.0 + fs.num_of_writes)) AS
avg_write_latency_ms,
((fs.io_stall_read_ms/1000/60)*100)/(CASE WHEN fs.io_stall/1000/60 = 0 THEN 1 ELSE fs.io_stall/1000/60 END) AS io_stall_read_pct,
((fs.io_stall_write_ms/1000/60)*100)/(CASE WHEN fs.io_stall/1000/60 = 0 THEN 1 ELSE fs.io_stall/1000/60 END) AS io_stall_write_pct,
ABS((fs.sample_ms/1000)/60/60) AS 'sample_HH',
((fs.io_stall/1000/60)*100)/(ABS((fs.sample_ms/1000)/60))AS 'io_stall_pct_of_overall_sample',
PIO.io_pending_ms_ticks,
PIO.scheduler_address,
os.scheduler_id,
os.pending_disk_io_count,
os.work_queue_count
FROM sys.dm_io_pending_io_requests AS
PIO
INNER JOIN sys.dm_io_virtual_file_stats (NULL,NULL) AS fs
ON fs.file_handle = PIO.io_handle
INNER JOIN sys.master_files AS f
ON fs.database_id = f.database_id AND fs.[file_id] = f.[file_id] INNER JOIN sys.dm_os_schedulers AS os
ON PIO.scheduler_address =
os.scheduler_address
5.DMV - sys.dm_exec_query_memory_grants
Reference
ABOUT
Returns information about the queries that
have acquired a memory grant or that still require a memory grant to execute.
Queries that do not have to wait on a memory grant will not appear in this
view.
DMV
COLUMN DETAILS
Refer the below
link for column details
https://technet.microsoft.com/en-us/library/ms365393(v=sql.110).aspx
https://technet.microsoft.com/en-us/library/ms365393(v=sql.110).aspx
|
requested_memory_kb
|
Total requested amount of memory in kilobytes.
|
|
granted_memory_kb
|
Total amount of memory actually granted in kilobytes. Can
be NULL if the memory is not granted yet. For a typical situation, this value
should be the same as requested_memory_kb. For index creation, the
server may allow additional on-demand memory beyond initially granted memory.
|
|
required_memory_kb
|
Minimum memory required to run this query in
kilobytes. requested_memory_kb is the same or larger than
this amount.
|
|
used_memory_kb
|
Physical memory used at this moment in kilobytes.
|
REFERENCE QUERY
select session_id, requested_memory_kb, granted_memory_kb, ideal_memory_kb
from sys.dm_exec_query_memory_grants
from sys.dm_exec_query_memory_grants
SELECT mg.granted_memory_kb,
mg.session_id,
t.text, qp.query_plan
FROM sys.dm_exec_query_memory_grants AS
mg
CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) AS qp
ORDER BY 1 DESC
OPTION (MAXDOP 1)
6.DMV - sys.dm_exec_sql_text
Reference
ABOUT
Returns the text of the SQL batch that is identified by the
specified sql_handle.
REFERENCE QUERY
Select * from sys.dm_exec_requests cross apply sys.dm_exec_sql_text(sql_handle)
7.DMV - sys.dm_exec_query_resource_semaphores
Reference
ABOUT
Returns the information
about the current query-resource semaphore status in SQL Server. sys.dm_exec_query_resource_semaphores provides general query-execution
memory status and allows you to determine whether the system can access enough
memory
COLUMN DETAILS
|
total_memory_kb
|
Memory held by the resource semaphore in kilobytes. If
the system is under memory pressure or if forced minimum memory is granted
frequently, this value can be larger than the target_memory_kb or max_target_memory_kbvalues.
Total memory is a sum of available and granted memory.
|
|
available_memory_kb
|
Memory available for a new grant in kilobytes.
|
|
granted_memory_kb
|
Total granted memory in kilobytes.
|
|
used_memory_kb
|
Physically used part of granted memory in kilobytes.
|
|
grantee_count
|
Number of active queries that have their grants
satisfied.
|
|
waiter_count
|
Number of queries waiting for grants to be satisfied.
|
REFERENCE QUERY
select * from sys.dm_exec_query_resource_semaphores
8.DMV - sys.dm_os_wait_stats
Reference
ABOUT
Returns information about all the waits
encountered by threads that executed. You can use this aggregated view to
diagnose performance issue Since SQL server started
COLUMN DETAILS
|
Column name
|
Description
|
|
wait_type
|
Name of the wait type.
|
|
waiting_tasks_count
|
Number of waits on this wait type. This counter is
incremented at the start of each wait.
|
|
wait_time_ms
|
Total wait time for this wait type in milliseconds. This
time is inclusive of signal_wait_time_ms.
|
|
max_wait_time_ms
|
Maximum wait time on this wait type.
|
|
signal_wait_time_ms
|
Difference between the time that the waiting thread was
signaled and when it started running
|
REFERENCE QUERY
An excellent query
for this purpose is from Paul S. Randal blog, which will give the cumulative wait
statistic for the most important wait statistic types
Select * from sys.dm_os_wait_stats order
by wait_time_ms desc
SELECT *
FROM sys.dm_os_wait_stats WHERE waiting_tasks_count > 0
ORDER BY wait_time_ms DESC
GO
SELECT [wait_type] ,
[wait_time_ms] ,
DATEADD(SS, -[wait_time_ms] /
1000, GETDATE()) AS
"Date/TimeCleared" ,
CASE WHEN [wait_time_ms] <
1000
THEN
CAST([wait_time_ms]
AS VARCHAR(15)) + ' ms'
WHEN
[wait_time_ms] BETWEEN 1000 AND 60000
THEN
CAST((
[wait_time_ms] / 1000 )
AS VARCHAR(15)) + ' seconds'
WHEN
[wait_time_ms] BETWEEN 60001 AND 3600000
THEN
CAST((
[wait_time_ms] / 60000 )
AS VARCHAR(15)) + ' minutes'
WHEN
[wait_time_ms] BETWEEN 3600001 AND 86400000
THEN
CAST((
[wait_time_ms] / 3600000 ) AS VARCHAR(15)) + ' hours'
WHEN
[wait_time_ms] > 86400000
THEN
CAST((
[wait_time_ms] / 86400000 ) AS VARCHAR(15)) + ' days'
END AS "TimeSinceCleared"
FROM [sys].[dm_os_wait_stats]
--WHERE [wait_type] =
'SQLTRACE_INCREMENTAL_FLUSH_SLEEP';
Order By [wait_time_ms] desc
9.DMV - sys.dm_os_schedulers
Reference
ABOUT
Occurs when a task
voluntarily yields the scheduler for other tasks to execute. During this wait
the task is waiting for its quantum to be renewed.
SOS_SCHEDULER_YIELD
is a fairly common wait type and occurs when there is CPU pressure. SQL Server
runs multiple threads and tries to allow all threads to run without problems.
However, if all the threads are busy on each scheduler and can't let other
threads run, it will yield itself for another thread, which in turn creates the
SOS_SCHEDULER_YIELD wait type.
WITH Waits AS
(
SELECT
wait_type,
wait_time_ms / 1000. AS
wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms
DESC) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type
NOT IN
('CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE',
'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH',
'WAITFOR',
'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT')
) -- filter out additional irrelevant waits
SELECT W1.wait_type,
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2 ON W2.rn <= W1.rn
GROUP BY W1.rn,
W1.wait_type,
W1.wait_time_s,
W1.pct
HAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold;
COLUMN DETAILS
This DMV has
several columns including number or workers, active tasks, and the status of
each scheduler, etc., which can help troubleshoot certain problems, but the
most important column is the one used for measuring queued tasks, the
runnable_tasks_count column. This column displays the count of tasks yielding
to other tasks resulting in a SOS_SCHEDULER_YIELD wait type. If this column is
frequently greater than 0 then CPU pressure may be present and blocking may
occur.
REFERENCE QUERY
-- Get Avg task count and Avg runnable task count
SELECT AVG(current_tasks_count) AS [Avg Task Count],
AVG(runnable_tasks_count) AS [Avg Runnable Task Count]
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255
AND [status] = 'VISIBLE ONLINE';
This query
will help detect blocking and can help detect and confirm CPU pressure. High,
sustained values for current_tasks_count usually indicates you are seeing lots
of blocking. I have also seen it be a secondary indicator of I/O pressure.
High, sustained values for runnable_tasks_count is usually a very good
indicator of CPU pressure. By “high, sustained values”, I mean anything
above about 10-20 for most systems.
-- Is NUMA enabled
SELECT
CASE COUNT(DISTINCT parent_node_id)
WHEN 1
THEN 'NUMA disabled'
ELSE 'NUMA enabled'
END
FROM sys.dm_os_schedulers
WHERE parent_node_id <> 32;
The second query will tell you whether Non-uniform memory
access (NUMA) is enabled on your SQL Server instance. AMD based servers have
supported hardware based NUMA for several years, while Intel based Xeon
servers, have added hardware based NUMA with the Xeon 5500, 5600, and 7500
series. There is also software based NUMA.
10.Extended Proc Sp_who5
ABOUT
Extended SP to
monitor the sql server running processes.
REFERENCE QUERY
Create proc [dbo].[SP_Who5]
As
SELECT SPID = er.session_id
, ot.Threads
, RunningThreads
= coalesce(rsp.RunningThreads,0)
, Pct_Comp = er.percent_complete
,
Est_Comp_Time = CASE
er.estimated_completion_time WHEN 0 THEN NULL ELSE dateadd(ms, er.estimated_completion_time, getdate()) END
, er.status
, er.command
, database_name = sd.name
, BlockedBy
= wt.blocking_session_id
,
HeadBlocker = coalesce(hb5.session_id, hb4.session_id, hb3.session_id, hb2.session_id, hb1.session_id)
, wait_type
= coalesce(CASE er.wait_type WHEN 'CXPACKET' THEN 'CXPACKET - ' + sp.lastwaittype1 ELSE
sp.lastwaittype1 END, lower(er.last_wait_type)) --Lowercase denotes it's
not currently waiting, also noted by a wait time of 0.
,
Wait_Time_Sec = Cast(er.wait_time/1000.0 as DEC(20,3))
, er.wait_resource
,
Duration_Sec = Cast(DATEDIFF(s, er.start_time, GETDATE()) as DEC(20,0))
, CPU_Sec = Cast(er.cpu_time/1000.0 as DEC(20,3))
, Reads_K = Cast(er.reads/1000.0 as DEC(20,3))
, Writes_K = Cast(er.writes/1000.0 as DEC(20,3))
,
[Statement] = SUBSTRING (st.text, er.statement_start_offset/2,
abs(CASE WHEN er.statement_end_offset
= -1
THEN
LEN(CONVERT(NVARCHAR(MAX), st.text)) * 2
ELSE
er.statement_end_offset END - er.statement_start_offset)/2)
, st.text as Query
, es.login_time
, es.host_name
, program_name = CASE LEFT(es.program_name, 29)
WHEN
'SQLAgent - TSQL JobStep (Job '
THEN 'SQLAgent Job: ' + (SELECT name FROM msdb..sysjobs sj
WHERE substring(es.program_name,32,32)=(substring(sys.fn_varbintohexstr(sj.job_id),3,100))) + ' - ' + SUBSTRING(es.program_name, 67, len(es.program_name)-67)
ELSE
es.program_name
END
, es.client_interface_name
, es.login_name
, es.status
, es.total_scheduled_time
, es.total_elapsed_time
, er.start_time
, es.last_request_start_time
, es.last_request_end_time
, er.database_id
--, qp.query_plan
FROM sys.dm_exec_requests er
INNER JOIN sys.dm_exec_Sessions es
on er.session_id=es.session_id
LEFT JOIN sys.databases sd on er.database_id=sd.database_id
INNER JOIN (SELECT session_id, count(1) Threads FROM sys.dm_os_tasks GROUP BY session_id) ot on er.session_id=ot.session_id
LEFT JOIN (SELECT spid, LastWaitType1 = MIN(lastwaittype), LastWaitType2 = MAX(lastwaittype) FROM sysprocesses sp WHERE
waittime > 0 AND
lastwaittype <> 'cxpacket'
GROUP BY spid) sp ON er.session_id = sp.spid
LEFT JOIN (SELECT spid, RunningThreads = COUNT(1) FROM sysprocesses sp WHERE
waittime = 0 GROUP
BY spid) rsp ON er.session_id = rsp.spid
LEFT JOIN (SELECT session_id, max(blocking_session_id)
blocking_session_id FROM sys.dm_os_waiting_tasks wt WHERE
wt.blocking_session_id <>
wt.session_id GROUP
BY session_id)
wt ON er.session_id
= wt.session_id
LEFT JOIN (SELECT session_id, max(blocking_session_id)
blocking_session_id FROM sys.dm_os_waiting_tasks wt GROUP
BY session_id)
hb1 ON wt.blocking_session_id
= hb1.session_id
LEFT JOIN (SELECT session_id, max(blocking_session_id)
blocking_session_id FROM sys.dm_os_waiting_tasks wt GROUP
BY session_id)
hb2 ON hb1.blocking_session_id
= hb2.session_id
LEFT JOIN (SELECT session_id, max(blocking_session_id)
blocking_session_id FROM sys.dm_os_waiting_tasks wt GROUP
BY session_id)
hb3 ON hb2.blocking_session_id
= hb3.session_id
LEFT JOIN (SELECT session_id, max(blocking_session_id)
blocking_session_id FROM sys.dm_os_waiting_tasks wt GROUP
BY session_id)
hb4 ON hb3.blocking_session_id
= hb4.session_id
LEFT JOIN (SELECT session_id, max(blocking_session_id)
blocking_session_id FROM sys.dm_os_waiting_tasks wt GROUP
BY session_id)
hb5 ON hb4.blocking_session_id
= hb5.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
--CROSS APPLY
sys.dm_exec_query_plan(er.plan_handle) qp
WHERE er.session_id <> @@SPID
--AND
es.host_name like '%%'
--AND
er.session_id = 2702
ORDER BY er.percent_complete
DESC, er.cpu_time DESC, er.session_id
GO
11.Performance Monitor - PerfMon
Reference
ABOUT
The
Microsoft Windows Performance Monitor is a tool that
administrators can use to examine how programs running on their computers
affect the computer's performance.
Understanding PerfMon Counters and their Results
|
Performance Counter
|
Information Provided
|
Expected Value
|
|
% Disk Read Time
|
Amount of time
your disks are being read
|
Less than 15-20%
|
|
% Disk Time
|
Amount of time
your disks are in use
|
Less than 15-20%
|
|
% Disk Write Time
|
Amount of time
your disks are being written to
|
Less than 15-20%
|
|
% Idle Time
|
Amount of time
your disks are idle or not performing any action
|
Over 85%
|
|
Current Disk Queue
Length
|
Amount of time the
Operating System must wait to access the disks
|
Less than 1**
|
|
Disk Reads/sec
|
Overall rate of
read operations on the disk (Can be used to determine IOP’s to evaluate
hardware needs and as a benchmark for hardware upgrades.)
|
Less than 70-80%*
|
|
Disk Writes/sec
|
Overall rate of
write operations on the disk (Can be used to determine IOP’s to evaluate
hardware needs and as a benchmark for hardware upgrades.)
|
Less than 70-80%*
|
|
Split IO/sec
|
Overall rate at
which the operating system divides I/O requests to the disk into multiple
requests.
|
On single disk
volumes, a high Split IO/sec value may indicate a badly fragmented drive.
Defraying may increase performance.
|
For detail, refer
the above link.
Configure Perfmon Counter
Important Counters
·
PhysicalDisk/%idle time - should not be less than ~%60
·
PhysicalDisk/Avg. Disk sec/Read should not be higher than ~20ms
·
PhysicalDisk/Avg. Disk sec/Write should not be higher than ~20ms
·
PhysicalDisk/Current disk queue length. should not be higher than
2
·
Memory\Available Mbytes, minimum 10% of memory should be free and available
·
\Memory\Pages/sec should not be higher than 1000
·
Network Interface(*)\Bytes Total/sec
o
Less than 40% of the
interface consumed = Healthy
o
41%-64% of the
interface consumed = Monitor or Caution
o
65-100% of the
interface consumed = Critical, performance will be adversely affected
·
Network Interface(*)\Output Queue Length
o
0 = Healthy
o
1-2 = Monitor or
Caution
o
Greater than 2 =
Critical, performance will be adversely affected
·
Hyper-V Hypervisor Logical Processor(_Total)\% Total Run Time
o
Less than 60%
consumed = Healthy
o
60% - 89% consumed =
Monitor or Caution
o
90% - 100% consumed
= Critical, performance will be adversely affected
·
Paging File\%Usage should not be higher than %10
No comments:
Post a Comment