Monitoring

Monitoring Performance Monitoring

sql Copy

-- Create Extended Events Session
CREATE EVENT SESSION [Performance]
ON SERVER
ADD EVENT sqlserver.sql_statement_completed,
ADD EVENT sqlserver.sql_batch_completed
ADD TARGET package0.event_file
(SET filename=N'C:\temp\Performance.xel');
GO
-- Start Session
ALTER EVENT SESSION [Performance]
ON SERVER
STATE = START;

Key Metrics Performance Counters:

`` sql Copy

SELECT object_name, counter_name, instance_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Buffer Manager%';
Wait Statistics:



sql
Copy

SELECT wait_type, waiting_tasks_count, wait_time_ms FROM sys.dm_os_wait_stats ORDER BY wait_time_ms DESC;