[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

dropping and recreating indexes dynamically

Posted on 2004-11-03
12
Medium Priority
?
866 Views
Last Modified: 2012-06-27
We have an insert that is running poorly and we believe it is because of the number of indexes on the table.  It is a very large table (approx 30 million rows).  The insert is run in a batch process utilizing a stored proc.  We would like to dynamically drop and recreate any indexes on it.  I'd rather not hardcode them in the even we add some in the future and forget to address the hardcoding.  I also think we could get by with only dropping the non-clustereds so the recreation doesn't take so long so it would be nice if we could identify these specifically.  

Is there anyway to dynamically identify the indexes, drop them before the insert and recreate them after the insert?
0
Comment
Question by:babybird
  • 5
  • 4
  • 3
12 Comments
 
LVL 34

Assisted Solution

by:arbert
arbert earned 800 total points
ID: 12488969
You can use SP_HELPINDEX to return a list of indexes on the table.  I agree that I would probably leave the clustered index in place.  You'll also have to see if dropping the indexes works better for you.  I have a couple update processes that run all weekend, but it's still faster for me to do the updates with indexes IN PLACE instead of dropping them and rebuilding them--just depends on your data, the drives, and the number of procs....

Brett
0
 
LVL 16

Accepted Solution

by:
muzzy2003 earned 1200 total points
ID: 12504646
This stored procedure is one I had lying around from a previous occasion. If you pass in a table name, will drop all non-clustered and non-unique indexes on a table and create a stored procedure, named after the table, to recreate them. Hope it's useful.

CREATE PROCEDURE usp_DropIndexes
      @strTableName      sysname
AS

      DECLARE      @intObjectID      int,
            @strCommandDrop      varchar(4000),
            @strCommand      varchar(4000),
            @sintIndID      smallint,
            @strIndexName      sysname,
            @tintFillFactor      tinyint,
            @strColumnName      sysname

      IF EXISTS      (SELECT      *
                  FROM      sysobjects
                  WHERE      name = 'usp_RecreateIndexes' + @strTableName)
            EXEC('DROP PROCEDURE usp_RecreateIndexes' + @strTableName)

      SET @strCommandDrop = ''
      SET @strCommand = 'CREATE PROCEDURE usp_RecreateIndexes' + @strTableName + ' AS' + CHAR(10)

      SET @intObjectID = OBJECT_ID(@strTableName)

      DECLARE      curIndexes CURSOR
      FOR      SELECT      indid, name, OrigFillFactor
            FROM      sysindexes
            WHERE      id = @intObjectID
            AND      status & 0x800 = 0
            AND      status & 0x2 = 0
            AND      status <> 0
            AND      first <> 0

      OPEN curIndexes

      FETCH      NEXT
      FROM      curIndexes
      INTO      @sintIndID,
            @strIndexName,
            @tintFillFactor

      WHILE @@FETCH_STATUS = 0
      BEGIN

            SET @strCommandDrop = @strCommandDrop + 'DROP INDEX ' + @strTableName + '.' + @strIndexName + CHAR(10)

            SET @strCommand = @strCommand + 'CREATE INDEX ' + @strIndexName + ' ON ' + @strTableName + ' ('

            DECLARE      curKeys CURSOR
            FOR      SELECT      c.name
                  FROM      sysindexkeys ik
                        INNER JOIN
                              syscolumns c
                              ON ik.id = c.id AND ik.colid = c.colid
                  WHERE      ik.indid = @sintIndID
                  AND      ik.id = @intObjectID
                  ORDER BY ik.keyno

            OPEN curKeys

            FETCH      NEXT
            FROM      curKeys
            INTO      @strColumnName

            WHILE @@FETCH_STATUS = 0
            BEGIN

                  SET @strCommand = @strCommand + @strColumnName + ','

                  FETCH      NEXT
                  FROM      curKeys
                  INTO      @strColumnName

            END

            CLOSE curKeys
            DEALLOCATE curKeys

            SET @strCommand = LEFT(@strCommand, LEN(@strCommand) - 1) + ') WITH FILLFACTOR = ' + CONVERT(varchar(3), @tintFillFactor) + CHAR(10)

            FETCH      NEXT
            FROM      curIndexes
            INTO      @sintIndID,
                  @strIndexName,
                  @tintFillFactor

      END

      CLOSE curIndexes
      DEALLOCATE curIndexes

      EXEC (@strCommandDrop)
      EXEC (@strCommand)

GO

Basically, you run:

EXEC usp_DropIndexes <table_name>

before the batch inserts, and

EXEC usp_RecreateIndexes<table_name> afterwards
0
 

Author Comment

by:babybird
ID: 12508340
muzzy2003 - I can't quite get this statement to work:

FOR     SELECT     indid, name, OrigFillFactor
          FROM     sysindexes
          WHERE     id = @intObjectID
          AND     status & 0x800 = 0
          AND     status & 0x2 = 0
          AND     status <> 0
          AND     first <> 0

I did get it further along but am not clear on the how the status criteria and first criteria work as far as identifying the rows I need.  I get several rows for the system indexes starting with _WA_Sys when I remove the 'status' and 'first' criteria.  If I use the criteria as you have it entered, it eliminates all my rows. I hope this isn't a stupid question but I don't understand what 'status & 0x800' does.

This is the status column and the first column for the actual rows I want:
18450      0x28B609000100
0      0x6A0100000100
0      0x067501000100

The rows I don't want look like this for that particular table
10485856      0x000000000000
8388704      0x000000000000




0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 16

Expert Comment

by:muzzy2003
ID: 12513113
Take out the status <> 0 and you should get all but your first. My recollection of the 0x800 and 0x2 flags are that they are the primary key and unique flags for an index. The first one you list has both set, and is therefore (if my recollection is right) the primary key of the table. Are you sure you want to drop this as well? If not, then hopefully removing the status <> 0 will sort you out - I can't remember why I added it in the first place.
0
 
LVL 16

Expert Comment

by:muzzy2003
ID: 12664580
Do you need any further help with this problem? Thanks.
0
 

Author Comment

by:babybird
ID: 12665966
I might...I was able to get started but haven't gotten to complete it yet because some other things have taken priority but I hope to be hitting on it next week.  I do have one question offhand....security....we don't have dbo rights in production so I would assume we would have issues with dropping and recreating indexes within our proc unless we set something up special.  Do you have any thoughts on approaches to this?

Thanks for being so patient.  

0
 
LVL 16

Expert Comment

by:muzzy2003
ID: 12665996
No problem.

You need to be in the db_owner or db_ddladmin roles to drop and recreate the indexes. Since this is a batch process not an ad hoc thing, I would negotiate with the DBAs for this to run under a special login in the db_ddladmin role.
0
 
LVL 34

Expert Comment

by:arbert
ID: 12666377
Of course, the above proc that goes against the sysindexes table might not work in SQL2005....
0
 

Author Comment

by:babybird
ID: 12666413
arbert - why do you say that? What's changing?
0
 
LVL 16

Expert Comment

by:muzzy2003
ID: 12666453
No, true.

The system tables are being replaced in Yukon with different ones. They are implementing many of the old names as views for backwards compatibility, but there's no guarantee.
0
 
LVL 34

Expert Comment

by:arbert
ID: 12666846
"The system tables are being replaced in Yukon with different ones."

I wouldn't say replaced--there are a lot of changes.  There are never any guarantees when you use the system tables directly--that's why you use the Information_schema views or built in (documented) stored procedures.  Not only can an upgrade of SQL Server break things, but even a service pack/hotfix can cause problems.
0
 

Author Comment

by:babybird
ID: 12699047
I ended up using Muzzy2003's logic tweaked to used sp_helpindex results and metadata functions where possible. This hopefully will limit the impact if they make changes to sysindexes.  I greatly appreciate both of your help.  You saved me a great deal of time and the process works beautifully!!
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

864 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