July 2, 2009 at 1:59 pm
Hi,
I added a new single column (non-unique) index on a 21 million row table and the create index stmt took about 30 secs to run. I then dropped a different non-unique multi-column index from the same table and the DROP took more than 5 minutes to complete. The server load was no less/more between the CREATE and the DROP. Can someone explain or help me understand what is happening under the covers that would result in the DROP taking so much longer than the CREATE?:unsure:
Not sure it matters but just in case, we are running SS2005 Standard Edition on a 64bit machine.
Thanks in advance!
Chris.
July 2, 2009 at 2:09 pm
The indexing operation was largely a read operation, depending on the memory of your server and the size of the table it could have largely been in memory, which would have helped. Also, if that field was in other indexes that might have helped too.. Not sure there..
But the drop column statement, that had to visit all the pages of that table and do a read and a write operation. Very expensive.. And in the process of doing that it generates a lot of data into the transaction log..
CEWII
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply