Clustered Index question

Question

Clustered Index question

jonwolds 8288

SSC Enthusiast

Points: 125
More actions
November 3, 2010 at 6:14 am

#228592

Hi,
Come across a couple of strange things when working with a clustered indexes using a test table of 300million records:
Q1. It takes 6 minutes to drop a non unique clustered index on the table. There are no non clustered indexes on the table so I assumed this would just update a meta tag. What is it doing for 6 minutes?
Q2. TableA has a non unique clustered index on ColumnA int column which does not allow nulls.
TableA also has a non clustered PK on ColumnB int column
If I run alter table TableA alter column ColumnA int null The operation runs for a long time (killed after 10 mins). Changing a column to allow null should again be a meta tag switch but I am guessing something is changing in the clusteredkey that the nonclustered PK is using? Not sure what is happening?
Any explanation would be appreciated.
Thanks,
Jon

Viewing 6 posts - 1 through 6 (of 6 total)

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

sturner SSC-Insane Points: 22411 More actions · Answer 1

Null state of a column is determined by a bit in a null bitmap for each row. If there is no null bitmap, or an existing one is full a new byte needs to be allocated for the the entire table... one for each row. This involves a lot of page splits and page allocations and data shuffling so it will run for a long time on a big table like you describe.

As far as why it takes a long time to drop a large clustered index.... the pages need to be collected up and added to the list of free data pages. It could take a lot longer if the the table/index is highly fragmented.

The probability of survival is inversely proportional to the angle of arrival.

jonwolds 8288 SSC Enthusiast Points: 125 More actions · Answer 2

Hi,

Thanks for your response.

I now understand the reason for the time taken to drop a clustered index.

I am still confused regarding the answer to Q2. If there are no non-clustered indexes on the table then altering the clustered index column to allow nulls happens in mili-seconds. This leads me to the conclusion that it's the exitance of the non-clustered primary key which is slowing the operation down considerably?

Any thoughts?

Thanks,

Jon

sturner SSC-Insane Points: 22411 More actions · Answer 3

Any non-clustered indexes need to be updated to point to the new data pages when page splits occur so it will take longer when other indexes are involved.

The probability of survival is inversely proportional to the angle of arrival.

jonwolds 8288 SSC Enthusiast Points: 125 More actions · Answer 4

....but when it's just the clustered index with no non-clustered indexes it's mili-seconds rather than 10s of minutes? This would suggest it's just updating the meta data.

jonwolds 8288 SSC Enthusiast Points: 125 More actions · Answer 5

I did some further testing last night and the delay in the alter table alter column statement only occurs when a non-clustered primary key exists on the table.

It completes in mili-seconds even with the presence of a non-clustered index.