Guest Spot: Data Repository Fragmentation Can Slow Performance

Contributed by: Edward Chisam, President, DR Validate

Is your Data Repository SQL Server suffering from performance issues? Consider these symptoms:

• bottlenecking at the disk level
• numerous SQL warnings of I/O taking a very long time
• performance monitor shows a very long disk queue
• slow running queries

Before sinking more money into hardware, consider checking your SQL Server for fragmentation, which occurs as data is modified. Fragmentation is defined as the inefficient use of storage space on the SQL Server, which reduces storage capacity and slows performance. Two types of fragmentation include Logical Disk Fragmentation and for SQL Server Index Fragmentation.

Logical Disk Fragmentation

The SQL Server runs on a Windows Operating System, and database files can become fragmented at the Windows level just like any other windows files can. As the files grow larger, file system cannot store the database file in a single location, which results in the disk head needing to move back and forth over the disk while searching through the data. To address this type of fragmentation you can use Disk Defragmenter, included with Windows, or various third party tools.

When using Disk Defragmenter, large portions of the disk scan shown in red are a sure sign of fragmentation issues at the windows level. Before running the option to defragment the disk, you’ll need to stop all SQL Server services.

Fragmentation at the Windows level can be reduced by careful planning at the start of your Data Repository implementation process. Rather than rely upon SQL Server’s auto grow setting to add space to your SQL Server system on a regular basis, allocate as much space as possible for your database files from the outset. When more growth for your database is required, increase the space in large blocks of data. If possible, avoid installing other applications directly onto the server which may also affect the file system.

SQL Server Index Fragmentation

SQL Server storage involves the use of pages to store table data. Fragmentation at the SQL level can occur in a couple of ways. One involves a side effect of Bulk Delete commands MEDITECH occasionally runs on table data, either as part of a Ring Release update, or during normal daily processing. Delete commands remove rows from pages, leaving some half full. Because there is a lot of empty space among the pages, more page reads are required to query for data than are otherwise necessary.

More commonly, page splits can occur when data is inserted or updated. For example, suppose you have detected that some tables in your Data Repository have data missing. MEDITECH corrects this problem by re-running Initial Loads on the affected tables – these perform updates and inserts on every row in each table involved. Inevitably page splits will occur during this intensive process. This will lead to slower running queries, particularly those that seek to retrieve data in logical order.

To detect the level of index fragmentation, either for all indexes on a table or just the MEDITECH provided clustered index, use the DBCC SHOWCONTIG command. The most important metrics returned by this query are the Average Page Density (Full) and the Scan Density. The Average Page Density shows, on average, how full the pages are – it is desirable for this percentage to be as high as possible. Scan Density is a ratio indicating how contiguous storage space is on the SQL Server: if the value is 100, everything is contiguous; if the number is less than 100 some fragmentation exists.

To repair fragmented indexes, it is possible to drop and recreate the indexes completely, but this requires server downtime and may be somewhat risky. A safer option is to use Microsoft’s Alter Index function, as in this example:

ALTER INDEX mtpk_patccdqm
ON dbo.MriPatientClinicalQueriesMult
REBUILD WITH ( ONLINE = ON )

Depending upon your version of SQL Server, rebuilding of indexes may not be allowed online, meaning that DR Transfers will need to be halted and users kept off the database while this repair work is underway. Older versions of SQL, such as SQL Server 2000, will require usage of DBCC DBREINDEX or DBCC INDEXDEFRAG functions.

Summary

As we’ve seen, fragmentation of various types can degrade performance on your SQL Server, leading to slower running queries, I/O bottlenecks, and the like. It’s a good idea to run the Windows defrag tools and schedule DBCC SHOWCONTIG at the SQL level on a regular basis, to discover these problems sooner rather than later. Your users will thank you for the time you take to “sharpen the blade” on a regular basis.

Questions regarding Data Repository and Meaningful Use? Email them to Edward at echisam@drvalidate.com.

***

Edward Chisam is President at DR Validate and was a Senior Development Programmer of the Data Repository application at MEDITECH.