Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 SQL 2008 DROP Index Overhead?

Author  Topic 

dsteinmetz
Starting Member

6 Posts

Posted - 2011-01-10 : 17:07:16
Should I expect there to be any large overhead with actually running a drop index statement? I'm just planning ahead before I run it but want to make sure it won't affect any users when I'm running it. The indexes I will be removing are no longer used and exist on a very large table (187 million rows).

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-01-10 : 17:10:35
Are you running Enterprise edition? If so, then you can use ONLINE=ON option.

Otherwise, blocking is going to occur. ONLINE=OFF is the default. Here is what BOL says about offline mode:

quote:

Table locks are applied and the table is unavailable for the duration of the index operation.



See DROP INDEX in BOL for more details.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page
   

- Advertisement -