Sql Monitor query one of the Top 10 expensive queries

avibooks

Hi There,

After upgrading to Sql Monitor v4.0 yesterday, I am seeing the following query against the Sql monitor data repository taking long time and consuming high amounts of CPU and Physical reads.

SELECT * FROM (
SELECT
      tq.[Id]
        , tq.[ParentId]
        , tq.[IdCollectionDate]
        , tq.[IdCollectionDate_DateTime]
        , tq.[Cluster_Name]
        , tq.[Cluster_SqlServer_Name]
        , tq.[CollectionDate]
    , tq.[Cluster_SqlServer_TopQueries_DatabaseName]
    , tq.[Cluster_SqlServer_TopQueries_SqlHandle]
    , tq.[Cluster_SqlServer_TopQueries_StatementEnd]
    , tq.[Cluster_SqlServer_TopQueries_StatementStart]
    , tq.[Cluster_SqlServer_TopQueries_ExecutionCount]
    , tq.[Cluster_SqlServer_TopQueries_ExecutionTime]
    , tq.[Cluster_SqlServer_TopQueries_LogicalReads]
    , tq.[Cluster_SqlServer_TopQueries_LogicalWrites]
    , tq.[Cluster_SqlServer_TopQueries_PhysicalReads]
    , tq.[Cluster_SqlServer_TopQueries_WorkerTime]
        , tqi.[_QueryText] AS [Cluster_SqlServer_TopQueries_QueryText]
    , tqs.[Cluster_SqlServer_TopQueries_PlanHandle]
    , tqs.[Cluster_SqlServer_TopQueries_CreateDate]
    , ROW_NUMBER() OVER(ORDER BY tq.[Cluster_SqlServer_TopQueries_ExecutionCount] DESC) AS [Cluster_SqlServer_TopQueries_ExecutionCount_Rank]
    , ROW_NUMBER() OVER(ORDER BY tq.[Cluster_SqlServer_TopQueries_ExecutionTime] DESC) AS [Cluster_SqlServer_TopQueries_ExecutionTime_Rank]
    , ROW_NUMBER() OVER(ORDER BY tq.[Cluster_SqlServer_TopQueries_LogicalReads] DESC) AS [Cluster_SqlServer_TopQueries_LogicalReads_Rank]
    , ROW_NUMBER() OVER(ORDER BY tq.[Cluster_SqlServer_TopQueries_LogicalWrites] DESC) AS [Cluster_SqlServer_TopQueries_LogicalWrites_Rank]
    , ROW_NUMBER() OVER(ORDER BY tq.[Cluster_SqlServer_TopQueries_PhysicalReads] DESC) AS [Cluster_SqlServer_TopQueries_PhysicalReads_Rank]
    , ROW_NUMBER() OVER(ORDER BY tq.[Cluster_SqlServer_TopQueries_WorkerTime] DESC) AS [Cluster_SqlServer_TopQueries_WorkerTime_Rank]
    , ROW_NUMBER() OVER(ORDER BY tq.[Cluster_SqlServer_TopQueries_ExecutionTime] / CASE WHEN [Cluster_SqlServer_TopQueries_ExecutionCount] > 0 THEN [Cluster_SqlServer_TopQueries_ExecutionCount] ELSE 1 END DESC) AS [Cluster_SqlServer_TopQueries_ExecutionTime_Avg_Rank]
    , ROW_NUMBER() OVER(ORDER BY tq.[Cluster_SqlServer_TopQueries_LogicalReads] / CASE WHEN [Cluster_SqlServer_TopQueries_ExecutionCount] > 0 THEN [Cluster_SqlServer_TopQueries_ExecutionCount] ELSE 1 END DESC) AS [Cluster_SqlServer_TopQueries_LogicalReads_Avg_Rank]
    , ROW_NUMBER() OVER(ORDER BY tq.[Cluster_SqlServer_TopQueries_LogicalWrites] / CASE WHEN [Cluster_SqlServer_TopQueries_ExecutionCount] > 0 THEN [Cluster_SqlServer_TopQueries_ExecutionCount] ELSE 1 END DESC) AS [Cluster_SqlServer_TopQueries_LogicalWrites_Avg_Rank]
    , ROW_NUMBER() OVER(ORDER BY tq.[Cluster_SqlServer_TopQueries_PhysicalReads] / CASE WHEN [Cluster_SqlServer_TopQueries_ExecutionCount] > 0 THEN [Cluster_SqlServer_TopQueries_ExecutionCount] ELSE 1 END DESC) AS [Cluster_SqlServer_TopQueries_PhysicalReads_Avg_Rank]
    , ROW_NUMBER() OVER(ORDER BY tq.[Cluster_SqlServer_TopQueries_WorkerTime] / CASE WHEN [Cluster_SqlServer_TopQueries_ExecutionCount] > 0 THEN [Cluster_SqlServer_TopQueries_ExecutionCount] ELSE 1 END  DESC) AS [Cluster_SqlServer_TopQueries_WorkerTime_Avg_Rank]
FROM [data].[Cluster_SqlServer_TopQueries_UnstableSamples_CumulativeDifferential](@MinDate, @MaxDate) tq
INNER JOIN [data].[Cluster_SqlServer_TopQueries_Instances] tqi on tqi.[Id] = tq.[Id]
 
CROSS APPLY (
        SELECT TOP 1 
                [_PlanHandle] [Cluster_SqlServer_TopQueries_PlanHandle]
          , [_CreateDate] [Cluster_SqlServer_TopQueries_CreateDate] 
        FROM [data].[Cluster_SqlServer_TopQueries_StableSamples] x
        WHERE x.Id = tq.Id AND x.CollectionDate <= @MaxDate
        ORDER BY x.CollectionDate DESC) tqs
WHERE (
    tq.[Cluster_SqlServer_TopQueries_ExecutionCount] > 0
    OR
    tq.[Cluster_SqlServer_TopQueries_ExecutionTime] > 0
    OR
    tq.[Cluster_SqlServer_TopQueries_LogicalReads] > 0
    OR
    tq.[Cluster_SqlServer_TopQueries_LogicalWrites] > 0
    OR
    tq.[Cluster_SqlServer_TopQueries_PhysicalReads] > 0
    OR
    tq.[Cluster_SqlServer_TopQueries_WorkerTime] > 0
)
AND (
    tq.[Cluster_Name] = @Cluster_Name
    AND
    tq.[Cluster_SqlServer_Name] = @Cluster_SqlServer_Name
    AND
    (@Cluster_SqlServer_TopQueries_DatabaseName IS NULL OR tq.[Cluster_SqlServer_TopQueries_DatabaseName] = @Cluster_SqlServer_TopQueries_DatabaseName)
    AND
    (@Cluster_SqlServer_TopQueries_SqlHandle IS NULL OR tq.[Cluster_SqlServer_TopQueries_SqlHandle] = @Cluster_SqlServer_TopQueries_SqlHandle)
    AND
    (@Cluster_SqlServer_TopQueries_StatementStart IS NULL OR tq.[Cluster_SqlServer_TopQueries_StatementStart] = @Cluster_SqlServer_TopQueries_StatementStart)
    AND
    (@Cluster_SqlServer_TopQueries_StatementEnd IS NULL OR tq.[Cluster_SqlServer_TopQueries_StatementEnd] = @Cluster_SqlServer_TopQueries_StatementEnd)
    )
) s 
WHERE (
    [Cluster_SqlServer_TopQueries_ExecutionCount_Rank] <= @MaxRank
    OR
    [Cluster_SqlServer_TopQueries_ExecutionTime_Rank] <= @MaxRank
    OR
    [Cluster_SqlServer_TopQueries_LogicalReads_Rank] <= @MaxRank
    OR
    [Cluster_SqlServer_TopQueries_LogicalWrites_Rank] <= @MaxRank
    OR
    [Cluster_SqlServer_TopQueries_PhysicalReads_Rank] <= @MaxRank
    OR
    [Cluster_SqlServer_TopQueries_WorkerTime_Rank] <= @MaxRank
    OR
    [Cluster_SqlServer_TopQueries_ExecutionTime_Avg_Rank] <= @MaxRank
    OR
    [Cluster_SqlServer_TopQueries_LogicalReads_Avg_Rank] <= @MaxRank
    OR
    [Cluster_SqlServer_TopQueries_LogicalWrites_Avg_Rank] <= @MaxRank
    OR
    [Cluster_SqlServer_TopQueries_PhysicalReads_Avg_Rank] <= @MaxRank
    OR
    [Cluster_SqlServer_TopQueries_WorkerTime_Avg_Rank] <= @MaxRank
)

After putting a SQL Server Profiler trace for instance of this query, it seems to be running roughly every 1 minute and 1 second. Here is an example of the full query executed.

exec sp_executesql N'
DECLARE @Version4Date BIGINT
SELECT @Version4Date = utils.DateTimeToTicks(MIN(Date)) FROM versioning.SchemaVersionHistory WHERE LEFT(CodeVersion, 1) >= 4

IF (@Version4Date IS NULL) OR (@MaxDate >= @Version4Date)
SELECT * FROM (
SELECT
      tq.[Id]
        , tq.[ParentId]
        , tq.[IdCollectionDate]
        , tq.[IdCollectionDate_DateTime]
        , tq.[Cluster_Name]
        , tq.[Cluster_SqlServer_Name]
        , tq.[CollectionDate]
    , tq.[Cluster_SqlServer_TopQueries_DatabaseName]
    , tq.[Cluster_SqlServer_TopQueries_SqlHandle]
    , tq.[Cluster_SqlServer_TopQueries_StatementEnd]
    , tq.[Cluster_SqlServer_TopQueries_StatementStart]
    , tq.[Cluster_SqlServer_TopQueries_ExecutionCount]
    , tq.[Cluster_SqlServer_TopQueries_ExecutionTime]
    , tq.[Cluster_SqlServer_TopQueries_LogicalReads]
    , tq.[Cluster_SqlServer_TopQueries_LogicalWrites]
    , tq.[Cluster_SqlServer_TopQueries_PhysicalReads]
    , tq.[Cluster_SqlServer_TopQueries_WorkerTime]
        , tqi.[_QueryText] AS [Cluster_SqlServer_TopQueries_QueryText]
    , tqs.[Cluster_SqlServer_TopQueries_PlanHandle]
    , tqs.[Cluster_SqlServer_TopQueries_CreateDate]
    , ROW_NUMBER() OVER(ORDER BY tq.[Cluster_SqlServer_TopQueries_ExecutionCount] DESC) AS [Cluster_SqlServer_TopQueries_ExecutionCount_Rank]
    , ROW_NUMBER() OVER(ORDER BY tq.[Cluster_SqlServer_TopQueries_ExecutionTime] DESC) AS [Cluster_SqlServer_TopQueries_ExecutionTime_Rank]
    , ROW_NUMBER() OVER(ORDER BY tq.[Cluster_SqlServer_TopQueries_LogicalReads] DESC) AS [Cluster_SqlServer_TopQueries_LogicalReads_Rank]
    , ROW_NUMBER() OVER(ORDER BY tq.[Cluster_SqlServer_TopQueries_LogicalWrites] DESC) AS [Cluster_SqlServer_TopQueries_LogicalWrites_Rank]
    , ROW_NUMBER() OVER(ORDER BY tq.[Cluster_SqlServer_TopQueries_PhysicalReads] DESC) AS [Cluster_SqlServer_TopQueries_PhysicalReads_Rank]
    , ROW_NUMBER() OVER(ORDER BY tq.[Cluster_SqlServer_TopQueries_WorkerTime] DESC) AS [Cluster_SqlServer_TopQueries_WorkerTime_Rank]
    , ROW_NUMBER() OVER(ORDER BY tq.[Cluster_SqlServer_TopQueries_ExecutionTime] / CASE WHEN [Cluster_SqlServer_TopQueries_ExecutionCount] > 0 THEN [Cluster_SqlServer_TopQueries_ExecutionCount] ELSE 1 END DESC) AS [Cluster_SqlServer_TopQueries_ExecutionTime_Avg_Rank]
    , ROW_NUMBER() OVER(ORDER BY tq.[Cluster_SqlServer_TopQueries_LogicalReads] / CASE WHEN [Cluster_SqlServer_TopQueries_ExecutionCount] > 0 THEN [Cluster_SqlServer_TopQueries_ExecutionCount] ELSE 1 END DESC) AS [Cluster_SqlServer_TopQueries_LogicalReads_Avg_Rank]
    , ROW_NUMBER() OVER(ORDER BY tq.[Cluster_SqlServer_TopQueries_LogicalWrites] / CASE WHEN [Cluster_SqlServer_TopQueries_ExecutionCount] > 0 THEN [Cluster_SqlServer_TopQueries_ExecutionCount] ELSE 1 END DESC) AS [Cluster_SqlServer_TopQueries_LogicalWrites_Avg_Rank]
    , ROW_NUMBER() OVER(ORDER BY tq.[Cluster_SqlServer_TopQueries_PhysicalReads] / CASE WHEN [Cluster_SqlServer_TopQueries_ExecutionCount] > 0 THEN [Cluster_SqlServer_TopQueries_ExecutionCount] ELSE 1 END DESC) AS [Cluster_SqlServer_TopQueries_PhysicalReads_Avg_Rank]
    , ROW_NUMBER() OVER(ORDER BY tq.[Cluster_SqlServer_TopQueries_WorkerTime] / CASE WHEN [Cluster_SqlServer_TopQueries_ExecutionCount] > 0 THEN [Cluster_SqlServer_TopQueries_ExecutionCount] ELSE 1 END  DESC) AS [Cluster_SqlServer_TopQueries_WorkerTime_Avg_Rank]
FROM [data].[Cluster_SqlServer_TopQueries_UnstableSamples_CumulativeDifferential](@MinDate, @MaxDate) tq
INNER JOIN [data].[Cluster_SqlServer_TopQueries_Instances] tqi on tqi.[Id] = tq.[Id]

CROSS APPLY (
        SELECT TOP 1 
                [_PlanHandle] [Cluster_SqlServer_TopQueries_PlanHandle]
          , [_CreateDate] [Cluster_SqlServer_TopQueries_CreateDate] 
        FROM [data].[Cluster_SqlServer_TopQueries_StableSamples] x
        WHERE x.Id = tq.Id AND x.CollectionDate <= @MaxDate
        ORDER BY x.CollectionDate DESC) tqs
WHERE (
    tq.[Cluster_SqlServer_TopQueries_ExecutionCount] > 0
    OR
    tq.[Cluster_SqlServer_TopQueries_ExecutionTime] > 0
    OR
    tq.[Cluster_SqlServer_TopQueries_LogicalReads] > 0
    OR
    tq.[Cluster_SqlServer_TopQueries_LogicalWrites] > 0
    OR
    tq.[Cluster_SqlServer_TopQueries_PhysicalReads] > 0
    OR
    tq.[Cluster_SqlServer_TopQueries_WorkerTime] > 0
)
AND (
    tq.[Cluster_Name] = @Cluster_Name
    AND
    tq.[Cluster_SqlServer_Name] = @Cluster_SqlServer_Name
    AND 
    (@Cluster_SqlServer_TopQueries_DatabaseName IS NULL OR tq.[Cluster_SqlServer_TopQueries_DatabaseName] = @Cluster_SqlServer_TopQueries_DatabaseName)
    AND 
    (@Cluster_SqlServer_TopQueries_SqlHandle IS NULL OR tq.[Cluster_SqlServer_TopQueries_SqlHandle] = @Cluster_SqlServer_TopQueries_SqlHandle)
    AND 
    (@Cluster_SqlServer_TopQueries_StatementStart IS NULL OR tq.[Cluster_SqlServer_TopQueries_StatementStart] = @Cluster_SqlServer_TopQueries_StatementStart)
    AND 
    (@Cluster_SqlServer_TopQueries_StatementEnd IS NULL OR tq.[Cluster_SqlServer_TopQueries_StatementEnd] = @Cluster_SqlServer_TopQueries_StatementEnd)
    )
) s 
WHERE (
    [Cluster_SqlServer_TopQueries_ExecutionCount_Rank] <= @MaxRank
    OR
    [Cluster_SqlServer_TopQueries_ExecutionTime_Rank] <= @MaxRank
    OR
    [Cluster_SqlServer_TopQueries_LogicalReads_Rank] <= @MaxRank
    OR
    [Cluster_SqlServer_TopQueries_LogicalWrites_Rank] <= @MaxRank
    OR
    [Cluster_SqlServer_TopQueries_PhysicalReads_Rank] <= @MaxRank
    OR
    [Cluster_SqlServer_TopQueries_WorkerTime_Rank] <= @MaxRank
    OR
    [Cluster_SqlServer_TopQueries_ExecutionTime_Avg_Rank] <= @MaxRank
    OR
    [Cluster_SqlServer_TopQueries_LogicalReads_Avg_Rank] <= @MaxRank
    OR
    [Cluster_SqlServer_TopQueries_LogicalWrites_Avg_Rank] <= @MaxRank
    OR
    [Cluster_SqlServer_TopQueries_PhysicalReads_Avg_Rank] <= @MaxRank
    OR
    [Cluster_SqlServer_TopQueries_WorkerTime_Avg_Rank] <= @MaxRank
)
ELSE -- This is to ensure that post v4.0 Top query retrieval retrieval works on v3.5 data

WITH [SightedIds]([Id]) AS
(
    -- The statistics on the sightings tables can be wildly inaccurate (e.g. expected 15M rows in a 17M row table, actually 1k rows).
    -- We need to seek by date rather than scanning the whole table, so here we force the date-biased index.
    SELECT DISTINCT [Id]
    FROM [data].[Cluster_SqlServer_TopQueries_Sightings] WITH (INDEX ([Cluster_SqlServer_TopQueries_Sightings_SightingDate_Id])) -- ([SightingDate], [Id])
    WHERE [data].[Cluster_SqlServer_TopQueries_Sightings].[SightingDate] >= @MinDate AND [data].[Cluster_SqlServer_TopQueries_Sightings].[SightingDate] <= @MaxDate
)
SELECT * FROM (
SELECT
      tq.[Id]
        , tq.[ParentId]
        , tq.[IdCollectionDate]
        , tq.[IdCollectionDate_DateTime]
        , tq.[Cluster_Name]
        , tq.[Cluster_SqlServer_Name]
        , tq.[CollectionDate]
    , tq.[Cluster_SqlServer_TopQueries_DatabaseName]
    , tq.[Cluster_SqlServer_TopQueries_SqlHandle]
    , tq.[Cluster_SqlServer_TopQueries_StatementEnd]
    , tq.[Cluster_SqlServer_TopQueries_StatementStart]
    , tq.[Cluster_SqlServer_TopQueries_ExecutionCount]
    , tq.[Cluster_SqlServer_TopQueries_ExecutionTime]
    , tq.[Cluster_SqlServer_TopQueries_LogicalReads]
    , tq.[Cluster_SqlServer_TopQueries_LogicalWrites]
    , tq.[Cluster_SqlServer_TopQueries_PhysicalReads]
    , tq.[Cluster_SqlServer_TopQueries_WorkerTime]
        , tqi.[_QueryText] AS [Cluster_SqlServer_TopQueries_QueryText]
    , tqs.[Cluster_SqlServer_TopQueries_PlanHandle]
    , tqs.[Cluster_SqlServer_TopQueries_CreateDate]
    , ROW_NUMBER() OVER(ORDER BY tq.[Cluster_SqlServer_TopQueries_ExecutionCount] DESC) AS [Cluster_SqlServer_TopQueries_ExecutionCount_Rank]
    , ROW_NUMBER() OVER(ORDER BY tq.[Cluster_SqlServer_TopQueries_ExecutionTime] DESC) AS [Cluster_SqlServer_TopQueries_ExecutionTime_Rank]
    , ROW_NUMBER() OVER(ORDER BY tq.[Cluster_SqlServer_TopQueries_LogicalReads] DESC) AS [Cluster_SqlServer_TopQueries_LogicalReads_Rank]
    , ROW_NUMBER() OVER(ORDER BY tq.[Cluster_SqlServer_TopQueries_LogicalWrites] DESC) AS [Cluster_SqlServer_TopQueries_LogicalWrites_Rank]
    , ROW_NUMBER() OVER(ORDER BY tq.[Cluster_SqlServer_TopQueries_PhysicalReads] DESC) AS [Cluster_SqlServer_TopQueries_PhysicalReads_Rank]
    , ROW_NUMBER() OVER(ORDER BY tq.[Cluster_SqlServer_TopQueries_WorkerTime] DESC) AS [Cluster_SqlServer_TopQueries_WorkerTime_Rank]
    , ROW_NUMBER() OVER(ORDER BY tq.[Cluster_SqlServer_TopQueries_ExecutionTime] / CASE WHEN [Cluster_SqlServer_TopQueries_ExecutionCount] > 0 THEN [Cluster_SqlServer_TopQueries_ExecutionCount] ELSE 1 END DESC) AS [Cluster_SqlServer_TopQueries_ExecutionTime_Avg_Rank]
    , ROW_NUMBER() OVER(ORDER BY tq.[Cluster_SqlServer_TopQueries_LogicalReads] / CASE WHEN [Cluster_SqlServer_TopQueries_ExecutionCount] > 0 THEN [Cluster_SqlServer_TopQueries_ExecutionCount] ELSE 1 END DESC) AS [Cluster_SqlServer_TopQueries_LogicalReads_Avg_Rank]
    , ROW_NUMBER() OVER(ORDER BY tq.[Cluster_SqlServer_TopQueries_LogicalWrites] / CASE WHEN [Cluster_SqlServer_TopQueries_ExecutionCount] > 0 THEN [Cluster_SqlServer_TopQueries_ExecutionCount] ELSE 1 END DESC) AS [Cluster_SqlServer_TopQueries_LogicalWrites_Avg_Rank]
    , ROW_NUMBER() OVER(ORDER BY tq.[Cluster_SqlServer_TopQueries_PhysicalReads] / CASE WHEN [Cluster_SqlServer_TopQueries_ExecutionCount] > 0 THEN [Cluster_SqlServer_TopQueries_ExecutionCount] ELSE 1 END DESC) AS [Cluster_SqlServer_TopQueries_PhysicalReads_Avg_Rank]
    , ROW_NUMBER() OVER(ORDER BY tq.[Cluster_SqlServer_TopQueries_WorkerTime] / CASE WHEN [Cluster_SqlServer_TopQueries_ExecutionCount] > 0 THEN [Cluster_SqlServer_TopQueries_ExecutionCount] ELSE 1 END  DESC) AS [Cluster_SqlServer_TopQueries_WorkerTime_Avg_Rank]
FROM 
(
    SELECT
    [data].[Cluster_SqlServer_TopQueries_Keys].[Id]
    , [data].[Cluster_SqlServer_TopQueries_Keys].[ParentId]
    , [data].[Cluster_SqlServer_TopQueries_Keys].[CollectionDate] AS [IdCollectionDate]
    , [utils].[TicksToDateTime]([data].[Cluster_SqlServer_TopQueries_Keys].[CollectionDate]) AS [IdCollectionDate_DateTime]
    , [data].[Cluster_Keys].[_Name] AS [Cluster_Name]
    , [data].[Cluster_SqlServer_Keys].[_Name] AS [Cluster_SqlServer_Name]
    , [data].[Cluster_SqlServer_TopQueries_Keys].[_DatabaseName] AS [Cluster_SqlServer_TopQueries_DatabaseName]
    , [data].[Cluster_SqlServer_TopQueries_Keys].[_SqlHandle] AS [Cluster_SqlServer_TopQueries_SqlHandle]
    , [data].[Cluster_SqlServer_TopQueries_Keys].[_StatementEnd] AS [Cluster_SqlServer_TopQueries_StatementEnd]
    , [data].[Cluster_SqlServer_TopQueries_Keys].[_StatementStart] AS [Cluster_SqlServer_TopQueries_StatementStart]
    , [RightLeaf].[CollectionDate]
    , ([RightLeaf].[Cluster_SqlServer_TopQueries_ExecutionCount] - ISNULL([LeftLeaf].[Cluster_SqlServer_TopQueries_ExecutionCount],0)) AS [Cluster_SqlServer_TopQueries_ExecutionCount]
    , ([RightLeaf].[Cluster_SqlServer_TopQueries_ExecutionTime] - ISNULL([LeftLeaf].[Cluster_SqlServer_TopQueries_ExecutionTime],0)) AS [Cluster_SqlServer_TopQueries_ExecutionTime]
    , ([RightLeaf].[Cluster_SqlServer_TopQueries_LogicalReads] - ISNULL([LeftLeaf].[Cluster_SqlServer_TopQueries_LogicalReads],0)) AS [Cluster_SqlServer_TopQueries_LogicalReads]
    , ([RightLeaf].[Cluster_SqlServer_TopQueries_LogicalWrites] - ISNULL([LeftLeaf].[Cluster_SqlServer_TopQueries_LogicalWrites],0)) AS [Cluster_SqlServer_TopQueries_LogicalWrites]
    , ([RightLeaf].[Cluster_SqlServer_TopQueries_PhysicalReads] - ISNULL([LeftLeaf].[Cluster_SqlServer_TopQueries_PhysicalReads],0)) AS [Cluster_SqlServer_TopQueries_PhysicalReads]
    , ([RightLeaf].[Cluster_SqlServer_TopQueries_WorkerTime] - ISNULL([LeftLeaf].[Cluster_SqlServer_TopQueries_WorkerTime],0)) AS [Cluster_SqlServer_TopQueries_WorkerTime]
    FROM [SightedIds]
    -- The statistics on the sightings tables can be wildly inaccurate (e.g. expected 400k rows in a 4M row table, actually 2k rows).
    -- This causes SQL Server to prefer scans to seeks, wreaking havoc on query times and the cache.
    -- Forcing LOOP joins causes SQL Server to prefer seeks again.
     INNER LOOP JOIN [data].[Cluster_SqlServer_TopQueries_Keys] ON [data].[Cluster_SqlServer_TopQueries_Keys].[Id] = [SightedIds].[Id]
     INNER LOOP JOIN [data].[Cluster_SqlServer_Keys] ON [data].[Cluster_SqlServer_Keys].[Id] = [data].[Cluster_SqlServer_TopQueries_Keys].[ParentId]
     INNER LOOP JOIN [data].[Cluster_Keys] ON [data].[Cluster_Keys].[Id] = [data].[Cluster_SqlServer_Keys].[ParentId]
    OUTER APPLY
    (
        SELECT TOP 1 [CollectionDate]
        ,  [data].[Cluster_SqlServer_TopQueries_UnstableSamples].[_ExecutionCount] AS [Cluster_SqlServer_TopQueries_ExecutionCount]
        ,  [data].[Cluster_SqlServer_TopQueries_UnstableSamples].[_ExecutionTime] AS [Cluster_SqlServer_TopQueries_ExecutionTime]
        ,  [data].[Cluster_SqlServer_TopQueries_UnstableSamples].[_LogicalReads] AS [Cluster_SqlServer_TopQueries_LogicalReads]
        ,  [data].[Cluster_SqlServer_TopQueries_UnstableSamples].[_LogicalWrites] AS [Cluster_SqlServer_TopQueries_LogicalWrites]
        ,  [data].[Cluster_SqlServer_TopQueries_UnstableSamples].[_PhysicalReads] AS [Cluster_SqlServer_TopQueries_PhysicalReads]
        ,  [data].[Cluster_SqlServer_TopQueries_UnstableSamples].[_WorkerTime] AS [Cluster_SqlServer_TopQueries_WorkerTime]
        FROM [data].[Cluster_SqlServer_TopQueries_UnstableSamples]
        WHERE [data].[Cluster_SqlServer_TopQueries_UnstableSamples].[Id] = [data].[Cluster_SqlServer_TopQueries_Keys].[Id]
          AND [data].[Cluster_SqlServer_TopQueries_UnstableSamples].[CollectionDate] > @MinDate
          AND [data].[Cluster_SqlServer_TopQueries_UnstableSamples].[CollectionDate] <= @MaxDate
        ORDER BY [data].[Cluster_SqlServer_TopQueries_UnstableSamples].[CollectionDate] ASC
    )
     AS [LeftLeaf]CROSS APPLY
    (
        SELECT TOP 1 [CollectionDate]
        ,  [data].[Cluster_SqlServer_TopQueries_UnstableSamples].[_ExecutionCount] AS [Cluster_SqlServer_TopQueries_ExecutionCount]
        ,  [data].[Cluster_SqlServer_TopQueries_UnstableSamples].[_ExecutionTime] AS [Cluster_SqlServer_TopQueries_ExecutionTime]
        ,  [data].[Cluster_SqlServer_TopQueries_UnstableSamples].[_LogicalReads] AS [Cluster_SqlServer_TopQueries_LogicalReads]
        ,  [data].[Cluster_SqlServer_TopQueries_UnstableSamples].[_LogicalWrites] AS [Cluster_SqlServer_TopQueries_LogicalWrites]
        ,  [data].[Cluster_SqlServer_TopQueries_UnstableSamples].[_PhysicalReads] AS [Cluster_SqlServer_TopQueries_PhysicalReads]
        ,  [data].[Cluster_SqlServer_TopQueries_UnstableSamples].[_WorkerTime] AS [Cluster_SqlServer_TopQueries_WorkerTime]
        FROM [data].[Cluster_SqlServer_TopQueries_UnstableSamples]
        WHERE [data].[Cluster_SqlServer_TopQueries_UnstableSamples].[Id] = [data].[Cluster_SqlServer_TopQueries_Keys].[Id]
          AND [data].[Cluster_SqlServer_TopQueries_UnstableSamples].[CollectionDate] > @MinDate
          AND [data].[Cluster_SqlServer_TopQueries_UnstableSamples].[CollectionDate] <= @MaxDate
        ORDER BY [data].[Cluster_SqlServer_TopQueries_UnstableSamples].[CollectionDate] DESC
    )
     AS [RightLeaf]
) tq
INNER JOIN [data].[Cluster_SqlServer_TopQueries_Instances] tqi on tqi.[Id] = tq.[Id]
CROSS APPLY (
        SELECT TOP 1 
                [_PlanHandle] [Cluster_SqlServer_TopQueries_PlanHandle]
          , [_CreateDate] [Cluster_SqlServer_TopQueries_CreateDate] 
        FROM [data].[Cluster_SqlServer_TopQueries_StableSamples] x
        WHERE x.Id = tq.Id AND x.CollectionDate <= @MaxDate
        ORDER BY x.CollectionDate DESC) tqs
WHERE (
    tq.[Cluster_SqlServer_TopQueries_ExecutionCount] > 0
    AND
    tq.[Cluster_SqlServer_TopQueries_ExecutionTime] > 0
    AND
    (   
        tq.[Cluster_SqlServer_TopQueries_LogicalReads] > 0
        OR
        tq.[Cluster_SqlServer_TopQueries_LogicalWrites] > 0
        OR
        tq.[Cluster_SqlServer_TopQueries_PhysicalReads] > 0
        OR
        tq.[Cluster_SqlServer_TopQueries_WorkerTime] > 0
    )
)
AND (
    tq.[Cluster_Name] = @Cluster_Name
    AND
    tq.[Cluster_SqlServer_Name] = @Cluster_SqlServer_Name
    AND 
    (@Cluster_SqlServer_TopQueries_DatabaseName IS NULL OR tq.[Cluster_SqlServer_TopQueries_DatabaseName] = @Cluster_SqlServer_TopQueries_DatabaseName)
    AND 
    (@Cluster_SqlServer_TopQueries_SqlHandle IS NULL OR tq.[Cluster_SqlServer_TopQueries_SqlHandle] = @Cluster_SqlServer_TopQueries_SqlHandle)
    AND 
    (@Cluster_SqlServer_TopQueries_StatementStart IS NULL OR tq.[Cluster_SqlServer_TopQueries_StatementStart] = @Cluster_SqlServer_TopQueries_StatementStart)
    AND 
    (@Cluster_SqlServer_TopQueries_StatementEnd IS NULL OR tq.[Cluster_SqlServer_TopQueries_StatementEnd] = @Cluster_SqlServer_TopQueries_StatementEnd)
    )
) s 
WHERE (
    [Cluster_SqlServer_TopQueries_ExecutionCount_Rank] <= @MaxRank
    OR
    [Cluster_SqlServer_TopQueries_ExecutionTime_Rank] <= @MaxRank
    OR
    [Cluster_SqlServer_TopQueries_LogicalReads_Rank] <= @MaxRank
    OR
    [Cluster_SqlServer_TopQueries_LogicalWrites_Rank] <= @MaxRank
    OR
    [Cluster_SqlServer_TopQueries_PhysicalReads_Rank] <= @MaxRank
    OR
    [Cluster_SqlServer_TopQueries_WorkerTime_Rank] <= @MaxRank
    OR
    [Cluster_SqlServer_TopQueries_ExecutionTime_Avg_Rank] <= @MaxRank
    OR
    [Cluster_SqlServer_TopQueries_LogicalReads_Avg_Rank] <= @MaxRank
    OR
    [Cluster_SqlServer_TopQueries_LogicalWrites_Avg_Rank] <= @MaxRank
    OR
    [Cluster_SqlServer_TopQueries_PhysicalReads_Avg_Rank] <= @MaxRank
    OR
    [Cluster_SqlServer_TopQueries_WorkerTime_Avg_Rank] <= @MaxRank
)
',N'@Cluster_SqlServer_TopQueries_QueryWaitStats_WaitType nvarchar(4000),@MinDate bigint,@MaxDate bigint,@MaxRank bigint,@Cluster_Name nvarchar(21),@Cluster_SqlServer_Name nvarchar(4000),@Cluster_SqlServer_TopQueries_DatabaseName nvarchar(4000),@Cluster_SqlServer_TopQueries_SqlHandle nvarchar(4000),@Cluster_SqlServer_TopQueries_StatementEnd nvarchar(4000),@Cluster_SqlServer_TopQueries_StatementStart nvarchar(4000)',@Cluster_SqlServer_TopQueries_QueryWaitStats_WaitType=NULL,@MinDate=635281898328112865,@MaxDate=635281934328112865,@MaxRank=10,@Cluster_Name=N'sqlcluster2.itc.local',@Cluster_SqlServer_Name=N'',@Cluster_SqlServer_TopQueries_DatabaseName=NULL,@Cluster_SqlServer_TopQueries_SqlHandle=NULL,@Cluster_SqlServer_TopQueries_StatementEnd=NULL,@Cluster_SqlServer_TopQueries_StatementStart=NULL

