April 5, 2012 at 9:52 am
Gentlemen, give me some advice about the following.
i had to recreate a clustered index(~10 GB) on a table asap.
i stopped all applications running on our sql server(no activity on server) and ran drop statement in sql studio.
it was running for 3 hours:w00t:
Meanwhile i was checking this process in Activity monitor- sometimes it was suspended and it waited(PAGEIOLATCH in fact) page to be allocated within its DB.
Also i noticed big AVG Read queue in physical disk(in system monitor) where mdf file relevant to this index is located.
i have the idea , might it be due to high files fragmentation (specifically mdf file) on ours physical drive?or just common disk fragmentation?
Please share your thoughts.
April 5, 2012 at 9:55 am
The data had to be sorted into the logical order of the clustered index. In addition, if the table had any nonclustered indexes defined, those also had to be rebuilt when the clustered index was created.
April 5, 2012 at 9:57 am
And if you dropped the existing clustered index first, that also would have caused any currently defined nonclustered indexes to be rebuilt as well.
April 5, 2012 at 9:59 am
In addition to what Lynn said, yes your disk fragmentation can play a part in that.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 6, 2012 at 3:05 am
Lynn Pettis (4/5/2012)
And if you dropped the existing clustered index first, that also would have caused any currently defined nonclustered indexes to be rebuilt as well.
hi Lynn,
are you sure that any nonclusted indexes are engaged in this process consequently?any links/docs?
nothing is mentioned in BOL:
When a clustered index is dropped, the index definition is removed from metadata and the data rows that were stored in the leaf level of the clustered index are stored in the resulting unordered table, a heap. All the space previously occupied by the index is regained. This space can then be used for any database object.
also i forgot to mention - i was droping it with off line option , it should be even faster:
When a clustered index is dropped OFFLINE, only the upper levels of clustered indexes are removed; therefore, the operation is quite fast.
April 6, 2012 at 3:12 am
den_sidr (4/6/2012)
hi Lynn,are you sure that any nonclusted indexes are engaged in this process consequently?any links/docs?
Yes, absolutely sure.
All nonclustered indexes contain the clustered index key as a row locator. When the clustered index is dropped, all nonclustered indexes have to be rebuilt to change the clustered index key to the RID (row identifier). When a clustered index is recreated, all nonclustered indexes have to be rebuilt again with the new clustered index key.
That's why it's recommended that all nonclustered indexes be dropped before you mess with the clustered index
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 6, 2012 at 3:24 am
SQLRNNR (4/5/2012)
In addition to what Lynn said, yes your disk fragmentation can play a part in that.
hi SQLRNNR, can you give me the tip how to check it .i am not admin( currently our db admin is on vacation) but what i know about storage stuff is:
that table/index/mdf file are physically located on mounted LUNS storage: HP MSA2312I SCSI.
any Utility integrated into windows2008 server or separate one?
April 6, 2012 at 3:34 am
GilaMonster (4/6/2012)
den_sidr (4/6/2012)
hi Lynn,are you sure that any nonclusted indexes are engaged in this process consequently?any links/docs?
Yes, absolutely sure.
All nonclustered indexes contain the clustered index key as a row locator. When the clustered index is dropped, all nonclustered indexes have to be rebuilt to change the clustered index key to the RID (row identifier). When a clustered index is recreated, all nonclustered indexes have to be rebuilt again with the new clustered index key.
That's why it's recommended that all nonclustered indexes be dropped before you mess with the clustered index
hello GilaMonster,
i am sorry unclear. you mean All nonclustered indexes contain the clustered index key will be rebuilt automatically during "drop clustered index.. on .. with (online=off)" statement is running?
April 6, 2012 at 3:38 am
Yes.
And all nonclustered indexes contain the clustered index key.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 6, 2012 at 3:55 am
GilaMonster (4/6/2012)
Yes.And all nonclustered indexes contain the clustered index key.
thank you GilaMonster, i have checked that table - two nonclustered indexes(~15GB) have [date] field (based on which the clustered index is created in fact) in its key. Anyway 3 hours is too long i think - there was no any activity on a server unless this process . besides disc fragmentation what might be the reason ? maybe ineffective cash buffer usage(as i saw big read queue)?
April 6, 2012 at 5:32 am
ALL nonclustered indexes have the clustered index key in them. All of them, whether you put it in the index key or include or not. All nonclustered indexes on a table have to be rebuilt when the clustered index is created, dropped or changed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 6, 2012 at 7:11 am
GilaMonster (4/6/2012)
den_sidr (4/6/2012)
hi Lynn,are you sure that any nonclusted indexes are engaged in this process consequently?any links/docs?
Yes, absolutely sure.
All nonclustered indexes contain the clustered index key as a row locator. When the clustered index is dropped, all nonclustered indexes have to be rebuilt to change the clustered index key to the RID (row identifier). When a clustered index is recreated, all nonclustered indexes have to be rebuilt again with the new clustered index key.
That's why it's recommended that all nonclustered indexes be dropped before you mess with the clustered index
And to add a little more to Gail's explaination, yes I am quite sure. Years ago I was working on a process to drop and recreate indexes on tables in a data warehouse that used a truncate and load for some of the tables. I kept wondering why it took so long to drop or create the indexes. That is when I noticed the order I was doing it in. When I dropped NC indexes first then the clustered index, it was faster. Same with creating the clustered index first then the NC indexes.
April 6, 2012 at 8:03 am
Lynn Pettis (4/6/2012)
GilaMonster (4/6/2012)
den_sidr (4/6/2012)
hi Lynn,are you sure that any nonclusted indexes are engaged in this process consequently?any links/docs?
Yes, absolutely sure.
All nonclustered indexes contain the clustered index key as a row locator. When the clustered index is dropped, all nonclustered indexes have to be rebuilt to change the clustered index key to the RID (row identifier). When a clustered index is recreated, all nonclustered indexes have to be rebuilt again with the new clustered index key.
That's why it's recommended that all nonclustered indexes be dropped before you mess with the clustered index
And to add a little more to Gail's explaination, yes I am quite sure. Years ago I was working on a process to drop and recreate indexes on tables in a data warehouse that used a truncate and load for some of the tables. I kept wondering why it took so long to drop or create the indexes. That is when I noticed the order I was doing it in. When I dropped NC indexes first then the clustered index, it was faster. Same with creating the clustered index first then the NC indexes.
HI Lynn , thank you.
i have known this logic for creation process now i know for drop:-)
Anyway 30-40 GB volume is not a warehouse and sql server should drop (and rebuilt NCs) CL in few minutes , i think.
in general it looks like our production system has run into Memory pressure/disk IO subsystem issue.
i think so cause PAGEIOLATCH wait type is our top wait type now:
PAGEIOLATCH_SH 618778 ms 16.50%
DISPATCHER_QUEUE_SEMAPHORE 380294 10.14
PAGEIOLATCH_EX 268131 7.15
OLEDB 226788 6.05
REQUEST_FOR_DEADLOCK_SEARCH 226428 6.04
XE_TIMER_EVENT 226416 6.04
XE_DISPATCHER_WAIT 226356 6.03
SQLTRACE_BUFFER_FLUSH 226244 6.03
LOGMGR_QUEUE 225955 6.02
FT_IFTS_SCHEDULER_IDLE_WAIT 224527 5.99
so i would like to make our admins think about bad hardware configuration/maintanance.
April 6, 2012 at 8:11 am
den_sidr (4/6/2012)
Lynn Pettis (4/6/2012)
GilaMonster (4/6/2012)
den_sidr (4/6/2012)
hi Lynn,are you sure that any nonclusted indexes are engaged in this process consequently?any links/docs?
Yes, absolutely sure.
All nonclustered indexes contain the clustered index key as a row locator. When the clustered index is dropped, all nonclustered indexes have to be rebuilt to change the clustered index key to the RID (row identifier). When a clustered index is recreated, all nonclustered indexes have to be rebuilt again with the new clustered index key.
That's why it's recommended that all nonclustered indexes be dropped before you mess with the clustered index
And to add a little more to Gail's explaination, yes I am quite sure. Years ago I was working on a process to drop and recreate indexes on tables in a data warehouse that used a truncate and load for some of the tables. I kept wondering why it took so long to drop or create the indexes. That is when I noticed the order I was doing it in. When I dropped NC indexes first then the clustered index, it was faster. Same with creating the clustered index first then the NC indexes.
HI Lynn , thank you.
i have known this logic for creation process now i know for drop:-)
Anyway 30-40 GB volume is not a warehouse and sql server should drop (and rebuilt NCs) CL in few minutes , i think.
in general it looks like our production system has run into Memory pressure/disk IO subsystem issue.
i think so cause PAGEIOLATCH wait type is our top wait type now:
PAGEIOLATCH_SH 618778 ms 16.50%
DISPATCHER_QUEUE_SEMAPHORE 380294 10.14
PAGEIOLATCH_EX 268131 7.15
OLEDB 226788 6.05
REQUEST_FOR_DEADLOCK_SEARCH 226428 6.04
XE_TIMER_EVENT 226416 6.04
XE_DISPATCHER_WAIT 226356 6.03
SQLTRACE_BUFFER_FLUSH 226244 6.03
LOGMGR_QUEUE 225955 6.02
FT_IFTS_SCHEDULER_IDLE_WAIT 224527 5.99
so i would like to make our admins think about bad hardware configuration/maintanance.
In your original post, you indicated that the table in question was approximately 10GB in size. Here are some questions:
1. How many rows of data.
2. How wide is each row (Number of columns and how many bytes (max,min,avg if variable length columns exist)
3. Current and new clustered index definitions (basically, how are you dropping and recreating the index)
4. How many non clustered indexes and their definitions.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply