Query produces two query plans (at different times). How do I always produce the better query plan?












0















I have a fairly straightforward query that usually performs well (i.e. returns in a couple of seconds) but sometimes it will timeout and I need to flush the query plan before it will start behaving again.



I was able to get both query plans through SQL Profiler - so I know what a good plan looks like, but I'm not sure how to encourage SQL Server to always produce the better plan.



The query plans are very similar (and big) so I will include a screenshot of only the parts that are different. I can post the entire XML if requested.



Partial query plan - before flush (bad performance)
Partial query plan before flush



Partial query plan - after flush (good performance)
Partial query plan after flush



Query



SELECT Records.[Id],
Property.Id AS [PropertyId],
CASE
WHEN Records.Id IS NOT NULL THEN Records.[Name]
ELSE '(In Transit)'
END AS [Name],
Records.[Area],
Records.[AreaUnitId],
Records.[Lng],
Records.[Lat],
Records.[Zoom],
Records.[Code],
Records.[Guid],
[Property].[SecurityId],
Records.[DateCreated],
Records.[Modified],
Records.[DateModified],
Records.[ModifiedByUserId],
Records.[SortOrder],
Records.[Deleted],
COALESCE(Property.Name, '') AS PropertyName,
COALESCE(Unit.Name, '') AS UnitName,
COALESCE(Unit.Code, '') AS UnitCode,
COALESCE(AggregateData.Head, 0) AS Head,
COALESCE(AggregateData.TotalWeight, 0) AS TotalWeight,
CASE
WHEN COALESCE(AggregateData.Head, 0) > 0 THEN AggregateData.TotalWeight/AggregateData.Head
ELSE 0
END AS AverageWeight
FROM
(
SELECT Pen.Id,
Pen.PropertyId
FROM Pen
LEFT JOIN [Security] O ON Pen.SecurityId = O.Id
WHERE (@TenantId IS NULL
OR O.TenantId = @TenantId)
AND (@Deleted IS NULL
OR Pen.Deleted = @Deleted)

-- Add null row per property, so animals in transit are included further down
UNION ALL
SELECT 0 AS Id,
Property.Id AS PropertyId
FROM Property
LEFT JOIN [Security] O ON Property.SecurityId = O.Id
WHERE (@TenantId IS NULL
OR O.TenantId = @TenantId)
AND Property.[External] = 0
AND Property.Deleted = 0
) AS PenIds
LEFT JOIN
(
SELECT L.PropertyId,
COALESCE(L.PenId, 0) AS PenId,
COUNT(M.BeastId) AS Head,
SUM(WeightLog.[Weight]) AS TotalWeight
FROM BeastMaster M
LEFT JOIN BeastMovement L ON M.BeastMovementId = L.Id
LEFT JOIN WeightLog ON M.WeightLogId = WeightLog.Id
LEFT JOIN [Security] O ON L.SecurityId = O.Id
WHERE (@TenantId IS NULL
OR O.TenantId = @TenantId)
AND (@Soh IS NULL
OR M.Soh = @Soh)
AND (@PropertyId IS NULL
OR L.[PropertyId] = @PropertyId)
GROUP BY L.PropertyId,
COALESCE(L.PenId, 0)
) AS AggregateData ON PenIds.Id = AggregateData.PenId
AND PenIds.PropertyId = AggregateData.PropertyId
LEFT JOIN [Pen] AS Records ON Records.Id = PenIds.Id
LEFT JOIN Property ON PenIds.PropertyId = Property.Id
LEFT JOIN Unit ON Records.AreaUnitId = Unit.Id
WHERE (@Id IS NULL
OR PenIds.[Id] = @Id)
AND (@PropertyId IS NULL
OR PenIds.[PropertyId] = @PropertyId)
AND
(
@ManagementAreaId IS NULL
OR EXISTS
(
-- Properties linked to Management Area
SELECT *
FROM [Permission] Pm
WHERE Pm.Deleted = 0
AND Pm.UserGroupId = @ManagementAreaId
AND Pm.RequestedSecurityId = Property.SecurityId )
)
ORDER BY Records.Name