After dissecting the query further, I narrowed it down to the following function: data.Cluster_SqlServer_TopQueries_UnstableSamples_CumulativeDifferential which was consuming most of the resources.

Thanks,
Avinash

chriskelly

To improve things you could try

(1) Rebuilding all indexes in the data.ClusterSqlServer_TopQueries* tables. Adding this to the maintenance plan is recommended.
(2) Reducing the purge window for top queries to as small a window as you are happy with. I'd suggest 3 days, personally. You can always gradually increase this later.

avibooks

Hi Chris,

Thanks for your response.

The database RedGateMonitor used as the SQL Monitor 4 data repository is already in a maintenance plan and the indexes are reorganized or rebuilt on a daily basis based on their fragmentation level.

Our current purge window for the Performance diagnostics data is 1 week. I will try reducing the purge window to 3 days to see if this helps. Just wanted to point out that in your product documentation it states that the default purge window for this type of data is 1 month (http://documentation.red-gate.com/display/SM4/Purging+SQL+Monitor+data) so was just wondering what the impact would have been if we were storing 1 months worth of that type of data.

This issue was not present in SQL Monitor v3.5 though.

Thank you for your help in this matter.

Regards,
Avinash

avibooks

Hi Chris,

I have reduced the data purge window for the "Performance diagnostics data" and "SQL process data" to 3 days.

However, this does not seem to have solved the issue stated initially.

Regards,
Avinash

avibooks

I have not heard back yet regarding this issue. Is there a resolution to the problem?

Thanks,
Avinash

chriskelly

I have resent an email from a couple of days ago. Please let me know if you do not receive it.

avibooks

Hi Chris,

Thanks for your response. I received your email a few hours ago and have carried out the changes that you suggested.

However, performing those changes entirely disables the data collection for those types of metrics. After performing the change there is no data displayed under Top 10 Waits" or "Top 10 Queries" which is not really what we want.

Even though the data collection is disabled, this does not prevent that query (in the initial post) from executing. It just means that the query runs in practically no time with extremely low resource consumption due to there being no data present for the period run for.

Thanks and Regards,
Avinash

chriskelly

Thank you for your reply.

Yes, this is as I thought it would be. The query that you have mentioned is one which performs processing on the collected data to enable to various features that use the data to make sense. As the collections are disabled, the query has no processing to perform and there is no data to display in the Top 10 waits etc.

The fact that the data collections themselves do not appear to be problematic but the subsequent processing appears to be leads me to suggest the following:
- leave the collections disabled. The features will therefore have no data to display, but the data repository host will not see the pressure on its resources.
- migrate the database to a machine with more resources available. This wouldn't change the usage, but would ensure that there are sufficient resources available to handle it.

I know that neither of these options are particularly great. However, the only other option would be to get a developer to change the frequency of the execution of the query you have quoted. However, this wouldn't affect the spikes, it would only affect the frequency of them.

I hope this makes sense. Please let me know if you have any questions.