Oh my goodness, I have been buried and learning tons about Indexing. Something super cool that I learned was how to know if you need to adjust your fillfactors on your indexes. My whole DBA career I have usually set it 85 and forgotten it. I learned that I can check my CommandLog and see how often that index is being rebuilt. I am currently using Ola and when my indexes are rebuilt for maintenance (rebuilt nightly in this case), it is all logged to a table named CommandLog in the master database. If an index is rebuilt everyday, adjust the fillfactor down (75) so that it will rebuild less often. If it nearly never rebuilds, adjust the fillfactor up (90) so it gets rebuilt once in a while and to avoid wasting space in an index. Here is the query I am using to see how often an index has rebuilt in the last 10 days.
SELECT Command, COUNT(StartTime) AS Rebuilt
WHERE CommandType LIKE '%Index%' AND StartTime > GETDATE()-10
GROUP BY Command
ORDER BY Rebuilt DESC
When I see ones that have a rebuilt number of 10, 9 or 8, I know those are rebuilding nearly everyday. I will adjust their fillfactors down so that I don’t rebuild as often. My sweet spot right now is once a week, but that doesn’t work for every index (or environment). This is where the art part comes into the DBA world and I have to think about what is best for the system. If I have already adjusted something down to 75 and it is still rebuilding frequently, I will adjust that number down more (65, then 55), and back up if it is too low. I also evaluate how much that index is used and how important it is to keep healthy. If it is hardly ever used, do I want to waste fillfactor space?
Indexing is absolutely an art, but now I have a new brush for my kit by being able to query the CommandLog.
We have a lovely Availability Group that holds A LOT of data that is broken into partitions. We have 42 partitions and they are usually moving information around daily between them. The index rebuilds on them were making our logs HUGE because the the Availability Group was taking too long to catch up, we tried both Synchronous and Asynchronous mode. We would see all kinds of errors. We were doing horrible things like auto shrinking our transaction log after the indexing finished and ignoring alarms during the time the database was rebuilding. We had requested more and more space from our storage team and sometimes the job wouldn’t even finish because it ran out of space. Our first idea was to split out the index rebuilds so that we could do one partition at a time.
It looks like this:
ALTER INDEX IndexName
REBUILD PARTITION = 42
By splitting this out, we were able to get the job to finish, but with tons of alarms, and log growth.
Then we had a thought, maybe the server is just spinning too fast and we need to give the Availability Group time to catch up. So we added some simple waits in between each step.
WAITFOR DELAY '00:10';
As an example this is in minutes and will wait for 10 minutes before running the next step.
It has now been a quiet week and we are looking forward to the Sound of Silence.
Now the funny thing about it, not a story without it, but the story is mine and it ended just fine…Happily Ever After
Today I have a Vendor Application that has WAY too many indexes and they need to rebuild them all tonight in a very tight window. I am disabling all the unused indexes to allow for a faster rebuild and then will rebuild the disabled indexes tomorrow night after all the heavy work is done. The Vendor is nervous about us disabling indexes that haven’t been used in over a month. The good news is that you can simply disable them and then rebuild them if they are needed. I know the rebuild will take some time, but we have all agreed that if they haven’t been used in over a month, they most likely won’t be used in the next 24 hours. Now the good part. Running this t-sql code not only tells you what indexes have not been used since the last reboot, but it also gives you a rebuild indexes script that can easily be changed to a disable script. The scripts can all be run at once and will save you oodles of time.
SELECT OBJECT_SCHEMA_NAME(I.OBJECT_ID) AS SchemaName, OBJECT_NAME(I.OBJECT_ID) AS ObjectName, I.NAME AS IndexName, 'ALTER INDEX [' + I.Name + '] ON ' + OBJECT_NAME(I.OBJECT_ID) + ' ReBuild' --' Disable' AS SQLToRebuildOrDisable FROM sys.indexes I WHERE /* only get indexes for user created tables*/ OBJECTPROPERTY(I.OBJECT_ID, 'IsUserTable') = 1 /* making sure it is not a primary key*/ and i.is_primary_key = 0 and i.is_unique = 0 /* If the are returned in this table, they are in use so we are getting everything not in this table.*/ AND NOT EXISTS ( SELECT index_id FROM sys.dm_db_index_usage_stats WHERE OBJECT_ID = I.OBJECT_ID AND I.index_id = index_id -- This will limit to the db you are on. AND database_id = DB_ID()) -- The Index must have a name for this to work. AND I.name IS NOT NULL ORDER BY SchemaName, ObjectName, IndexName
I hope it helps you as much as it has helped me today!
Have a magical day!