A slow SQL Server is one of those problems that tends to creep up on you. Everything works fine for months, sometimes years, and then suddenly queries that used to take a second are taking thirty. Users complain, timeouts appear, and the database becomes the prime suspect.
The good news is that most performance problems come down to a small number of common causes. Below are seven of the issues we see most often, along with practical ways to identify and address them.
1. Missing or outdated indexes
Without the right indexes, SQL Server has no choice but to scan entire tables to find the rows it needs. On small tables this might be barely noticeable, but on large tables it can quickly become painfully slow.
Well designed indexes allow SQL Server to locate data efficiently, often reducing query execution times from seconds to milliseconds.
How to fix it
The Missing Index DMVs can be a useful starting point, highlighting areas where SQL Server believes an index could improve performance. However, these recommendations are generated in isolation and don’t consider the wider workload, existing indexes, or how queries are actually used by the application.
Effective indexing means designing indexes around the most important and frequently executed queries. Tools like the Database Engine Tuning Advisor can analyse a representative workload and suggest indexing strategies, but their output should always be reviewed and refined rather than applied blindly.
2. Poorly written queries
A query can return the correct results while still placing far more load on SQL Server than necessary. Common examples include selecting more columns than are required, writing predicates that prevent index usage (often called non sargable conditions), and overusing table variables where SQL Server has very limited insight into the data they contain.
Individually, these issues may seem minor. In a busy system, they add up quickly, driving higher CPU usage, memory pressure, and longer execution times across the entire instance.
How to fix it
Execution plans are the most effective diagnostic tool. They show exactly where time and resources are being spent and often make inefficiencies immediately obvious. Warning signs include table scans where you would expect index seeks, expensive sort operations, and repeated key lookups.
In many cases, small and targeted changes to a query can unlock significant performance improvements. If query tuning isn’t something your team does regularly, bringing in specialist help can save a great deal of time and frustration while delivering measurable results much sooner.
3. Insufficient memory (RAM)
SQL Server performs best when it has enough memory to cache data pages, execution plans, and internal structures. This allows it to avoid repeatedly reading from disk and keeps query performance consistent.
When memory becomes constrained, SQL Server is forced to evict frequently used pages from cache and rely more heavily on disk I/O. When this happens, performance across the system can degrade very quickly.
How to fix it
Wait statistics provide a high level view of what SQL Server is spending time waiting on. Memory pressure often appears as PAGEIOLATCH_* waits, indicating that SQL Server is waiting for data pages to be read from disk rather than memory.
Page Life Expectancy (PLE) is another useful indicator. PLE measures how long pages stay in the buffer pool. If it is consistently low and does not quickly recover, it usually indicates sustained memory pressure.
Finally, review SQL Server’s maximum memory configuration. By default, SQL Server will attempt to consume almost all available memory, which can starve the operating system. Setting a sensible maximum ensures SQL Server performs well while still leaving headroom for the OS and other services.
4. TempDB contention
TempDB is SQL Server’s shared scratch space, used for temporary tables, sorting, and hashing operations, row versioning, and many internal processes. Every database on the instance relies on the same TempDB, which means it can easily become a bottleneck under concurrent load.
When TempDB is under pressure, performance issues often affect the entire system rather than a single query, making the root cause harder to identify. This is especially common on older installations where TempDB is configured with a single data file on shared storage.
How to fix it
A well configured TempDB can remove a major bottleneck. Start by configuring multiple data files, typically one per logical CPU core up to a maximum of eight. All files should be the same size and grow by the same increment to prevent uneven usage.
Storage also matters. Where possible, place TempDB on fast, dedicated storage separate from user databases and transaction logs so it isn’t competing for I/O.
5. Blocking and deadlocks
Blocking occurs when one query holds a lock that another query needs, forcing the second query to wait. When this happens frequently or for long periods, the system can feel slow and unresponsive.
Deadlocks are a more severe form of the same problem. They occur when two sessions each hold locks that the other needs, creating a circular dependency. SQL Server resolves this by terminating one of the queries, which usually appears to users as an error.
Blocking and deadlocks are usually symptoms rather than root causes. They often point to long running transactions, inefficient queries, or application patterns that hold locks longer than necessary.
How to fix it
Start by identifying where blocking is occurring and which queries are involved. Tools such as Adam Machanic’s sp_whoisactive, Michael J Swart’s Blocked Process Report, and Brent Ozar’s sp_BlitzLock make it much easier to identify which sessions are causing the most impact.
The most effective fix is to reduce how long locks are held. Keep transactions short, avoid user interaction inside transactions, and optimise queries so they complete quickly. Enabling Read Committed Snapshot Isolation (RCSI) can also help by allowing readers to access versioned data without being blocked by writers.
6. Outdated statistics
Statistics describe how data is distributed within tables and indexes, allowing the query optimiser to estimate how many rows a query will return and choose an efficient execution plan.
When statistics are out of date, those estimates become inaccurate and the optimiser may make poor decisions, leading to inefficient plans and slower queries.
How to fix it
Ensure auto create statistics and auto update statistics are enabled on all databases. These defaults provide a solid baseline for most workloads.
While modern versions of SQL Server use more adaptive thresholds for automatic updates, they are still reactive. On large or business critical tables, it is often beneficial to take a more proactive approach by scheduling regular statistics updates as part of routine maintenance. This can be done directly with UPDATE STATISTICS or by using tools such as Ola Hallengren’s maintenance scripts.
7. Hardware and storage bottlenecks
Sometimes SQL Server is doing exactly what it should, and the limiting factor is the hardware it’s running on.
Storage is the most common culprit. Slow disks or high latency shared storage cause SQL Server to wait for reads and writes to complete, which directly impacts query response times. CPU bottlenecks are less common but do occur, particularly with complex queries, high concurrency, or aggressive parallelism.
How to fix it
Start by measuring I/O latency. Windows PerfMon counters such as Avg. Disk sec/Read and Avg. Disk sec/Write provide a useful first indicator. Within SQL Server, sys.dm_io_virtual_file_stats can show which files are experiencing the most I/O stall time.
As a rough guide, consistently seeing I/O latency above 10–15ms under normal load suggests storage may be a bottleneck. Modern SSD or NVMe storage can make a dramatic difference, particularly for read heavy workloads and TempDB activity.
If CPU is the constraint, review query patterns and parallelism settings. Tuning Max Degree of Parallelism (MAXDOP) and Cost Threshold for Parallelism based on your workload can significantly improve overall concurrency.
When to bring in expert help
If your SQL Server has been running well for years and has suddenly started to struggle, one or more of these issues is almost certainly behind it.
If your business relies on software that runs on SQL Server and performance is holding you back, it’s worth getting the right people involved sooner rather than later.
At Koderly, we’ve spent over 25 years building and optimising software that runs on SQL Server. If your system is slowing down and you need help diagnosing the problem, get in touch and we’ll take a look.
