000027679 - SQL script to verify RSA Archer database Index fragmentation levels and degradation percentage

RSA Archer Database performance check - SQL (Structured Query Language) script to verify RSA Archer database Index fragmentation levels and percentage.

SQL script will identify the performance degradation caused by database Index fragmentation. This SQL script will provide information on items like Database Name, Table Name, Index Name, Object ID, Percent Fragmentation, Total Fragments, Pages Per Fragment and Number of Pages.

Note: Please replace <ArcherInstanceDB> with the actual RSA Archer Instance database name.

USE <ArcherInstanceDB>
DECLARE @DBID INTEGER
SET @DBID = DB_ID()
print @DBID
SELECT
DB_NAME(@DBID) as DBNAME, st.name as TableName, b.name AS IndexName, a.object_id, a.avg_fragmentation_in_percent AS PercentFragment,
a.fragment_count AS TotalFrags,a.avg_fragment_size_in_pages AS PagesPerFrag,
a.page_count AS NumPages
FROM
sys.dm_db_index_physical_stats (@DBID, NULL, NULL, NULL, NULL) AS a
JOIN sys.indexes AS b
ON a.object_id = b.object_id
AND a.index_id = b.index_id
JOIN sys.tables AS st
ON st.object_id = a.object_id
WHERE b.type > 0 and a.avg_fragmentation_in_percent > 0 and  left(st.name,1) != 'z'

Explanation of important columns

(PercentFragment) avg_fragmentation_in_percent
==============================================
It indicates the amount of external fragmentation you have for the given objects.The lower the number the better - as this number approaches 100% the more pages you have in the given index that are not properly ordered. For heaps, this value is actually the percentage of extent fragmentation and not external fragmentation.

(TotalFrags) fragment_count
============================
A fragment is made up of physically consecutive leaf pages in the same file for an allocation unit. An index has at least one fragment. The maximum fragments an index can have are equal to the number of pages in the leaf level of the index. So the less fragments the more data is stored consecutively.

(PagesPerFrag) avg_fragment_size_in_pages
=========================================
Larger fragments mean that less disk I/O is required to read the same number of pages. Therefore, the larger the avg_fragment_size_in_pages value, the better the range scan performance.

To reduce the fragmentation levels on all Indexes, please deploy RSA Archer database maintenance scripts. For details and the scheduling of these SQL scripts, please review topic: 'Run the Maintenance SQL Script' located in RSA Archer installation guide.

• Turn on SQL Server Agent. User must have access to MSDB to do this.
• Run the "jobDeplyScript.sql" script located in:  C:\Program Files\RSA Archer\Tools.