August 7, 2014 at 8:20 am
Shurgenz (8/4/2014)
when creating the new index on a large table that already has some indexes, the new index could take a long time when tehre is alredy index with indexed columns in it that will be in a new index. in such case SQL Server use that index bookmarking to the clustered index taking from there the columns needed for the new, which do not exists in the index.for example, table T is declared as T (ID int, A char, B char, C Char)
it has say, a clustered index CLIX_ID on ID, nonclustered IX_A on A
if You are creating the new index as INDEX IX_A_inc_B on A include(B), it scans IX_A, taking A from it, and bookmarks CLIX_ID to take B. The good way to create IX_A_inc_B faster is to drop IX_A first, then create IX_A_inc_B and then recreate IX_A
in this way first it would scan CLIX_ID when creating IX_A_inc_B and then IX_A_inc_B to create IX_A
in my tests i've got the execution time reduced from 3 hours to 10 minutes
IN this case, why would you create more duplicate indexes? If the index needed is just a matter of adding a column or columns to the definition of an index, why not alter the existing index? Creating multiple indexes with these kinds of definitions will indeed slow things down. They are duplicate indexes and should be re-examined for better implementation.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 9, 2024 at 10:33 pm
Shurgenz (8/4/2014)
when creating the new index on a large table that already has some indexes, the new index could take a long time when tehre is alredy index with indexed columns in it that will be in a new index. in such case SQL Server use that index bookmarking to the clustered index taking from there the columns needed for the new, which do not exists in the index. for example, table T is declared as T (ID int, A char, B char, C Char) it has say, a clustered index CLIX_ID on ID, nonclustered IX_A on A if You are creating the new index as INDEX IX_A_inc_B on A include(B), it scans IX_A, taking A from it, and bookmarks CLIX_ID to take B. The good way to create IX_A_inc_B faster is to drop IX_A first, then create IX_A_inc_B and then recreate IX_A in this way first it would scan CLIX_ID when creating IX_A_inc_B and then IX_A_inc_B to create IX_A in my tests i've got the execution time reduced from 3 hours to 10 minuteswowww what a great idea!!! I tried mine ...dropping all the indexes first ... the duration for creating the index from 8 hours become 20 minutes Thankssss 🙂
In theory this is a great piece of knowledge. I just want to simply point out, before anyone decides to drop an index, that if you are on a production/live system then dropping an index for even a few minutes can have immense performance consequences to your system. So this may not be an option for everyone.
----------------------------------------------------
May 9, 2024 at 10:34 pm
Shurgenz (8/4/2014)
when creating the new index on a large table that already has some indexes, the new index could take a long time when tehre is alredy index with indexed columns in it that will be in a new index. in such case SQL Server use that index bookmarking to the clustered index taking from there the columns needed for the new, which do not exists in the index. for example, table T is declared as T (ID int, A char, B char, C Char) it has say, a clustered index CLIX_ID on ID, nonclustered IX_A on A if You are creating the new index as INDEX IX_A_inc_B on A include(B), it scans IX_A, taking A from it, and bookmarks CLIX_ID to take B. The good way to create IX_A_inc_B faster is to drop IX_A first, then create IX_A_inc_B and then recreate IX_A in this way first it would scan CLIX_ID when creating IX_A_inc_B and then IX_A_inc_B to create IX_A in my tests i've got the execution time reduced from 3 hours to 10 minuteswowww what a great idea!!! I tried mine ...dropping all the indexes first ... the duration for creating the index from 8 hours become 20 minutes Thankssss 🙂
In theory this is a great piece of knowledge. I just want to simply point out, before anyone decides to drop an index, that if you are on a production/live system then dropping an index for even a few minutes can have immense performance consequences to your system. So this may not be an option, unfortunately.
----------------------------------------------------
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply