Database Maint
-
Okay, I'm confused. What kind of commands do I need to run to ensure good performance? Is dbccdbreindex the best weay to rebuild indexes or should I use create index with drop existing? What are the best ways to resolve table and and index fragmentation? I need to make sure that I am getting the best disk performance for our current config. Thanks,
Jeff.
-
Hi,
DBCC rindex do the same thing drop and create index again.
so u can do DBCC reindex or drop the index and create again.
but i will suggest to go for DBCC reindex.
Hope this help.
from
Killer
-
What sort of database are you maintaining? How big is the database? How often is information added and removed? Does it need to be available 24x7, or do you have a maintenance window? What is your hardware configuation?
For smaller databases that have maintenance windows, the best thing is to setup a maintenance plan.
For larger databases and databases without maintenance windows you'll want to create you own maintenance routines that mirror the maintenance plan tasks. EG: DBCC CHECKCATALOG, DBCC CHECKTABLE, etc... Check the DBCC topic in Books Online.
Running DBCC DBREINDEX to fix index fragmentation can be a bit of an overkill. This rebuilds ALL the indexes in the database whether they are fragmented or not. A better way to resolve index fragmentation is to analyse it first by running DBCC SHOWCONTIG. If this command shows that there is fragmentation, run DBCC INDEXDEFRAG.
A big factor in disk performance is the I/O configuration. You'll need to determine the read/write ration of the database and arrange disk arrays/partitions to suit. It's considered best practice to have the database and transaction log located on different physical disk arrays. Depending on you requirements, you could even require secondary database files on yet another seperate physical disk array.
Also, this should probably have been posted over here http://www.sqlservercentral.com/forums/messages.aspx?forumid=5
--------------------
Colt 45 - the original point and click interface
-
If you have a maintenance window, the optimization maintenance plan is the simplest way to manage fragmentation. It may be overkill, but it is the easiest to set up. Downsides include performance impact while running, and huge log activity. If you have a fully logged database, the log backup after the maint plan may be the size of a full backup.
The DBCC SHOWCONTIG page in BOL has a sample script that will check fragmentation and only rebuild indexes that are fragmented beyond some threshhold. I would add DBCC UPDATEUSAGE to it (assuming you can afford the performance hit) to make sure SHOWCONTIG is getting accurate information.
In some cases, such as only slight to moderate fragmentation, INDEXDEFRAG can be more efficient than DBREINDEX.
Finally, all tables should have a clustered index or key. Tables with no clustered index/key are not affected by DBREINDEX or INDEXDEFRAG.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply