I wanted to capture a few notes on performance tuning in SQL Server.
CPU and memory:
select * from sys.dm_os_performance_counters
where counter_name in (‘Batch Requests/sec’, ‘SQL Compilations/sec’ , ‘SQL Re-Compilations/sec’, ‘Buffer Cache Hit Ratio’, ‘Buffer Cache Hit Ratio Base’)
— these counters accumulate since server startup, so we need to calculate usage over time.
— for example, over the past 10 seconds (I’d expect to see this lower than 100, but that is only a guess until we learn how this server behaves – it depends on hardware, CPU type, etc.):
DECLARE @BatchRequests BIGINT;
SELECT @BatchRequests = cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = ‘Buffer cache hit ratio ‘;
WAITFOR DELAY ’00:00:10’;
SELECT (cntr_value – @BatchRequests) / 10 AS ‘Buffer cache hit ratio ‘
FROM sys.dm_os_performance_counters
WHERE counter_name = ‘Buffer cache hit ratio
— The Batch Requests/sec value depends on hardware used, but it should be under 1000. The recommended value for SQL Compilations/sec is less than 10% of Batch Requests/sec and for SQL Re-Compilations/sec is less than 10% of SQL Compilations/sec
Page Life Expectancy:
SELECT [cntr_value]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE ‘%Manager%’
AND [counter_name] = ‘Page life expectancy’
Wait stats:
SELECT
[wait_type],
[wait_time_ms] / 1000.0 AS [WaitS],
([wait_time_ms] – [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
[signal_wait_time_ms] / 1000.0 AS [SignalS],
[waiting_tasks_count] AS [WaitCount],
100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
FROM sys.dm_os_wait_stats
Reference: https://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/
