What SQL ConstantCare® Has Been Finding In Your Servers Lately

SQL ConstantCareSQL ConstantCare® analyzed over 3,000 of your SQL Servers this month. Here are the things we check for and the number of servers where we found this problem this month:

Backup

Corruption

Patching

Performance

Eyes up here, kid

“I’ve seen things you wouldn’t believe.”

Planning

Query Tuning

Reliability

And The Top 10 Overall

  1. Update Available – 1,676 servers affected. I get it, patching is hard, and nobody’s doing it.
  2. Queries may have non-SARGable predicates – 1,076 servers affected.
  3. Consider Enabling Query Store – 1,025 servers affected. Keep in mind that this is 1,025 servers that are patched to the level where I’d be comfortable telling them to turn on Query Store, and…they’re still not doing it. I haven’t gotten to the point where I’m going to stop recommending it, but I’m getting pretty close.
  4. Check for Corruption ASAP – 824 servers affected. This one just blows me away: even when people know they need to do it, they won’t. Sure, sometimes they offload it, but there are even tiny sub-100GB servers in here that aren’t getting checked. That’s why I’ve asked Microsoft to run DBCC CHECKDB in the background.
  5. Query Forced to Run Single-Threaded – 819 servers affected. People love them some scalar functions, and we’re only warning you about queries in your top ~50 resource consumers.
  6. Transaction Log Larger than Data File – 736 servers affected. This alert filters for log files over 5GB, too, so it’s not even like these are small databases. Folks just don’t know how to get their log files under control for some apps, and they’ve given up trying. (I need to write a blog post about the rules that are getting ignored the longest.) Why would I be concerned? Because it affects restore times: the log file doesn’t get Instant File Initialization.
  7. Query with Missing Index Request – 733 servers affected. I don’t think this is just about Clippy’s bizarre suggestions, but it’s also about third party vendor apps where folks can’t touch the indexes.
  8. Compilation Timeout – 724 servers affected. We’re specifically warning here about queries that are in your top ~50 resource consumers.
  9. Joining to Table Valued Functions – 599 servers affected. SQL Server 2019’s scalar function inlining doesn’t help this, either.
  10. Heap Needs to Be Rebuilt – 598 servers affected. A lot of folks are using Ola Hallengren’s maintenance solution, and they’re surprised to hear that Ola purposely ignores heaps.

What I Think About All This

“There’s so much corruption in here, there isn’t even any real data left.”

Now, I know you, dear reader. You have great confidence in your ability to manage your environment. You believe you’ve got everything under control, and you couldn’t possibly have the above problems on your servers, right? Right?

Except you do.

When companies hire me for consulting, they get SQL ConstantCare® for free, and the first email is usually pretty shocking. Just last week, a customer found out that SQL Server was dutifully reporting database corruption, but they didn’t know because they didn’t have corruption alerts configured correctly and no one was monitoring the suspect_pages table.

I get it: monitoring is expensive. Except it isn’t: SQL ConstantCare® is $495 a year, and that isn’t per-server: that’s one price to keep an eye on all of your servers. You’re busy. Offload some work to me.

Previous Post
[Video] How to Think Like Clippy (Subtitle: Watch Brent Wear Another Costume)
Next Post
Remember “Nothing Stops a Hekaton Transaction?” Yeah, About That.

10 Comments. Leave new

  • I believe you’re missing some text for #2 of the top 10 list:

    “2. Queries may have non-SARGable predicates – 1,076 servers affected. This”

    Reply
  • Scott Curtis
    July 28, 2020 9:56 am

    Very, very interesting. I immediately think of the selection bias, that these results are from SQL CC customers, likely those already paying attention or at least trying to pay attention to these details. This is hopefully not the sa password = “password” crowd.

    These are also great reference points to show the…we’ll use the term “under-informed manager”…about what DB administration really entails and how good a job you are doing, or why you need more staff/newer servers in order to do a better job.

    Reply
  • Of course the number 824 comes up on the Check For Corruption ASAP warning.

    Reply
  • Bernhard Pallas
    July 28, 2020 11:38 pm

    Hello everybody,
    I work with Ola’s script since years. Due to the fact, that I have more than one instance of SQL Server on my machines, I expanded Ola’s Script to a solution, which backups and maintains all SQL instances on an entire machine automatically.
    It is located in a separate SQL instance, that mainatains and backups all other instances.
    If anyone is interested, I will share it on github.

    Reply
    • Depending on how active you are on Github, you might even think about working with Ola to fork his repo and take it over permanently. There are a bunch of pull requests and issues over there, and he hasn’t been active in Github for months.

      Reply
      • Bernhard Pallas
        July 29, 2020 6:19 am

        Hello Brent, I just opened an account on github, in order to publish the solution if sombody likes it. In Our company we work with MS TeamFoundation. So, I am not familiar with github.

        Reply
  • Bill Goetschius
    July 30, 2020 9:22 am

    Brent,

    We run Index update jobs from Ola Hallengren at 4 AM. At 6 AM we clean Cache out. In theory we are getting the most up to date statistics for our queries etc. every day. We have Parameter Sniffing turned off. We are not seeing any performance issues when filling cache back up at that time in the morning. What is flawed about this process?

    Reply
    • Bill – I wish I could do free personal consulting for everyone, but that’s kinda beyond what I can do quickly. If you’re a SQL ConstantCare customer, shoot us an email. Thanks!

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}