to compensate for not being able to drop NC index online
when dropping an NC index, because you are not able to keep the index online, what are the other alternatives, to avoid for the incoming queries to be locked out?
thanks
thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
sorry, online = ON option works only with clustered index.
Only a clustered index can be dropped online.
Non Clustered index cannot be dropped online.
Only a clustered index can be dropped online.
Non Clustered index cannot be dropped online.
ASKER
>>Non Clustered index cannot be dropped online.
any alternatives to avoid the locking? (when user queries hit the underlying table)
any alternatives to avoid the locking? (when user queries hit the underlying table)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
dqmq, while restoring a database in another box, there is no need for a bunch of NC indexes in the new environment, and they all get dropped together at one time, before users start hitting the database tables again. in that short time between the indexes being dropped, if a query hits the tables under the index, it causes endless locks..
Do you see the apparent contradiction:
"before users start hitting the database tables...if a query hits the tables..., it causes endless locks..."
What exactly is the sequence of events, for example:
Backup Database (Old)
Restore Database (New)
Users Query (New)
Drop All Indexes (New)
"before users start hitting the database tables...if a query hits the tables..., it causes endless locks..."
What exactly is the sequence of events, for example:
Backup Database (Old)
Restore Database (New)
Users Query (New)
Drop All Indexes (New)
ASKER
Backup Database (Old)-Box A
Restore Database (New)-Box B
Drop All Indexes (New) -BoxB
Users Query (New) --While Step #3 is not yet finished, people start step #4, and hence the locks.
Restore Database (New)-Box B
Drop All Indexes (New) -BoxB
Users Query (New) --While Step #3 is not yet finished, people start step #4, and hence the locks.
So, you restore the indexes in step 2 THEN drop them in step 3? does that make sense?
ASKER
yes; because backup/restore is a package- we do not say what we need or not; so we get them all in, and then remove what we do not need...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
dqmq, thanks for your wise input. I will take your word, and focus on fg.
but in the current setup, would you think there are any alternatives to avoid the locking?
but in the current setup, would you think there are any alternatives to avoid the locking?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
dqmq,
>>Dropping NC indexes is a relatively quick operation and should not involve long term locks
I put the DB on single user mode and did it, still took an hour. (90 indexes in all).
is the size of the index a factor in this?
>>Dropping NC indexes is a relatively quick operation and should not involve long term locks
I put the DB on single user mode and did it, still took an hour. (90 indexes in all).
is the size of the index a factor in this?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the idea. Will try and let you know.
ASKER
this was quiet efficient. Thanks dqmq for the patient guidance.
ASKER