Building SQL ConstantCare®: Warning You About New Large Tables & Indexes

SQL ConstantCareWhen I’m doing a SQL Server health check, one of the things I like showing clients is a list of their top tables by size.

Inevitably, there are a bunch of tables that:

  • Tables whose names start with “temp” or “backup” or “just_in_case”
  • Tables with no clustered index that were clearly created with a select-into, and then forgotten about
  • Tables that haven’t been accessed in months or years

We have some chuckles, make a quick calculation, and realize that our database is 10%-20% larger than it needs to be. That means our backups are taking 10-20% longer, corruption checking, restore testing, backup space, disaster recovery test time, you name it – it’s all 10-20% higher.

So now if SQL ConstantCare® sees a new table with at least a million rows, we give you a heads-up in your daily email:

The first table it found on the first day

This is such a good example of what SQL ConstantCare® is all about: giving you practical insight, not just raw metrics.

Previous Post
The 4 Presentations I’m Proudest Of, and What Inspired Them
Next Post
Announcing a New Class: Fundamentals of Columnstore

4 Comments. Leave new

  • I would like to be able to use ConstantCare but our servers live in a secure environment.
    Is supporting that market segment on your radar?

    Reply
  • I am using a similar approach for a number of years already and based on what I experienced I also include in my checks schema names like tmp,temp, delete, trash, etc. I also include table names that include dates, like dbo.customers_20100101.

    Reply
  • Simon Holzman
    October 30, 2020 9:44 am

    I have a database on my Production Server called “Archived Data”. Every few months, I run a script manually (so I can verify the tables affected), which copies all of the tables that have names starting with an underscore, or have _bak, _temp, _bk, _backup, or an 8 digit date as a suffix to the Archived Data database and then, after confirming that the table was copied successfully, it Truncates and then Drops the original Production database table.

    This allows me to recover the tables, if they are actually needed (never happened yet, but it is possible that something gets missed), and it minimizes the time that Production backups take.

    A side benefit is that, because I really will be clearing them out of Production, it allows me to make backup tables of the whole table, rather than a subset of the data. This often allows me to check exactly when a specific field update was made even if the record only has a basic Last_Modified_Date field.

    I do the Truncate/Drop because these tables are so large, a straight Drop will generate huge transaction logs that are completely pointless in this situation (that is why I check that the table WAS copied successfully first)… but your mileage may vary on that decision.

    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