How to choose a good index fill factor - SQL Server Questions Answered Blog. Q: I’ve been reading all the recent blog posts here about how some index keys can lead to fragmentation (and we have some that are GUIDs unfortunately).
I’d like to reduce the amount of fragmentation that occurs but I can’t change the database schema at all. Should I set a default fill factor for the instance? A: This answer ties in nicely with my previous blog post on Fragmentation from using snapshot isolation where one of the workarounds is to use an index fill factor. You are right that making use of index fill factors is a great way to avoid fragmentation if you’re unable to change the indexes themselves, but I would strongly advise against setting an instance-wide fill factor using sp_configure.
As background for other readers, an index fill factor instructs the Storage Engine to leave some free space in leaf-level index pages during index build or rebuild operations (note that the fill factor is NOT maintained during regular insert/update/delete operations). Spring cleaning your indexes - Part I - Kimberly L. Tripp.
First and foremost, happy spring!
I truly hope we’re on the path to summer (although who would know it here – we’re in Florida for SQL Connections and the weather is a bit chilly and it’s been raining off/on today – I hope this is short term (no, I don’t want to look at the forecast as I don’t want to jinx it :)). But, wherever you are – I hope you’re on your way to nicer weather and minimal cold weather (ok, I guess I only have warm wishes for the northern hem… for you southies - I hope your fall is lovely!!).
But, for everyone – now’s a good time to start thinking about cleaning out the [non-literal index] closet… and getting rid of some of those dusty indexes? Why/when should you get rid of some of your indexes? So, for this post, I’m going to target #1: are there any indexes that just aren’t being used at all… First, how do you know if your indexes are being used? Therefore, you don’t want to just run the following query and drop all of the indexes that aren’t being used.
Exceptions–what sys.dm_db_index_usage_stats doesn't tell you (Part II) - Joe Sack. Last November I blogged about how index usage stats don’t get updated when the associated index statistics (but not index) are used.
This post will describe another scenario that you should be aware of (the topic came up today in class while Kimberly was teaching – as we were trying to recall tricks to clearing stats for sys.dm_db_index_usage_stats)… Imagine that I’ve queried a specific table as follows: SELECT member_no, lastname, firstname, middleinitial, street, city, state_prov, country FROM dbo.member WHERE member_no = 1; If I check sys.dm_db_index_usage_stats for any reference to the member table, I’ll see the following: SELECT i.index_id, i.name, u.user_seeks, u.user_lookups, u.user_scans FROM sys.dm_db_index_usage_stats u INNER JOIN sys.indexes i ON u.object_id = i.object_id AND u.index_id = i.index_id WHERE u.object_id=object_id('dbo.member') This returns: Now let’s say that you have a weekly rebuild of specific indexes (for example): Why does this matter?
Exceptions - what sys.dm_db_index_usage_stats doesn't tell you - Joe Sack. I was thinking back to a conversation I had some time ago regarding cases when sys.dm_db_index_usage_stats does not get updated after an index is used for just the statistics associated with the indexed columns.
What makes this profession so interesting is that almost every tool we use has a "yes, but…" associated with it. What a DMV gives with one hand it might hide with another. While I recalled the conversation, I needed to double check who I had actually discussed it with. Admittedly, it’s a short list. After checking with him over email, I realized that conversation had been with MVP, Microsoft RD, MCM Greg Low. While the conversation stuck with me, I had never taken time to test it out.