Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
blakmk
Starting Member
45 Posts |
 Posted - 2003-12-04 : 11:14:00
|
| I run a large sql server for a development environment. On a daily basis databases will be created and then deleted as part of the development process and test harness. Over time performance seems to have degraded and im concearned that it may be because the system catalogs are becoming fragmented.If this is true, is there anyway to rectify the problem? |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2003-12-04 : 11:59:35
|
quote:
Originally posted by blakmk
I run a large sql server for a development environment. On a daily basis databases will be created and then deleted as part of the development process and test harness. Over time performance seems to have degraded and im concearned that it may be because the system catalogs are becoming fragmented.If this is true, is there anyway to rectify the problem?
There could be other explanations for your slowdown. More likely is external fragmentation of your databases.Go to www.sysinternals.com and download their contig application (free). Run this on your data directories to see how fragmented your datafiles are.btw, you can run this while the system is online, you just can't fix the fragmentation while the database is hot.-ec |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-12-04 : 12:15:58
|
| When you say fragmented, do you mean internally in the objects or externally on the files? For internal fragmentation, you run DBCC DBREINDEX or DBCC INDEXDEFRAG.How do you know that they are fragmented?Tara |
|
|
|
blakmk
Starting Member
45 Posts |
Posted - 2003-12-05 : 05:14:35
|
| Im just guessing they are fragmented. In oracle indexes tend to fragmented when there is a lot of delete activity on them. Also is it safe to use DBREINDEX and DBCC INDEXDEFRAG on master indexes? |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-12-05 : 05:22:44
|
| This rings a bell...[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=28393[/url]DavidM"SQL-3 is an abomination.." |
|
|
|
|
|
|
Fragmented master system tables/indexes