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

Edited: Link to full query plan (sorry, it doesn't seem to have rendered as well as some query plans I've seen)

Partial query plan - after flush (good performance) Partial query plan after flush Edited Link to full query plan

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
6
  • You should paste your plans as sqlplan, not as a picture, you have some parameters inside and we need to know for WHATvalues of these parameters the plans were built.
    – sepupic
    Jan 24, 2019 at 6:53
  • Please use brentozar.com/pastetheplan and update your question with the links to your plans
    – sepupic
    Jan 24, 2019 at 6:54
  • Add OPTION(RECOMPILE) to your query Jan 24, 2019 at 7:07
  • 1
    >>>Add OPTION(RECOMPILE) to your query <<< It may be not an option for frequently used queries on busy OLTP systems. OP said the plans are big, big plans take some time to recompile that may be not acceptable
    – sepupic
    Jan 24, 2019 at 7:10
  • @sepupic, you're right, that's why it's just a comment. I would split the query to remove its condition @PropertyId IS NULL outside, but who knows which indexes are there. If schemas and plans were provided, the answer could be better. Jan 24, 2019 at 7:47

3 Answers 3

2

If you were on 2016 or higher, you could have used Query Store to track your plans and force the good one through Query Store functionality. You can still force the plan,though, easiest is probably sp_create_plan_guide_from_handle: https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-create-plan-guide-from-handle-transact-sql?view=sql-server-2017 .

Other options could be to use OPTIMIZE FOR or even RECOMPILE query hints, depending on the circumstances and what suits you best.

2

Your query seems to be some kind of report where some parameters can be omitted.

Conditions like these:

WHERE (@TenantId IS NULL
      OR O.TenantId = @TenantId)
   AND (@Soh IS NULL
      OR M.Soh = @Soh)
   AND (@PropertyId IS NULL
      OR L.[PropertyId] = @PropertyId)


WHERE (@Id IS NULL
   OR PenIds.[Id] = @Id)
AND (@PropertyId IS NULL
   OR PenIds.[PropertyId] = @PropertyId)


WHERE (@TenantId IS NULL
      OR O.TenantId = @TenantId)
    AND (@Deleted IS NULL
      OR Pen.Deleted = @Deleted) 

always lead to suboptimal plans.

When I need to produce a report of this kind I use dynamic sql which may seem to be difficult to read but always produce good plans because only conditions that have values on every execution are used. In this case you examine every condition, and if the corresponding parameter was supplied you add this condition to you query that you construct dynamically.

Here is a greate article that may help you: Dynamic Search Conditions in T‑SQL and what I'd used in this situation is described in 5.5 Implementing search_orders with a Parameterised Query. I'll post here the example from this article in case it may change url:

CREATE PROCEDURE search_orders_1                                   --  1
                 @orderid     int          = NULL,                 --  2
                 @fromdate    datetime     = NULL,                 --  3
                 @todate      datetime     = NULL,                 --  4
                 @minprice    money        = NULL,                 --  5
                 @maxprice    money        = NULL,                 --  6
                 @custid      nchar(5)     = NULL,                 --  7
                 @custname    nvarchar(40) = NULL,                 --  8
                 @city        nvarchar(15) = NULL,                 --  9
                 @region      nvarchar(15) = NULL,                 -- 10
                 @country     nvarchar(15) = NULL,                 -- 11
                 @prodid      int          = NULL,                 -- 12
                 @prodname    nvarchar(40) = NULL,                 -- 13
                 @employeestr varchar(MAX) = NULL,                 -- 14
                 @employeetbl intlist_tbltype READONLY,            -- 15
                 @debug       bit          = 0 AS                  -- 16
                                                                   -- 17
DECLARE @sql        nvarchar(MAX),                                 -- 18
        @paramlist  nvarchar(4000),                                -- 19
        @nl         char(2) = char(13) + char(10)                  -- 20
                                                                   -- 21
SELECT @sql =                                                      -- 22
    'SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,     -- 23
            c.CustomerID, c.CompanyName, c.Address, c.City,        -- 24
            c.Region,  c.PostalCode, c.Country, c.Phone,           -- 25
            p.ProductID, p.ProductName, p.UnitsInStock,            -- 26
            p.UnitsOnOrder, o.EmployeeID                           -- 27
     FROM   dbo.Orders o                                           -- 28
     JOIN   dbo.[Order Details] od ON o.OrderID = od.OrderID       -- 29
     JOIN   dbo.Customers c ON o.CustomerID = c.CustomerID         -- 30
     JOIN   dbo.Products p ON p.ProductID = od.ProductID           -- 31
     WHERE  1 = 1' + @nl                                           -- 32
                                                                   -- 33
IF @orderid IS NOT NULL                                            -- 34
   SELECT @sql += ' AND o.OrderID = @orderid' +                    -- 35
                  ' AND od.OrderID = @orderid' + @nl               -- 36
                                                                   -- 37
IF @fromdate IS NOT NULL                                           -- 38
   SELECT @sql += ' AND o.OrderDate >= @fromdate' + @nl            -- 39
                                                                   -- 40
IF @todate IS NOT NULL                                             -- 41
   SELECT @sql += ' AND o.OrderDate <= @todate'  + @nl             -- 42
                                                                   -- 43
IF @minprice IS NOT NULL                                           -- 44
   SELECT @sql += ' AND od.UnitPrice >= @minprice'  + @nl          -- 45
                                                                   -- 46
IF @maxprice IS NOT NULL                                           -- 47
   SELECT @sql += ' AND od.UnitPrice <= @maxprice'  + @nl          -- 48
                                                                   -- 49
