使用DMV，诊断和调优DB性能。
时间：2021-07-01 10:21:17
帮助过：4人阅读
SELECT wait_type ,
SUM(wait_time_ms
/ 1000)
AS [wait_time_s]
FROM sys.dm_os_wait_stats DOWS
WHERE wait_type
NOT IN (
N‘BROKER_EVENTHANDLER‘, N
‘BROKER_RECEIVE_WAITFOR‘,
N‘BROKER_TASK_STOP‘, N
‘BROKER_TO_FLUSH‘,
N‘BROKER_TRANSMITTER‘, N
‘CHECKPOINT_QUEUE‘,
N‘CHKPT‘, N
‘CLR_AUTO_EVENT‘,
N‘CLR_MANUAL_EVENT‘, N
‘CLR_SEMAPHORE‘,
N‘DBMIRROR_DBM_EVENT‘, N
‘DBMIRROR_EVENTS_QUEUE‘,
N‘DBMIRROR_WORKER_QUEUE‘, N
‘DBMIRRORING_CMD‘,
N‘DIRTY_PAGE_POLL‘, N
‘DISPATCHER_QUEUE_SEMAPHORE‘,
N‘EXECSYNC‘, N
‘FSAGENT‘,
N‘FT_IFTS_SCHEDULER_IDLE_WAIT‘, N
‘FT_IFTSHC_MUTEX‘,
N‘HADR_CLUSAPI_CALL‘, N
‘HADR_FILESTREAM_IOMGR_IOCOMPLETION‘,
N‘HADR_LOGCAPTURE_WAIT‘, N
‘HADR_NOTIFICATION_DEQUEUE‘,
N‘HADR_TIMER_TASK‘, N
‘HADR_WORK_QUEUE‘,
N‘KSOURCE_WAKEUP‘, N
‘LAZYWRITER_SLEEP‘,
N‘LOGMGR_QUEUE‘, N
‘ONDEMAND_TASK_QUEUE‘,
N‘PWAIT_ALL_COMPONENTS_INITIALIZED‘,
N‘QDS_PERSIST_TASK_MAIN_LOOP_SLEEP‘,
N‘QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP‘,
N‘REQUEST_FOR_DEADLOCK_SEARCH‘, N
‘RESOURCE_QUEUE‘,
N‘SERVER_IDLE_CHECK‘, N
‘SLEEP_BPOOL_FLUSH‘,
N‘SLEEP_DBSTARTUP‘, N
‘SLEEP_DCOMSTARTUP‘,
N‘SLEEP_MASTERDBREADY‘, N
‘SLEEP_MASTERMDREADY‘,
N‘SLEEP_MASTERUPGRADED‘, N
‘SLEEP_MSDBSTARTUP‘,
N‘SLEEP_SYSTEMTASK‘, N
‘SLEEP_TASK‘,
N‘SLEEP_TEMPDBSTARTUP‘, N
‘SNI_HTTP_ACCEPT‘,
N‘SP_SERVER_DIAGNOSTICS_SLEEP‘, N
‘SQLTRACE_BUFFER_FLUSH‘,
N‘SQLTRACE_INCREMENTAL_FLUSH_SLEEP‘,
N‘SQLTRACE_WAIT_ENTRIES‘, N
‘WAIT_FOR_RESULTS‘,
N‘WAITFOR‘, N
‘WAITFOR_TASKSHUTDOWN‘,
N‘WAIT_XTP_HOST_WAIT‘, N
‘WAIT_XTP_OFFLINE_CKPT_NEW_LOG‘,
N‘WAIT_XTP_CKPT_CLOSE‘, N
‘XE_DISPATCHER_JOIN‘,
N‘XE_DISPATCHER_WAIT‘, N
‘XE_TIMER_EVENT‘)
GROUP BY wait_type
ORDER BY SUM(wait_time_ms)
DESC
WITH [Waits] AS
(SELECT
[wait_type],
[wait_time_ms] / 1000.0 AS [WaitS],
([wait_time_ms] - [signal_wait_time_ms])
/ 1000.0 AS [ResourceS],
[signal_wait_time_ms] / 1000.0 AS [SignalS],
[waiting_tasks_count] AS [WaitCount],
100.0 * [wait_time_ms] / SUM (
[wait_time_ms])
OVER()
AS [Percentage],
ROW_NUMBER() OVER(
ORDER BY [wait_time_ms] DESC)
AS [RowNum]
FROM sys.dm_os_wait_stats
WHERE [wait_type] NOT IN (
N‘BROKER_EVENTHANDLER‘, N
‘BROKER_RECEIVE_WAITFOR‘,
N‘BROKER_TASK_STOP‘, N
‘BROKER_TO_FLUSH‘,
N‘BROKER_TRANSMITTER‘, N
‘CHECKPOINT_QUEUE‘,
N‘CHKPT‘, N
‘CLR_AUTO_EVENT‘,
N‘CLR_MANUAL_EVENT‘, N
‘CLR_SEMAPHORE‘,
N‘DBMIRROR_DBM_EVENT‘, N
‘DBMIRROR_EVENTS_QUEUE‘,
N‘DBMIRROR_WORKER_QUEUE‘, N
‘DBMIRRORING_CMD‘,
N‘DIRTY_PAGE_POLL‘, N
‘DISPATCHER_QUEUE_SEMAPHORE‘,
N‘EXECSYNC‘, N
‘FSAGENT‘,
N‘FT_IFTS_SCHEDULER_IDLE_WAIT‘, N
‘FT_IFTSHC_MUTEX‘,
N‘HADR_CLUSAPI_CALL‘, N
‘HADR_FILESTREAM_IOMGR_IOCOMPLETION‘,
N‘HADR_LOGCAPTURE_WAIT‘, N
‘HADR_NOTIFICATION_DEQUEUE‘,
N‘HADR_TIMER_TASK‘, N
‘HADR_WORK_QUEUE‘,
N‘KSOURCE_WAKEUP‘, N
‘LAZYWRITER_SLEEP‘,
N‘LOGMGR_QUEUE‘, N
‘ONDEMAND_TASK_QUEUE‘,
N‘PWAIT_ALL_COMPONENTS_INITIALIZED‘,
N‘QDS_PERSIST_TASK_MAIN_LOOP_SLEEP‘,
N‘QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP‘,
N‘REQUEST_FOR_DEADLOCK_SEARCH‘, N
‘RESOURCE_QUEUE‘,
N‘SERVER_IDLE_CHECK‘, N
‘SLEEP_BPOOL_FLUSH‘,
N‘SLEEP_DBSTARTUP‘, N
‘SLEEP_DCOMSTARTUP‘,
N‘SLEEP_MASTERDBREADY‘, N
‘SLEEP_MASTERMDREADY‘,
N‘SLEEP_MASTERUPGRADED‘, N
‘SLEEP_MSDBSTARTUP‘,
N‘SLEEP_SYSTEMTASK‘, N
‘SLEEP_TASK‘,
N‘SLEEP_TEMPDBSTARTUP‘, N
‘SNI_HTTP_ACCEPT‘,
N‘SP_SERVER_DIAGNOSTICS_SLEEP‘, N
‘SQLTRACE_BUFFER_FLUSH‘,
N‘SQLTRACE_INCREMENTAL_FLUSH_SLEEP‘,
N‘SQLTRACE_WAIT_ENTRIES‘, N
‘WAIT_FOR_RESULTS‘,
N‘WAITFOR‘, N
‘WAITFOR_TASKSHUTDOWN‘,
N‘WAIT_XTP_HOST_WAIT‘, N
‘WAIT_XTP_OFFLINE_CKPT_NEW_LOG‘,
N‘WAIT_XTP_CKPT_CLOSE‘, N
‘XE_DISPATCHER_JOIN‘,
N‘XE_DISPATCHER_WAIT‘, N
‘XE_TIMER_EVENT‘)
AND [waiting_tasks_count] > 0
)
SELECT
MAX (
[W1].
[wait_type])
AS [WaitType],
CAST (
MAX (
[W1].
[WaitS])
AS DECIMAL (
16,
2))
AS [Wait_S],
CAST (
MAX (
[W1].
[ResourceS])
AS DECIMAL (
16,
2))
AS [Resource_S],
CAST (
MAX (
[W1].
[SignalS])
AS DECIMAL (
16,
2))
AS [Signal_S],
MAX (
[W1].
[WaitCount])
AS [WaitCount],
CAST (
MAX (
[W1].
[Percentage])
AS DECIMAL (
5,
2))
AS [Percentage],
CAST ((
MAX (
[W1].
[WaitS])
/ MAX (
[W1].
[WaitCount]))
AS DECIMAL (
16,
4))
AS [AvgWait_S],
CAST ((
MAX (
[W1].
[ResourceS])
/ MAX (
[W1].
[WaitCount]))
AS DECIMAL (
16,
4))
AS [AvgRes_S],
CAST ((
MAX (
[W1].
[SignalS])
/ MAX (
[W1].
[WaitCount]))
AS DECIMAL (
16,
4))
AS [AvgSig_S]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
ON [W2].
[RowNum] <= [W1].
[RowNum]
GROUP BY [W1].
[RowNum]
HAVING SUM (
[W2].
[Percentage])
- MAX (
[W1].
[Percentage])
< 95;
-- percentage threshold
GO
获取一段时间内累计的File reads：
SELECT DB_NAME(mf.database_id)
AS databaseName ,
mf.physical_name ,
divfs.num_of_reads ,
--other columns removed in this section. See Listing 6.14 for complete code
GETDATE()
AS baselineDate
INTO #baseline
FROM sys.dm_io_virtual_file_stats(
NULL,
NULL)
AS divfs
JOIN sys.master_files
AS mf
ON mf.database_id
= divfs.database_id
AND mf.
file_id = divfs.
file_id
--过一段时间后执行下面的SQL
WITH currentLine
AS (
SELECT DB_NAME(mf.database_id)
AS databaseName ,
mf.physical_name ,
num_of_reads ,
--other columms removed
GETDATE()
AS currentlineDate
FROM sys.dm_io_virtual_file_stats(
NULL,
NULL)
AS divfs
JOIN sys.master_files
AS mf
ON mf.database_id
= divfs.database_id
AND mf.
file_id = divfs.
file_id
)
SELECT currentLine.databaseName ,
currentLine.physical_name ,
--gets the time difference in milliseconds since the baseline was taken
DATEDIFF(millisecond,baseLineDate,currentLineDate)
AS elapsed_ms,
--gets the change in time since the baseline was taken
currentLine.num_of_reads
- #baseline.num_of_reads
AS num_of_reads
--other columns removed
FROM currentLine
INNER JOIN #baseline
ON #baseLine.databaseName
= currentLine.databaseName
AND #baseLine.physical_name
= currentLine.physical_name
根据IP获取连接个数
-- Get a count of SQL connections by IP address
SELECT dec.client_net_address ,
des.program_name ,
des.host_name ,
--des.login_name ,
COUNT(
dec.session_id)
AS connection_count
FROM sys.dm_exec_sessions
AS des
INNER JOIN sys.dm_exec_connections
AS dec
ON des.session_id
= dec.session_id
-- WHERE LEFT(des.host_name, 2) = ‘WK‘
GROUP BY dec.client_net_address ,
des.program_name ,
des.host_name
-- des.login_name
-- HAVING COUNT(dec.session_id) > 1
ORDER BY des.program_name,
dec.client_net_address ;
查看谁在通过SSMS执行什么
SELECT dec.client_net_address ,
des.host_name ,
dest.text
FROM sys.dm_exec_sessions des
INNER JOIN sys.dm_exec_connections
dec
ON des.session_id
= dec.session_id
CROSS APPLY sys.dm_exec_sql_text(
dec.most_recent_sql_handle) dest
WHERE des.program_name
LIKE ‘Microsoft SQL Server Management Studio%‘
ORDER BY des.program_name ,
dec.client_net_address
查看当前session的session级别的设置
SELECT des.text_size ,
des.language ,
des.date_format ,
des.date_first ,
des.quoted_identifier ,
des.arithabort ,
des.ansi_null_dflt_on ,
des.ansi_defaults ,
des.ansi_warnings ,
des.ansi_padding ,
des.ansi_nulls ,
des.concat_null_yields_null ,
des.transaction_isolation_level ,
des.lock_timeout ,
des.deadlock_priority
FROM sys.dm_exec_sessions des
WHERE des.session_id
= @@SPID
查看超过一个session的登录信息
SELECT login_name ,
COUNT(session_id)
AS session_count
FROM sys.dm_exec_sessions
WHERE is_user_process
= 1
GROUP BY login_name
ORDER BY login_name
识别有context swithing的sessions信息
SELECT session_id ,
login_name ,
original_login_name
FROM sys.dm_exec_sessions
WHERE is_user_process
= 1
AND login_name
<> original_login_name
识别不活动的sessions
DECLARE @days_old SMALLINT
SELECT @days_old = 5
SELECT des.session_id ,
des.login_time ,
des.last_request_start_time ,
des.last_request_end_time ,
des.[status] ,
des.[program_name] ,
des.cpu_time ,
des.total_elapsed_time ,
des.memory_usage ,
des.total_scheduled_time ,
des.total_elapsed_time ,
des.reads ,
des.writes ,
des.logical_reads ,
des.row_count ,
des.is_user_process
FROM sys.dm_exec_sessions des
INNER JOIN sys.dm_tran_session_transactions dtst
ON des.session_id
= dtst.session_id
WHERE des.is_user_process
= 1
AND DATEDIFF(dd, des.last_request_end_time,
GETDATE())
> @days_old
AND des.status
!= ‘Running‘
ORDER BY des.last_request_end_time
识别孤立事务引起的闲置sessions
SELECT des.session_id ,
des.login_time ,
des.last_request_start_time ,
des.last_request_end_time ,
des.host_name ,
des.login_name
FROM sys.dm_exec_sessions des
INNER JOIN sys.dm_tran_session_transactions dtst
ON des.session_id
= dtst.session_id
LEFT JOIN sys.dm_exec_requests der
ON dtst.session_id
= der.session_id
WHERE der.session_id
IS NULL
ORDER BY des.session_id
查看当前活动请求的执行计划、CPU消耗等
SELECT der.session_id ,
DB_NAME(der.database_id)
AS database_name ,
deqp.query_plan ,
SUBSTRING(dest.
text, der.statement_start_offset
/ 2,
( CASE WHEN der.statement_end_offset
= -1
THEN DATALENGTH(dest.
text)
ELSE der.statement_end_offset
END - der.statement_start_offset )
/ 2)
AS [statement executing] ,
der.cpu_time
--der.granted_query_memory
--der.wait_time
--der.total_elapsed_time
--der.reads
FROM sys.dm_exec_requests der
INNER JOIN sys.dm_exec_sessions des
ON des.session_id
= der.session_id
CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) dest
CROSS APPLY sys.dm_exec_query_plan(der.plan_handle) deqp
WHERE des.is_user_process
= 1
AND der.session_id
<> @@spid
ORDER BY der.cpu_time
DESC ;
-- ORDER BY der.granted_query_memory DESC ;
-- ORDER BY der.wait_time DESC;
-- ORDER BY der.total_elapsed_time DESC;
-- ORDER BY der.reads DESC;
查看当前谁在运行什么
-- Who is running what at this instant
SELECT dest.
text AS [Command text] ,
des.login_time ,
des.[host_name] ,
des.[program_name] ,
der.session_id ,
dec.client_net_address ,
der.status ,
der.command ,
DB_NAME(der.database_id)
AS DatabaseName
FROM sys.dm_exec_requests der
INNER JOIN sys.dm_exec_connections
dec
ON der.session_id
= dec.session_id
INNER JOIN sys.dm_exec_sessions des
ON des.session_id
= der.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
AS dest
WHERE des.is_user_process
= 1
比SP_WHO2更好的SQL
SELECT des.session_id ,
des.status ,
des.login_name ,
des.[HOST_NAME] ,
der.blocking_session_id ,
DB_NAME(der.database_id)
AS database_name ,
der.command ,
des.cpu_time ,
des.reads ,
des.writes ,
dec.last_write ,
des.[program_name] ,
der.wait_type ,
der.wait_time ,
der.last_wait_type ,
der.wait_resource ,
CASE des.transaction_isolation_level
WHEN 0 THEN ‘Unspecified‘
WHEN 1 THEN ‘ReadUncommitted‘
WHEN 2 THEN ‘ReadCommitted‘
WHEN 3 THEN ‘Repeatable‘
WHEN 4 THEN ‘Serializable‘
WHEN 5 THEN ‘Snapshot‘
END AS transaction_isolation_level ,
OBJECT_NAME(dest.objectid, der.database_id)
AS OBJECT_NAME ,
SUBSTRING(dest.
text, der.statement_start_offset
/ 2,
( CASE WHEN der.statement_end_offset
= -1
THEN DATALENGTH(dest.
text)
ELSE der.statement_end_offset
END - der.statement_start_offset )
/ 2)
AS [executing statement] ,
deqp.query_plan
FROM sys.dm_exec_sessions des
LEFT JOIN sys.dm_exec_requests der
ON des.session_id
= der.session_id
LEFT JOIN sys.dm_exec_connections
dec
ON des.session_id
= dec.session_id
CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) dest
CROSS APPLY sys.dm_exec_query_plan(der.plan_handle) deqp
WHERE des.session_id
<> @@SPID
ORDER BY des.session_id
清除指定数据库的计划缓存
--Determine the id of your database
DECLARE @intDBID INTEGER
SET @intDBID = (
SELECT dbid
FROM master.dbo.sysdatabases
WHERE name
= ‘mydatabasename‘
)
--Flush the procedure cache for your database
DBCC FLUSHPROCINDB (
@intDBID)
Retrieving the text for a currently executing ad hoc query
SELECT dest.
text ,
dest.dbid ,
dest.objectid
FROM sys.dm_exec_requests
AS der
CROSS APPLY sys.dm_exec_sql_text(der.sql_handle)
AS dest
WHERE session_id
= @@spid ;
Retrieving the text for a currently executing batch
SELECT dest.
text
FROM sys.dm_exec_requests
AS der
CROSS APPLY sys.dm_exec_sql_text(der.sql_handle)
AS dest
WHERE session_id
<> @@spid
AND text LIKE ‘%waitfor%‘ ;
Returning the text of an executing stored
procedure
SELECT dest.dbid ,
dest.objectid ,
dest.encrypted ,
dest.text
FROM sys.dm_exec_requests
AS der
CROSS APPLY sys.dm_exec_sql_text(der.sql_handle)
AS dest
WHERE objectid
= object_id(
‘test‘,
‘p‘);
Parsing the SQL text using statement_start_offset
and statement_end_offset
SELECT der.statement_start_offset ,
der.statement_end_offset ,
SUBSTRING(dest.
text, der.statement_start_offset
/ 2,
( CASE WHEN der.statement_end_offset
= -1
THEN DATALENGTH(dest.
text)
ELSE der.statement_end_offset
END - der.statement_start_offset )
/ 2)
AS statement_executing ,
dest.text AS [full statement code]
FROM sys.dm_exec_requests der
INNER
