First things first: pick up that filthy keyboard, take it over to the trash can, turn it upside down, and give it a good, vigorous shake.

Next, go to your biggest SQL Server and run:

This will give you an inventory of all of the objects in your databases, ordered from biggest to smallest. Here’s what to look for:

Ask simple questions about the 10 biggest tables. Are they supposed to be this large, and contain this much history? Do we have history-deletion jobs that are supposed to be running, but aren’t? I swear, this happens at almost every client I work with! By simply cleaning out the unused, unwanted history, we can make our maintenance jobs like DBCC CHECKDB run faster.

Look for objects with names like Backup_ or Temp_ or ToBeDeleted. We’ve all done a quick backup by copying data into another table, and then accidentally forgotten to go back and delete it afterwards. Check the Reads column to make sure it’s 0, and then rename the objects with a prefix like __To_Be_Dropped_On_20250425. Over the coming days, make sure you’ve got a good copy of the backup so that you can undelete those objects if necessary, and then on April 25, do the needful. (And this time, actually drop them – set yourself a calendar reminder.) Again, the goal here is faster maintenance jobs.

Next, look for indexes with Reads = 0, and Writes > 0. These are indexes that are slowing down your inserts, updates, and deletes, and they don’t have the payoff of making select queries go faster. Script out their definitions in an undo script so you can recreate ’em if necessary, and then drop ’em. (I’m not a fan of disabling them because I’ve seen too many homemade index maintenance scripts that simply rebuild indexes without checking the disabled status, and when you rebuild a disabled index, it goes back into place.)

Finally, look for tables with Reads = 0 and Writes = 0. Do we have old versions of tables lying around that used to be part of the production app, but no longer are? Do we still need those?

This work only takes an hour or two, and it reduces dead weight that’s lengthening your backups, DBCC CHECKDB, statistics updates, and more.