share







New contributor




Graham is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

























    0















    I have a fairly straightforward query that usually performs well (i.e. returns in a couple of seconds) but sometimes it will timeout and I need to flush the query plan before it will start behaving again.



    I was able to get both query plans through SQL Profiler - so I know what a good plan looks like, but I'm not sure how to encourage SQL Server to always produce the better plan.



    The query plans are very similar (and big) so I will include a screenshot of only the parts that are different. I can post the entire XML if requested.



    Partial query plan - before flush (bad performance)
    Partial query plan before flush



    Partial query plan - after flush (good performance)
    Partial query plan after flush



    Query



    SELECT Records.[Id],
    Property.Id AS [PropertyId],
    CASE
    WHEN Records.Id IS NOT NULL THEN Records.[Name]
    ELSE '(In Transit)'
    END AS [Name],
    Records.[Area],
    Records.[AreaUnitId],
    Records.[Lng],
    Records.[Lat],
    Records.[Zoom],
    Records.[Code],
    Records.[Guid],
    [Property].[SecurityId],
    Records.[DateCreated],
    Records.[Modified],
    Records.[DateModified],
    Records.[ModifiedByUserId],
    Records.[SortOrder],
    Records.[Deleted],
    COALESCE(Property.Name, '') AS PropertyName,
    COALESCE(Unit.Name, '') AS UnitName,
    COALESCE(Unit.Code, '') AS UnitCode,
    COALESCE(AggregateData.Head, 0) AS Head,
    COALESCE(AggregateData.TotalWeight, 0) AS TotalWeight,
    CASE
    WHEN COALESCE(AggregateData.Head, 0) > 0 THEN AggregateData.TotalWeight/AggregateData.Head
    ELSE 0
    END AS AverageWeight
    FROM
    (
    SELECT Pen.Id,
    Pen.PropertyId
    FROM Pen
    LEFT JOIN [Security] O ON Pen.SecurityId = O.Id
    WHERE (@TenantId IS NULL
    OR O.TenantId = @TenantId)
    AND (@Deleted IS NULL
    OR Pen.Deleted = @Deleted)

    -- Add null row per property, so animals in transit are included further down
    UNION ALL
    SELECT 0 AS Id,
    Property.Id AS PropertyId
    FROM Property
    LEFT JOIN [Security] O ON Property.SecurityId = O.Id
    WHERE (@TenantId IS NULL
    OR O.TenantId = @TenantId)
    AND Property.[External] = 0
    AND Property.Deleted = 0
    ) AS PenIds
    LEFT JOIN
    (
    SELECT L.PropertyId,
    COALESCE(L.PenId, 0) AS PenId,
    COUNT(M.BeastId) AS Head,
    SUM(WeightLog.[Weight]) AS TotalWeight
    FROM BeastMaster M
    LEFT JOIN BeastMovement L ON M.BeastMovementId = L.Id
    LEFT JOIN WeightLog ON M.WeightLogId = WeightLog.Id
    LEFT JOIN [Security] O ON L.SecurityId = O.Id
    WHERE (@TenantId IS NULL
    OR O.TenantId = @TenantId)
    AND (@Soh IS NULL
    OR M.Soh = @Soh)
    AND (@PropertyId IS NULL
    OR L.[PropertyId] = @PropertyId)
    GROUP BY L.PropertyId,
    COALESCE(L.PenId, 0)
    ) AS AggregateData ON PenIds.Id = AggregateData.PenId
    AND PenIds.PropertyId = AggregateData.PropertyId
    LEFT JOIN [Pen] AS Records ON Records.Id = PenIds.Id
    LEFT JOIN Property ON PenIds.PropertyId = Property.Id
    LEFT JOIN Unit ON Records.AreaUnitId = Unit.Id
    WHERE (@Id IS NULL
    OR PenIds.[Id] = @Id)
    AND (@PropertyId IS NULL
    OR PenIds.[PropertyId] = @PropertyId)
    AND
    (
    @ManagementAreaId IS NULL
    OR EXISTS
    (
    -- Properties linked to Management Area
    SELECT *
    FROM [Permission] Pm
    WHERE Pm.Deleted = 0
    AND Pm.UserGroupId = @ManagementAreaId
    AND Pm.RequestedSecurityId = Property.SecurityId )
    )
    ORDER BY Records.Name








    share







    New contributor




    Graham is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
    Check out our Code of Conduct.























      0












      0








      0








      I have a fairly straightforward query that usually performs well (i.e. returns in a couple of seconds) but sometimes it will timeout and I need to flush the query plan before it will start behaving again.



      I was able to get both query plans through SQL Profiler - so I know what a good plan looks like, but I'm not sure how to encourage SQL Server to always produce the better plan.



      The query plans are very similar (and big) so I will include a screenshot of only the parts that are different. I can post the entire XML if requested.



      Partial query plan - before flush (bad performance)
      Partial query plan before flush



      Partial query plan - after flush (good performance)
      Partial query plan after flush



      Query



      SELECT Records.[Id],
      Property.Id AS [PropertyId],
      CASE
      WHEN Records.Id IS NOT NULL THEN Records.[Name]
      ELSE '(In Transit)'
      END AS [Name],
      Records.[Area],
      Records.[AreaUnitId],
      Records.[Lng],
      Records.[Lat],
      Records.[Zoom],
      Records.[Code],
      Records.[Guid],
      [Property].[SecurityId],
      Records.[DateCreated],
      Records.[Modified],
      Records.[DateModified],
      Records.[ModifiedByUserId],
      Records.[SortOrder],
      Records.[Deleted],
      COALESCE(Property.Name, '') AS PropertyName,
      COALESCE(Unit.Name, '') AS UnitName,
      COALESCE(Unit.Code, '') AS UnitCode,
      COALESCE(AggregateData.Head, 0) AS Head,
      COALESCE(AggregateData.TotalWeight, 0) AS TotalWeight,
      CASE
      WHEN COALESCE(AggregateData.Head, 0) > 0 THEN AggregateData.TotalWeight/AggregateData.Head
      ELSE 0
      END AS AverageWeight
      FROM
      (
      SELECT Pen.Id,
      Pen.PropertyId
      FROM Pen
      LEFT JOIN [Security] O ON Pen.SecurityId = O.Id
      WHERE (@TenantId IS NULL
      OR O.TenantId = @TenantId)
      AND (@Deleted IS NULL
      OR Pen.Deleted = @Deleted)

      -- Add null row per property, so animals in transit are included further down
      UNION ALL
      SELECT 0 AS Id,
      Property.Id AS PropertyId
      FROM Property
      LEFT JOIN [Security] O ON Property.SecurityId = O.Id
      WHERE (@TenantId IS NULL
      OR O.TenantId = @TenantId)
      AND Property.[External] = 0
      AND Property.Deleted = 0
      ) AS PenIds
      LEFT JOIN
      (
      SELECT L.PropertyId,
      COALESCE(L.PenId, 0) AS PenId,
      COUNT(M.BeastId) AS Head,
      SUM(WeightLog.[Weight]) AS TotalWeight
      FROM BeastMaster M
      LEFT JOIN BeastMovement L ON M.BeastMovementId = L.Id
      LEFT JOIN WeightLog ON M.WeightLogId = WeightLog.Id
      LEFT JOIN [Security] O ON L.SecurityId = O.Id
      WHERE (@TenantId IS NULL
      OR O.TenantId = @TenantId)
      AND (@Soh IS NULL
      OR M.Soh = @Soh)
      AND (@PropertyId IS NULL
      OR L.[PropertyId] = @PropertyId)
      GROUP BY L.PropertyId,
      COALESCE(L.PenId, 0)
      ) AS AggregateData ON PenIds.Id = AggregateData.PenId
      AND PenIds.PropertyId = AggregateData.PropertyId
      LEFT JOIN [Pen] AS Records ON Records.Id = PenIds.Id
      LEFT JOIN Property ON PenIds.PropertyId = Property.Id
      LEFT JOIN Unit ON Records.AreaUnitId = Unit.Id
      WHERE (@Id IS NULL
      OR PenIds.[Id] = @Id)
      AND (@PropertyId IS NULL
      OR PenIds.[PropertyId] = @PropertyId)
      AND
      (
      @ManagementAreaId IS NULL
      OR EXISTS
      (
      -- Properties linked to Management Area
      SELECT *
      FROM [Permission] Pm
      WHERE Pm.Deleted = 0
      AND Pm.UserGroupId = @ManagementAreaId
      AND Pm.RequestedSecurityId = Property.SecurityId )
      )
      ORDER BY Records.Name








      share







      New contributor




      Graham is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.












      I have a fairly straightforward query that usually performs well (i.e. returns in a couple of seconds) but sometimes it will timeout and I need to flush the query plan before it will start behaving again.



      I was able to get both query plans through SQL Profiler - so I know what a good plan looks like, but I'm not sure how to encourage SQL Server to always produce the better plan.



      The query plans are very similar (and big) so I will include a screenshot of only the parts that are different. I can post the entire XML if requested.



      Partial query plan - before flush (bad performance)
      Partial query plan before flush



      Partial query plan - after flush (good performance)
      Partial query plan after flush



      Query



      SELECT Records.[Id],
      Property.Id AS [PropertyId],
      CASE
      WHEN Records.Id IS NOT NULL THEN Records.[Name]
      ELSE '(In Transit)'
      END AS [Name],
      Records.[Area],
      Records.[AreaUnitId],
      Records.[Lng],
      Records.[Lat],
      Records.[Zoom],
      Records.[Code],
      Records.[Guid],
      [Property].[SecurityId],
      Records.[DateCreated],
      Records.[Modified],
      Records.[DateModified],
      Records.[ModifiedByUserId],
      Records.[SortOrder],
      Records.[Deleted],
      COALESCE(Property.Name, '') AS PropertyName,
      COALESCE(Unit.Name, '') AS UnitName,
      COALESCE(Unit.Code, '') AS UnitCode,
      COALESCE(AggregateData.Head, 0) AS Head,
      COALESCE(AggregateData.TotalWeight, 0) AS TotalWeight,
      CASE
      WHEN COALESCE(AggregateData.Head, 0) > 0 THEN AggregateData.TotalWeight/AggregateData.Head
      ELSE 0
      END AS AverageWeight
      FROM
      (
      SELECT Pen.Id,
      Pen.PropertyId
      FROM Pen
      LEFT JOIN [Security] O ON Pen.SecurityId = O.Id
      WHERE (@TenantId IS NULL
      OR O.TenantId = @TenantId)
      AND (@Deleted IS NULL
      OR Pen.Deleted = @Deleted)

      -- Add null row per property, so animals in transit are included further down
      UNION ALL
      SELECT 0 AS Id,
      Property.Id AS PropertyId
      FROM Property
      LEFT JOIN [Security] O ON Property.SecurityId = O.Id
      WHERE (@TenantId IS NULL
      OR O.TenantId = @TenantId)
      AND Property.[External] = 0
      AND Property.Deleted = 0
      ) AS PenIds
      LEFT JOIN
      (
      SELECT L.PropertyId,
      COALESCE(L.PenId, 0) AS PenId,
      COUNT(M.BeastId) AS Head,
      SUM(WeightLog.[Weight]) AS TotalWeight
      FROM BeastMaster M
      LEFT JOIN BeastMovement L ON M.BeastMovementId = L.Id
      LEFT JOIN WeightLog ON M.WeightLogId = WeightLog.Id
      LEFT JOIN [Security] O ON L.SecurityId = O.Id
      WHERE (@TenantId IS NULL
      OR O.TenantId = @TenantId)
      AND (@Soh IS NULL
      OR M.Soh = @Soh)
      AND (@PropertyId IS NULL
      OR L.[PropertyId] = @PropertyId)
      GROUP BY L.PropertyId,
      COALESCE(L.PenId, 0)
      ) AS AggregateData ON PenIds.Id = AggregateData.PenId
      AND PenIds.PropertyId = AggregateData.PropertyId
      LEFT JOIN [Pen] AS Records ON Records.Id = PenIds.Id
      LEFT JOIN Property ON PenIds.PropertyId = Property.Id
      LEFT JOIN Unit ON Records.AreaUnitId = Unit.Id
      WHERE (@Id IS NULL
      OR PenIds.[Id] = @Id)
      AND (@PropertyId IS NULL
      OR PenIds.[PropertyId] = @PropertyId)
      AND
      (
      @ManagementAreaId IS NULL
      OR EXISTS
      (
      -- Properties linked to Management Area
      SELECT *
      FROM [Permission] Pm
      WHERE Pm.Deleted = 0
      AND Pm.UserGroupId = @ManagementAreaId
      AND Pm.RequestedSecurityId = Property.SecurityId )
      )
      ORDER BY Records.Name






      sql-server sql-server-2014 execution-plan





      share







      New contributor




      Graham is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.










      share







      New contributor




      Graham is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.








      share



      share






      New contributor




      Graham is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.









      asked 8 mins ago









      GrahamGraham

      1




      1




      New contributor




      Graham is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.





      New contributor





      Graham is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.






      Graham is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.






















          0






          active

          oldest

          votes











          Your Answer








          StackExchange.ready(function() {
          var channelOptions = {
          tags: "".split(" "),
          id: "182"
          };
          initTagRenderer("".split(" "), "".split(" "), channelOptions);

          StackExchange.using("externalEditor", function() {
          // Have to fire editor after snippets, if snippets enabled
          if (StackExchange.settings.snippets.snippetsEnabled) {
          StackExchange.using("snippets", function() {
          createEditor();
          });
          }
          else {
          createEditor();
          }
          });

          function createEditor() {
          StackExchange.prepareEditor({
          heartbeatType: 'answer',
          autoActivateHeartbeat: false,
          convertImagesToLinks: false,
          noModals: true,
          showLowRepImageUploadWarning: true,
          reputationToPostImages: null,
          bindNavPrevention: true,
          postfix: "",
          imageUploader: {
          brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
          contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
          allowUrls: true
          },
          onDemand: true,
          discardSelector: ".discard-answer"
          ,immediatelyShowMarkdownHelp:true
          });


          }
          });






          Graham is a new contributor. Be nice, and check out our Code of Conduct.










          draft saved

          draft discarded


















          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f227930%2fquery-produces-two-query-plans-at-different-times-how-do-i-always-produce-the%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown

























          0






          active

          oldest

          votes








          0






          active

          oldest

          votes









          active

          oldest

          votes






          active

          oldest

          votes








          Graham is a new contributor. Be nice, and check out our Code of Conduct.










          draft saved

          draft discarded


















          Graham is a new contributor. Be nice, and check out our Code of Conduct.













          Graham is a new contributor. Be nice, and check out our Code of Conduct.












          Graham is a new contributor. Be nice, and check out our Code of Conduct.
















          Thanks for contributing an answer to Database Administrators Stack Exchange!


          • Please be sure to answer the question. Provide details and share your research!

          But avoid



          • Asking for help, clarification, or responding to other answers.

          • Making statements based on opinion; back them up with references or personal experience.


          To learn more, see our tips on writing great answers.




          draft saved


          draft discarded














          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f227930%2fquery-produces-two-query-plans-at-different-times-how-do-i-always-produce-the%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown





















































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown

































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown







          Popular posts from this blog

          Liste der Baudenkmale in Friedland (Mecklenburg)

          Single-Malt-Whisky

          Czorneboh