Improving SQL Server Instances is Hard

Most of the SQL Servers I see are a mess. A functional mess, sure…but still a mess.

The most common issues: Patching is way behind, Default settings are in place, and indexes are all over the place (or non-existent)

I’ve been doing this a long time and have developed a 3 step “outside in” approach. Each step is really a category of steps…

Outside In:

Server settings

• Windows:

  • Verify version and patch levels

  • Verify VM configuration if applicable

  • Block size of data and log drives

  • Layout of data, logs, tempdb drives (no databases on the C drive, ever.)

  • Instant File Initialization (Windows Local policy that SQL Server uses)

• SQL Server (instance level):

  • Version and patch level

  • SQL Server is not auto-patched along with Windows.

  • Installation defaults (some of these are in the installer):

    • Tempdb config (files, growth, and location)

    • MaxMemory (4GB or 10% free for a stand-alone)

    • MAXDOP

    • Cost Threshold for Parallelism

    • Backup Compression

  • Maintenance – Backups, Stats, Index defrags, CheckDB

  • HA/DR – is anything in place? Tested?

  • Replication – if used, is it stable?

• Database Level:

  • Database settings

  • Compatibility level – I’m seeing this as an issue more and more

  • “Auto” settings (AutoClose, AutoShrink, etc)

  • Query Store settings

  • Recovery Model – Full for Production is my norm. Most non-DBAs don’t understand this at al

  • Indexes

    • Too many

    • Unused indexes are a BIG problem, especially from 3^rd party vendors)

    • Not enough

    • Duplicates

      • We combine indexes all the time

    • Heaps – some tables should be, most should not

  • File sizes, especially Transaction Logs and VLF counts

  • Deadlocks – are there any?

  • Query Tuning – sort of.

    • I hunt down and investigate queries that are taking a lot of time, CPU or memory. Generally, I can point them out but don’t get to tune them since I don’t usually have the business logic necessary.

There is more of course, and any one of the above can be 5 minutes or 5 hours. Having deep knowledge on each lets me quickly isolate which items are the most important to help the client.

The Bottom Line:

Nobody expects you to be a Senior DBA (unless that is actually your title), so use this process as a starting point.

If the above struck a nerve and you want some help, please reach out.

Fun-Sized Consulting, 4-10 hours, October only!

My recent LinkedIn post(s):

Know Your Audience!

Don't be ashamed of Neurodiversity

Interesting Stuff I Read This Week

Milton Made a Mess | LinkedIn

DOJ weighs suggesting Google breakup as "remedy" to search monopoly (axios.com) – “Google” is a verb…will breaking them up change that? Should it?

Eating less can lead to a longer life: massive study in mice shows why (nature.com) – I know this from experience, despite not being a mouse.

SQL tidBITs:

If you change your recovery model from FULL to SIMPLE, you’re done.

If you change from SIMPLE to FULL, you need to take a Full backup to start the Log backups.

Please share with the next Uber Eats person you see…your job may be easier than theirs!