cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

MS SQL Database Table Fragmentation

chris_nelson
Guide

Hello:

Is there a way for Dynatrace to see down to the MS SQL Table level and extract fragmentation statistics?

3 REPLIES 3

DanielS
DynaMight Champion
DynaMight Champion

Hello, the metrics that you can see through the plugin are these   https://www.dynatrace.com/support/help/shortlink/ms-sql#metrics

For any other metric you can do it by ingesting them but you need to get it by other means.

The true delight is in the finding out rather than in the knowing.

chris_nelson
Guide

How would one go about ingesting Table Fragmentation levels?

Or is that a metric that is supported?

SELECT S.name as 'Schema',
T.name as 'Table',
I.name as 'Index',
DDIPS.avg_fragmentation_in_percent,
DDIPS.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
INNER JOIN sys.schemas S on T.schema_id = S.schema_id
INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id
AND DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID()
and I.name is not null
AND DDIPS.avg_fragmentation_in_percent > 0
ORDER BY DDIPS.avg_fragmentation_in_percent desc
 
and you could ingest the data creating dimensions and metrics as you need following this guide
The true delight is in the finding out rather than in the knowing.