IF @custid IS NOT NULL                                             -- 50
   SELECT @sql += ' AND o.CustomerID = @custid' +                  -- 51
                  ' AND c.CustomerID = @custid' + @nl              -- 52
                                                                   -- 53
IF @custname IS NOT NULL                                           -- 54
   SELECT @sql += ' AND c.CompanyName LIKE @custname + ''%''' + @nl -- 55
                                                                   -- 56
IF @city IS NOT NULL                                               -- 57
   SELECT @sql += ' AND c.City = @city' + @nl                      -- 58
                                                                   -- 59
IF @region IS NOT NULL                                             -- 60
   SELECT @sql += ' AND c.Region = @region' + @nl                  -- 61
                                                                   -- 62
IF @country IS NOT NULL                                            -- 63
   SELECT @sql += ' AND c.Country = @country' + @nl                -- 64
                                                                   -- 65
IF @prodid IS NOT NULL                                             -- 66
   SELECT @sql += ' AND od.ProductID = @prodid' +                  -- 67
                  ' AND p.ProductID = @prodid' + @nl               -- 68
                                                                   -- 69
IF @prodname IS NOT NULL                                            --70
   SELECT @sql += ' AND p.ProductName LIKE @prodname + ''%''' + @nl-- 71
                                                                   -- 72
IF @employeestr IS NOT NULL                                        -- 73
   SELECT @sql += ' AND o.EmployeeID IN' +                         -- 74
                  ' (SELECT number FROM dbo.intlist_to_tbl(@employeestr))' + @nl
                                                                   -- 76
IF EXISTS (SELECT * FROM @employeetbl)                             -- 77
   SELECT @sql += ' AND o.EmployeeID IN (SELECT val FROM @employeetbl)' + @nl
                                                                   -- 79
SELECT @sql += ' ORDER BY o.OrderID' + @nl                         -- 80
                                                                   -- 81
IF @debug = 1                                                      -- 82
   PRINT @sql                                                      -- 83
                                                                   -- 84
SELECT @paramlist = '@orderid     int,                             -- 85
                     @fromdate    datetime,                        -- 86
                     @todate      datetime,                        -- 87
                     @minprice    money,                           -- 88
                     @maxprice    money,                           -- 89
                     @custid      nchar(5),                        -- 90
                     @custname    nvarchar(40),                    -- 91
                     @city        nvarchar(15),                    -- 92
                     @region      nvarchar(15),                    -- 93
                     @country     nvarchar(15),                    -- 94
                     @prodid      int,                             -- 95
                     @prodname    nvarchar(40),                    -- 96
                     @employeestr varchar(MAX),                    -- 97
                     @employeetbl intlist_tbltype READONLY'        -- 98
                                                                   -- 99
EXEC sp_executesql @sql, @paramlist,                               -- 100
                   @orderid, @fromdate, @todate, @minprice,        -- 101
                   @maxprice,  @custid, @custname, @city, @region, -- 102
                   @country, @prodid, @prodname, @employeestr, @employeetbl
0

Predicate like this WHERE (@TenantId IS NULL OR O.TenantId = @TenantId) create Parameter Sniffing problem.

One of solution is to use Hint , Option Recompile it will compile the proc each time it is exceuted,so every time new plan is created so it perform better.

We should avoid Option Recompile whereever possible because it require additional system resource to recompile store procedure.

Most basic thing is Query optimization i.e. it can make even Parameter Sniffing problem also bearable.

You are repeating same set of predicate and resultset multiple times avoiding to which can boost the performance tremendously.

My script below will not run as it is,you can understand the idea and make it run.

Store the value of Security table in temp table,

Insert into #SecurityTemp(id)
 SELECT Pen.Id,
       Pen.PropertyId
    FROM [Security] O 
    WHERE (@TenantId IS NULL
      OR O.TenantId = @TenantId)

      --Create index if it contain more than 100 rows
      Create clustered index ix_security_temp on #SecurityTemp(id)

Again instead of using Table Pen,Property etc again and again join just once and get all columns which is require in your final resultset.

;WITH PenIds as
      (

      SELECT Pen.Id,
       Pen.PropertyId
        -- include all columns that is required and rest as null to sync with UNION ALL
    FROM Pen
    WHERE  Exists(select 1 from #SecurityTemp O where Property.SecurityId = O.Id)
    and ((@Deleted IS NULL)
      OR (Pen.Deleted = @Deleted) )


   UNION ALL 

   SELECT 0 AS Id,
      Property.Id AS PropertyId
      -- include all columns that is required and rest as null to sync with UNION ALL
   FROM Property
   WHERE  Exists(select 1 from #SecurityTemp O where Property.SecurityId = O.Id)

   AND Property.[External] = 0
   AND Property.Deleted = 0

      )
  ,AggregateData as
  (
  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 (@Soh IS NULL
      OR M.Soh = @Soh)
   AND (@PropertyId IS NULL
      OR L.[PropertyId] = @PropertyId)
      and Exists(select 1 from #SecurityTemp O where L.SecurityId = O.Id)
    --  and exists(select 1 from  PenIds where PenIds.Id = AggregateData.PenId AND PenIds.PropertyId = AggregateData.PropertyId)
   GROUP BY L.PropertyId,
        COALESCE(L.PenId, 0)
  )
  select * from PenIds
  left join AggregateData
   where ((@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

Read my comments inside PenIds CTE

After I have optimize the query like above and after Index Tuning I will go for dynamic Sql like suggested.

Understand the difference.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.