Performance Strategies For SQL Server

Elevating SQL Server Performance: Strategies for Efficiency

Introduction:

Enhancing SQL Server performance is paramount in today’s data-driven landscape. This post delves into strategies to optimize performance, ensuring responsive applications and efficient database operations.

Indexing and Query Optimization:

Boost performance with proper indexing and query refinement. Optimize query execution plans for efficiency.

• Index Usage Statistics: 

To identify underutilised or unused indexes, execute the following command:

SELECT * FROM sys.dm_db_index_usage_stats
WHERE user_seeks = 0 AND user_scans = 0 AND user_lookups = 0

• Missing Indexes:

To identify missing indexes that can improve query performance, use the following command:

SELECT * FROM sys.dm_db_missing_index_details

Partitioning:

Partition large tables for parallelism and quicker query execution.

a) Creating partitioned table

SELECT * FROM sys.tables AS t JOIN sys.indexes AS i ON t.[object_id] = i.[object_id] AND i.[TYPE] IN (0, 1) JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id 
WHERE t.name = 'PatientsPartitionTable'; 
GO

b) Creating the file  groups and adding files to each file group based on MS replication_option 

CREATE PARTITION FUNCTION MSreplication_options (int)  AS RANGE LEFT FOR VALUES (101, 102, 103) ;

GO

Regular Database Maintenance:

Perform routine tasks like index rebuilding and statistics updating for a healthy database.

1. Backup Script:

• Full BackupBACKUP DATABASE [YourDatabase] TO DISK = ‘C:\Backup\YourDatabaseFull.bak’ WITH INIT;

• Differential Backup

BACKUP DATABASE [YourDatabase] TO DISK = ‘C:\Backup\YourDatabaseDiff.bak’ WITH DIFFERENTIAL;

• Transaction Log Backup

BACKUP LOG [YourDatabase] TO DISK = ‘C:\Backup\YourDatabaseLog.trn’;

Resource Management and Configuration:

Configure resources like memory and CPU settings to match workload requirements.

• Monitor Memory Usage:

 To view current memory usage by SQL Server, execute the following command:

SELECT * FROM sys.dm_os_process_memory

• Monitor CPU Usage:

To monitor CPU usage by SQL Server processes, use the following command:

SELECT * FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N’RING_BUFFER_SCHEDULER_MONITOR’

Server Configuration:

Proper configuration of SQL Server settings is crucial for achieving optimal performance. Here are a few important commands for server configuration:

• Max Degree of Parallelism (MAXDOP):

To set the maximum degree of parallelism, use the following command:

EXEC sys.sp_configure N’max degree of parallelism’, 8
RECONFIGURE WITH OVERRIDE 

• Optimize for Ad Hoc Workloads:

To optimize SQL Server for ad hoc queries, execute the following command:

EXEC sys.sp_configure N’optimize for ad hoc workloads’, 1
RECONFIGURE WITH OVERRIDE

 Query and Database Tuning:

Review queries, analyze execution plans, and ensure optimal database design.

• Index Fragmentation Analysis:

Fragmented indexes can impact query performance. Use the following script to identify fragmented indexes:

Caching and Query Result Optimization:

Implement caching to reduce database load and enhance response times.

1.  Top CPU Consuming Queries

 SELECT TOP 10

qs.execution_count,

qs.total_worker_time AS TotalCPU,

qs.total_worker_time / qs.execution_count AS AvgCPU,

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 QueryText

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st

ORDER BY qs.total_worker_time DESC;

1. Top I/O Consuming Queries

SELECT TOP 10

qs.execution_count,

total_logical_reads + total_logical_writes AS TotalIO,

(total_logical_reads + total_logical_writes) / qs.execution_count AS AvgIO,

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 QueryText

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st

ORDER BY (total_logical_reads + total_logical_writes) DESC;

• Query Result Caching:

 SQL Server has built-in query result caching capabilities that can improve performance for frequently executed queries. Use the OPTION (RECOMPILE) query hint to prevent query result caching for specific queries that may benefit from fresh query plans and result sets.

 SELECT  *  FROM autoadmin_managed_databases OPTION (RECOMPILE);

Hardware and Storage Considerations:

Invest in quality hardware components, consider SSDs, and optimize storage.

• Get CPU information

 SELECT cpu_count AS ‘Number of Processors’, hyperthread_ratio AS ‘Hyperthread Ratio’ FROM sys.dm_os_sys_info;

• Get memory information

SELECT

total_physical_memory_kb / 1024 AS ‘Total Physical Memory (MB)’,

available_physical_memory_kb / 1024 AS ‘Available Physical Memory (MB)’,

total_page_file_kb / 1024 AS ‘Total Page File (MB)’,

available_page_file_kb / 1024 AS ‘Available Page File (MB)’

FROM sys.dm_os_sys_memory;

• Get disk information

EXEC xp_fixeddrives;

Query Performance Monitoring:

Implement monitoring to identify and address bottlenecks proactively.

• Query Execution Plan:

SET SHOWPLAN_TEXT ON
GO

• Query Statistics:

SET STATISTICS IO ON
SET STATISTICS TIME ON
GO

Conclusion:

Optimizing SQL Server performance ensures faster queries, responsive applications, and improved user experience. Continuously adapt strategies to evolving workload demands for efficient database-driven applications.