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 |
|
Lumbago
Norsk Yak Master
3271 Posts |
 Posted - 2009-05-12 : 07:15:56
|
| Hi,I just discovered that the 31GB database I'm an admin for with 1000+ tables has 818 heap tables. These tables are all stuffed with live production data and is considered the core of the system. Obviously this is terrible and increases all kinds of performance problems so I need a strategy to convert all the nonclustered primary key indexes to clustered. How do you recommend I go about doing this? I'd prefer not doing it manually...- Lumbago |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-05-12 : 09:20:43
|
The main goal is to have all columns referenced in WHERE and JOINS as key columns, and other column INCLUDEd in the index.This is however not an option for clustered indexes.You can script all indexes out (both drop and create), replace "nonclustered" with "clustered" and run the script.If you have Enterprise Edition you have the option to run this online, otherwise do this on nights.
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2009-05-12 : 09:38:57
|
| I found a script for getting the index definitions so I'll probably be able to script that part somewhat easily. The problem is that there are over 800 tables quite a few of which has in excess of 10 mill rows in them. I'm afraid this will run for weeks...Which of the following two do you think is better:1. create a new table with the clustered index, copy data, drop original table, rename new table2. drop nonclustered primary key index, create new clustered primary key index- Lumbago |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-05-12 : 09:51:48
|
A table with 10 million records? About 10-20 minutes to create a CI.800 of those? 16000 minutes is 11 days. Or 5 days if fast SAN.I would drop all the indexes, create the clustered index first and then the non-clustered.After each table I would backup the transaction log to free space.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
|
|
|
Creating clustered index on many heap tables