Thursday, September 25, 2008

Index fragmentation causes significant performance problems in EnterpriseOne systems. If access to the underlying data is slow, end users will not have the 'snappy' experience they expect, particularly on the web-client interface.

Determining index fragmentation on SQL 2000 was simple, using the DBCC SHOWCONTIG command with a few extra arguments:

Determining index fragmentation in SQL 2005 is a little more difficult as Microsoft has switched to the use of Dynamic Management Views to grant access to important information.

The code below is fast but requires two steps - a one-time per server creation of a function and the SELECT statement that uses the function. The function was created based on knowledge gleaned from Paul Randal's In Recovery blog posting on combining CROSS APPLY and Dynamic Management Views.