| Author |
Topic |
|
kmarshall
Starting Member
4 Posts |
 Posted - 2004-01-13 : 08:10:45
|
| Hi,I am familiar with setting and rebuilding indexes in SQL Server 2000, but I have been asked to investigate how to rebuild the data in a database.We have a user table that contains an large amount of data. Several new columns have been added to this table and my boss says I will need to rebuild the data as well as the indexes so that it is stored contiguously instead of being scattered all over the place.I've had a look through books online and can only come up with references to rebuilding the master database.Can anyone point me in the right direction? Can you even do this?Thanks for your time.Regards,Kevin.SQL Server 2000 SP3 | Microsoft SQL Server 2000 |
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2004-01-13 : 08:14:00
|
| I don't think you need to 'rebuild' the data. just the indexes and also run sp_updatestats. to update the statistics, this is what your boss may mean. |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-01-13 : 08:22:17
|
| DBCC DBREINDEX will rebuild indexes. If your table(s) have clustered indexes (recommended), it will rebuild those as well and will make data more continguous in the database. Books Online has more details. Also look at DBCC SHRINKDATABASE and SHRINKFILE.And you really do not have to worry too much about the data being "scattered" all over the place. SQL Server is very efficient at locating, reading, and caching data to reduce disk I/O. It's OK to perform a reindexing operation occasionally (once a week, once a month) but doing it excessively just wastes resources on the server. |
|
|
|
kmarshall
Starting Member
4 Posts |
Posted - 2004-01-13 : 08:38:11
|
| Thanks for getting back with answers so quickly!I have set up a scheduled job that runs a sproc to reindex all of the user tables. This runs once per week.I'll look at both the commands mentioned, but to satisfy him that this is a suitable answer can you confirm wether it is or is not possible to actually rebuild the data?Once again, thanks!SQL Server 2000 SP3 | Microsoft SQL Server 2000 |
|
|
|
kmarshall
Starting Member
4 Posts |
Posted - 2004-01-13 : 09:04:06
|
| Hmmm,Just been looking at the Maintenance Plan.Under the optimizations tab there is the followin option:Reorganize data and index pages Reorganize pages with the original amount of free space Change free space per page percentage to x%Do you think this does what I am looking for?SQL Server 2000 SP3 | Microsoft Windows 2000 Server |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-01-13 : 11:56:19
|
| To rebuild the data, you will need to export all of the data into files using DTS or bcp. Then from those files, import the data back in. This is very time consuming especially if you need to figure out the order to import the data due to referential integrity and it doesn't buy you much as Rob mentioned.Tara |
|
|
|
kmarshall
Starting Member
4 Posts |
Posted - 2004-01-13 : 12:00:13
|
| Excellent, thanks everyone.I had planned on exporting and importing the data as a last resort. Hopefully we'll agree we don't have to.SQL Server 2000 SP3 | Microsoft Windows 2000 Server |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-01-13 : 13:22:00
|
| Just as an fyi. If you are wanting to rebuild the indexes and INSURE that your tables are sorted most efficiently for use, you might on certain instances want to drop all the indexes, and rebuild one at a time.To give you a rundown:DBCC SHOWCONTIG will give you a summary of how optimized(sorted) the datapages and index levels are of your table.The DBCC DBREINDEX will reindex the table; however, on many indexes it uses the current sort order to determine the sort order that will be used on the rebuild. Because of this, there are times when you see a performance increase by actually physically dropping and recreating the indexes.The DROP and CREATE will resort all the data if you DROP all indexes first, recreate the CLUSTERED second, then CREATE all of your NON-CLUSTERED last.There really isn't ever a reason to move data in and out from a performance perspective. Whatever you do, make sure you update the statistics and shrink the database when you are done, as you will have quite a bit of free space in the database.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
Rebuilding a databases data