Performance of Applications Using Microsoft SQL Server

Sooner or later, every system administrator deals with database performance issues. This happens because developers, when creating new applications, often use small amounts of test data without dedicating much time to optimizing their performance. Once an application is launched in a production environment, the amount of data accumulated in the database increases over time, causing the application to run slower... to the point where it sometimes makes it impossible for users to work.

Fortunately, for applications using the Microsoft SQL Server database server, administrators have access to tools that can significantly improve the performance of such applications in many cases. This entire performance optimization process is called database tuning and consists of several steps.

The primary optimization is identifying the resource that is the bottleneck. Such a performance bottleneck can be disk access, the amount of RAM, CPU load but also an excessive number of users accessing the same data, resulting in mutual blocking. Diagnosis can be made by checking system counters (CPU usage percentage, free RAM, disk queue) as well as database counters (buffer and cache utilization, and lock-related counters).

Next, the application administrator should identify the queries that are the source of the problems. A key tool in this process is SQL Profiler as well as built-in database reports—particularly "Top Queries by Total CPU Time" and "Top Queries By Total IO." If disk and CPU utilization are low, but queries are timing out, it is also important to check "Object Execution Statistics," "Top Queries By Average CPU Time," and "Top Queries By Average IO."

A common problem that can reduce performance is missing indexes. These can be identified by analyzing the queries that are causing issues using the built-in "Database Engine Tuning Advisor" or by checking the appropriate dynamic views that collect information on missing indexes based on historical data (search for: "missing index script"). However, it is important to remember that having too many unnecessary indexes can also lead to performance problems, as they need to be maintained during insert, update, and delete operations. In some cases, these tools may not point to a solution, and manual query plan analysis may be required (for example, identifying a missing index on a functional column).

If, despite having indexes in place, the database chooses the wrong query plan, the issue might be outdated or completely missing statistics. The solution is to set the appropriate options on the database (auto create/update statistics), or establish a proper maintenance plan that includes recalculating statistics. It is also possible to force a specific query plan, but this is usually the last resort, and one should be very cautious when taking this approach.

In some cases, issues related to blocking can be addressed by setting the appropriate isolation level for transactions. The "snapshot" isolation level, which can be enabled in the database properties, can help in such situations. In some cases, it may be necessary to rewrite certain functions, procedures, or views in the database if the application is utilizing these objects.

In the case of business applications, a common problem is caused by reports that are generated during the normal operation of the application. A solution to this issue could be to move report generation to night hours, or to separate the reporting part from the part responsible for the ongoing operation of the application.

Finally, it is also worth mentioning index defragmentation, data partitioning, and the proper arrangement of structures on disk (for example, the transaction log file should be on a different disk than the database file, the tempdb database should be on a different disk than user databases, and indexes can be placed on a different disk than the table from which the data is retrieved). Of course, in this last case, we're referring to physical disks—assigning files to different partitions on the same disk or on a RAID5 array doesn't make much sense.

The above methods are important because they allow administrators to resolve performance issues with applications without modifying them. Proper database modeling or modifying the application code to enhance database performance are topics that warrant a separate discussion.