Sql-server – Scan Count of a query increased from 1 to 2,226 after creating a non-clustered index

execution-planindex-tuningsql server

On my query 1st time it was taking almost 39 seconds and showing me non-clustered index is missing and

Scan count 1, logical reads 14553, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0

After Creating the non-clustered index the duration of the query execution comes down to 1sec and it's showing

Scan count 2266, logical reads 4539, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0

Logical reads is decreased after creating the non-clustered index, but Scan count increased to 2266. So my question is the performance of the query is developed or the Scan count 2266 make my query non-optimal?

Best Answer

First scenario, it's scanning the whole table and looking through 14553 pages of data. Second scenario, it's doing 2266 seeks (but counted as range scans) which each look at just 2 pages. So the second one is way better. Plus, many of those seeks will probably be looking at pages which have just been looked at, so on a cold cache it will be an even larger performance benefit. And, the second is more likely to parallelise better, being lots of small operations rather than a large one (which could still be parallelised, but it's more effort).

Related Solutions

Sql-server – Every query plan statistic says the query should be faster, but it is not

The sub query in the second query it's what's killing you that requires that the sub query needs to be run once per row that's returned by the record set. If you look at the plan it'll tell you how many times each operator is executed. I'll bet that you'll see a high number of executions on some operators of the second query. (I'm on my phone at the moment so I can't open the plan.)

Sql-server – Multi-statement TVF vs Inline TVF Performance

Your numbers table is a heap and is potentially being fully scanned each time.

Add a clustered primary key on Number and try the following with a forceseek hint to get the desired seek.

As far as I can tell this hint is needed as SQL Server just estimates that 27% of the table will match the predicate (30% for the <= and reduced down to 27% by the <>). And therefore that it will only have to read 3-4 rows before finding one that matches and it can exit the semi join. So the scan option is costed very cheaply. But in fact if any palindromes do exist then it will have to read the whole table so this is not a good plan.

CREATE FUNCTION dbo.InlineIsPalindrome
(
    @Word NVARCHAR(500)
)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN (
    WITH Nums AS
    (
      SELECT
        N = number
      FROM
        dbo.Numbers WITH(FORCESEEK)
    )
    SELECT
      IsPalindrome =
        CASE
          WHEN EXISTS
          (
            SELECT N
            FROM Nums
            WHERE N <= L / 2
              AND SUBSTRING(S, N, 1) <> SUBSTRING(S, 1 + L - N, 1)
          )
          THEN 0
          ELSE 1
        END
    FROM
      (SELECT LTRIM(RTRIM(@Word)), LEN(@Word)) AS v (S, L)
);
GO

With those changes in place it flies for me (takes 228ms)

Best Answer

Related Solutions

Sql-server – Every query plan statistic says the query should be faster, but it is not

Sql-server – Multi-statement TVF vs Inline TVF Performance

Related Question