July 14, 2014 at 6:31 pm
hmm I have dropped the index anyway (moreover, I noticed the statistic of the unused index is quite convincing )...
btw how do we find out which stored proc that use that index ? I really have no idea about it because we have a lot of SP ...
Pls kindly response
Thanks a lot ...
July 14, 2014 at 7:03 pm
murnilim9 (7/10/2014)
Hi All,At the moment I am tuning the indexes with high fragmentation ..I created a maintenance plan for rebuild /reorganize ( set the logic for fragmentation above 50% then I will do rebuild , otherwise I will reorganize ... fill factor = 80 . I also filtered by the number of page count.
noticed there is 1 index which is fragmented very quickly starting from 2 am until 7 am .
I did rebuild / reorganize every 2 am ( after log backup at 12 am)
I believe after rebuild it will become 0% but after 3 hours it will become 80% I guess...
I check the unused index data ( using the script ) and for that index i got this data :
User_Seek : 0
User_Scans : 16
User_lookup : 0
User_updates : 1.128.932
I wonder that I should just drop this index or keep maintain it with rebuild it AGAIN after a few hours later ...
Please kindly advice...thanks heaps
Cheers,
Me
Hi, Just posted this in the 2012 section but same applies. Ask your self the below question before you start setting up Index rebuild maintenance plans. DBA's have noted in the past that this can be one of the main causes of bring a SQL server offline.
Before you start reorg'ing or rebuilding your indexes, ask yourself:
Are we actually having any performance issues with the database? What have your users said?
What is the profile of the data in the table? How many inserts / deletes / updates?
When were the statistics last updated on the tables?
If rebuilding, when can you do this? Controlled outage? Online or Offline rebuild? How will this affect the disk space on the server?
Have a read of these two articles before you proceed as it requires a bit more thought other than which option you should choose.
http://www.mssqltips.com/sqlservertip/2361/rebuilding-sql-server-indexes-using-the-online-option/
cheers,
Shaun
July 14, 2014 at 9:44 pm
oh thanks so much for your links..very useful information !
i am performing offline index since the edition of my SQL is Standard which will not support online index.
I noticed the duration of performing rebuild/reorganize is around 20 - 30 seconds for 1 database ( I have just tested 1 database ) ..To be honest , I was a bit worried of table lock but I guess it was a very short time ..so I think it will be alright ....
any thought ?
Thanks!
July 15, 2014 at 1:11 am
murnilim9 (7/14/2014)
hmm I have dropped the index anyway (moreover, I noticed the statistic of the unused index is quite convincing )...btw how do we find out which stored proc that use that index ? I really have no idea about it because we have a lot of SP ...
Pls kindly response
Thanks a lot ...
There is no way to correlate between the missing index information and a particular query. Instead, you can query the plan cache to look at missing index information in the execution plans. I have a query posted on my blog [/url]that will get you started there.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
July 16, 2014 at 9:24 pm
Thanks a lot on your response !
Btw I performed rebuild indexes on certain databases ..maybe around 22 indexes need to be rebuilt and it failed and showed an error :
/*********************************************..." failed with the following error: "Transaction (Process ID 87) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
at the moment there are still 10 indexes that need to be rebuilt...
I am thinking to drop some unused indexes and filter by page_count which is above 300
Any idea about this issue ?
Thank you
Cheers
July 16, 2014 at 10:45 pm
murnilim9 (7/16/2014)
Thanks a lot on your response !Btw I performed rebuild indexes on certain databases ..maybe around 22 indexes need to be rebuilt and it failed and showed an error :
/*********************************************..." failed with the following error: "Transaction (Process ID 87) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
at the moment there are still 10 indexes that need to be rebuilt...
I am thinking to drop some unused indexes and filter by page_count which is above 300
Any idea about this issue ?
Thank you
Cheers
Be a bit careful about dropping seemingly unused indexes. While they might not be used by queries, they might be used to enforce unique constraints. Dropping those could be detrimental to data integrity.
--Jeff Moden
July 16, 2014 at 10:53 pm
Jeff Moden (7/16/2014)
murnilim9 (7/16/2014)
Thanks a lot on your response !Btw I performed rebuild indexes on certain databases ..maybe around 22 indexes need to be rebuilt and it failed and showed an error :
/*********************************************..." failed with the following error: "Transaction (Process ID 87) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
at the moment there are still 10 indexes that need to be rebuilt...
I am thinking to drop some unused indexes and filter by page_count which is above 300
Any idea about this issue ?
Thank you
Cheers
Be a bit careful about dropping seemingly unused indexes. While they might not be used by queries, they might be used to enforce unique constraints. Dropping those could be detrimental to data integrity.
thanks for your response...
How do I check that those indexes might be used to enforce unique constraints ?
Thanks
July 17, 2014 at 1:14 am
murnilim9 (7/16/2014)
Jeff Moden (7/16/2014)
murnilim9 (7/16/2014)
Thanks a lot on your response !Btw I performed rebuild indexes on certain databases ..maybe around 22 indexes need to be rebuilt and it failed and showed an error :
/*********************************************..." failed with the following error: "Transaction (Process ID 87) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
at the moment there are still 10 indexes that need to be rebuilt...
I am thinking to drop some unused indexes and filter by page_count which is above 300
Any idea about this issue ?
Thank you
Cheers
Be a bit careful about dropping seemingly unused indexes. While they might not be used by queries, they might be used to enforce unique constraints. Dropping those could be detrimental to data integrity.
thanks for your response...
How do I check that those indexes might be used to enforce unique constraints ?
Thanks
Check the index to see if it's unique.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply