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

deleting INdexes Expand / Collapse
Author
Message
Posted Monday, March 17, 2014 9:02 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:35 AM
Points: 2,598, Visits: 3,884
Hi Experts,

What are precautions and factors need to consider while deleting indexes. We have found more than 500 unused indexes in one of our db and planning to get rid of those.


Please advise.
Post #1551786
Posted Monday, March 17, 2014 9:49 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:55 AM
Points: 5,989, Visits: 12,927
first be sure they are not being used, so remove in a non-prod environment first and test thoroughly.

Avoid removing non-clustered indexes defined as unique. They may not show up as having being used to satisfy queries, but they may be being used instead to enforce a unique constraint.

I presume you are not removing any clustered indexes?


---------------------------------------------------------------------

Post #1551817
Posted Monday, March 17, 2014 10:18 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 29, 2014 1:14 PM
Points: 1,194, Visits: 2,217
Also make sure your script those indexes \ backup the database before dropping them. If you have issues later bcs of dropping, then they will come handy.

--
SQLBuddy
Post #1551837
Posted Monday, March 17, 2014 11:41 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 3:23 PM
Points: 15,664, Visits: 28,062
I think SQLBuddy hit the most important point. Backup all the indexes as scripts first. Dropping or adding an index is a pretty trivial event, but knowing exactly which index to add, was it unique, did it have a fill factor or a filter or INCLUDE columns... Backups are the way to go with that.

----------------------------------------------------
"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 #1551858
Posted Monday, March 17, 2014 11:51 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 9:31 AM
Points: 3,910, Visits: 7,138
And here is a pain-free way to script them all out and store them into a table (for safe keeping)
http://www.sqlservercentral.com/scripts/Indexes/101725/


______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1551862
Posted Monday, March 17, 2014 6:50 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:35 AM
Points: 2,598, Visits: 3,884
Thanks George,sqlbuddy,Grant jessie
Post #1551971
Posted Monday, March 17, 2014 6:51 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:35 AM
Points: 2,598, Visits: 3,884
Thanks George,sqlbuddy,Grant & Jessie
Post #1551972
Posted Tuesday, March 18, 2014 7:31 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 12:15 PM
Points: 80, Visits: 252
I would check the uptime of the server. Lot's of indexes might seem unused shortly after a reboot. I'm usually hesitant to do any index changes like this until business communicated cycles have completed.

Depending on what you are looking at (0 reads vs. just a few reads), I also check if there are any uses in the plan cache.

If the index is under source control...sweet. If not, get it in there (or script it out), I prefer using Red Gate SQL Source Control and Git

I would then disable the index and eventually by some business approved length of time, drop.

I mention these steps here http://www.codepimp.org/2014/02/source-controlling-indexes/ In order to get SQL Source Control to detect the an index being disabled I had to rename it first.


Why is it that people who can't take advice always insist on giving it? - James Bond, Casino Royale
Post #1552170
Posted Tuesday, March 18, 2014 7:43 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:35 AM
Points: 2,598, Visits: 3,884
Thanks SQL4GNT
Post #1552179
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse