SQL Server Performance Bottlenecks

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

Share This Story

Share on facebook
Share on twitter
Share on linkedin
Share on whatsapp

Share Your Comments

About The Author

Search Articles

Categories

Follow Fard Solutions

Share Your Comments

Fard Solutions Sdn. Bhd. Company was established in 2012 in Kuala Lumpur. We are engaged in development, implementation, integration and maintenance of the most advanced database solutions and IT research & development (innovation).

(+60) 03 8994 6788

info@fard-solutions.com

Fard Technology Corp.
700-1199 West Hastings Street,
Vancouver, British Colombia,
V6E 3T5 Canada.

Fard Solutions Sdn. Bhd.
1-1C & 1-2C Incubator 1,
Technology Park Malaysia,
Bukit Jalil, 57000
Kuala Lumpur, Malaysia.

  • Copyrights © 2020 Fard Solutions Sdn. Bhd. , All rights reserved.
  • Privacy Policy
  • Terms of Service
  • A Project By Website Artisan