MS SQL Database Table Fragmentation

[chris_nelson]

Hello:

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

[DanielS · Accepted solution]

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.

[chris_nelson]

How would one go about ingesting Table Fragmentation levels?

Or is that a metric that is supported?

[DanielS]

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

https://www.dynatrace.com/support/help/shortlink/local-api