5 Typical Ways to Solve

SQL Server Problems

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.

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


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.

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

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


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.


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)


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:

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.

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


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

  • 8GB RAM installed
  • approx. 4GB RAM available (MemoryAvailable 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.


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


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


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


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

Comment on this article

Leave a comment to let us know what you think about this topic!

Leave a comment


Frank Sander, Author SoftwareONE Blog

Frank Sander

SQL Server Consultant

Computer scientist working in the field of SQL server consulting and databases

Related Articles

  • 31 October 2022
  • Marco Vogel
  • Cloud Services, Path to the Cloud, Publisher Advisory
  • VMware, Cloud, Migration

This is how cloud works with VMware!

Do you have to give up the VMware experience altogether when you move to the cloud, and what useful alternatives are there? Here is the comparison.

Understanding What the Oracle Microsoft Partnership Means to You
  • 18 October 2022
  • Blog Editorial Team
  • Publisher Advisory
  • Oracle, Microsoft, Azure, Cloud, Application Modernization

What the Oracle Microsoft Partnership Means to You

When two software behemoths make a joint announcement, it’s worth taking the time to understand what the implications may be for you. Gordon Davey and Richard Spithoven share their insights on the Oracle Database Services for Microsoft Azure.

VMware vSphere+: vSphere available as subscription | SoftwareONE Blog
  • 20 September 2022
  • Marco Vogel
  • Publisher Advisory, Digital Transformation, Future Datacenter
  • VMware

VMware vSphere+: vSphere available as subscription

There are several benefits of the vSphere+ offer - it’s worth taking a closer look.