SQL Server Performance Bottleneck analysis

SQL Server Performance Bottleneck analysis

First, memory bottleneck analysis--sqlserver:buffer Manager

SELECT TOP 312 * from Sys.dm_os_performance_counters
where object_name = ' Sqlserver:buffer Manager ' ORDER by Counter_name


1.Buffer Cache Hit Ratio average >=90%

Buffers are buffer page pools that create data pages, which is typically the largest portion of the SQL Server memory pool. This counter should be as high as possible.

A low value indicates that only a few requests are in the buffer, and the other request is the disk fetch service

This can happen if SQL Server is still in the ready state, or the buffer grows more than the maximum available memory, so if this value is low, you should consider increasing the memory for the system.

2.Checkpoint pages/sec Average <30

Nice one. The number of pages that are moved to disk by a checkpoint operation should be fairly low, and a high value means that more pages in the buffer are marked as dirty pages.

A dirty page is a page that has been modified in the buffer. Once modified, it is marked dirty and is written back to disk at the next checkpoint.

If this counter is high, it indicates that the system has a large number of write operations, which may be a symptom of I/O problems.

3.Lazy writes/sec Average <20

Records the number of buffers written by the buffer Manager's lazy write process per second. This process removes the long-time dirty pages from the buffer and frees the memory

High values indicate an I/O problem or memory problem.

4.Page life expectancy Average >300

The average life of the page, indicating how long an unreferenced page stays in the buffer pool. Low value means that the page is moved from the buffer

Reduces the efficiency of the buffer and indicates the possibility of memory pressure.

SELECT TOP 312 * from Sys.dm_os_performance_counters
where object_name = ' sqlserver:memory Manager ' ORDER by Counter_name

1.Memory Grants Pending Average =0

High value indicates memory shortage and needs to suspend progress waiting for memory to be idle

2. Target Server Memory (KB) close to physical memory size

Represents the total amount of dynamic memory that SQL Server wants to consume.

3.Total Server Memory (KB) close to target server memory
The total memory currently assigned to the Sqlerver. If the target Server Memory is below

Then it may be that SQL Server memory requirements are low and the maximum memory settings for the servers are too low

Second, disk bottleneck analysis physical

%disk time percentage of disk times average <85%
Currentdiskqueuelength Current Disk Queue Length average <2
Disk transfers/sec average number of disks transferred per second <100
Disk bytes/sec Average Transfer bytes per second < 10MB per second (this value needs to be evaluated by disk performance test, different disk metrics are different)
Avg.Disk Sec/read (write) Average time required to read and write per disk <10 milliseconds

Third, CPU processor

%process Time percent average <80%

%privileged time percent of privilege hours average <10%

Windows Server includes user mode and privileged mode two%privileged time is 20%-25% indicates that the system is doing a lot of IO operations, a dedicated SQL Server system%privileged time cannot exceed 10%

Sqlserver:sql Statistics Batch requests/sec number of SQL command batches received per second according to machine configuration General 1000 is busy, more than 1000 should pay attention to

SQL Compilations/sec SQL compilation Count <100

SQL Reompilations/sec SQL re-compilation times

Four networks

Network Interface bytes total/sec Total Transmit bytes per second average value <nic 50% of capacity

Networksegment%net Utilization Network utilization average <80%

SQL Server Performance Bottleneck analysis