About myself and everyone else.
Some time ago I made a post about sys.dm_index_usage_stats.
A key thing to take from this view is to get the ratio of reads and writes on your system. If you dump into a celeb DBA at a conference and you start talking about your systems you need to know your system inside out. The reason for this besides the obvious embarrassment is that if you haven’t pursued all avenues of your server your accuracy when it comes to finding issues will be lacking.
Reads and Writes.
Now that you know the overall reads and writes of your system you can also drill down into the tables. This can make a nice report if you were so inclined. So you drill down into the tables and heck even the index that is responsible for the IO. The point of knowing this is that you can really improve your indexing by knowing the reads and writes.
Fillfactor. We know that we can squeeze a table nice and compact by using a 100% fill factor for static tables.
That’s it, or is it.
You can actually tune you tables and the fill factor depending on how the tables is accessed. This is because of the way that an index may be seeked or scanned. If the index participates in a seek the a fillfactor of 50% doesn’t affect performance for querying the table. I will also have nice inserts with any severe page splits. This is something you will see in replication systems where the primary might be a GUID or where a lot of your data gets updated all the time which may cause splits.
The reason why the performance is only slightly different is that because of the seek operation the index can be navigated directly and doesn’t have to transcend through the 50% free space. i.e. half full pages and thus more pages to be accessed.
The scan operation however is not good if you combine it with large amount of free space. If the page is 50% full it will scan 50% more pages. This is significant. For this I suggest a fuller fill factor. In order to find the right fillfactor for your data you can test each fill factor and look at the page splits counter and you can also look at insert and update times.
Generally you will increase the fillfactor to save disk space but this can cause your indexes to become heavily fragmented when page splits occur. You might find that you need to rebuild your 40GB table every day and wonder how can this table get fragmented if it takes only a few updates. Those few updates may have large data types which is adding to the effect or splitting pages.
There is a bit of investigation required into what fillfactor to use but instead of just looking at static VS dynamic tables you can also look at the way that your tables are accessed which will allow you to further tune your system.
It might save a second here and there.
Note; you have to be a DBA that is leaning towards pedantic to really appreciate the few seconds that you shave of queries 🙂