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 |
|
davidagnew37
Starting Member
33 Posts |
 Posted - 2008-10-20 : 12:18:15
|
| Hi, Sql server 2005 enterprise. db is in Simple recovery mode. I have a non-partitioned table - approx 600,000,000 rows (8 columns). There are 3 indexes on this table. 1 mdf file, 1 ldf file. files are set to grow 10%. The total database size before re-index on this table is 126GB. when I re-index this table (dbcc dbreindex(tablename)), the mdf grows from 126gb to 190gb + (and still growing). I know if I skrinked this db - it would reduced back to nearly original size. Should I shrink the database after reindexing this table? or does this reindexing want this space? I am not admin guy and would appreciate any help here. Is it heavily affected by index fill factors. that aside, any advice would be great..thanks |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-20 : 12:47:27
|
| 1)First of all, It's not good to have 1 mdf and ldf file for 126 GB. You should spread your data files accross separate FG across multiple disk to reduce I/O contention2)Donot set autgrow option to 10%. It will grow 12.6GB at once and you will see performance issue.3)Yes ,mdf files will grow .You should not DBCC REINDEX whole table at once.The best idea is to use Tara's script to find fragmentation level and Reindex or Reorganize |
 |
|
|
davidagnew37
Starting Member
33 Posts |
Posted - 2008-10-20 : 13:50:14
|
| thanks for this..I will make these changes. But pelase answer the question. Is it best not to shrink the db after such growth in the mdf file?Ive set the files to:mdf = 1Mb unrestricted growth.ldf - 10% growth..note - this is a DW table and needed to populate a web app. I therefore wanted minimum fragmentation for max performance and therefore reindexed whoel table. Please advise on best practice with regards to shrinking db after reindex. |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2008-10-20 : 15:27:21
|
| The base of the problem here is that DBCC DBREINDEX, and the SQL 2005 ALTER INDEX REBUILD commands will create a brand new copy of the indexes in question. If you have a clustered index, you get a brand new copy of the table. Once the new copies are created, the old ones are deleted (Don't worry. No data is lost during this delete). For tables of this size, you may want to consider ALTER INDEX REORGANIZE which will move rows and/or pages around within the same file. It does not generally give the same level of defragmentation, but it does pretty well. Testing will have to dictate whether you can live with the difference, but for most people, the difference is not too noticeable.Another approach you can take is to physically drop and rebuild the indexes during some downtime. Dropping the clustered index does not delete any data, but rebuilding it will move data pages around (which is what defragmentation is all about anyway). Look for rebuilding the clustered index to take a while. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-20 : 15:34:20
|
| Donot set to 1MB(Growth). We set to 500MB atleast for big database as yours. As I Said, you need to use TARA's script for whether rebuilding or Reorganizing instead of using old DBCC reindex command. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-20 : 15:36:55
|
quote:
Originally posted by mcrowley
The base of the problem here is that DBCC DBREINDEX, and the SQL 2005 ALTER INDEX REBUILD commands will create a brand new copy of the indexes in question. If you have a clustered index, you get a brand new copy of the table. Once the new copies are created, the old ones are deleted (Don't worry. No data is lost during this delete). For tables of this size, you may want to consider ALTER INDEX REORGANIZE which will move rows and/or pages around within the same file. It does not generally give the same level of defragmentation, but it does pretty well. Testing will have to dictate whether you can live with the difference, but for most people, the difference is not too noticeable.Another approach you can take is to physically drop and rebuild the indexes during some downtime. Dropping the clustered index does not delete any data, but rebuilding it will move data pages around (which is what defragmentation is all about anyway). Look for rebuilding the clustered index to take a while.
This will not be complete solution. Reorganizing doesn't update statistics but that depends on fragmentation level. Reorganize organize leaf level of index. |
|
|
|
davidagnew37
Starting Member
33 Posts |
Posted - 2008-10-20 : 16:05:06
|
| thanks for the info. I will implement taras script (have used this on other dbs we have) and also look at partitioning also. ..still not sure if I should shrink db :-) thanks david |
|
|
|
|
|
|
|
|
dbcc dbreindex(table) is creating huge mdf file