Optimizing SQL Server CPU Performance.

I am trying to add some more additional explanation to the below points.

A) SQL Server Access Methods:

(1) Index Search/Per Second:

Number of index searches. Index searches are used to start range scans, single index record fetches, and to reposition within an index. Index searches are preferable to index and table scans.  For OLTP applications, optimize for more index searches and less scans (preferably, 1 full scan for every 1000 index searches). Index and table scans are expensive I/O operations.

(2) Full Scans/Sec:This counter monitors the number of full scans on base tables or indexes. Values greater than 1 or 2 indicate that we are having table / Index page scans. If we see high CPU then we need to investigate this counter, otherwise if the full scans are on small tables we can ignore this counter.  A few of the main causes of high Full Scans/sec are
• Missing indexes
• Too many rows requested
Queries with missing indexes or too many rows requested will have a large number of logical reads and an increased CPU time.

(3) PageSplits/Per Sec:

Number of page splits per second that occur as the result of overflowing index pages. Interesting counter that can lead us to our table / index design. This value needs to be low as possible. If you find out that the number of page splits is high, consider increasing the fillfactor of your indexes. An increased fillfactor helps to reduce page splits because there is more room in data pages before it fills up and a page split has to occur.

Note that this counter also includes the new page allocations as well and doesn’t necessarily pose a problem.  The other place we can confirm the page splits that involve data or index rows moves are the fragmented indexes on page splits.

B) SQL SERVER BUFFER MANAGER:

(1)Buffer Cache Hit Ratio:

  This counter indicates how often SQL Server goes to the buffer. Not the hard disk, to get data. The higher the ratio the less often the SQL Server has to go to the hard disk to fetch the data and performance overall boosted. Unlike many other counter counters available for monitoring SQL Server, this counter averages the buffer cache hit ratio from the time the last  instance of SQL server was restarted. In OLTP applications this ratio should exceed 90-95%. IF IT DOES NOT  THEN WE NEED TO ADD MORE RAM to server to increase performance. In OLAP applications the ratio should be much less  because of the nature of how OLAP works.In any case more RAM should increase the performance of SQL Server

SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 as BufferCacheHitRatio

FROM sys.dm_os_performance_counters a
JOIN (SELECT cntr_value,OBJECT_NAME FROM sys.dm_os_performance_counters WHERE counter_name = 'Buffer cache hit ratio base'AND OBJECT_NAME = 'SQLServer:Buffer Manager') b ON a.OBJECT_NAME = b.OBJECT_NAME WHERE a.counter_name = 'Buffer cache hit ratio'AND a.OBJECT_NAME = 'SQLServer:Buffer Manager'

(2)Lazy Writes/Sec:

The lazy writer is a process the periodically checks the available free space in the buffer cache between two checkpoints and ensure that there is always enough free memory. when a lazy writer determines free pages are needed in the buffer pool for better performance. It removes the old pages before regular checkpoint occurs.
 If a dirty page(a page read and/or modified) in buffer has not been used for a while. The lazy writer flushes it to disk  and then mark as free in the buffer cache. THE LAZY WRITER VALUES IS CONSTANTLY HIGHER THAN 20 to be sure the server is under Pressure. So it should be <20 b="">

But how can we find out this per second values. Below I am providing a sample query and we can use this for all Per Second counters. The below one I have written for Page Splits counter we can use the same for all the remaining persec counters.

DECLARE @LazyWrites1 BIGINT
SELECT @LazyWrites1=cntr_value
FROM SYS.DM_OS_PERFORMANCE_COUNTERS
WHERE Counter_Name='Page Splits/sec'
WAITFOR DELAY '00:00:10';

SELECT(cntr_value-@LazyWrites1)/10 AS 'Page Splits/sec'
FROM SYS.DM_OS_PERFORMANCE_COUNTERS
WHERE counter_name='Page Splits/sec'

(3)Page life expectancy:

Page life expectancy is number of seconds a page will stay in the buffer pool. IF A PAGE STAYS IN THE BUFFER POOL LESS THAN 300 SECONDS THIS IS A CLEAR INDICATION OF MEMORY PRESSURE. So expected value is more than 300 seconds.

SELECT *

FROM sys.dm_os_performance_counters WHERE counter_name = 'Page life expectancy'AND OBJECT_NAME = 'SQLServer:Buffer Manager'

(4)Free Pages:

Total number free buffer pages in RAM. It should be >640. For example in this scenario where Free pages are less than 640 and buffer cache hit ratio is between 90%-95% we can ignore Free pages counter values where as Buffer cache hit ratio is perfect.

(5) Page LookUps/Sec: (Page Lookup/Sec)/(BatchRequest/Sec):Number of requests to find a page in the buffer pool. When the ratio of page lookups to batch requests is much greater than 100, this is an indication that while query plans are looking up data in the buffer pool, these plans are inefficient. Identify queries with the highest amount of logical I/O's and tune.Expected behaviour is It should be less than 100 otherwise there is a problem.

