Clustered Indexes

Question

Clustered Indexes

Green Armchair

Hall of Fame

Points: 3561
More actions
July 15, 2003 at 2:41 am

#64965

Hi
I understand that in SQL Server, a clustered index is a sorted copy of the heap data (or maybe not??). Can anybody explain what is going on beneath the covers when a clustered index is dropped? Sequence of events:
1) drop the non-clustered indexes (takes seconds) - to avoid their rebuild when the clustered index is dropped.
2) drop the clustered index - takes 6 hours!!
This is approx equal to the time it takes to build the clustered index - its a big table.
What is SQL doing to the underlying data during this clustered index drop?
Thanks in advance.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Hi acudlip,

quote:
1) drop the non-clustered indexes (takes seconds) - to avoid their rebuild when the clustered index is dropped.
2) drop the clustered index - takes 6 hours!!
This is approx equal to the time it takes to build the clustered index - its a big table.

how do you drop the indexes?

Via EM or QA?

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Green Armchair Hall of Fame Points: 3561 More actions · Answer 2

Frank

The indexes were dropped via QA. The PK constraint was dropped via EM.

Thanks

Andy

G.R.Prithiviraj Kulasingham SSCarpal Tunnel Points: 4105 More actions · Answer 3

I think you have the answer in your question:

quote:
drop the clustered index - takes 6 hours!!
This is approx equal to the time it takes to build the clustered index - its a big table

When a clustered index is droped sqlserver will automatically generate a system specified clustered index to identify each row. ( this index includes extend and page inforamtion as well).

Always try to avoid droping the clustered index. Try to alter.

G.R. Preethiviraj Kulasingham

Chief Technology Officer.

Softlogic Information Systems Limited,

14 De Fonseka Place,

Colombo 05.

Sri Lanka.

Cheers,
Prithiviraj Kulasingham

http://preethiviraj.blogspot.com/

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

quote:
When a clustered index is droped sqlserver will automatically generate a system specified clustered index to identify each row. ( this index includes extend and page inforamtion as well).
Always try to avoid droping the clustered index. Try to alter.

just a quick cross thought

Hello Antares686, could this be what the author mentioned ???

quote:
If you create a nonclustered index and don''t already have a clustered index, SQL Server has to create a ''phantom'' clustered index anyway because nonclustered indexes always point to clustered index keys. So you might create your own clustered index , ensuring that it will be of some use.

See the thread http://www.sqlservercentral.com/forum/link.asp?TOPIC_ID=14025

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Antares686 SSC Guru Points: 125444 More actions · Answer 5

I don't believe so but I didn't get a chance to copy a 4 gb test DB today for use at home so I copuld watch everything that happens. There is no seperate index anywhere in the underlying tables for a heap just a pointer to the first data page. Now if could be some data movement occurred because I am sure there is a pointer to the next page on each page and in fact when there is no clustered index in place the table size is listed as larger than when there is. If the pages were 100% full then he would have suffered page splits and data movement which is what I suspect due to the fact there can only be 8k worth of data on a single page plus a little overhead. I may not have a chance to test all this thou since I have a baby due anyday right now. But if I do I will post what I find. Or if anyone else wants to look, please let us know.

G.R.Prithiviraj Kulasingham SSCarpal Tunnel Points: 4105 More actions · Answer 6

What I meant as a clustered index generated by the system may what franch quotes as "phantom clustered index"

I believe that always system keeps "some sort of clustered index" like setup to identify the data.

Preethi

Cheers,
Prithiviraj Kulasingham

http://preethiviraj.blogspot.com/

Antares686 SSC Guru Points: 125444 More actions · Answer 7

Just curious but how big is this table as far as number of rows?

Antares686 SSC Guru Points: 125444 More actions · Answer 8

Tested today. Table with 10million rows. Added the PK and took 17minutes (PIII 500, 768 RAM, 20GB IDE HD), dropping took 2. So I wonder if there is something else you need to factor in that is causing this too you.

Green Armchair Hall of Fame Points: 3561 More actions · Answer 9

Thanks for the useful input so far.

Table is currently 980,229,298 rows and approaching 250GB in size (data & indexes).

I tried DBCC INDEXDEFRAG abandoned after 20 hours. DBCC DBREINDEX ran out of space as you can't specify sort in TEMPDB. Next thing I'll try is CREATE INDEX ... WITH DROP_EXISTING. BOL suggests this may be the most efficient.

Andy

Antares686 SSC Guru Points: 125444 More actions · Answer 10

Also, take a look at your Transaction Log size. Even when removing an index that file grew but I have my test machine set to truncate on checkpoint so the file doesn't continue to grow when testing various things. Could be the TL has gotten large and become slow due to file fragmentation if it is allowed to grow.

Green Armchair Hall of Fame Points: 3561 More actions · Answer 11

The database is in SIMPLE recovery mode, so the TL shouldn't grow too much