SQL Server Performance "The Good The Bad Ugly". (Part 1 of 3)

SQL Performance: Mastering the Mystical Art

SQL performance can often feel like a mystical art; the more time you practice, the better you get.

The Common Complaint

“The SQL server is slow.” This is always the easy target to blame when users start to call the IT department. My first exposure to dealing with complaints of a slow SQL server was over 20 years ago. Back then, my responsibility was desktop support, not the SQL database and related applications.

My First Encounter

A third-party vendor responsible for the SQL server and the application suggested a shiny new HP server with an Itanium IA-64 processor. I knocked that on the head. The server came at a premium, and being HP server certified, I recommended an Intel server instead, specifically a top-of-the-range model HP recommended for SQL.

All new and shiny with hot-swap CPU and Memory Raid 10 with active/active connection to storage. This was a monster that could never be switched off. The application ran twice as fast, and the server was in production for seven years.

The Unexpected Disappointment

Everyone was happy apart from me. I expected performance to be at least five times faster. The third-party provider for the SQL application had badly written code and got all their customers to throw lots of money at server hardware as a fix.

A similar issue with another SQL application with inconsistent search results turned out to be a storage problem. I virtualized the server and ran it on a desktop PC—yes, a desktop PC. The result was the storage was slow and needed to be upgraded at a cost of £200,000.

The Basics of SQL Performance Tuning

Before you get to the ugly—the tuning of indexes and the optimization of T-SQL queries—you need to go for the low-hanging fruit, the good.

- Server Requirements: Is the server correct for the workload? Does it have fast storage, sufficient RAM, and a powerful CPU?

- Indexing and Maintenance: Are you using indexing and running maintenance plans?

Monitoring and Managing SQL Servers

Working in a large environment, I used SentryOne to monitor and manage hundreds of SQL servers. With a few clicks, I could locate and diagnose a bad query that ran at 3 AM last night. The quickest turnaround on a ticket was ten minutes: access the server via SentryOne, capture the bad T-SQL transaction, attach a full description of the issue to an email, and send this to the client for the developer to review the T-SQL.

Open Source Solutions

If you don't have the luxury of a monitoring solution like SentryOne, you can use open-source solutions. My two recommendations are Brent Ozar's First Responder Kit and Glenn Berry's SQL Performance Scripts. Both solutions will highlight low-hanging fruit problems and provide some guidance on those problems. Don't go old school and use built-in solutions unless you support one server and need to look busy.

Key Takeaways

1. Use One Script or Management Solution: Get a quick win on performance issues.

2. Fit for Purpose: Ensure the server is fit for purpose to handle the workload.

If you reach a point where you need assistance from a consultant who charges by the hour, ensure they aren't just resolving low-hanging fruit issues quickly and charging you. You want them to fix the ugly, the hard problems that can save you thousands in cloud costs.

By focusing on both the quick wins and the deeper, more complex issues, you can significantly improve SQL performance and avoid unnecessary expenses. Happy tuning! 🎻✨