July 10, 2014 at 7:22 pm
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
July 11, 2014 at 2:23 am
It doesn't look all that useful. However what time frame does that index usage stats data cover?
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
July 11, 2014 at 3:43 am
If the index isn't needed by the data load process, yeah, I'd drop and recreate it. But verify that it's not needed first.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 11, 2014 at 11:38 am
fillfactor = 80 is rather low. What did you base that on?
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 13, 2014 at 6:19 pm
Hi ...
Thanks for your response. Much appreciate it !.
hmm Do you mean the period of time of that index has been used ?
if that what you mean , that index will be used from 2 am until 6 am ( in writing process) ... I monitor again the user scan after 2 days --> 24 and the user updates : 2.282.679
Pls let me know your thought about it
thanks!!
July 13, 2014 at 6:22 pm
hi ..Thanks for your response ! much appreciate it ...
previously i used 90 as fillfactor but the page split is too fast so i lower it down become 80 ...
i am thinking to use Ola'script but i find it is too complicated to understand..moreover in that script ..it has also backup script which i dont need
July 13, 2014 at 7:29 pm
Hi, just curious, how big is this table?
If the table is very small, table scan is sometimes faster than seek...so, that's why the fragmentation is getting high again.
Experts, correct me if I'm wrong.
Regards,
SQLisAwe5oMe.
July 13, 2014 at 7:43 pm
hi too ... hmm the table has 12.135 rows and the number of page on that index is 51 ...
What do you think ?
Thanks 🙂
July 13, 2014 at 7:55 pm
In addition ...comparing with other tables ...this table is quite small ..other tables are hundred thousands of records ...
so it means this index is useless ? cause SQL prefer to use scan rather than index seek ??
if so , i can drop this index rather than maintain it ?
July 13, 2014 at 8:39 pm
Yes, that's my understanding. Index is useless since table scan is faster...in this particular scenario.
Regards,
SQLisAwe5oMe.
July 13, 2014 at 9:25 pm
OK Cool .. I will just drop it 🙂
Thanks so much !!
July 13, 2014 at 10:34 pm
Btw I have another issue after running the rebuild/reorganize index script ( once in 2 days ) ...the log files grows very fast ..we just added new harddisk a few days ago but now it is running out space again ... we also have doing the log transaction back up but it seems doesnt help much ...
Pls kindly advice 🙂
July 14, 2014 at 12:59 am
SQLisAwE5OmE (7/13/2014)
If the table is very small, table scan is sometimes faster than seek...so, that's why the fragmentation is getting high again.
Fragmentation has nothing to do with scans (other than slowing them down if they're from disk), so I don't know what you're trying to say here.
Analysing whether or not an index is useless does not involve looking at the table size, unless the table is under a page (8k) in size. 12000 rows is well over that. Analysing whether or not an index is useful involves finding the queries which use that index, testing them with the index and without and seeing whether the performance change is acceptable.
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
July 14, 2014 at 1:01 am
murnilim9 (7/13/2014)
Btw I have another issue after running the rebuild/reorganize index script ( once in 2 days ) ...the log files grows very fast ..we just added new harddisk a few days ago but now it is running out space again ... we also have doing the log transaction back up but it seems doesnt help much ...
Please see the thread you started specifically for this question - http://www.sqlservercentral.com/Forums/Topic1592060-391-1.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
July 14, 2014 at 5:52 am
Thanks Gail for correcting...I'm way off then...sorry.
Regards,
SQLisAwe5oMe.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply