dropping and recreating indexes dynamically

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?
babybirdAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arbertCommented:
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
muzzy2003Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
babybirdAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

muzzy2003Commented:
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
muzzy2003Commented:
Do you need any further help with this problem? Thanks.
0
babybirdAuthor Commented:
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
muzzy2003Commented:
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
arbertCommented:
Of course, the above proc that goes against the sysindexes table might not work in SQL2005....
0
babybirdAuthor Commented:
arbert - why do you say that? What's changing?
0
muzzy2003Commented:
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
arbertCommented:
"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
babybirdAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.