MONITORING FRAGMENTION LEVELS OF THE SUNRISE XA MASTER DATABASE
By Brandon Hansen
One of the best ways to achieve outstanding performance of your Sunrise XA System is to ensure that the
fragmentation of your Sunrise XA Master Active database is consistently monitored and maintained. Monitoring
the fragmentation levels of the Sunrise database requires that tests are done on a regular basis.
The test to verify the fragmentation levels of your Production database can be done by running the “DBCC
SHOWCONTIG” SQL statement. However, when this statement is run, it will affect performance of the database and
therefore, should never be executed against a ‘live’ version of the Production database.
The best practice to verify fragmentation levels of the Production database is to restore a recent backup of
the Production database into a Development environment and then run the “DBCC SHOWCONTIG” statement against the
copy of the Production database.
There are a number of ways this process can be done. One way is to create a scheduled task that copies the
Production database backup file onto a drive that is accessible in the Development environment. The database
copy should be done during off-hours when there is less network traffic.
The Production database backup file should also be copied from a drive that does not contain the SQL data and
log files so that the database and SQL Server performance will not be affected. Also, a good copy utility such
as Robocopy, which can compensate for network disruptions, should be used so that the database copy can be
successfully completed.
After the database backup copy is complete, you can now restore the database into Development. If you create
a SQL job or script to restore the database, you can schedule the restore to automatically run at a time when
fewer users are in the Development environment.
When the database restore is complete, you should now be able to verify the fragmentation levels of your
Production database. As mentioned earlier, the database fragmentation test is ran through the use of the “DBCC
SHOWCONTIG” SQL statement. You can run this statement from SQL Query Analyzer or through an automated process
as part of a SQL job or script.
Once the “DBCC SHOWCONTIG” command completes and the results are returned, you will want to evaluate your
results to determine if a re-index of the database tables should be done. You can determine if the database
needs to be re-indexed by verifying the Scan Density of some of the major tables in the Sunrise XA Master Active
database.
If the “DBCC SHOWCONTIG” query shows Scan Density values that are 20% or lower and performance of your
Sunrise XA system is degrading, then a re-index should be done. A downtime will need to be scheduled and the
entire system will need to be taken off-line to complete the re-index process.
Once the re-index is complete, the performance of your Sunrise XA system should now improve to levels that
are acceptable to your end-users. You will now want to continue to monitor the fragmentation levels of your
database on a regular basis.
By following the steps outlined in this article, you will be able to keep your Sunrise XA System at optimum
performance levels through consistent and regular monitoring of the Sunrise XA Master Active database.
These steps will allow you to determine when the database becomes too fragmented and when a re-index is again
necessary. All these steps will help you to provide the best possible support and service at your hospital
site. If you would like additional information on this or our Eclipsys practice, please email us at
vcs@getvitalized.com or call
our corporate office at 610-444-1233.