SQL Server Performance Bottlenecks

The estimated reading time for this post is 2 minutes

SQL Server performance is all depends on the resource bottlenecks, if there is a bottleneck, and then the SQL Server performance will be affected. SQL Server bottleneck can be on memory, disk and processor. To analyze each of these bottlenecks you need to measure it based on some counters. The most critical bottleneck which can reduce SQL Server performance is Memory bottleneck.

Memory Bottleneck

memory bottleneck

When the SQL Server goes out of cache, the Lazywriter process swap in some part of the memory to the disk to provide enough cache space for SQL Server, which this process extremely increase the CPU cycles, Therefore the Disk I/O operations will be increased because of moving the pages from Memory to Disk and From Disk to Memory. Missing indexes can cause more I/O operation, which it can cause Memory and Processor bottleneck.

Insufficient memory can cause processor and disk I/O bottlenecks. You can analyze the memory bottleneck by performance monitor tool by selecting below performance counters:

mem perf 1

mem perf 2

mem perf 3

To resolve memory bottleneck, the following techniques should be applied:-

  1. Optimizing Application Workload
  2. Allocating more memory to SQL Server
  3. Increasing System Memory
  4. Changing from a 32-bit to a 64-bit processor
  5. Enabling 3GB of process space
  6. Using memory beyond 4GB within 32-bit SQL Server

Disk Bottleneck

SQL Server is heavy user of Disk resources, as disk is much slower than memory and processor, therefore contention in disk can significantly degrade the SQL Server performance, SQL Server performance can be boosted up immediately by resolving Disk bottleneck.

You can analyze the disk bottleneck by the following performance counters:-

disk perf 1

disk perf 2

To resolve the Disk bottleneck, you can apply below solutions: 

  1. Optimizing Application Workload 
  2. Using a Faster Disk Drive 
  3. Using a RAID array 
  4. Using a SAN System 
  5. Aligning Disk Properly 
  6. Using Battery-Backed Controller Cache 
  7. Adding System Memory 
  8. Creating Multiple Files and File groups 
  9. Placing the Table and the Index for that Table on Different Disks 
  10. Saving Log files on Different Physical Disk 
  11. Using Partitioned Tables

Key Note: Always optimize the database and software first instead of adding hardware to the server first.

Processor Bottleneck

Basically SQL Server is not heavily using Processor, so remember that Processor (CPU) bottleneck can be cause by heavy I/O Operations or Calculations in the database procedures. You can analyze processor performance by following performance counters:-

proc perf 1

 

The following techniques can improve the processor performance:- 

  1. Optimizing Application Workload 
  2. Eliminating or Reducing Excessive Compiles or Recompiles 
  3. Using more or Faster Processor 
  4. Using a Large L2/L3 Cache 
  5. Running with more efficient Controllers/Drivers 
  6. Not Running unnecessary Software

Network Bottleneck

SQL Server uses a lot of network operations, special when there are some heavy OLTP databases as well. Network bottleneck can cause User Request Timeout and it affects the overall performance as well. You can analyze the network performance by the following performance counters:-

network perf 1

 

Key Note: You must install the Network Monitor Driver to collect performance data using the Network Segment object counters.

Network bottleneck can be resolved by the following techniques: 

  1. Optimizing Application Workload 
  2. Adding Network Adapters 
  3. Moderating and Avoiding Interruptions

Author: Hamid Jabarpour Fard

Hamid J. Fard

I am SQL Server Data Platform Expert with more than 9 years’ of professional experience, I am currently Microsoft Certified Master: SQL Server 2008, Microsoft Certified Solutions Master: Charter-Data Platform, Microsoft Data Platform MVP and CIW Database Design Specialist. I also do Pig, Hive and Sqoop development regarding Hadoop Big Data platform. After a few years of being a production database administrator I jumped into the role of Data Platform Expert. Being a consultant allows me to work directly with customers to help solve questions regarding database issues for SQL Server.

More Posts

Follow Me:
FacebookLinkedIn


Leave a Comment

Be the First to Comment!

Notify of
avatar
1000
wpDiscuz