Impact of Fusion-io based tempdb on reindex duration

Do you have tempdb on Fusion-io (or equivalent) technology? Is your reindexing job left on default settings (re-orgs at 5-15% and full rebuilds at @ 30%)? If so, then you might be interested in the findings in this post. The benefits of flash memory as a replacement for spinning drives in order to boost SQL Server performance is well documented. However, I’m not sure if one particular benefit has been fully exploited.

SQL Server wait stats

The single biggest clue to the source of a performance problem will be from something referred to as the wait stats. What are waitstats? In short wait stats are statistics gathered on what resources SQL Server is waiting on internally whilst executing queries. Why are they useful? They can often provide very quick and objective evidence of a performance bottleneck by showing cumulative wait times that SQL Server spent on getting access to the CPU, memory or disk access as well as numerous other internal resources.

The SQL Server default trace

The SQL Server default trace is useful! The SQL Server default trace (introduced from SQL Server 2005 onwards) is a background sql server trace that runs continuously and records event information that, despite the adverse comments on the web about its value, can be useful in troubleshooting problems. Not only that, but it’s there out-of-the box so you don’t have to do put any effort into enabling or managing it.