Sql-server – Composing the “ultimate database maintenance script”

dbcc-checkdbindexmaintenancesql serverstatistics

I'm trying to compose T-SQL script that will:

A) Shrink database size to the minimum possible size;
B) Make integrity checks, and completely "reorganize" and optimize the database so that it is in a "fresh" state.

Here is what I currently have:

-- Check database integrity:

DBCC CHECKDB WITH NO_INFOMSGS;
GO

-- Get space usage information:

EXEC sp_spaceused @updateusage = N'TRUE';
GO

-- Shrink database:

DBCC SHRINKDATABASE (0, 0);
GO

-- Reindex all tables:

EXEC sp_msforeachtable N'PRINT ''Indexing table: ?''; DBCC DBREINDEX (''?'');'
GO

-- Update statistics for all tables:

EXEC sp_msforeachtable N'UPDATE STATISTICS ? WITH FULLSCAN'
GO

-- Clear procedure cache:

DBCC FREEPROCCACHE;
GO

-- Update all usage info in the database:

DBCC UPDATEUSAGE (0);
GO

Can you please validate if all these are correct? The precedence of commands (e.g. updating statistics after index rebuild, or vice versa), syntax, arguments, etc. are all optimal? Can you suggest any other commands? Performance is not a problem, I'll be executing this script probably once in a month, night hours, so even if it takes hours it doesn't matter. I just need the maximum optimization of the database. Any input would be greatly appreciated.

Thanks!

Best Answer

I'm afraid your script is littered with misconceptions about how databases work, at least in SQL Server.

  • First, you shrink the database as much as possible, using the generic DBCC SHRINKDATABASE command. Why? If the database is going to grow again, what have you gained by shrinking the file? Are you going to temporarily lease out that space in the meantime, until the database needs it again? This will cause fragmentation and make your reindex work harder. More on that in a minute.
  • Next, you reindex all tables in full, whether they need it or not. It is usually much better to analyze the tables first, and perform conditional actions on specific indexes (e.g. reorganize or rebuild) when necessary.
  • Since you're rebuilding all of the indexes, and an index rebuild requires - on average - 1.5x the space occupied by the index, where do you think it's going to get that space? You've just shrunk the database, so there is no free space. You shrunk the database for nothing because now it's going to have to grow to provide some space to accommodate the index rebuilds.
  • Then you update statistics with fullscan. You just rebuilt all of the indexes. Do you think updating statistics is necessary or helpful at this point? I don't, since rebuilding an index automatically updates its statistics. So this extra step is just wasteful.
  • Finally, clearing out the entire proc cache is unlikely to ever lead to overall performance improvement. It may be the case that you get an improvement in a few queries, but it will be much more effective IMHO to sniff those out and clear them out individually (you can manually clear plans in modern versions of SQL Server without throwing the baby out with the bathwater).

I would have to say that what you've got so far is far from optimal and there are much better solutions out there that will help you optimize your database in much more efficient and less wasteful ways. Check out Ola's solution, SQLFool's solution, and SQL Sentry. These don't shrink your database but they do optimize your indexes in an intelligent way and you shouldn't be shrinking your databases anyway.

Related Question