Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Dropping indexes Expand / Collapse
Author
Message
Posted Thursday, September 13, 2012 7:46 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 11, 2013 11:07 PM
Points: 38, Visits: 169
Hi All,

I need to drop indexes on a table over 500mil records, I wandering is it better to drop the indexes the following sequence

non-clustered indexes -> primary -> clustered index

or the following

Clustered index -> primary -> non-clustered indexes

I would assume the first one will be slower?

Is my assumption correct?
Post #1359017
Posted Thursday, September 13, 2012 10:17 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 29, 2013 9:13 PM
Points: 20, Visits: 68
Yes. The first one will be slower compared to second since the first one will be doing FTS....I mean Full Table Scan and then based on the inetrnal ordering will delete the indexes.
Post #1359034
Posted Thursday, September 13, 2012 10:25 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:19 AM
Points: 1,101, Visits: 5,271
How many indexes are there in the table?
Why do you want to delete all the indexes?
Why do you want to delete the primary key as well?

I believe second one would be slower as dropping the CI would rebuild the NC indexes.
Post #1359037
Posted Thursday, September 13, 2012 10:43 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 11, 2013 11:07 PM
Points: 38, Visits: 169
4 non-clustered, 1 clustered and 1 primary key.

For bulk insert.

Post #1359039
Posted Friday, September 14, 2012 12:03 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 5:49 AM
Points: 397, Visits: 616
Catcha (9/13/2012)
4 non-clustered, 1 clustered and 1 primary key.

For bulk insert.



But do you think that dropping the index will save your time ?
Because as you mentioned that the table contains millions of records, so I guess to rebuild the indexes will be time taking.


--rhythmk
------------------------------------------------------------------
To post your question use below link

http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1359053
Posted Friday, September 14, 2012 1:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 11, 2013 11:07 PM
Points: 38, Visits: 169
Yes, with recreating the index I will use the following order

Non-clustered -> primary key -> clustered

So rebuilding of whole table will be performed last.

Thought?


Post #1359071
Posted Friday, September 14, 2012 3:49 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 5:49 AM
Points: 397, Visits: 616
Catcha (9/14/2012)
Yes, with recreating the index I will use the following order

Non-clustered -> primary key -> clustered

So rebuilding of whole table will be performed last.

Thought?




Again I would like to know that how many records you are going to insert.As in case the number records are very less than the existing records comparatively, it is recmmonded not to drop the indexes.

Note - The records(500 millions) as you mentioned in your table will break your head during index rebuild.So better to give it a try on development or test environment instead of doing on production.


--rhythmk
------------------------------------------------------------------
To post your question use below link

http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1359138
Posted Friday, September 14, 2012 9:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:30 PM
Points: 14,802, Visits: 27,275
Catcha (9/14/2012)
Yes, with recreating the index I will use the following order

Non-clustered -> primary key -> clustered

So rebuilding of whole table will be performed last.

Thought?




Nope. Backwards. If you create the clustered index last, it will recreate all the other indexes. Recreating, clustered first, then everything else.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1359375
Posted Sunday, September 16, 2012 10:49 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 11, 2013 11:07 PM
Points: 38, Visits: 169
Yes, that makes sense.
Post #1359967
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse