I have experiencing problems with ApexSQL Defrag console when refreshing indexex information, I captured this query using thirdy party tool, this correspond to only one index:
SELECT SCHEMA_NAME([sObj].[schema_id]) AS [SchemaName], [sObj]
.[name] AS [ObjectName], [sIdx]
.[object_id] as ObjectId, [sIdx]
.[index_id] AS [IndexID],
ISNULL([sIdx].[name], 'N/A') AS [IndexName], [sIdx]
.[type] AS [IndexType],
CAST([IndexSizeInKB] AS float) AS IndexSizeInKB, [sIdx]
.[fill_factor] AS [FillFactor],
CAST([sdmfIPS].[avg_fragmentation_in_percent] AS float) AS [AvgPctFrag],
is_disabled AS IsDisabled,
sdmfIPS.fragment_count,
sdmfIPS.page_count as [PagesCount],
CAST (0 AS bit) AS IsDuplicate,
CAST (0 AS bit) AS CanDrop,
CAST (0 AS bit) AS IsUnused,
0 AS OriginalId, [sdmfIPS]
.[avg_page_space_used_in_percent] AS [PageDensity],
SERVERPROPERTY('ProductVersion') AS ProductVersion, [sIdx]
.[is_padded] AS [IsPadded]
FROM [sys].[indexes] AS [sIdx]
INNER JOIN [sys].[objects] AS [sObj]
ON [sIdx].[object_id] = [sObj].[object_id]
LEFT JOIN
(
SELECT[sIdx].[object_id], [sIdx]
.[index_id],
SUM([sAU].[used_pages]) * 8 AS[IndexSizeInKB]
FROM[sys].[indexes] AS[sIdx]
INNER JOIN[sys].[partitions] AS[sPtn]
ON[sIdx].[object_id] = [sPtn].[object_id]
AND[sIdx].[index_id] = [sPtn].[index_id]
INNER JOIN[sys].[allocation_units] AS[sAU]
ON[sPtn].[partition_id] = [sAU].[container_id]
GROUP BY[sIdx].[object_id], [sIdx]
.[index_id]
) [IdxSizeDetails]
ON[sIdx].[object_id] = [IdxSizeDetails].[object_id]
AND[sIdx].[index_id] = [IdxSizeDetails].[index_id]
LEFT JOIN ((SELECT alloc_unit_type_desc, database_id, object_id, index_id, SUM(page_count) AS
page_count, MAX(avg_fragmentation_in_percent) as avg_fragmentation_in_percent,SUM(
fragment_count) AS fragment_count, SUM(avg_page_space_used_in_percent) AS
avg_page_space_used_in_percent FROM [sys].[dm_db_index_physical_stats] (DB_ID(),NULL,NULL,
NULL,'LIMITED') GROUP BY index_id, object_id, database_id, alloc_unit_type_desc ))
[sdmfIPS]
ON [sIdx].[object_id] = [sdmfIPS].[object_id]
AND [sIdx].[index_id] = [sdmfIPS].[index_id]
AND [sdmfIPS].[database_id] = DB_ID()
AND [sdmfIPS].alloc_unit_type_desc = 'IN_ROW_DATA'
WHERE [sObj].[type] IN ('U','V')
AND [sObj].[is_ms_shipped] = 0x0
AND [sIdx].[index_id] <> 0
AND sIdx.type not in (5,6,7)
AND [sIdx].[object_id] = 511392941
AND [sIdx].[index_id] = 1
If I ran this query using MS SQL Server Management Studio it takes around 5 to 9 minutes.
Also I have this messages in Activity tab for the selected index:
"The server did not provide a meaningful reply; this might be caused by a contract mismatch, a premature session shutdown or an internal server error"
Please any information to improve the ApexSQL Defrag console in my server, will be very appreciated
