Top 5 Most Common SQL Server Performance Problems

Top 5 Most Common SQL Server Performance Problems

The following article will introduce you to the five most common SQL Server performance issues.

The most effective database administrators don’t make rash decisions based on what seems apparent on the surface. They look more deeply at problems to better understand the root cause before taking action.

This is an incredibly important approach for optimizing your SQL Server. Here are the top five SQL Server performance problems I see and why it’s important never to make hasty assumptions and apply a critical lens to everything.

________________________________________________________________________

You might also enjoy:  Learn How to Use SQL Server With Node.js

________________________________________________________________________

1. Tempdb PAGELATCH Contention

This increasingly prevalent problem is usually due to a system making heavy use of tempdb for some type of extract, transform, and load (ETL) process. This is especially common if it is an ongoing "real-time" style ETL process.

The symptoms can vary, but some things are always the same: high PAGELATCH waits in tempdb and poor performance recorded for processes using tempdb. I'll typically follow the waits to Top SQL in Performance Advisor, and see lots of queries that use temp tables listed in Top SQL. These queries usually run in milliseconds, and should never be counted among the "Top SQL" for the server. This can have people feeling like these queries are a large part of the problem, but that is not necessarily the case at all. The queries are the victims of the real problem.

Once I suspect this to be the case, I will usually jump to the Disk Activity tab in Performance Advisor to see how tempdb is configured. Most times I actually see the same thing: A busy tempdb with a single data file defined. From here I'll usually recommend reconfiguring tempdb.

2. Expecting Auto Update Statistics to Keep Statistics Updated

The problem here is that the thresholds for triggering auto statistics updates end up being the same in most cases, even for a very large table. The threshold is about 20% of the rows in the table. On a really big table, it takes a lot of data change to trigger an update.

The reason this makes the list is that database administrators seem really surprised to find out that the auto-update isn't taking care of things the way the name implies. Then there are also many DBAs that believe it should be handled by their maintenance job. Then after looking at the maintenance, they are doing index reorgs most of the time, and that won't update the statistics either (though a rebuild will).

The lesson is to keep an eye on statistics and make sure they're updated regularly, especially on large tables, which are becoming more and more common. Another option is to use trace flag 2371 to actually change the formula used to trigger the update.

3. The CXPACKET Wait Type

This is hands down the single most common wait type I see on larger SQL Server systems when someone asks me to look into query performance with them.

Sadly, I still see a lot of people make the initial assumption that the problem should be solved by having either the query or the entire SQL Server set max degree of parallelism (MAXDOP) to 1. Often, the problem can be handled by proper indexing or statistics maintenance. It could also be that the plan cached for this query is not optimal, and you can mark it for recompile using sp_recompile, set recompile at the query level, or just evict the plan using DBCC FREEPROCCACHE with a plan handle. It is best to exhaust these options before deciding to change MAXDOP to 1 because you could be throwing away a lot of processing power without realizing it.

4. Misunderstood "Timeout Expired Prior to the Completion of…"

This one is huge. Outside of some very edge case behavior, there are two basic types of timeouts you might deal with for SQL Server. These are connection timeouts and operation (or query) timeouts. In both cases, these are values set by the client connecting to the SQL Server. On the server side, there is a remote query timeout setting, but this is the very edge case.

Operation timeouts are the most common and might be the most misunderstood situation I come across. The cause boils down to one simple factor: The client executing the command has set a maximum amount of time that it will wait for the command to complete. If this maximum is reached prior to completion the command is aborted. An error is raised from the client.

Often, the timeout error will induce a panic mode, because the error can look intimidating. The reality is that this is not much different than hitting the stop button in SQL Server Management Studio because the query was taking too long. It will show up exactly the same in a profiler trace with Error = 2 (Aborted).

A timeout like this tells us that queries are taking longer than expected. We should go into "performance tuning" mode rather than "something is broken" mode. The error information from the client is good information on where you might start to focus your tuning efforts.

This is true for any system that uses an RDBMS for a repository. Your database needs some TLC now and again. Without it you may indeed experience some timeouts from your client. We spend a lot of time tuning our queries for performance before they ever make it out the door, but proper maintenance will ensure they continue to run as intended.

5. Memory Pressure

This is the big one because I see it so often, and also because it is so often mistaken for poor disk performance.

