Monday, March 2, 2009

Undocumented system procedure

Critical path for most folks is preventive maintenance, whether they know it or not.

If you're ever looking for a quick way of doing something on all (user) tables in a database, there's a system stored procedure for it.

EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"

This command will run the "dbcc DBREINDEX" command on each table in the database.

Your table-level preventive maintenance regiman, CRUCIAL to performance success, include:

  • keeping all table statistics up to date
  • Keeping index structures balanced; this may be an indexdefrag (which, while intrusive, can be performed online) or preferably by rebuilding indexes (dbcc DBREINDEX )
  • Making sure you have the indexes you need, and remove indexes you don't use (see new managment views for that!)

No comments:

Post a Comment