SQL Servers Are Like Avocados

I sliced into an avocado while making lunch recently. The size of the seed compared to the fruit was shocking! It was huge. There was only a thin layer of fruit surrounding a large, bulbous seed. Interestingly, some avocados are just the opposite – the seed is tiny compared to the amount of fruit inside. You never know about the seed inside the avocado until you open it. The SQL Servers we work with are sometimes like avocados.

SQL Server Health Checks and avocados

When we first begin working with a new client, often for a SQL Server Health Check, we never know what we’ll find.

SQL Server performs well despite the configuration

Sometimes the SQL Servers perform relatively well, yet, there are lots of remediation tasks to recommend.

Maybe standard alerts such as errors 823, 824, and 825 aren’t configured. Perhaps no one is notified when errors with severities above 16 are raised. Or, maybe individual users own critical SQL Agent jobs. Often integrity checks haven’t been run in a long time. Frequently, the default settings are still in place for MAXDOP and Cost Threshold for Parallelism.

The list goes on and on.

Yet, the SQL Servers perform well, at least well enough to keep user complaints to a minimum.

For these SQL Servers, we recommend applying best practices any way. They are best practices for a reason and can help prevent problems in the future.

SQL Server is slow despite the configuration

At other times, we find relatively few configuration settings that are not aligned with best practices. MAXDOP is set appropriately. The Cost Threshold for Parallelism has been updated to a reasonable number. There is no sign of contention in tempdb. Windows is configured for the High Performance Power Plan. And all disks have the right block size defined.

Still, there’s no performance joy.

In those cases, we work with the application vendor or in-house developers to improve performance through identifying the most expensive queries, analyzing query plans, reviewing indexing strategies, rewriting stored procedures, etc.

SQL Servers performance != SQL Server health

The take away?

A SQL Server that performs well isn’t necessarily a healthy SQL Server. There may be quite a few departures from best practices that should be addressed.

And the opposite is true. Just because users experience poor performance, that doesn’t necessarily mean the SQL Server is configured incorrectly.

You’ve got to look inside.

Like the avocados, you never know what you’ll find until you look inside your SQL Server.

More information

Here are some additional resources that you may find helpful.

• Should I Add Hardware Resources to My SQL Server? Do This First.
• How do Views Affect SQL Server Performance?
• Identifying SQL Server Disk Latency
• Identify Disk I/O Performance Issues for Your SQL Server Using DiskSpd
• SQL Server Performance and Windows Power Plan
• Checklist: Best practices & guidelines – SQL Server on Azure VMs | Microsoft Docs
• SQL Server Design Considerations | Microsoft Docs

Want to work with The SERO Group?

Have some uncertainty about your SQL Server? Schedule a free 30-minute call to see if our SQL Server Health Check or one of our SEROShield plans is right for you.