topic Re: MS SQL Database Table Fragmentation in Open Q&A

MS SQL Database Table Fragmentation

runatyr — Thu, 06 Oct 2022 14:51:54 GMT

Hello:

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

Re: MS SQL Database Table Fragmentation

DanielS — Thu, 06 Oct 2022 15:14:28 GMT

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.

Re: MS SQL Database Table Fragmentation

runatyr — Tue, 18 Oct 2022 17:38:45 GMT

How would one go about ingesting Table Fragmentation levels?

Or is that a metric that is supported?

Re: MS SQL Database Table Fragmentation

DanielS — Wed, 02 Nov 2022 22:18:44 GMT

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

Re: MS SQL Database Table Fragmentation

andre_vdveen — Thu, 16 Jan 2025 07:45:59 GMT

@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