When do you compact a Sql Server Database?

Question

We have a SQL Server 2000 database that holds most of our teams databases running on a relatively old server.

Lately we have been having some weird slow down issues on some of our database calls in various applications. (So I know it isn't application specific)

It was mentioned to me that we should look into compacting some of our databases. What are some general rules of thumb for doing this?

Is compacting a database just considered to be general maintenance? Is there a magic number of databases, tables, or records that might lead to this slow down and thus be helped be a compaction?

Number of actively used databases: 6

Average size of database: 20MB with the exception of Fogbugz, that is about 11000MB

Answer 1

You don't compact a SQL Server database (you "SHRINK" it) unless you really need to. It's not MS Access and won't reclaim unused space (well, it can, but it masks a more important problem)

More likely, you have a index/statistic problem:

• out of data stats
• fragmented indexes
• missing indexes

For starters with that database size (small), I'd suggest you look

• at "maintenance plans" to start with to ensure the DBs are in tip-top condition
• for missing indexes

Answer 2

In SQL Server, there's various bits of maintenance you should do on your databases.

10MB is a very very small database, so shrinking a database is not worthwhile for you ("compact" is what you'd do with an MS Access database, in Sql Server you can shrink).

Instead, it's far more likely that you don't have suitable indexes on columns that are frequently queried on in your tables. Or, the indexes you do have are fragmented.

A good place to start is to see exactly what queries are taking a long time (good idea to start up SQL Profiler and monitor). You can then see what those queries are doing, what tables/columns they are accessing and then review whether you have appropriate indexes.