There are lots of caches in SQL Server, but the most well-known is the data cache (aka buffer pool). The easiest way to describe the data cache is that it is the data stored in memory, rather than persisted to disk. It’s desirable to store lots of data in memory long term because working with data in memory is generally much quicker than having to perform physical I/Os.

Usually, memory pressure manifests as a few different symptoms. When viewed individually, some of these symptoms can lead you to incorrect, and sometimes costly, conclusions.

The two misleading symptoms are that you may start to see higher than normal latency across the disk subsystem, and you may start to see abnormally high waits related to disk activity. If you only look at these two symptoms, you may conclude that you need to work on your disk system.

This is why being presented with all relevant metrics on one dashboard is so important. You have to look at the bigger picture, and having the memory-related data available along with the disk activity and waits helps to paint a clearer picture of what is really going on.

Typically, I’ll see a PLE (Page Lifetime Expectancy) that is fairly low for this server. The larger your buffer cache is, the higher your "critical" threshold will be for PLE. The more data there is to churn in and out of the buffer, the worse off you will be when the "churn" happens. Another consideration is non-uniform memory access (NUMA). The way the PLE counter is calculated can cause this value to be very misleading when multiple NUMA nodes are involved.

I'll usually also see consistently higher lazy writer activity, and SQL Server page faults (SQL Server going to disk). Sometimes I'll see what I call buffer tearing. This occurs when the data buffer is up and down, frequently creating a jagged (or torn) edge on the history chart in Performance Advisor. I may also see an abnormally large plan cache reducing available memory for the data cache.

All of these factors together spell memory pressure. There are various ways to deal with them, but the important point is that this is not a disk issue. I wouldn't call up your storage area network contact and order new hardware based on this situation. Once you get the memory pressure situation under control, SQL Server will not need to go to disk as much, and the few symptoms related to disk may disappear entirely!

Conclusion

The top SQL Server performance issues/topics will continue to evolve as organizations face new database requirements. If there is one major takeaway from all of these potential problems, it’s to always consider the full picture of performance because looking at one factor out of context could severely limit your options for a solution.

Further Reading

ASP Net Core, SQL Server, and Angular 7: Web App Authentication

Building Web App using ASP.NET Web API Angular 7 and SQL Server

Originally published by Jason Hall at  dzone.com

=======================================================

Thanks for reading If you liked this post, share it with all of your programming buddies!

Follow me on Facebook | Twitter


sql-server sql web-development

Bootstrap 5 Complete Course with Examples

Bootstrap 5 Tutorial - Bootstrap 5 Crash Course for Beginners

Nest.JS Tutorial for Beginners

Hello Vue 3: A First Look at Vue 3 and the Composition API

Building a simple Applications with Vue 3

Deno Crash Course: Explore Deno and Create a full REST API with Deno

How to Build a Real-time Chat App with Deno and WebSockets

Convert HTML to Markdown Online

HTML entity encoder decoder Online

Introduction to Structured Query Language SQL pdf

SQL stands for Structured Query Language. SQL is a scripting language expected to store, control, and inquiry information put away in social databases. The main manifestation of SQL showed up in 1974, when a gathering in IBM built up the principal model of a social database. The primary business social database was discharged by Relational Software later turning out to be Oracle.

SCHEMAS in SQL Server -MS SQL Server – Zero to Hero Query Master

This is part 3 of “MS SQL Server- Zero to Hero” and in this article, we will be discussing about the SCHEMAS in SQL SERVER. Before getting into this article, please consider to visit previous articles in this series from below.

Hire Web Developer

Looking for an attractive & user-friendly web developer? HourlyDeveloper.io, a leading web, and mobile app development company, offers web developers for hire through flexible engagement models. You can **[Hire Web...

Introduction to Structured Query Language SQL pdf

SQL stands for Structured Query Language. SQL is a scripting language expected to store, control, and inquiry information put away in social databases. The main manifestation of SQL showed up in 1974, when a gathering in IBM built up the principal model of a social database. The primary business social database was discharged by Relational Software later turning out to be Oracle.

Welcome Back the T-SQL Debugger with SQL Complete – SQL Debugger

Debug SQL stored procedures and develop your SQL database project with dbForge SQL Complete, a new add-in for Visual Studio and SSMS. When you develop large chunks of T-SQL code with the help of the SQL Server Management Studio tool, it is essential to test the “Live” behavior of your code by making sure that each small piece of code works fine and being able to allocate any error message that may cause a failure within that code.