Slow reading/writing of log values and events might be caused by fragmented index in SQL database.

[Product_Support]

Warning

Potential for Data Loss: The steps detailed in the resolution of this article may result in a loss of critical data if not performed properly. Before beginning these steps, make sure all important data is backed up in the event of data loss. If you are unsure or unfamiliar with any complex steps detailed in this article, please contact Product Support Services for assistance.

Issue

Slow reading/writing of log values and events might be caused by a fragmented index in the SQL database.

Product Line

TAC Vista

Environment

• Vista Server
• SQL log/event data
• SQL Server 2005, 2008
• Microsoft SQL Server Management Studio Express

Cause

If the SQL database has become fragmented in its index, these scripts can be run in SQL query software (e.g. Microsoft SQL Server Management Studio Express). Downloaded this software for free at:
http://www.microsoft.com/downloads/details.aspx?FamilyID=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displa...

Resolution

Warning: Always take backups of the system prior to performing maintenance on the database.

1. Run the following SQL query:
   
   

   SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(N'taclogdata'), 
   OBJECT_ID(N'TrendLogValue'), NULL, NULL , 'DETAILED') 
   WHERE index_level = 0 order by index_id

2. Look at "avg_fragmentation_in_percent"
3. Or DBCC SHOWCONTIG
4. Look at Logical scan fragmentation
5. If the database is fragmented, e.g more than 50% run this script to defrag the index:
   
   

   USE TACLOGDATA
   DBCC INDEXDEFRAG('taclogdata','TrendLogValue')

   Or
   

   USE TACLOGDATA
   DBCC DBREINDEX('TrendLogValue')