Fill Factor And The Performance Tradeoff

Tara Kizer explains the performance tradeoff when setting fill factor for an index:

There are workloads where frequent page splits are a problem. I thought I had a system like this many years ago, so I tested various fill factor settings for the culprit table’s clustered index. While insert performance improved by lowering the fill factor, read performance drastically got worse. Read performance was deemed much more critical than write performance on this system. I abandoned that change and instead recommended a table design change since it made sense for that particular table.

Click through for a demo.

Related Posts

Tuning Azure SQL Database

Tim Radney walks us through some of the tools we have available to tune Azure SQL Databases: Many instance-level items that you have been used to configuring on full installations are off limits. Some of these items include:– Setting min and max server memory– Enabling optimize for ad hoc workloads– Changing cost threshold for parallelism– […]

Read More

Finding Missing Index Hints in Query Store

Grant Fritchey shows us another place where we can find missing index hints: A couple of notes on the query. I cast the query_plan as xml so that I can use the XQuery to pull out the information. It is possible that the plan might be so large that you get an error because of […]

Read More

Categories

December 2018
MTWTFSS
« Nov Jan »
 12
3456789
10111213141516
17181920212223
24252627282930
31