5 Typical SQL Server Problems and How to Get Rid of Them

Many IT administrators struggle repeatedly with similar problems when dealing with SQL Server . It’s time to put an end to this predicament! Frank Sander uses clear case examples to analyze the five most frequent performance problems and to demonstrate adequate solutions.

A blog article by Frank Sander, SQL Server Consultant at COMPAREX

Frank Sander, SQL Server Consultant at COMPAREX

Frank Sander

It’s only human to make mistakes, and more often than not they’re caused by a lack of knowledge. So anything I say is not intended to pillory database administrators (DBAs). Quite the contrary, I’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 onboarding 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.

Problem 1: “My application keeps getting slower.”

Analysis

I believe that when hearing this kind of statement, the first step is to check the clustered and nonclustered 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.

Script to analyse and control the speed of your application

Columnstore indexes are not yet relevant in many cases, and their fragmentation can be calculated in the Dynamic Management View (DMV):

Dynamic Management View

e.g. based on deleted entries and the total number of entries.

Solution

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. I advise my 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.

Summary

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.

Problem 2: “My application keeps getting slower.” (for other reasons)

Analysis

Of course I have used the same script I 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 I 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

Script to see the current jobs

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.

Solution

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.

Summary

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.

Problem 3: “Generating my reports (SSRS) takes forever”

Analysis

A quick rummage through the environment using the Performance Monitor (matching counters in brackets) revealed:

  • 8GB RAM installed
  • approx. 4GB RAM available (Memory\Available Mbytes)
  • CPU load negligible (Processor\%ProcessorTime)
  • SQL Server and Reporting Services installed.

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.

Solution

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

  • more RAM
  • increase in RAM for SQL Server only during the monthly import
  • import optimization

Summary

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, I should also point out that performance counters that can be monitored by the Windows Server Performance Monitor are also created when installing SQL Server.

Problem 4: “Data generation in my application takes a week.”

Analysis

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.

Solution

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 .

Summary

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. I always recommend considering this factor when planning a migration as well.

Problem 5: SQL Server crashes

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).

Analysis

Memory ballooning meant that the storage assigned to the virtual machine (VM) was reduced by two thirds.

Solution

The VM was transferred to a different host, as the original host had insufficient memory.

Summary

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.


Conclusion

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.).

More questions regarding the SQL Server?

Need more details about the latest version of the SQL Server Microsoft is providing?

Read all details about Microsoft SQL Server 2016

Contact us to find out what SQL Server 2016 can and cannot do, when migration is worthwhile, and how licensing works.

 Get in touch with us

Leipzig, 19.06.2017

Stay Up-to-date

For regular updates and articles from COMPAREX, click below to follow us:

 Follow us on LinkedIn

 Follow us on Twitter

Related articles

How can Additional License Fees be Avoided for Windows Server 2016?

Microsoft is all set to launch the new versions of Windows Server in the second half of 2016. Besides necessary product modifications and new cloud-inspired features, there will be a number of changes to the license rights. Read the full article ...

Operating virtual systems in the cloud?

Virtual machines on the cloud platform Microsoft Azure. MVP Eric Berg takes a closer look at what it’s all about and stumbles across a couple of terms and concepts. Read the full article ...

Archive

Get an overview of all published blog articles of the past months.

 Read more

Share this Article

Leave a Comment

Do you have a question or remark on this article you want to share with us?
 Post it here.