-1
SELECT   Col1, Col2, Col3, Col4, Col5, Col6, Col25...
FROM     Tabl1
WHERE    (   Col30 IS NOT NULL
       AND   Col28 <> @P0
       AND   DATEDIFF(
                 dd,
                 Col31,
                 CAST(SUBSTRING(@P1, 1, 4) + '-' + SUBSTRING(@P2, 5, 2) + '-' + SUBSTRING(@P3, 7, 2) + 'T00:00:00.000' AS DATETIME)) <= @P4
       AND   DATEDIFF(
                 dd,
                 Col31,
                 CAST(SUBSTRING(@P5, 1, 4) + '-' + SUBSTRING(@P6, 5, 2) + '-' + SUBSTRING(@P7, 7, 2) + 'T00:00:00.000' AS DATETIME)) >= @P8 )
AND      Col27 = @P9
ORDER BY 18 ASC, 31 DESC, 30 ASC;

The above example query is from third party application. I have no control over the application. The query is more like user query.

The query uses an index seek but it has a key lookup for 20 columns.

When I add 20 included columns in the non-clustered index, the query does not seek the index anymore - it uses a non-clustered index scan. The non-clustered index scan resolves the deadlock, but CPU usage is 15% higher than using index seek + key lookup.

Index keys are Col30,Col28,Col27,Col31,ClusteredIndexKey and 20 columns are included columns.

I am using SQL Server 2016 SP2.

Now, I enabled query store in prod. I see 3 plan_id, One of them failed. Triangle sign, The other two has the same query execution plan but they have different plan_id. One takes longer than the other one. Any ideas?

Any thoughts?

closed as unclear what you're asking by Erik Darling, hot2use, LowlyDBA, Sean Gallardy, Max Vernon Feb 22 at 16:24

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    Are you really passing in a single date YYYYMMDD three times and taking the year from @P5, the month from @P6, and the day from @P7? This seems... sub-optimal. – Aaron Bertrand Feb 14 at 19:46
  • 2
    Please 1) post index definition from before change and 2) share both before and after plans on www.pastetheplan.com – Forrest Feb 14 at 22:44
  • If you have a separate question about how to interpret query store output, you should ask a new question. – Josh Darnell Feb 20 at 15:32
2

Do you have any control over the application?

If all of the columns aren't actually needed, the ideal solution to this issue is to change the application to select fewer columns. This looks like a query generated by an ORM (like Entity Framework), which will select all columns by default (full disclosure: that's my blog).

There are details in that blog on how to get an Entity Framework query to select fewer columns. The essence of the solution would be to define a "projection" for the query before it executes.

In LINQ for Entity Framework and NHibernate, this is done with the Select(...) extension method when using the method syntax (or the select keyword if you're using the query syntax).


As you've discovered, there's not really a great index-based solution to this problem. You've covered two options:

  • seeks with key lookups (you mentioned there were issues with deadlocks with this plan shape)
  • lots of includes in the NC index, leading to index scan and high CPU

One other option would be to remove the nonclustered (rowstore) indexes, and add a nonclustered columnstore index instead. If the index gets used, you'd still have a scan - but the scan should be more efficient. This is mostly speculation, as it depends a lot on the size of the data types of the columns, the number of rows in the table, etc.

0

I've found that when my query is spending a lot of time doing key that if I change it up a little that I can give the engine a better mechanism for accessing the main table. CTE_Keys uses just the nonclustered index and the join allows it to avoid either key looks or clustered index scans. I'm assuming that Col1 is the primary key for the table.

;WITH CTE_Keys AS
    (
    SELECT Col1
    FROM Tabl1
    WHERE    (   Col30 IS NOT NULL
       AND   Col28 <> @P0
       AND   DATEDIFF(
                 dd,
                 Col31,
                 CAST(SUBSTRING(@P1, 1, 4) + '-' + SUBSTRING(@P2, 5, 2) + '-' + SUBSTRING(@P3, 7, 2) + 'T00:00:00.000' AS DATETIME)) <= @P4
       AND   DATEDIFF(
                 dd,
                 Col31,
                 CAST(SUBSTRING(@P5, 1, 4) + '-' + SUBSTRING(@P6, 5, 2) + '-' + SUBSTRING(@P7, 7, 2) + 'T00:00:00.000' AS DATETIME)) >= @P8 )
    AND      Col27 = @P9 
    )
SELECT T.Col1, T.Col2, T.Col3, T.Col4, T.Col5, T.Col6, T.Col25...
FROM Tabl1 AS T
    INNER JOIN CTE_Keys AS K ON K.Col1 = T.Col1
ORDER BY 18 ASC, 31 DESC, 30 ASC;

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