create and dropping indexes

Posted on 2009-02-23
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
    LVL 11

    Accepted Solution

    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 142

    Assisted Solution

    by:Guy Hengel [angelIII / a3]
    >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

    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).

    LVL 11

    Expert Comment

    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
    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
    DEALLOCATE TableCursor

    Open in new window


    Author Comment

    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
    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

    I think everything is cleared up then.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
    Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now