Developer's Closet A place where I can put my PHP, SQL, Perl, JavaScript, and VBScript code.

12Dec/140
Performance Tuning in SQL Server

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/

 

Filed under: SQL Leave a comment
Comments (0) Trackbacks (0)

No comments yet.


Leave a comment

No trackbacks yet.