March 8, 2010 at 1:09 am
Hi All,
i used sql dmv views to check index usage, i found that some indexes are not used at all
i.e: (scans=0,lookups=0,seeks=0) meantime those indexes are updated extensively.
I thought that will lower performance so i tried dropping those indexes, it worked for some of them while other gave me error message:
Drop index failed.
Could that be because they are being updated while i am trying to drop them and is there any solution for that.
Thanks in advance
Nader
March 8, 2010 at 2:43 am
yes...if its a production server then u must wait for the maintenance window...other wise change the database to single user mode and drop the index..but before dropping the index u must save the index details..
this may help..
http://www.sqlservercentral.com/articles/Indexing/68079/
Regards,
[font="Verdana"]Sqlfrenzy[/font]
March 8, 2010 at 3:13 am
Please don't automatically drop indexes that show low seeks/scans. That DMV is cleared whenever the DB is started, so if SQL is restarted often that will not show complete information. What's going to happen if the indexes you dropped are used only during month-end processing?
You can drop an index at any time, neither the DB nor the server have to be in single user mode. If the index is in use then the DROP INDEX statement simply waits for currently running queries to finish.
What was the full message for the drop index failed? There will have been a reason given.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 8, 2010 at 3:34 am
GilaMonster (3/8/2010)
Please don't automatically dropYou can drop an index at any time, neither the DB nor the server have to be in single user mode. If the index is in use then the DROP INDEX statement simply waits for currently running queries to finish.
you are right..but we follow this procedure..a kind of best practice..
Regards,
[font="Verdana"]Sqlfrenzy[/font]
March 8, 2010 at 3:44 am
Sqlfrenzy (3/8/2010)
GilaMonster (3/8/2010)
Please don't automatically dropYou can drop an index at any time, neither the DB nor the server have to be in single user mode. If the index is in use then the DROP INDEX statement simply waits for currently running queries to finish.
you are right..but we follow this procedure..a kind of best practice..
It's a good idea to do any form of schema modifications during a maintenance period. However if it does need to be done while users are accessing the system, setting the DB to single user mode adversely impacts users, whereas the drop index, by itself, won't.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 8, 2010 at 3:47 am
Thanks for ur reply.
Yes i am doing this on production db.
i tried doing it first from mangment studio, it gave me time out so i generated sql script but it kept running for minutes so i was afraid it causes any slowliness on server so i stopped it.
for changing to single user i am not sure i could do that on production db.
Thanks
March 8, 2010 at 3:49 am
certainly...that's y we plan our day to run maintenance activity..also a drop statement on large table with around 1000 transactions/sec is also not a good idea...
Regards,
[font="Verdana"]Sqlfrenzy[/font]
March 8, 2010 at 3:50 am
Thanks Gail,
U said a very important comment about dmv views which is when db or sql is restarted all this is cleared and actually one of my colleagues was going to fall in this problem but i warned him and we kept monitoring for a long period till we decided its not used.
The thing i needed to confirm is droping an unused index has no impact on performance
March 8, 2010 at 3:57 am
nadersam (3/8/2010)
Thanks for ur reply.Yes i am doing this on production db.
i tried doing it first from mangment studio, it gave me time out so i generated sql script but it kept running for minutes so i was afraid it causes any slowliness on server so i stopped it.
for changing to single user i am not sure i could do that on production db.
Thanks
don't change it single_user mode...u should schedule it after having discussion with your seniors and client...
Regards,
[font="Verdana"]Sqlfrenzy[/font]
March 8, 2010 at 3:59 am
nadersam (3/8/2010)
The thing i needed to confirm is droping an unused index has no impact on performance
No impact, providing the index really is unused. Make sure that you've monitored for long enough and tested on the dev system to ensure that there really is no impact.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 8, 2010 at 4:02 am
nadersam (3/8/2010)
for changing to single user i am not sure i could do that on production db.
No. Don't even consider changing a production DB to single user mode. It will prevent anyone from working until you change it back.
Index maintenance should, unless absolutely necessary, during a maintenance period.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 8, 2010 at 4:08 am
Thanks All,
ur replies were very helpfull.
Best Regards
Nader
March 9, 2010 at 1:28 am
Dear All,
Yesterday i stayed up for a long time and finally i was able to find out the reason i couldnt drop index.
The application is using a view containing the table i am trying to drop one of its indexes.
The user has application has executed its query already but user didn't close his screen so data is still cashed in memory.
The strange thing is that in this view we are using with (nolock) hint on all involved tables but eventhough the whole table is locked!!!!.
I found out that by running the drop index script then checking for blocking on server, i found that the query is the statement thats blocking and the blocking object is the table itself.
pls note the at that time view was not running but cashed in memory because i checked using profiler too.
March 9, 2010 at 1:41 am
nadersam (3/9/2010)
The user has application has executed its query already but user didn't close his screen so data is still cashed in memory.
Just cached in memory wouldn't affect anything. There has to be an open transaction for this to cause a problem
The strange thing is that in this view we are using with (nolock) hint on all involved tables but eventhough the whole table is locked!!!!.
NoLock only removes shared locks. Exclusive and Schema locks are still taken and a held schema lock (held by anything reading the table) will stop indexes been dropped.
Also... http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 9, 2010 at 1:50 am
i ran dbcc opentran to check if any open transactions, sql server stated there are no active transactions
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
if i run sp_block_info stored procedure here is what i get
OBJECTMPlusProduction731305815Sch-M526333DROP INDEX [IX_LabSampleCollect_1] ON [dbo].[LabSampleCollect] WITH ( ONLINE = OFF ) DROP INDEX [IX_LabSampleCollect_1] ON [dbo].[LabSampleCollect] WITH ( ONLINE = OFF ) 53select * from testdiagnostic
pls note i already have ran the view and currently i am doing that on my local machine to make sure no one else running anything.
is there anything else to check or try.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply