Performance Issues: Analysing Slow SQL Servers and Queries

Queries or updates take longer than expected to perform for a variety of reasons. Most slow-running queries may be due to performance problems related to the network or the machine where the SQL server is running. Issues with the physical database design or code problem can also cause them.

Analyse why the server and query are running slow to determine your SQL troubleshooting needs.

The Query

When analysing a slow query, it is important to examine the execution plan to pinpoint the cause of the problem. You can do this by using an SQL server management studio, which displays the actual graphical execution plan or the estimated graphical execution plan. Other methods to use include SQL Server Profiler event classes and Transact-SQL SET statement options.

The Database

A poorly designed database scheme is one of main causes of slow database performance. This can be experienced when several I/O requests occur while the queries are being performed. You can see or check your SQL Server Profiler to capture queries that have the highest I/O load.

The I/O Statistics

Another way to measure SQL server performance is to examine I/O statistics through dynamic management functions and dynamic management views. This will enable you to obtain the statistics about a system’s condition. The information you can gather will help you monitor your system, improve its performance, and solve any issues that may arise.

The Buffer Pool

This is the location of stored database pages read or written by the SQL server. Buffer pools that are too small can also be the cause of performance problems, as it will put pressure on the disk subsystem. You can use performance monitor counters to check and manage your buffer pool. A small buffer pool will have higher server memory requirements.

Slow SQL server performance can have serious repercussions on business. It may take hours instead of minutes for reports to complete and users may be end up having to waste several minutes just waiting for the screens to load. Make sure to address this problem to avoid further business delays and save more money.