(6)Page reads/sec:Number of physical database page reads issued. 80 – 90 per second is normal, anything that is above indicates indexing or memory constraint.Expected behaviour is it should be <90 a="" indexing="" is="" memory="" or="" otherwise="" problem.="" span="" there="">

(7)Page writes/sec: Page writes/Sec should be <90 .="" span="">Number of physical database page writes issued. 80 – 90 per second is normal, anything more we need to check the lazy writer/sec and checkpoint counters, if these counters are also relatively high then, it’s memory constraint.

I am also adding some other counters which we need to check in the time of performance problem occurs. And the below ones I have extracted from sql book SQL Server 2008 internals Wrox publication. Page No:314

LogicalDisk and PhysicalDisk:

For example, if we note that CPU is higher than usual, or disk reads are slow, you can focus on high CPU queries or queries with expensive disk access

➤ Avg. Disk Bytes/{Read, Write, or Transfer}
➤ Disk Bytes/sec
➤ Disk Transfers/sec
➤ Avg. Disk sec/Read
➤ Avg. Disk sec/Write

Memory:
Before checking in Perf monitor counters please check the status description in the below DMV.

use master
go
select system_memory_state_desc,*
from sys.dm_os_sys_memory

To monitor for a low-memory condition, use the following three MAIN object counters:

• Memory: Available Bytes
• Memory: Pages/sec
• Memory: Page Faults/sec 

Memory: Available Bytes:

The Available Bytes counter indicates how many bytes of memory are currently available for use by processes

Memory: Pages/sec:

The Pages/sec counter indicates the number of pages that either were retrieved from disk due to hard page faults or written to disk to free space in the working set due to page faults.

Monitor the Memory: Page Faults/sec counter to make sure that the disk activity is not caused by paging.

➤ Available Mbytes
➤ Free System Page Table Entries

SQL Server Buffer Node:
➤ Page Life Expectancy
➤ Target Pages
➤ Total Pages

SQL Server Databases:
➤ Active Transactions
➤ Log File(s) Size (KB)
➤ Log File(s) Used Size (KB)
➤ Percent Log Used
➤ Transactions/sec

SQL Server General Statistics:
➤ Processes Blocked
➤ User Connections

SQL Server Latches:
➤ Average Latch Wait Time (ms)
➤ Latch Waits/sec

SQL Server Locks:
➤ Average Wait Time (ms)
➤ Lock Requests/sec
➤ Lock Timeouts/sec
➤ Lock Waits/sec
➤ Number of Deadlocks/sec

SQL Server Memory Manager:
➤ Optimizer Memory (KB)
➤ SQL Cache Memory (KB)
➤ Target Server Memory (KB)
➤ Total Server Memory (KB)

SQL Server SQL Statistics:
➤ All counters

SQL Server Transactions:
. Free Space in tempdb (KB)
. Longest Transaction Running Time
. The other counters if you are using any type of snapshots, including triggers

SQL Server Wait Statistics:
➤ All counters

 Process (Choose at least your SQL Server instance(s) as well as any other applications/processes
that might compete with SQL Server.):
➤ % Processor Time
➤ Private Bytes
➤ Virtual Bytes
➤ Working Set
Processor (“” and Total):
➤ % Processor Time

Performance Counter	Counter Object	Threshold	Notes
% Processor Time	Processor	> 80%	Potential causes include memory pressure, low query plan reuse, non-optimized queries.
Context Switches/sec	System	> 5000 x processors	Potential causes include other applications on the server, more than one instance of SQL Server running on the same server, hyper-threading turned on.
Processor Queue Length	System	> 5 x processors	Potential causes include other applications on the server, high compilations or recompilations, more than one instance of SQL Server running on the same server.
Compilations/sec	SQLServer:SQL Statistics	Trend	Compare to Batch Requests/sec.
Re-Compilations/sec	SQLServer:SQL Statistics	Trend	Compare to Batch Requests/sec.
Batch Request/sec	SQLServer:SQL Statistics	Trend	Compare with the Compilation and Re-Compilations per second.
Page Life Expectancy	SQLServer:Buffer Manager	< 300	Potential for memory pressure.
Lazy Writes/sec	SQLServer:Buffer Manager	Trend	Potential for large data cache flushes or memory pressure.
Checkpoints/sec	SQLServer:Buffer Manager	Trend	Evaluate checkpoints against PLE and Lazy Writes/sec.
Cache Hit Ratio: SQL Plans	SQLServer:Plan Cache	< 70%	Indicates low plan reuse.
Buffer Cache Hit Ratio	SQLServer:Buffer Manager	< 97%	Potential for memory pressure.