Recently I was brought in to help troubleshoot performance issues on a database server.  Going through my typical check list I noticed memory pressure on the box and made the recommendationof more memory which was simple to fix. What caught my attention though was that blocking was occurring on a transaction that was trying to do a simple delete of a few records.

In a discussion with the vendor I learned that this system does massive inserts and deletes all day long. This system had only been in place for a few months and one table was nearly 30 GB in size. This was the particular table that was being blocked with a delete of a few records. We use a standard index maintenance script that reorg’s or rebuilds based on fragmentation so I was able to quickly rule out fragmentation as the issue however I still felt like I should dig into the indexes to see what was happening.

In looking at the indexes I found a clustered index that was the primary key nvarchar(255) and 13 non clustered indexes all with the default fill factor of 100%. In digging into looking at the number of page splits per second on this instance they were in the several hundred. Taking a look at the top 10 indexes with page splits, this particular table was 8 of the 10. With this information in hand I went back to the vendor. They basically told me that this level of trouble shooting and tuning was beyond their knowledge but that what I stated sounds good and to implement the changes. All I asked them was if they had an issue with me rebuilding the index and dropping the fill factor rate.

In dropping the fill factor from 100% to 80%, I decreased the page splits drastically. For the overall instance I am no longer in the 100′s per second, they are still active in the 20+ range and for my top 10 indexes this particular table only has one entry. With that being said, I still have more work to do, however this system is in much better shape. The table grew quite a bit but the tradeoff is well worth it.