How to check database index fragmentation percentages of TIBCO Spotfire Server application database for diagnosing performance issues

How to check database index fragmentation percentages of TIBCO Spotfire Server application database for diagnosing performance issues

Highly fragmented indexes in a database can lead to poor performance. Therefore when seeing performance issues in activities in Spotfire that rely heavily on the application database (using Information Designer, Administration Manager, Library Administration tool, etc), it can be useful to ensure the indices in the TIBCO Spotfire Server application database are not in poor condition.

It is the responsibility of your database administrator to ensure that the indices in the Spotfire application database are kept up to date and to perform any required index maintenance. A general best practice is to reorganize indexes with more than 10 percent and up to 30 percent fragmentation. An index rebuild operation could be more appropriate if you have fragmentation greater than 30 percent. Fragmentation of 10 percent or less should not be considered a problem. The following are example queries that can be run against the TIBCO Spotfire Server application database to check the fragmentation percentage of the existing indexes. Please consult with your database administrator for more detailed assistance and maintenance. 

Example for Microsoft SQL Server:

SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc

Example for Oracle:

• Before you execute this script replace the OWNER in the cursor statement with your Spotfire Server database username
• OWNER name should be in CAPS
• Make sure you are connected to Oracle with your Spotfire Server database username to analyze Spotfire database indexes because only the owner of the index can analyze that particular index. Otherwise it might fail with error 'specified index does not exist' error.

SET SERVEROUTPUT ON;
DECLARE
Fragmentation number;
CURSOR cs IS select * from all_indexes where OWNER='SPOTFIRE_DB103';
index_row cs%ROWTYPE;
cnt number;
hgt number;
leaf_blocks number;
leaf_rows number;
del_lf number;
BEGIN
fragmentation := 20;
cnt := 0;
OPEN cs;
LOOP
FETCH  cs  INTO index_row;
EXIT WHEN cs%NOTFOUND;
IF index_row.segment_created = 'NO' then
DBMS_OUTPUT.PUT_LINE('*TABLE_NAME*'||' - '||index_row.TABLE_NAME||' , '||'*INDEX*'||' - '||index_row.INDEX_NAME|| ' , ' ||'*Percentage*'|| ' - ' ||'0'|| '%');
cnt:=cnt+1;
continue;
END IF;
EXECUTE IMMEDIATE 'ANALYZE INDEX '||index_row.INDEX_NAME||' VALIDATE STRUCTURE';
select height, lf_blks, lf_rows, del_lf_rows into hgt, leaf_blocks, leaf_rows, del_lf from index_stats;
if leaf_rows=0 then
DBMS_OUTPUT.PUT_LINE('*TABLE_NAME*'||' - '||index_row.TABLE_NAME||' , '||'*INDEX*'||' - '||index_row.INDEX_NAME|| ' , ' ||'*Percentage*'|| ' - ' ||'0'|| '%');
cnt:=cnt+1;
CONTINUE;
END IF;
IF leaf_rows>100 AND del_lf>0 then
select round((del_lf_rows/lf_rows)*100,2) into fragmentation from index_stats;
DBMS_OUTPUT.PUT_LINE('*TABLE_NAME*'||' - '||index_row.TABLE_NAME||' , '||'*INDEX*'||' - '||index_row.INDEX_NAME|| ' , ' ||'*Percentage*'|| ' - ' ||fragmentation|| '%');
ELSE
DBMS_OUTPUT.PUT_LINE('*TABLE_NAME*'||' - '||index_row.TABLE_NAME||' , '||'*INDEX*'||' - '||index_row.INDEX_NAME|| ' , ' ||'*Percentage*'|| ' - ' ||'0'|| '%');
END IF;
cnt := cnt+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('TOTAL INDEXES ANALYZED'||' - '||cnt||'');
CLOSE cs;
END;

Disclaimer: The content of this article is for informational purposes only. The subject material may change in any new versions with no notice and there is no responsibility by TIBCO to maintain or support future access to this internal application content. Modification of any internal application content is not recommended and can lead to an unsupported configuration.  It is not intended to be used "As Is" in a Production environment. Always test in a Development environment.