Recognizing SQL Server’s Warning Signs

SQL Server

It’s crucial for database administrators to recognize signs of pending doom.  Make sure you’re able to quickly spot problems in SQL Server Management Studio by studying these screenshots.

Databases that haven’t been backed up in 24 hours
Databases with old compatibility levels
Databases running out of space on the C drive
SQL Server with Priority Boost enabled
Databases infested with zombies, cockroaches, and zombie cockroaches

What’s that?  You can’t see the differences in the screenshots?  That’s because SQL Server Management Studio doesn’t show you serious problems.

The more I learn about SQL Server and the more customer servers I examine, the more frustrated I get with SQL Server Management Studio.  It’s the year 2012, and the product’s been out for well over a decade – yet dangerous settings like Priority Boost and Lightweight Pooling don’t even have warnings in the GUI.  Just enable ’em, click OK, and your server’s performance and reliability can go right down the toilet without so much as a warning icon in SSMS.

When you’re facing performance or reliability problems, your first step should be to run our free sp_Blitz® stored proc to show you this stuff.  It just breaks my heart when we do our SQL Server health checks, find these problems within the first few minutes, and everyone’s surprised that someone enabled these obscure options long ago without telling anyone.

sp_Blitz®: because no one likes zombie cockroaches in the database server.

Previous Post
A Week of Conferences and Travel
Next Post
The Top Three Performance Tuning Mistakes

15 Comments. Leave new

  • It almost makes you wonder if Microsoft is leaving some of these simple warnings because they want to keep the 3rd Party Companies going.

    At the same time, if you have a hundred developers all using SSMS with read only access, you could drown the database by trying to display all the warnings. So some fore thought would definately be needed.

    Here’s to SQL 2016!

  • You’re not really surprised, right? After all, Microsoft built SSMS on top of a VS shell. Translation: development, development, development. It’s not optimized as an operational tool. Sure, we’ve got some bells and whistles that help, but let’s face it – the majority of the innovation into SSMS has been on the developer side.

  • Allen McGuire
    October 9, 2012 9:53 am

    I don’t look at SSMS as an administrative tool; rather it’s a development tool. Sure, if you are a sysadmin you might want to glean some of this information via SSMS (in fact you can review Compat Level by clicking on Databases and Object Explorer Details).

    Microsoft provides the plumbing to get this information, but as one poster noted I think they are leaving the UI portion up to 3rd parties like Quest, Idera, Red Gate, etc. It’s also possible that not providing these sorts of capabilities native (fancy dashboards, etc.) keeps the cost of the base product down.

    I personally like this model – don’t bloat the price tag of the product by introducing a bunch of fancy dashboards, but if you want you can go purchase the one you desire. The cost of 3rd party tools can often be justified by sheer efficiency – I save countless hours using Spotlight, which has been my tool of choice for a good six years. To each their own. Some may just want to use native monitoring methods.

  • I also do wonder if 3rd party vendors are behind missing or botched features (MDW, UCP etc)

    zombie cockroaches: crunchy on the outside, soft on the inside.

  • I kept staring at all those screen shots and trying to find the difference; like one of those games you played as a kid. I learned the unfortunate truth of it all when I read what was below!
    I was wondering what new features you discovered in management studio, thanks for the disappointment 🙂
    Going to be looking at sp_blitz next week (hopefully). Thanks for the heads up.

  • Brilliant! 🙂

  • I watched the Quest video awhile back and was introduced to sp_Blitz and the chest hair wig in a single afternoon. A real beauty and the beast moment. Anyway, I loved it then, love it now, even answered a Stack Overflow question just yesterday using it as an example.

  • Oh and by the way… people may not like zombie cockroaches in the database server but a little bird told me that Robert Davis likes the occasional raccoon in one.

  • I wish SSMS would warn about auto-shrink.

    Recently out production app suffered downtime because someone (intentionally) enabled auto-shrink. It produced a severe spike of load after a big table had been dropped. It was shrinking like mad for an hour or so.

    • If you want to be proactive, you can probably setup a policy using PBM that will audit and block such changes. The facet is Database and field is @AutoShrink. You can probably apply the policy to all your servers if you want to go that far out. Good luck finding the culprit.

  • informative article for SQL server error …we should have a continuous track on these errors becoz these errors may became critical in future …also we can create alert for these critical event though Lepide Event Log Manager will give a real time notification by email

  • Heh… I realize this thread is a couple of years old but just had to reply.

    None of this matters at all. And adding such a thing to SSMS will simply not matter. No one will read the instructions… even if they’re on-screen.


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.