ApexSQL Defrag Query Refresh too sloow

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

Parents
  • Hi, 

    Could you please clarify what kind of issues you are facing with the application itself? 

  • Hi Nodier Garcia,

    My problem is the ApexSQL Defrag is too slow to rebuild only one index and always end with a message "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"

    I captured all the activities on the target server using MS SQL Server Profiler:

    1.- Start execution the query mentioned in my previous post.

    2.- After 6 to 9 minutes when the process not finishing, and the message appear in the Activities Tab, I stop the ApexSQL Defrag Agent and suddenly in the SQL Server profiler all the query ends and run the ALTER INDEX.

    I sent to my customer provider an email with all the images captured.

    If you Speak Spanish and can provide to me an email to send a copy of the email.

    Bottom line, ApexSQL Defrag is unable to complete a only one index, because this query takes too log to execute:

    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

    Best Regards

  • Thanks for the details here. This looks like something that we would need to work on deeper. Could you please go to the quest support portal and open a Service Request with these details for better assistance? 

    Thanks!

  • Thank you again Nodler Garcia,

    Well I will try to open the Service Request.

    Regards.

  • Hello to all,

    About the problem, I have some news, my main DB is 250 GB size, my supervisor provide to me a trial for another tool(manage index too) after install in my server, this tool works like a charm, after review the activity for this tool, using MS SQL Server Profiler, it first collect all objects and schemas from desired database, then collect al indexes names also partitions and then execute per index individual sys.dm_db_physical_stats pasing dbid, objectid, indexid, and partitionID execution, using less resources from my server.

    From my point of view, as I mentioned in my earlier posts,  the problems it's in the way ApexSQL Defrag collect the statistics for databases indexes.

    From the query original apexsql defrag query if I run in MS SQL Server Management Studio the original query using:

    --------------------------------------------------------------------------------------------------
    -->>To use this block, in my tests, response more faster in my server
    ----------------------------------------------------------------------------------------------------
    outer apply(
                (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(),[sIdx].[object_id], [sIdx].[index_id],NULL,'LIMITED') stat
                 WHERE stat.alloc_unit_type_desc = 'IN_ROW_DATA'
                 GROUP BY index_id, object_id, database_id, alloc_unit_type_desc )
               ) [sdmfIPS]

    -------------------------------------------------------------------
    -->>Instead using this block - original code
    ------------------------------------------------------------------
    --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'  

    The query run more faster.

    Well I already posted my research and review in my service request.

    Regards!

  • Thank you for this great explanation on the queries you run!

    it seems that the statement '  WHERE stat.alloc_unit_type_desc = 'IN_ROW_DATA'  '  is the culprit of the performance boost, comparing both queries it shows that there is no 'Where' statement in the original query.

    This is a great opportunity for us to improve the performance of our tool's queries, We'll make sure in the Service Request you've opened this suggestion will be considered in a future update of the tool.

    Thanks for choosing quest

  • Hello Armando,   

    Thank you for your comments, also the query will run more faster if the objectID and indexid are passed directly to [sys].[dm_db_index_physical_stats] when  apply,  instead to "join" with it. Maybe, create a process to collect the index stats individually. 

    Regards

Reply
  • Hello Armando,   

    Thank you for your comments, also the query will run more faster if the objectID and indexid are passed directly to [sys].[dm_db_index_physical_stats] when  apply,  instead to "join" with it. Maybe, create a process to collect the index stats individually. 

    Regards

Children