1、查看等待统计信息,找出SQL Server慢在哪里:
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
2、获取一段时间内累计的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
3、根据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 ;
4、查看谁在通过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
5、查看当前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
6、查看超过一个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
7、识别有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
8、识别不活动的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
9、识别孤立事务引起的闲置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
10、查看当前活动请求的执行计划、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;
11、查看当前谁在运行什么
-- 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
12、比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
转:https://blog.csdn.net/burgess_liu/article/details/52813727