SQL Server is one of the most popular relational database management systems used by organizations worldwide. As your business grows, your database also needs to grow and adapt to changing requirements.
However, it is crucial to ensure that your database is performing optimally to avoid downtime, slow query execution, and other performance issues. In this article, we will explore some techniques to evaluate the performance of your SQL Server database using MS SQL Server 2022. as an example.
Step 1: Analyze database metrics
The first step in evaluating database performance is to analyze the key performance metrics of your database. SQL Server provides several performance counters that you can use to monitor and diagnose your database performance. Some of the key metrics you should consider are:
- Wait Types
- CPU usage
- Memory usage
- Disk I/O performance
- Network traffic
- Locking and blocking
- Page life expectancy
- Buffer cache hit ratio
- Batch requests/sec
- SQL compilations/sec
- SQL recompilations/sec
By analyzing these performance counters, you can identify potential performance bottlenecks and tune your database accordingly. You can use Performance Monitor or SQL Server Profiler to monitor these performance counters.
To get you started, we have included one query – written by Pinal Dave SQL authority blogs
This will tell you the highest amount of time your database spends waiting and what wait type is the culprit.
/*
— Script to Clear Wait Types
DBCC SQLPERF(‘sys.dm_os_wait_stats’, CLEAR);
GO
*/
— SQL Wait Stats and Queies
SELECT wait_type AS Wait_Type,
wait_time_ms / 1000.0 AS Wait_Time_Seconds,
waiting_tasks_count AS Waiting_Tasks_Count,
— CAST((wait_time_ms / 1000.0)/waiting_tasks_count AS decimal(10,4)) AS AVG_Waiting_Tasks_Count,
wait_time_ms * 100.0 / SUM(wait_time_ms) OVER() AS Percentage_WaitTime
–,waiting_tasks_count * 100.0 / SUM(waiting_tasks_count) OVER() AS Percentage_Count
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’CXCONSUMER’,
N’DBMIRROR_DBM_EVENT’,
N’DBMIRROR_DBM_MUTEX’,
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’LAZYWRITER_SLEEP’,
N’LOGMGR_QUEUE’,
N’MEMORY_ALLOCATION_EXT’,
N’ONDEMAND_TASK_QUEUE’,
N’PARALLEL_REDO_DRAIN_WORKER’,
N’PARALLEL_REDO_LOG_CACHE’,
N’PARALLEL_REDO_TRAN_LIST’,
N’PARALLEL_REDO_WORKER_SYNC’,
N’PARALLEL_REDO_WORKER_WAIT_WORK’,
N’PREEMPTIVE_HADR_LEASE_MECHANISM’,
N’PREEMPTIVE_OS_FLUSHFILEBUFFERS’,
N’PREEMPTIVE_OS_AUTHENTICATIONOPS’,
N’PREEMPTIVE_OS_AUTHORIZATIONOPS’,
N’PREEMPTIVE_OS_COMOPS’,
N’PREEMPTIVE_OS_CREATEFILE’,
N’PREEMPTIVE_OS_CRYPTOPS’,
N’PREEMPTIVE_OS_DEVICEOPS’,
N’PREEMPTIVE_OS_FILEOPS’,
N’PREEMPTIVE_OS_GENERICOPS’,
N’PREEMPTIVE_OS_LIBRARYOPS’,
N’PREEMPTIVE_OS_PIPEOPS’,
N’PREEMPTIVE_OS_QUERYREGISTRY’
N’PREEMPTIVE_OS_VERIFYTRUST’,
N’PREEMPTIVE_OS_WAITFORSINGLEOBJECT’,
N’PREEMPTIVE_OS_WRITEFILEGATHER’,
N’PREEMPTIVE_SP_SERVER_DIAGNOSTICS’,
N’PREEMPTIVE_XE_CALLBACKEXECUTE’,
N’PREEMPTIVE_XE_DISPATCHER’,
N’PREEMPTIVE_XE_GETTARGETSTATE’,
N’PREEMPTIVE_XE_SESSIONCOMMIT’,
N’PREEMPTIVE_XE_TARGETFINALIZE’,
N’PREEMPTIVE_XE_TARGETINIT’,
N’PWAIT_ALL_COMPONENTS_INITIALIZED’,
N’PWAIT_EXTENSIBILITY_CLEANUP_TASK’,
N’PWAIT_DIRECTLOGCONSUMER_GETNEXT’,
N’QDS_ASYNC_QUEUE’,
N’QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP’,
N’QDS_PERSIST_TASK_MAIN_LOOP_SLEEP’,
N’QDS_SHUTDOWN_QUEUE’,
N’REDO_THREAD_PENDING_WORK’,
N’REQUEST_FOR_DEADLOCK_SEARCH’,N’RESOURCE_QUEUE’,
N’SERVER_IDLE_CHECK’,
N’SOS_WORK_DISPATCHER’,
N’SOS_WORKER_MIGRATION’,
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’STARTUP_DEPENDENCY_MANAGER’,N’UCS_SESSION_REGISTRATION’,
N’VDI_CLIENT_OTHER’,
N’WAIT_FOR_RESULTS’,
N’WAIT_XTP_CKPT_CLOSE’,
N’WAIT_XTP_HOST_WAIT’,
N’WAIT_XTP_OFFLINE_CKPT_NEW_LOG’,
N’WAIT_XTP_RECOVERY’,
N’WAITFOR’,
N’WAITFOR_TASKSHUTDOWN’,
N’XE_BUFFERMGR_ALLPROCESSED_EVENT’,
N’XE_DISPATCHER_JOIN’,
N’XE_TIMER_EVENT’,
N’XE_DISPATCHER_WAIT’,
N’XE_LIVE_TARGET_TVF’
) AND wait_time_ms >= 1
ORDER BY Wait_Time_Seconds DESC
— ORDER BY Waiting_Tasks_Count DESC
Also, a free helpful tool you can use SP_WhoISActive stored procedure written by Adam Machanic: Click to visit who is active tool
Here, you get the following valuable columns:
- The task duration column (dd:hh:mm: ss.sss) shows for how long the query has been running
- The sql_text column gives the SQL query running under the session_id.
- Login_name: The login running the query
- CPU: The CPU assigned for the query
- tempdb_allocations: The tempDB allocations for the session_id
- blocking_session_id: If the process is blocked with other processes, you can find the culprit SP ID.
Step 2: Identify slow running queries
Slow running queries are a common cause of poor database performance. SQL Server provides several tools to identify slow running queries, such as the Query Store, SQL Server Profiler, and Extended Events. These tools can help you identify queries that are consuming too much CPU or I/O resources, taking too long to execute, or causing blocking.
Once you have identified the slow running queries, you can analyze their execution plans to identify potential performance issues. You can also use the SQL Server Database Engine Tuning Advisor to suggest performance improvements for these queries.
To identify slow running queries, you can use the following T-SQL script:
SELECT TOP 10 ddd
total_worker_time/1000.0 as total_cpu_time_ms,
total_elapsed_time/1000.0 as
total_elapsed_time_ms,
execution_count,
(total_worker_time/1000.0)/execution_count as avg_cpu_time_ms,
(total_elapsed_time/1000.0)/execution_count as avg_elapsed_time_ms,
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END – qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_worker_time/execution_count DESC;
This script retrieves the top 10 queries by CPU time and displays their total CPU time, total elapsed time, execution count, average CPU time, average elapsed time, and query text.
Step 3: Optimize database design
Another key factor that affects database performance is the design of your database. Poorly designed databases can lead to performance issues such as slow query execution, excessive disk I/O, and fragmentation. To optimize database design, you can consider the following techniques:
- Normalize your database schema to reduce data redundancy and improve data consistency.
- Use appropriate data types to reduce storage requirements and improve performance.
- Use indexes to improve query performance.
- Avoid excessive table joins and complex queries.
- Use stored procedures and triggers to improve performance and maintainability.
Step 4: Maintain database health
Regular maintenance is necessary to ensure that your database performs optimally. You should consider the following maintenance tasks:
- Regular backups to protect against data loss and corruption.
- Regular index maintenance to reduce fragmentation and improve query performance.
- Regular statistics updates to ensure accurate query execution plans.
- Regular database integrity checks to detect and repair data corruption.
- Regular monitoring of database growth to ensure sufficient disk space.
To perform regular index maintenance, you can use the following T-SQL script:
— Rebuild all indexes in the database
USE [YourDatabaseName];
GO
EXEC sp_MSforeachtable @command1=’PRINT ”Rebuilding index on ?”; ALTER INDEX ALL ON ? REBUILD;’;
GO
To update statistics, you can use the following T-SQL script:
— Update statistics for all tables in the database
USE [YourDatabaseName];
GO
EXEC sp_MSforeachtable @command1=’PRINT ”Updating statistics for ?”; UPDATE STATISTICS ? WITH FULLSCAN;’;
GO
SQL Server consultants for your database needs
In conclusion, evaluating the performance of your SQL Server database is a critical task that can help you identify potential performance bottlenecks and tune your database accordingly. By analyzing database metrics, identifying slow running queries, optimizing database design, and maintaining database health, you can ensure that your database performs optimally and meets the growing demands of your business.
The SQL Server consultancy services at Everconnect can help you optimize your SQL Server database for enhanced efficiency. Contact the database professionals at Everconnect today to learn more.