Performance Monitoring Related-
--Returns information about all the waits encountered by threads that executed for each session.
select * from sys.dm_exec_session_wait_stats where session_id in (select session_id from sys.dm_exec_sessions where status not like 'sleeping' and session_id <> @@SPID --and session_id <> *** ) order by 3 desc
--Returns information about running session.
--Find all queries waiting in the memory queue.
SELECT * FROM sys.dm_exec_query_memory_grants WHERE grant_time IS NULL;
-- Retrieve every query plan from the plan cache
SELECT * FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle);
--sp_whoisactive
https://clotat.blogspot.com/2022/10/spwhoisactive.html
--Active requests with memory grants
SELECT
--Session data
s.[session_id], s.open_transaction_count
--Memory usage
, r.granted_query_memory, mg.grant_time, mg.requested_memory_kb, mg.granted_memory_kb, mg.required_memory_kb, mg.used_memory_kb, mg.max_used_memory_kb
--Query
, query_text = t.text, input_buffer = ib.event_info, query_plan_xml = qp.query_plan, request_row_count = r.row_count, session_row_count = s.row_count
--Session history and status
, s.last_request_start_time, s.last_request_end_time, s.reads, s.writes, s.logical_reads, session_status = s.[status], request_status = r.status
--Session connection information
, s.host_name, s.program_name, s.login_name, s.client_interface_name, s.is_user_process
FROM sys.dm_exec_sessions s
LEFT OUTER JOIN sys.dm_exec_requests AS r
ON r.[session_id] = s.[session_id]
LEFT OUTER JOIN sys.dm_exec_query_memory_grants AS mg
ON mg.[session_id] = s.[session_id]
OUTER APPLY sys.dm_exec_sql_text (r.[sql_handle]) AS t
OUTER APPLY sys.dm_exec_input_buffer(s.[session_id], NULL) AS ib
OUTER APPLY sys.dm_exec_query_plan (r.[plan_handle]) AS qp
WHERE mg.granted_memory_kb > 0
ORDER BY mg.granted_memory_kb desc, mg.requested_memory_kb desc;
Working Method
/* Run both the below query to identify WAIT Type */
SELECT wait_type, db_name(database_id) as 'DB_Name', * FROM sys.dm_exec_requests where status not like 'sleeping' and session_id > 50 order by session_id
select cmd,* from sys.sysprocesses where blocked > 0
--How to check if a table is locked
SELECT
db_name(rsc_dbid) AS 'DATABASE_NAME',
case rsc_type when 1 then 'null'
when 2 then 'DATABASE'
WHEN 3 THEN 'FILE'
WHEN 4 THEN 'INDEX'
WHEN 5 THEN 'TABLE'
WHEN 6 THEN 'PAGE'
WHEN 7 THEN 'KEY'
WHEN 8 THEN 'EXTEND'
WHEN 9 THEN 'RID ( ROW ID)'
WHEN 10 THEN 'APPLICATION' end AS 'REQUEST_TYPE',
CASE req_ownertype WHEN 1 THEN 'TRANSACTION'
WHEN 2 THEN 'CURSOR'
WHEN 3 THEN 'SESSION'
WHEN 4 THEN 'ExSESSION' END AS 'REQUEST_OWNERTYPE',
OBJECT_NAME(rsc_objid ,rsc_dbid) AS 'OBJECT_NAME',
PROCESS.HOSTNAME ,
PROCESS.program_name ,
PROCESS.nt_domain ,
PROCESS.nt_username ,
PROCESS.program_name ,
SQLTEXT.text
FROM sys.syslockinfo LOCK JOIN
sys.sysprocesses PROCESS
ON LOCK.req_spid = PROCESS.spid
CROSS APPLY sys.dm_exec_sql_text(PROCESS.SQL_HANDLE) SQLTEXT
OS Related
--PLE BCH and Avg. disk
select * from sys.dm_os_performance_counters where counter_name like '%page life%' or counter_name like '%Buffer cache hit%' or counter_name like '%Avg disk%';
SELECT * FROM sys.dm_os_performance_counters WHERE object_name LIKE '%Buffer Manager%';
--Returns information about all the waits encountered by threads that executed.
SELECT * FROM sys.dm_os_wait_stats dows ORDER BY dows.wait_time_ms DESC;
--The contents of this dynamic management view can be reset. This T-SQL command resets all counters to 0:
DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);
-- Wait Type Analysis
-- > SQL 1
SELECT * FROM sys.dm_os_wait_stats dows ORDER BY dows.wait_time_ms DESC;
-- > SQL 2
SELECT * FROM sys.dm_os_waiting_tasks dowt WHERE dowt.wait_type LIKE '<copy the wait type from first query FROM sys.dm_os_wait_stats>%';
-- I/O latency stats by database/files execute
SELECT *
FROM sys.dm_io_virtual_file_stats(DB_ID('AdventureWorks2014'), NULL) divfs
ORDER BY divfs.io_stall DESC;
--Returns a miscellaneous set of useful information about the computer, and about the resources available to and consumed by SQL Server.
SELECT sqlserver_start_time, * FROM sys.dm_os_sys_info
How long a worker has been running in a SUSPENDED or RUNNABLE state.
SELECT
t1.session_id,
CONVERT(varchar(10), t1.status) AS status,
CONVERT(varchar(15), t1.command) AS command,
CONVERT(varchar(10), t2.state) AS worker_state,
w_suspended =
CASE t2.wait_started_ms_ticks
WHEN 0 THEN 0
ELSE
t3.ms_ticks - t2.wait_started_ms_ticks
END,
w_runnable =
CASE t2.wait_resumed_ms_ticks
WHEN 0 THEN 0
ELSE
t3.ms_ticks - t2.wait_resumed_ms_ticks
END
FROM sys.dm_exec_requests AS t1
INNER JOIN sys.dm_os_workers AS t2
ON t2.task_address = t1.task_address
CROSS JOIN sys.dm_os_sys_info AS t3
WHERE t1.scheduler_id IS NOT NULL
order by 2 desc ;
-------------------------------------------------------
session_id status command worker_state w_suspended w_runnable
52 suspended DELETE SUSPENDED 3443 3443
64 suspended UPDATE SUSPENDED 31369 31369
57 suspended BULK INSERT SUSPENDED 0 0
Identify High Disk Utilization Queries
Other Related Counters
Full Scans/sec -> Perfmon=>SQLServer:Access Methods
Index Searches/sec -> Perfmon=>SQLServer:Access Methods
--Average Disk sec/Read
Average Disk sec/Read -> Perfmon=> PhysicalDisk:Avg.Disk sec/Read Instance [Drive]
Note:
Average Disk sec/Read Value (in ms) < 8 ms then Performance Excellent
Average Disk sec/Read Value (in ms) between 8-12 ms then Performance Ok
Average Disk sec/Read Value (in ms) > 20 ms then Performance Bad
--Average Disk sec/Write
Average Disk sec/Write -> Perfmon=> PhysicalDisk:Avg.Disk sec/Write Instance [Drive]
Note:
Average Disk sec/Write Value (in ms) < 8 ms then Performance Excellent
Average Disk sec/Write Value (in ms) between 8-15 ms then Performance Ok
Average Disk sec/Write Value (in ms) between 15-20 ms then Performance Fair
Average Disk sec/Write Value (in ms) between > 20 ms then Performance Bad
--Current Disk Queue Length
Current Disk Queue Length -> Perfmon=> PhysicalDisk:Current Disk Queue Length [Drive]
Continues more then 1 value is indication of slow disk IO subsystem.
--Check Overall Memory Usage
SELECT
(physical_memory_in_use_kb / 1024) AS Physical_Memory_Used_MB,
(virtual_address_space_committed_kb / 1024) AS Virtual_Memory_Used_MB,
(locked_page_allocations_kb / 1024) AS Locked_Page_Allocations_MB,
(total_virtual_address_space_kb / 1024) AS Total_Virtual_Memory_MB,
(available_commit_limit_kb / 1024) AS Available_Commit_Limit_MB,
(process_physical_memory_low / 1024) AS Physical_Memory_Low_MB,
(process_virtual_memory_low / 1024) AS Virtual_Memory_Low_MB
FROM
sys.dm_os_process_memory;
--Buffer Pool Usage {This shows how much memory is used in the Buffer Pool.}
SELECT
(bpool_committed / 128) AS Buffer_Pool_Committed_MB,
(bpool_visible / 128) AS Buffer_Pool_Visible_MB,
(bpool_reserve / 128) AS Buffer_Pool_Reserve_MB
FROM
sys.dm_os_sys_info;
-- Total Server Memory vs. Target Server Memory {This will show the total memory currently used by SQL Server versus the target memory (maximum memory SQL Server will use).}
SELECT
(total_server_memory_kb / 1024) AS Total_Server_Memory_MB,
(target_server_memory_kb / 1024) AS Target_Server_Memory_MB
FROM
sys.dm_os_sys_memory;
--Memory Clerks Usage {Memory clerks help identify memory allocations by components within SQL Server.}
SELECT
type,
SUM(pages_kb / 1024) AS Memory_MB
FROM
sys.dm_os_memory_clerks
GROUP BY
type
ORDER BY
Memory_MB DESC;
Windows Performance Monitor (PerfMon)
On the server running SQL Server, open Performance Monitor (PerfMon).
Add counters for SQL Server under SQLServer:Memory Manager:
Total Server Memory (KB): Shows the total memory that SQL Server is currently using.
Target Server Memory (KB): Shows the total memory SQL Server is configured to use.
Memory Grants Pending: Shows the number of queries waiting for memory.