Shoudnt drop index be a metadata operation?

[Former Member]

I am not sure but drop index is taking seriously long time on my system. I always thought that it should be a metadata operation and should be done in 1-2 seconds max but it is taking too much time.

I checked for any blocking and there was no blocking.Also, I executed the drop index command putting it in single user mode as well but still it was taking time.

I used mon tables to look at monProcessObject and waits and activity. It was doing logical/physical IO's and index is quite large and it is not in memory and most of waits were type 29. I was wondering why drop index needs to read all the index pages. It is seriously strange.

ASE version is 15.0.3

Thanks

[former_member188958 · Accepted solution]

There is considerably more work involved than just a few rows updated in the system catalogs.

As part of dropping the index, all the extents belonging to the index need to be deallocated, and those deallocations generate log records.

[Former Member · Accepted solution]

Thanks. This explain why the drop index is taking lots of times. Is there plan to make it more efficient. I guess you work with the Product itself .