5

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!

Improve this question
3
  • 10
    I don't know why you think freeing the proc cache and shrinking the database could possibly be considered good things...
    – Aaron Bertrand
    Aug 7, 2012 at 12:23
  • Shrinking your database will cause internal fragmentation and as a result a reduction in performance of your queries. If you are going to shrink a database, you need to rebuild your indexes. You should also take a full backup afterwards to re-establish the log chain.
    – datagod
    Aug 7, 2012 at 13:49
  • shrink of database has the only one good reason - after huge delete of milions rows from tables, for example old useless data from the biggest table-s.
    – user58150
    Feb 6, 2015 at 19:53

4 Answers 4

Reset to default
22

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.

Improve this answer
0
9

Go here, and download/install the scripts.

If this is for production, I strongly advise you to not write the scripts yourself. It's far better (and easier!) to use an already-thoroughly-tested set of scripts instead of reinventing the wheel.

Improve this answer
7

Personally, I wouldn't shrink the database. It just takes more time when it needs to grow again. It may also make file system fragmentation worse.

Improve this answer
4
  • 4
    I wouldn't say may but will cause drastic fragmentation. sqlskills.com/BLOGS/PAUL/post/…
    – MatBailie
    Aug 7, 2012 at 11:42
  • wouldn't the reindexing fix the fragmentation issue? @Diego, there might be situations when flushing the plans could lead to performance increase. See this: stackoverflow.com/questions/7127776/dbcc-freeproccache
    – user10691
    Aug 7, 2012 at 12:45
  • 1
    @TX_ - reindexing will only defragment indexes, not files.
    – dezso
    Aug 7, 2012 at 13:09
  • @TX_ Also, reindexing will just increase the size of the DB again. Shrinking causes fragmentation but reduces slack space, and reindexing reduces fragmentation but increases slack space. You can have it big and fast or small and slow, basically, unless you rebuild the DB.
    – JNK
    Aug 7, 2012 at 13:33
5

why would you want to run DBCC FREEPROCCACHE; ?

it will remove all your cached exec plans. that's actually a bad thing. cached exec plans improve query execution time.

Improve this answer
1
  • my thought, too - i normally use DBCC FREEPROCCACHE; only to benchmark a new index and/or changes in the stored proc.
    – YS.
    Aug 7, 2012 at 12:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.