Share via

SQL Server Troubleshooting: Top 5 Performance Issues and Fixes

  • Article

Summary

An SQL administrator may come across various performance issues such as poor queries, slow processing, insufficient memory, inadequate indexing, etc. caused due to incorrect hardware configurations in the Disk I/O subsystem or excessive CPU utilization. This post highlights the performance issues in MS SQL Server, their causes and solutions.

Proper configuration of the hardware and SQL Server database engine is essential for smooth performance of the overall system. But various Disk I/O issues occur from time to time due to sizing capacity, incorrect workload isolation, incorrect partition alignment, and incorrect bandwidth using SAN configuration. 

Let’s learn more about these issues and their fixes: 

1. Sizing Capacity: 

Cause: Improper sizing of disk subsystem for an SQL Server results in performance issues on the server. The key factor used in provisioning of an SQL hardware is its storage. For example, an 800 GB database can be stored on a 1 TB drive, but the problem is that a single drive of 1 TB will not be sufficient to supply the I/O capacity. This will cause performance issues at the time of concurrent user access. 

Solution: The best solution to ensure a high performing disk configuration is to install multiple physical disks in a RAID configuration. A multiple-disk RAID configuration fulfills the required I/O capacity and server-storage. 

Another solution is SAN implementation. SAN provides an adequate number of disks necessary to handle I/O performance requirements. 

Note: Small and medium sized organizations may not implement SAN as a dedicated storage for SQL Server. A SAN may act as storage device for other servers as well, thus, sharing the I/O performance requirement of other servers. In such a situation, SAN may not be the best resolution, as it doesn’t fulfill the sizing capacity and may impact the Server performance.

2. Incorrect Workload Isolation: 

Cause: For an SQL Server storage planning, it is mandatory to decide the workload isolation. The I/O characteristic of Transaction Log file is different from a data file. Hence, to maintain the overall system performance, it is necessary to segregate the I/O for the transaction and data files, as these may result in incorrect workload isolation, in due course of time. 

Solution: With high-write workloads, it is recommended to write transaction log files on separate physical disks. 

In case of SAN-based storage implementation, no dedicated spindles are configured for SQL Server. As discussed earlier, if SAN is used by the entire organization to access all types of applications, as in the case of mid-size business, then it is not an appropriate solution. However, if SAN is implemented, it is good to use as a monitoring tool for configuration. 

3. Incorrect bandwidth using SAN configurations

Cause: Whether the organizations are using Fiber Channel, iSCSI, or traditional Ethernet for network connectivity, bandwidth remains a critical factor for storage performance, if SAN is attached through network connection. 

In a multi-path environment, it is necessary to monitor the impact that a path to the SAN has on the storage performance. Ideally, 1 GB/sec bandwidth connection to a SAN provides a throughput of 90-95 MB/sec, provided the underlying storage is also configured to provide such throughput. Therefore, theoretically, if a SAN uses two connections of 1GB/sec in a multi-path environment, it must provide 180–190 MB/sec of throughput, but results do not match the expectations, practically. 

There may be multiple reasons which lead to incorrect bandwidth – the number of disks underlying the RAID cannot support the throughput, SAN caching, multipath configuration is not aligned properly and may be working as a single path configuration to the SAN for I/O. 

Solution: Tracking the exact cause of incorrect bandwidth and troubleshooting the issue helps in fixing the bandwidth issue and enhances the SQL database performance. 

4. Inappropriate parallelism 

Cause: In query parallelism or parallel processing, a SQL query execution engine splits the work of a query into multiple threads and each thread is executed on a separate scheduler. These queries are parallelized at the operator level, making the queries run in a parallel form. 

But, sometimes, all query operators may not help in running the queries in parallel form, thus affecting the SQL Server performance. 

Such inappropriate parallelism can be diagnosed by investigating the wait statistics and the latch statistics for an SQL Server instance. If there is an issue during parallel execution of a query, the CXPACKET wait type is displayed as one of the top waits for the given SQL Server. 

Solution: Avoid inappropriate parallelism by reducing the maximum degree of parallelism to one-half or one-fourth the number of logical processors or processor cores on SQL Server. 

5. High CPU Utilization

Cause: A CPU is involved in all activities of SQL Server – starting from running database queries to moving data from in and out of memory. This means high CPU utilization in SQL Server may result in MS SQL Server performance issues. 

It is easy to determine SQL Server issues that are caused due to high CPU utilization with the help of tools including monitoring tools for SQL Server database and CPU utilization, and other SQL Server Dynamic Management Views.

Solution: Once an administrator has confirmed that high CPU usage is caused due to SQL Server processes and a particular set of queries, then increase the CPU capacity.  

Increase the CPU capacity by introducing some design changes such as:

  1. Add appropriate indexes
  2. Replace ad-hoc SQL with Stored Procedures. 
  3. Tweak SQL Server and Windows configuration settings

When an MS SQL Server comes into production, an administrator should examine the Server properties for the first time and establish a general picture regarding the health of the database Server. This picture should focus on all the parameters, taking into account even smallest details. In fact, noting down all the application and database characteristics can help in validating the information in future, especially when an SQL server encounters performance issues.

Conclusion

MS SQL Performance is affected by Disk I/O issues, excessive CPU utilization, insufficient memory, inappropriate queries, incorrect configuration option settings, or inadequate indexing. In the above paragraphs, we discussed about SQL performance issues and their solutions.  

The best strategy is to ensure that there is proper planning and testing. Consider the following factors while configuring MS SQL database:

  • Ensure that there is proper storage capacity when provisioning the disk subsystem
  • Consider I/O performance and throughput 
  • Ensure database files are separated as per the type of I/O workload. Make provision for storing all data files, tempdb files and log files on separate disks. Also, these files should be configured properly for the given workload type. 
  • Verify the performance of database disk subsystem by using realistic loads. Testing the performance ensures that the system meets I/O performance and throughput requirements.

If MS SQL Server still encounters performance issues, then it may be due to corrupt SQL database. Check for the database error and resolve the error by using a proper recovery approach.