MS SQL Database Table Fragmentation

[runatyr]

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.

[runatyr]

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

[andre_vdveen]

@DanielS thanks for sharing the query!
What I did was to set up an extension using the Extensions Creator app and the query you provided.

MSSQL query

SELECT S.name as 'schema',
T.name as 'table',
I.name as 'index',
DDIPS.avg_fragmentation_in_percent as 'fragmentation',
DDIPS.page_count
FROM sys.dm_db_index_physical_stats (NULL, 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

The config of the extension

The results in Data Explorer