EgdbDatastore - defrag % vs Azure SQL Database defrag %

AntheaTung — Thu, 28 Sep 2023 18:37:18 GMT

Hello there!

We are using ArcGIS Enterprise 10.9.1 with Azure SQL Database.

The defrag % output from Egdb compare to Azure SQL Database query shows different values. Wondering what could be the reason the values are different for the same table?

For example in the perf_FragmentedIndex tab in the export.xlsx file after a run:

Egdb perf_FragmentedIndex report shows 99.26% defrag:

database_name	owner	indexed_object_name	table_type	index_name	avg_fragmentation_in_percent	in_row_reserved_page_count
WATER	xx	WATERJUNCTIONOBJECT	User Table	UUID_51	99.25765543	3290

Azure defrag query shows 5.79% defrag:

schema_name	object_name	index_name	index_type	avg_fragmentation_in_percent	avg_page_space_used_in_percent	page_count	alloc_unit_type_desc
xx	WATERJUNCTIONOBJECT	UUID_51	NONCLUSTERED	5.79854208084824	96.4498393872004	3018	IN_ROW_DATA

SELECT OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,

OBJECT_NAME(ips.object_id) AS object_name,

i.name AS index_name,

i.type_desc AS index_type,

ips.avg_fragmentation_in_percent,

ips.avg_page_space_used_in_percent,

ips.page_count,

ips.alloc_unit_type_desc

FROM sys.dm_db_index_physical_stats(DB_ID(), default, default, default, 'SAMPLED') AS ips

INNER JOIN sys.indexes AS i

ON ips.object_id = i.object_id

AND

ips.index_id = i.index_id

ORDER BY page_count DESC;

Thank you !

topic EgdbDatastore - defrag % vs Azure SQL Database defrag % in Geodatabase Questions

EgdbDatastore - defrag % vs Azure SQL Database defrag %