When SQL Server is having performance issues, giving you error messages, or just running slowly, you need to figure out the root cause of the problem before making any changes. Don’t jump to conclusions—make sure you get a big-picture view of the issue. Start with these five troubleshooting tips to optimize your SQL Server performance.

Reconfigure Tempdb

If you have ongoing extract, transform, and load (ETL) processes, your system may be overwhelming tempdb. When you’re seeing high PAGELATCH waits in tempdb or poor performance for tempdb, check your Top SQL for queries using temp tables. These queries should only take milliseconds, so if they’re part of the Top SQL for the server, you should probably reconfigure tempdb to reduce the time they take.

Auto Update Statistics

The auto update statistics function should automatically update statistics, right? Thresholds for triggering auto-updates are usually standard across tables: when 20% of the rows have data changes, statistics will auto update. This works… until you have tables with thousands or hundreds of thousands of rows. Especially on larger tables, watch to make sure they’re updating when you expect them to or use trace flag 2371 to change the formula to trigger updates more frequently.

CXPACKET Waits

When looking into query performance on a larger SQL Server system, you’ll likely see many CXPACKET waits. Most people assume setting max degree of parallelism (MAXDOP) to 1 will fix this. And it can, but there are other options you should try first. Perhaps the plan cached for the query isn’t optimized—try marking it for recompile using sp_recompile, setting recompile at the query level, or evicting the plan using DBCC FREEPROCCACHE with a plan handle before resorting to setting MAXDOP to 1.

“Timeout Expired Prior to the Completion of…”

SQL Server will typically show you connection timeouts or operation (query) timeouts. These values are set by the client connecting to the SQL Server. An operation timeout occurs when a command takes too long to complete, and the client raises an error. Getting this timeout error indicates queries are taking longer than expected. Instead of looking for what’s broken, look for what queries you can optimize—starting with the error information from the client. Proper maintenance should get you back up to speed and reduce these timeout errors.

Memory Pressure

The symptoms of memory pressure can be misleading:

• Higher than normal latency across the disk subsystem
• Abnormally high waits associated with disk activity
• Low page lifetime expectancy (PLE) for the server
• Higher lazy writer activity
• Higher SQL Server page faults
• Abnormally large plan cache

When you only see one or two of these, you may think it’s due to poor disk performance. But when you have the big-picture view and see all these symptoms, you can conclude it’s more likely memory pressure. Once the memory pressure is under control, the disk symptoms might even go away. When troubleshooting SQL Server performance, remember to consider the full picture. Taking one factor out of context can lead you to troubleshoot in the wrong area. Maintaining your databases will also help you optimize them as your organization and its requirements change.