Many IT administrators struggle repeatedly with similar problems when dealing with SQL Server. It’s time to put an end to this predicament. In this article we use clear case examples to analyse the five most frequent performance problems and to demonstrate adequate solutions.
It’s only human to make mistakes, and more often than not they’re caused by a lack of knowledge. So anything said here is not intended to pillory database administrators (DBAs). Quite the contrary, we’d like to stick up for all IT administrators who, besides the databases themselves, are required to look after Windows servers, networks, storage, individual applications, virtualization tools and much more. In many cases their remit will be so extensive that they simply do not have time to get to grips sufficiently with each individual topic.
The issue of performance is a typical aspect of SQL servers that tends to slip through the cracks, whether it is during initial on-boarding or the one-time, well-conceived installation and configuration of an instance. And while the first two problems are identical in terms of what the customer says, their causes are completely different.
We believe that when hearing this kind of statement, the first step is to check the clustered and non-clustered indexes, or more precisely their fragmentation. The following script delivers the name of each index, the name and scheme of the table for which it was created, as well as the degree of fragmentation and the number of index pages. The restrictions within the WHERE clause mean that only indexes requiring maintenance are shown, i.e. indexes of a sufficient size, with sufficient fragmentation and of a type that needs maintenance. This script should be executed within a maintenance window, especially for large databases that are used frequently, as the value for average fragmentation is determined in runtime.
Columnstore indexes are not yet relevant in many cases, and their fragmentation can be calculated in the Dynamic Management View (DMV):
In cases in which indexes are shown at this point, it is necessary to review whether maintenance plans for their upkeep exist and, if they do, how often they are performed. This can reveal that a maintenance plan must be created or executed more frequently if possible. For index rebuild and index reorganize, SQL Server delivers a maintenance plan, which unfortunately is almost impossible to configure. In-house or otherwise available script solutions present another option. We advise our customers to use and adapt the solution by Ola Hallengren. Modifying the Standard Fill Factor for indexes can also provide a remedy in individual cases. But it is important to remember in this respect that this configuration option is universally applied to the entire instance and hence will be used for all databases.
Although index maintenance is part of the day-to-day business of a database administrator, in many cases it is not performed. This means that unmaintained indexes are among the principal causes for performance problems. All the same, a database administrator would be poorly advised to create database indexes without consulting the application vendor, as they can certainly create additional problems, for instance in bulk insert operations.
Of course we have used the same script we took for the first example in order to respond to this frequent statement. No indexes requiring maintenance were shown. So everything seemed hunky-dory and we reported to the customer that: “Your scripts are maintained.” But he just responded with an emphatic “No!”. A quick glance at the current jobs confirmed his doubts, as the query:
delivered the following results:
| object_id | name | index_id | type | type_desc | ... |
|---|---|---|---|---|---|
| 8 | NULL | 0 | 0 | HEAP | ... |
| 149575571 | NULL | 0 | 0 | HEAP |
... |
| 565577053 | NULL | 0 | 0 | HEAP | ... |
Even the best DBA will find his limits tested at this point. He will be unable to maintain any indexes if only heaps are defined.
The solution in this case was quite easy. The current application was replaced incrementally with its successor, whose database already contained the missing indexes. The DBA then agreed to ensure faster migration of the individual application parts. Contacting the application vendor to collaborate on a solution would definitely have been an alternative in this case as well.
A DBA cannot fix all problems and is also not responsible for their existence. But he should be able to indicate where they are located. It is also imperative that he does not use them merely as justification for ongoing problems, but also seeks to resolve the issue, for instance by collaborating with the vendor.
A quick rummage through the environment using the Performance Monitor (matching counters in brackets) revealed:
Nothing so far indicated performance problems. Only parallel installation of SQL Server reporting services is not recommended.
But a glance at the paging file (Paging File%Usage) did reveal a lurking issue. File usage was extremely high. This was caused by the 7GB limit on RAM assigned to SQL Server. It was needed for a monthly import process, reserved and therefore not released. It meant that only 1GB was available for the operating system and Reporting Services, and this was what caused the high load on the paging file when loading Reporting Services.
The maximum permitted RAM was reduced from 7GB to 5GB, resolving the issue in the short term. But SQL Server still needed 7GB once a month. This has to be dealt with downstream. Options include
It is important to remember when configuring the maximum permitted RAM for SQL Server that SQL Server will reserve the RAM, even when it is not currently in use. This is sensible otherwise, as it prevents repeat loading of data from storage when it is accessed. Moreover, we should also point out that performance counters that can be monitored by the Windows Server Performance Monitor are also created when installing SQL Server.
As expected, the relevant application takes 10GB of data and generates an additional 20GB. Standard performance counters (RAM, CPU) tend to show low load. But the size of the data file was originally 10GB and configured with auto-growth of 1MB. This meant the data was written, storage reserved and then overwritten with zeros in incremental steps of 1MB.
Auto-growth was increased to 2GB. In addition, the service account of the SQL Server was assigned the permission “Perform volume maintenance tasks”. The risks this involves can be checked in the Microsoft documentation.
In most cases it will be necessary to change the default settings for file size and growth to suit the requirements of the respective application when creating a database. Even if application vendors are already trying to come up with solutions, it is nevertheless necessary for the DBA to check the plausibility of configured values, as the data volume per user may differ. We always recommend considering this factor when planning a migration as well.
The customer’s virtual SQL Server ran very slowly or did not respond. It crashed on two occasions, showing a blue screen and the error message CLOCK_WATCHDOG_TIMEOUT (101).
Memory ballooning meant that the storage assigned to the virtual machine (VM) was reduced by two thirds.
The VM was transferred to a different host, as the original host had insufficient memory.
The use of SQL Server in virtualized environments is supported in principle. The vendors of virtualization applications have frequently published white papers on this issue. They need to be read on all accounts if SQL Server will be run in a virtualized environment.
The reasons for performance problems with SQL Server are not necessarily complex. But DBAs have to do their homework (index maintenance, correct configuration of the instance’s maximum RAM, correct configuration of the VM, etc.).
Our software experts optimise contracting and software spending with strategic software publishers.
Our software experts optimise contracting and software spending with strategic software publishers.
