Collecting Performance Statistics
SQL Server provides various tools and methods for collecting performance data:
Tools and Techniques
- Dynamic Management Views (DMVs) and Functions (DMFs): These are the primary programmatic way to access performance data in real-time. Examples include
sys.dm_exec_query_stats, sys.dm_os_wait_stats, and sys.dm_db_index_usage_stats.
- SQL Server Management Studio (SSMS): SSMS offers graphical tools like Activity Monitor and SQL Server Profiler (though Query Store is often preferred over Profiler for performance tuning).
- Query Store: Introduced in SQL Server 2016, Query Store automatically captures query history, execution plans, and runtime statistics, making it invaluable for performance analysis and regression detection.
- Performance Monitor (PerfMon): A Windows utility that can collect performance counters exposed by SQL Server.
Example: Querying Wait Statistics
You can retrieve current wait statistics using the following DMV:
SELECT
wait_type,
waiting_tasks_count,
wait_time_ms,
max_wait_time_ms,
signal_wait_time_ms
FROM
sys.dm_os_wait_stats
WHERE
wait_type NOT IN (
'BROKER_EVENTHANDLER', 'BROKER_RECEIVE', 'BROKER_SENDER', 'BROKER_TASK_STOP',
'BROKER_TRANSMITTER', 'CHECKPOINT_QUEUE', 'CHKPT', 'CLR_AUTO_EVENT',
'CLR_MANUAL_EVENT', 'CLR_SEMAPHORE', 'DBMIRROR_DBM_EVENT', 'DBMIRROR_EVENTS_QUEUE',
'DBMIRROR_WORKER_QUEUE', 'DBMIRRORING_CMD', 'DIRTY_PAGE_POLL',
'DISPATCHER_QUEUE_SEMAPHORE', 'EXECSYNC', 'FSAGENT', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
'FT_IFTSHC_MUTEX', 'HADR_CHANNEL_ERROR', 'HADR_LOCAL_INDEX_REBUILD_WAIT',
'HADR_NOTIFICATION_DEQUEUE', 'HADR_TIMER_TASK', 'HADR_TRADE_FINISH_WAIT',
'HADR_WAIT_CONFIRM', 'HADR_WORK_QUEUE', 'İKERT_RETRY_WAIT',
'INDEX_REORGANIZE', 'LAZYWRITER_SLEEP', 'LOGMGR_QUEUE', 'MEMORY_ALLOCATION_EXT',
'ONDEMAND_TASK_QUEUE', 'PAGEIOLATCH_SH', 'PAGEIOLATCH_UP', 'PWAIT_ALL_COMPONENTS_INITIALIZED',
'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', 'QDS_ASYNC_QUEUE',
'QDS_CLEANUP_STALE_QUERIES', 'QDS_SHUTDOWN_QUEUE', 'READY_TO_RUN_TASK',
'REQUEST_FOR_DEADLOCK_SEARCH', 'RESOURCE_QUEUE', 'SERVER_IDLE_CHECK',
'SLEEP_BPOOL_FLUSH', 'SLEEP_DBSTARTUP', 'SLEEP_DCOMSTARTUP', 'SLEEP_MASTERDBREADY',
'SLEEP_MASTERMDREADY', 'SLEEP_MASTERREDOERCALL', 'SLEEP_MASTERREDOERCALL',
'SLEEP_TASK', 'SLEEP_TEMPDBSTARTUP', 'SNI_HTTP_ACCEPT', 'SP_SERVER_DIAGNOSTICS_SLEEP',
'SQLTRACE_BUFFER_FLUSH', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
'SQLTRACE_WAIT_FOR_EVENT', 'SRVR_SHUTDOWN', 'SS Tugas_SYNC', 'STMTCOMPLETED',
'TASK_QUEUE_SEMAPHORE', 'WAIT_TEST', 'WAITFOR', 'WAITFOR_TASKSHUTDOWN',
'WAIT_XTP_RECOVERY', 'WRITELOG'
)
ORDER BY
wait_time_ms DESC;
Analyzing Index and Statistics
Properly maintained statistics are vital for the SQL Server query optimizer to generate efficient execution plans. Outdated or missing statistics can lead to poor performance.
Index Fragmentation
High index fragmentation can degrade read performance. You can check fragmentation levels using:
SELECT
i.name AS IndexName,
s.name AS SchemaName,
o.name AS TableName,
dmvs.avg_fragmentation_in_percent,
dmvs.fragment_count,
dmvs.page_count
FROM
sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS dmvs
JOIN
sys.indexes AS i ON i.object_id = dmvs.object_id AND i.index_id = dmvs.index_id
JOIN
sys.objects AS o ON o.object_id = dmvs.object_id
JOIN
sys.schemas AS s ON s.schema_id = o.schema_id
WHERE
dmvs.database_id = DB_ID()
AND o.type_desc = 'USER_TABLE'
ORDER BY
dmvs.avg_fragmentation_in_percent DESC;
You can then use ALTER INDEX REORGANIZE or ALTER INDEX REBUILD to address fragmentation.
Statistics Maintenance
Ensure that statistics are updated regularly. This can be done manually or by enabling auto-update statistics and auto-create statistics features.
-- To update statistics for a specific table
UPDATE STATISTICS YourTableName WITH FULLSCAN;
-- To enable auto-update statistics for a database
ALTER DATABASE YourDatabaseName SET AUTO_UPDATE_STATISTICS ON;