September 1, 2015 at 11:40 pm
Dear Jeff,
In that case, how to find unused indexes.
September 3, 2015 at 7:09 pm
coolchaitu (9/1/2015)
Dear Jeff,In that case, how to find unused indexes.
You can still use the information in sys.dm_db_index_usge_stats to guide you. You just don't want to do it based [font="Arial Black"]only [/font]on a 3 zeros return as has been suggested. You have to check to see if an index is unique (basically, is rarely able to be dropped without some serious research into PK, FK, AK, etc, etc) and what it's actually being used for if it's not unique. Before I dropped any index, I would simply disable it for a month (to make sure it's not used for critical month-end reporting, etc).
--Jeff Moden
September 4, 2015 at 4:21 am
Thanks for the suggestion sir
September 5, 2015 at 1:47 am
Dear Jeff sir,
Could you please let me know how to check how often an index is inserted
September 5, 2015 at 2:29 am
Dear Jeff sir,
I have attached information of the index whose fragmentation increased to 95% the next day, after rebuilding with fillfactor=90. Shall i try rebuilding with fillfactor=80.
September 5, 2015 at 8:18 pm
coolchaitu (9/5/2015)
Could you please let me know how to check how often an index is inserted
An index is ALWAYS inserted to whenever a row is inserted into the table it is attached to, unless the index is disabled (true for all of the following except clustered indexes which will deactivate the table if they're disabled). In fact, ALL indexes are ALWAYS inserted to any time the table is made to suffer an insert.
Clustered indexes are also affected for EVERY update of the table. Non-clustered indexes are only affected by updates on the columns they contain.
As with inserts, deletes ALWAYS affect EVERY index attached to the table deleted from.
One relatively "easy" method to determine how often a given index is affected is to capture the readings for it from sys.dm_db_index_usage_stats at the same time every day for a week (or by hour if you want to know where the peaks are in a day) and then compare the differences in the readings.
coolchaitu (9/5/2015)
I have attached information of the index whose fragmentation increased to 95% the next day, after rebuilding with fillfactor=90. Shall i try rebuilding with fillfactor=80.
I'd first find a way to measure how long inserts are taking right after the 90 rebuild and then try 80 and do the same measurement as well as keeping my eye on the next day fragmentation. If the problem continues, then I might try 70 but I personally wouldn't go any lower than that. If the problem still persisted to any great extent at 70, then I'd resign myself to the fact that it's a table that takes a whole lot of inserts and this particular index is going to need to be defragged more often than others.
There's a bit of a catch in all of this, though. The reads for this index pale in comparison to the writes and in the area of usage, there are only about 5K in seeks where there are 134K in scans. I'd try to find the queries that used the index (usually easier said than done but sometimes very worth it) to see if a more effective index could be created because that's a whole lot of scans compared to the seeks. The only saving grace in index scans is that there are more rows per page in such indexes than there are in the data row pages of the clustered index (table). That might make it a useful thing still, but I'd want to know for sure.
--Jeff Moden
September 7, 2015 at 5:12 am
Dear Jeff sir,
Thanks for contributing and helping beginner level people like me. Should the scans be lesser than seeks?
Actually, I did not understand the below statement:
" If the problem still persisted to any great extent at 70, then I'd resign myself to the fact that it's a table that takes a whole lot of inserts and this particular index is going to need to be defragged more often than others."
September 7, 2015 at 7:16 am
coolchaitu (9/7/2015)
Dear Jeff sir,Thanks for contributing and helping beginner level people like me. Should the scans be lesser than seeks?
Actually, I did not understand the below statement:
" If the problem still persisted to any great extent at 70, then I'd resign myself to the fact that it's a table that takes a whole lot of inserts and this particular index is going to need to be defragged more often than others."
The statement says pretty much what I mean. If you end up getting down to a Fill Factor of 70 on the table, then don't go any further down. Instead, defrag it more often because it needs it more often.
As for "should scans be lesser than seeks", it truly depends but I'd have to say yes in most cases. For example, a single scan of an index for a given batch query may be better than the batch query doing 60,000 seeks. On the other hand, a query meant to find just one or a small handful of related rows should almost never have to do a full index scan. It should be doing a seek or two. There are other scenarios but wanted to show you that the content of sys.dm_db_index_usage_stats needs to be interpreted by a human rather than handled on a wholesale basis by some form of automation. In plain English, it should only be used as one of many clues as to the health of your queries and related indexes.
--Jeff Moden
September 7, 2015 at 8:55 am
Dear Jeff sir,
Thanks a million for the wonderful explanation
September 7, 2015 at 8:59 am
You're welcome and thank you for the kind feedback.
--Jeff Moden
September 10, 2015 at 11:43 pm
Dear Jeff Sir, Good Morning
I tried rebuilding the index with fillfactor=80 yesterday night. I checked the fragmentation and it is 98%. I will rebuild the index with fillfactor=80 tonight.
September 11, 2015 at 6:46 am
Not sure which index this relates so, but reading this thread I have seen mention of two. The original was
CREATE NONCLUSTERED INDEX [idx_tblJobbooking_C3_C7] ON [dbo].[tblJobBooking]
(
CustomerMobileNo ASC, CustomerContactMobileNo ASC
)
INCLUDE
(
JobID], PickUpTime, CabRegistrationNo
)
but later there was an attached image
http://qa.sqlservercentral.com/Forums/Attachment17737.aspx
which I think indicates
INDEX = IX_tblGPSDataDetail_DeviceID_GPSDateTime ON dbo.tblGPSDataDetails
(
DeviceID {udt_DeviceID 4}
, GPSDateTime {varchar 50}
, DataReceviedDateTime {udt_date 8}
)
INCLUDE
(
MeterStatusID {udt_tinyId 1}
)
The usage stats has 2,501,264 rows, 139,608 reads and 122,214,002 writes
Assuming that the latest discussion refers to the index in the attached image, or one like it, my guess is that the rows are being updated, in situ, with a GPSDateTime and/or DataReceviedDateTime that is changing (e.g. representing "current time"). If I have guessed that correctly then presumably the effected rows are constantly being "moved" further down the index. I guess that is what is causing the fragmentation but, again assuming I have guessed all that correctly, I have no idea what sort of solution to suggest!
September 11, 2015 at 10:26 am
You are correct. It refers to the index in the attached image. Its definition is as below:
INDEX = IX_tblGPSDataDetail_DeviceID_GPSDateTime ON dbo.tblGPSDataDetails
(
DeviceID {udt_DeviceID 4}
, GPSDateTime {varchar 50}
, DataReceviedDateTime {udt_date 8}
)
INCLUDE
(
MeterStatusID {udt_tinyId 1}
)
I tried rebuilding the index with fillfactor=80 yesterday night. I checked the fragmentation and it is 98%. I will rebuild the index with fillfactor=70 tonight.
September 11, 2015 at 11:04 am
coolchaitu (9/11/2015)
I tried rebuilding the index with fillfactor=80 yesterday night. I checked the fragmentation and it is 98%. I will rebuild the index with fillfactor=70 tonight.
If GPSDateTime and/or DataReceviedDateTime are being continuously updated then I don't think changing the Fill Factor will help. Those columns are being set to values that do not exist in the index and thus are moving to new pages in the index and fragmenting it. I don't think reducing the fill factor will help ... but maybe it doesn't actually matter (to performance)?? Yes there is a lot of fragmentation, but it may just be an extension page on every page. Perhaps once that extension page is created the fragmentation doesn't get any worse, its just that (say) 100% of the index pages have been split ... but maybe they are not getting split again, or they are but shortly there afterwards ALL the rows in the index page have been moved to the new extension page and there are no longer any rows in the original before-split page. (That sounds bad, but maybe SQL removes / flags / ignored the now-empty page so, in effect, there is never more than only one extension)
Hopefully someone who has an idea how to solve this will be along shortly 🙂
September 11, 2015 at 12:27 pm
Kristen-173977 (9/11/2015)
coolchaitu (9/11/2015)
I tried rebuilding the index with fillfactor=80 yesterday night. I checked the fragmentation and it is 98%. I will rebuild the index with fillfactor=70 tonight.If GPSDateTime and/or DataReceviedDateTime are being continuously updated then I don't think changing the Fill Factor will help. Those columns are being set to values that do not exist in the index and thus are moving to new pages in the index and fragmenting it. I don't think reducing the fill factor will help ... but maybe it doesn't actually matter (to performance)?? Yes there is a lot of fragmentation, but it may just be an extension page on every page. Perhaps once that extension page is created the fragmentation doesn't get any worse, its just that (say) 100% of the index pages have been split ... but maybe they are not getting split again, or they are but shortly there afterwards ALL the rows in the index page have been moved to the new extension page and there are no longer any rows in the original before-split page. (That sounds bad, but maybe SQL removes / flags / ignored the now-empty page so, in effect, there is never more than only one extension)
Hopefully someone who has an idea how to solve this will be along shortly 🙂
I was looking for the reference where I read this (I am trying to recall something from Paul Randal), but - the defrag process only works up to the point where it can improve performance. If the item you're trying to defrag is less than a certain threshold (1 extent if I am remembering correctly), the entire thing will be loaded into memory anyway, so the defragger just gives up and doesn't do anything (there's not point in defragging it).
So I think to some of the points made earlier - is there a specific performance issue you're trying to solve or are you focusing on the percentage only?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply