Generate Index only Script for Whole db

Question

Generate Index only Script for Whole db

Admingod

SSCertifiable

Points: 5997
More actions
March 9, 2020 at 5:30 pm

#3732560

Is there a way to generate only the indexes for an entire DB. If I right click the DB and go to tasks>Generate scripts I can check create indexes but I always get the tables from Advanced option, so i just need all indexes with drop and create statement without table script. The reason I'm needing only the indexes is we are migrating from non SQL Server DB to SQL Server and we are using SSIS packages. With a fast load in the package the data transfer is extremely fast with no indexes but slower when indexes exists are the target tables. So after the data move we want to then apply all the required indexes.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 1

Create the indexes as you need them in SQL Server. You can then disable the NC indexes prior to loading the data - and rebuild the indexes after the data has been loaded.

  Create Procedure [dbo].[disableNCIndexes]
        @objectName sysname     -- include Schema in objectName (e.g. dbo.Table)
     As

/* ===========================================================================================
   Author:      Jeff Williams
   Created:     08/24/2017
   Description: Disables all Non-Clustered indexes for the specified object/table 
                
                This procedure is used during load processes to disable the non-clustered
                indexes on the object.  This can improve performance for the load process
                when you are loading a smaller subset of data into a very large table or
                when you have lots of indexes with multiple columns included.

                If you utilize this procedure - you then need to rebuild all indexes once
                the data has been loaded using ALTER INDEX ALL ON {object} REBUILD;

   Called From:
        SSIS or load procedures
        
   Example Call:
        Execute dbo.disableNCIndexes @objectName = 'supermart.v_demographic_denorm';

   Revision History
     Date       Edited By       Change
     ---------- --------------- --------------------------------------------------------------
     08/24/2017 Jeff Williams   Created
   =========================================================================================== */

    Set Nocount On;

Declare @objectId int
      , @indexName sysname
      , @schemaName sysname
      , @command nvarchar(max);

    Set @objectId = object_id(@objectName);

 --==== Loop through indexes for this object
Declare indexes Cursor Local Static 
    For 
 Select o.Name As ObjectName
      , object_schema_name(o.[object_id]) As SchemaName
      , i.Name As IndexName
   From sys.objects             o
  Inner Join sys.indexes        i On i.object_id = o.object_id
  Where o.type = 'U'
    And i.index_id > 1
    And o.object_id = @objectId;

   Open indexes;
  Fetch Next From indexes Into @objectName, @schemaName, @indexName;

  While @@fetch_status = 0
  Begin;

 --==== Build and execute the command to disable the index
    Set @command = concat('ALTER INDEX ', quotename(@indexName), ' ON ', quotename(@schemaName), '.', quotename(@objectName), ' DISABLE;');
Execute sp_executeSql @command; Print 'Executed: ' + @command;

  Fetch Next From indexes Into @objectName, @schemaName, @indexName;
    End;

 --==== Close/Deallocate the cursor
        Close indexes;
        Deallocate indexes;

After the load has completed - rebuild the indexes:

ALTER INDEX ALL ON dbo.MyTable REBUILD;

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Admingod SSCertifiable Points: 5997 More actions · Answer 2

Thanks! Would this work for whole db rather than individual table? How about Primary keys and clustered index?

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 3

Admingod wrote:

Thanks! Would this work for whole db rather than individual table? How about Primary keys and clustered index?

You've got to be really careful about disabling any PK or UNIQUE indexes. From the following BOL article...

https://docs.microsoft.com/en-us/sql/relational-databases/indexes/disable-indexes-and-constraints?view=sql-server-ver15

When disabling a unique index, the PRIMARY KEY or UNIQUE constraint and all FOREIGN KEY constraints that reference the indexed columns from other tables are also disabled. When disabling a clustered index, all incoming and outgoing FOREIGN KEY constraints on the underlying table are also disabled. The constraint names are listed in a warning message when the index is disabled. After rebuilding the index, all constraints must be manually enabled by using the ALTER TABLE CHECK CONSTRAINT statement.

There are a ton of "Before you begin" warnings on the same page.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 4

I'm with Jeff. Pick and choose which of these indexes you drop & recreate. There are many, many tests out there that show inserting into a clustered table can be (not guaranteed, your mileage may vary, testing needed, trust but verify, all the caveats and warnings) much faster than inserting into a heap.

As far as the rest goes, I strongly advocate for everyone to use source control to manage the code of your databases. Yes, databases are code. You can then use the source code of the indexes as a means of easily automating enabling/disabling or dropping & recreating, as needed.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 5

I've done many of those tests myself... if you can get Minimal Logging to come into play, the inserting into a Clustered Index is MUCH faster than inserting into a heap and then building the Clustered Index. If you have non-clustered indexes present, then pretty much not despite what the documentation says. I wish the people that did the documentation would have posted the code they used to test with... that's if they actually tested it and aren't just going by some spec that someone wrote.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Admingod SSCertifiable Points: 5997 More actions · Answer 6

Thanks! I ran some tests first with clustered index in-place and second with clustered index dropped, load the data and add the clustered index back . Noticed second one is faster. But still testing with more tables.

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 7

Admingod wrote:

Thanks! I ran some tests first with clustered index in-place and second with clustered index dropped, load the data and add the clustered index back . Noticed second one is faster. But still testing with more tables.

I can't see what you did. Which Recovery Model where you in, how many rows did you have in the table, and did you follow the rules for "Minimal Logging"?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Admingod SSCertifiable Points: 5997 More actions · Answer 8

Simple recovery model. Approx 15 million rows. Unless bulk-logged is faster than simple?

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 9

Admingod wrote:

Simple recovery model. Approx 15 million rows. Unless bulk-logged is faster than simple?

Simple and Bulk Logged are the same for this type of thing. You didn't answer the rest of the questions, though. Did you follow the rules for "Minimal Logging"?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)