create and dropping indexes

Posted on 2009-02-23
Medium Priority
Last Modified: 2012-05-06
Hi Folks,

is it necessary or even good practice to keep everyone off your database while you are create or dropping indexes?

Question by:david_32
  • 3
  • 2
  • 2
  • +1
LVL 11

Accepted Solution

rowansmith earned 1200 total points
ID: 23718599
Depends on your database load and weather droping an index will send your CPU through the roof and effect user performance.

Creating is the same but less critical, e.g., create indexes during times when user load is low.
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 400 total points
ID: 23718725
>is it necessary?
no, normally not.

>good practice?
it is good practise to do it while low load of the database (as mentioned)

if the index is important for user performance, you might consider creating a "replacement" index first, then drop the old one.

note: dropping an index by itself is not a problem, but if the index is important for user queries, the missing index will result in full table scans (and as mentioned, send the CPU and IO activity to higher levels). such an index should not be dropped, anyhow...

Author Comment

ID: 23718749
I believe when you rebuild and index ALTER INDEX <indexname> REBUILD.  everyone need to be off the system - why would that be? (as far as I know a REBUILD recreates the index).

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

LVL 11

Expert Comment

ID: 23718841
When you rebuild a primary XML index, the underlying user table is unavailable for the duration of the index operation.

So you may effectively want to ensure that all users are off the system.
LVL 57

Expert Comment

by:Raja Jegan R
ID: 23719012
Why are you trying to drop and create indexes instead you have the direct option of DBCC REINDEX.

DBCC DBREINDEX can rebuild all the indexes for a table in one statement. This is easier than coding multiple DROP INDEX and CREATE INDEX statements. Because the work is performed by one statement, DBCC DBREINDEX is automatically atomic, whereas individual DROP INDEX and CREATE INDEX statements must be included in a transaction to be atomic. Also, DBCC DBREINDEX offers more optimizations than individual DROP INDEX and CREATE INDEX statements.

DBCC DBREINDEX is an offline operation. If a nonclustered index is being rebuilt, a shared lock is held on the table in question for the duration of the operation. This prevents modifications to the table. If the clustered index is being rebuilt, an exclusive table lock is held. This prevents any table access, therefore effectively making the table offline.

To perform an index rebuild online, or to control the degree of parallelism during the index rebuild operation, use the ALTER INDEX REBUILD statement with the ONLINE option.

So you have the option to do it both ONLINE as well as OFFLINE.
The decision in your hands now..

The below code will help you to apply REINDEX for all tables in the Database which would make your work more simpler. Replace the DatabaseName and FIllFactor value '90' of your choice before running it.
USE DatabaseName --Enter the name of the database you want to reindex
DECLARE @TableName varchar(255)
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
CLOSE TableCursor

Open in new window


Author Comment

ID: 23753152
hi rregan,

I was just making the point that index rebuilding drop an index and when you do this you need to be off the system. Therefore it would make sense you need to have everyone off the system when you drop an index by itself. Thanks for the script though it may be handy :)
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 400 total points
ID: 23753236
Yes.. Index rebuild can be done only when the tables involved are not used by anyone and hence you need a minimum timegap for it.

But not necessarily, you can rebuild indexes online if you have provided the option while creating the index ONLINE = ON. This will work when users are in the system

Author Closing Comment

ID: 31550460
I think everything is cleared up then.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Screencast - Getting to Know the Pipeline

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