[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 545
  • Last Modified:

removing a clustered index sql 2000

i have a table with a clustered index on a GUID......  

it has 2.2 million records.

we are having a time out error when trying to do a summary query....

from what i have read  it is a bad idea to put a clustered index on an essentially random key.
records added are effectively placed all over the drive and there would be a lot of shuffling to get them back in order...


so i think i have made the correct decision to remove the clustering from the index.

from what i can determine   this is the command to do that
alter table tikdet
drop constraint PK_TikDet

however,  i think ... from what i read also...   that in order to do this sql does something with all the existing indexes as well

there are 7 indexes on this table and 1 clustered index   the indexes were mostly a result of running the sql profiler and using its reccomendations.


question 1...  am  i correct in the descision to remove the clustering from the guid?  i still want the index, just not clustered.

question 2...  to remove the clustering, does sql have to rebuild all the keys?  what kind of performance hit would the system get while this was happening.

0
CASorter
Asked:
CASorter
  • 2
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>we are having a time out error when trying to do a summary query....
you might need other/additional index(es) in the table

>from what i have read  it is a bad idea to put a clustered index on an essentially random key.
unless you have another "better" column where you would usually create range queries on, you could keep the clustered index anyhow.
so, the general message is: it depends

>so i think i have made the correct decision to remove the clustering from the index.
possibly :)

>from what i can determine   this is the command to do that: alter table tikdet drop constraint PK_TikDet
yes, that sounds like it is correct.

>however,  i think ... from what i read also...   that in order to do this sql does something with all the existing indexes as well
no. this will just remove the clustered index structure, the data itself will remain as heap, and all other indexes will stay to point to the current data locations.

>question 1...  am  i correct in the descision to remove the clustering from the guid?  i still want the index, just not clustered.
normally yes... however, you shall choose at least 1 index of your table to be the clustered index.
reason: otherwise you table remains a "heap", and will eventually result in unallocated space in the database

>question 2...  to remove the clustering, does sql have to rebuild all the keys?  what kind of performance hit would the system get while this was happening.
as you shall rebuild one of the other indexes to be a clustered index, you will hence rearrange data during that operation, and hence all indexes would be rebuild.
so, you might as well drop all indexes, create the clustered index, then create all other indexes.
=> requires some serious "downtime" window for the application, if possible. this order of processing is not required, but fastest




0
 
lluddenCommented:
question 1...  am  i correct in the descision to remove the clustering from the guid?  i still want the index, just not clustered.
Hopefully you have another column to use as a Clustered index that is more appropriate (IDENTITY field or something).  You need to replace it ASAP - without it your table becomes a heap and SQL will create RID indexes to use in your other indexes.  Data is stored physically based off of the clustered index, so while a steadily increasing value works best of INSERT heavy tables, it is not always the optimal index for other queries.  You will want to choose a clustered index that is relatively unique compared to the size of the table that reflects how that data is likely to be queried.  For example, I have a table that stored transportation requests.  They can be entered in for future dates, but almost every query looks at requests for a specific date or date range, so I use that date as my Clustered Index.

question 2...  to remove the clustering, does sql have to rebuild all the keys?  what kind of performance hit would the system get while this was happening.
The system will create a RID index (each index references the Clustered index).  Probably the fastest way to do this would be to drop all of the indexes, change the clustered index, and then recreate all of the indexes.  Otherwise you will be recalculating all of the indexes twice.

2.2 million rows should go pretty fast on any decent system.  Make a backup of the database and restore it on another computer and test and time your scripts beforehand.  This will give you a good idea of your required maintenance window.

0
 
CASorterAuthor Commented:
yes, i realize that i need a clustered index on the table.

the data gets added in chuncks of 30-40K records at a time and these records will all get accessed as a group...  

should i add an identity field to the table and cluster index on that field?
0
 
lluddenCommented:
If the data being added is usually referenced as a group, then an Identity field would probably make a good clustered index.
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now