Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

to compensate for not being able to drop NC index online

Posted on 2010-08-19
17
373 Views
Last Modified: 2012-08-14
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
0
Comment
Question by:anushahanna
  • 9
  • 6
  • 2
17 Comments
 
LVL 2

Accepted Solution

by:
rajeshprasath earned 100 total points
ID: 33476813
ONLINE = ON

ON
Long-term table locks are not held. This allows queries or updates to the underlying table to continue.

Specifies whether underlying tables and associated indexes are available for queries and data modification during the index operation. The default is OFF.

check the following link,
http://msdn.microsoft.com/en-us/library/ms176118.aspx
0
 
LVL 6

Author Comment

by:anushahanna
ID: 33476827
that is available only for clustered index; what options are advised for NC index?
0
 
LVL 2

Expert Comment

by:rajeshprasath
ID: 33476866
sorry, online = ON option works only with clustered index.

Only a clustered index can be dropped online.

Non Clustered index cannot be dropped online.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 6

Author Comment

by:anushahanna
ID: 33476930
>>Non Clustered index cannot be dropped online.

any alternatives to avoid the locking? (when user queries hit the underlying table)
0
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 400 total points
ID: 33477021
Please clarify.  Dropping an NC is pretty fast and should be infrequent.  So, why the concern about locks for the duration?  

The larger concern, in my mind, is an excution plan that relies on the NC index is no longer valid after the index is dropped.  At best, the SQL requires re-optimization; at worst, it will perform awful after the re-optimization.

 
0
 
LVL 6

Author Comment

by:anushahanna
ID: 33477179
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..
0
 
LVL 42

Expert Comment

by:dqmq
ID: 33477552
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)  

0
 
LVL 6

Author Comment

by:anushahanna
ID: 33477603
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.
0
 
LVL 42

Expert Comment

by:dqmq
ID: 33477814
So, you restore the indexes in step 2 THEN drop them in step 3?   does that make sense?
0
 
LVL 6

Author Comment

by:anushahanna
ID: 33477827
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...
0
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 400 total points
ID: 33479882
I can accept at face value that you do not "need" the indexes. But, aside from some disk storage, what harm do they cause?  I could understand if you did not want to take the time to load the indexes, but you load them then drop them.  Seems like the long way around the block.  Further, compiled execution plans that came along with the restore and rely on the dropped indexes will be rendered invalid.  

One of the classical uses for filegroups is for organizing backup/restore processes into usefult subsets.  You may want to consider that for isolating the indexes that you don't want.
0
 
LVL 6

Author Comment

by:anushahanna
ID: 33484570
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?
0
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 400 total points
ID: 33487190
My first response is to stop dropping the indexes.  And I don't say that to be funny. Weigh the cost of dropping indexes (high for now) with the benefit (not very much, it would seem) and see where that assessment takes you.  At a minimum, you might spread out index dropping or defer till a time locking is not such an issue.

Having said that, if you want to pursue it further, here's my quick synopsis.  Dropping NC indexes is a relatively quick operation and should not involve long term locks.  Actually, I question whether it involves locks on the underlying table, at all!  So start with identifying exactly what resource is getting locked, which process is locking it and which processes are getting blocked.  It very well may be that queries are blocking the drop index command and that in turn has a cascading effects.   If so, another strategy might be to hold off running the queries until the NC indexes are dropped.

Also, the fact that there is some "locking" relationship between the NC index and the queries makes a suggestion that queries are dependent on the index in some way.  So, beware of that!



0
 
LVL 6

Author Comment

by:anushahanna
ID: 33487366
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?
0
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 400 total points
ID: 33489828
Wow! Apparently index size does matter.  I had one more thought.  You might see what happens with a two step process:

single user mode
alter index indexname disable
alter index indexname disable
...
multi user mode/allow queries
drop index indexname
drop index indexname
....
0
 
LVL 6

Author Comment

by:anushahanna
ID: 33489875
Thanks for the idea. Will try and let you know.
0
 
LVL 6

Author Comment

by:anushahanna
ID: 33587284
this was quiet efficient. Thanks dqmq for the patient guidance.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server - TSQL - Removing duplicates/How to get max records per id 13 45
SQL 2014 always on 31 58
Solar Winds can't see SQL Server Express 17 32
TSQL convert date to string 4 34
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question