2

I'm developing a recursive CTE which utilizes an inlined TVF. In our dev environment, the recursive CTE executes exactly as expected.

In our production environment, the first iteration of the CTE executes as expected, but the iTVF on the recursive part suddenly switches to an index scan (different index) that can't even complete rather than the same index seek it used in the first iteration.

I've updated the statistics in our production and dev environment for the table the iTFV uses. I've even tried adding several relevant filtered statistics, to no avail. If I replace the iTVF with its content and use the relevant index hint, it also performs as desired, however I would like to avoid using index hints so I can maintain schemabinding.

The stripped down CTE looks like this:

WITH [ItemsOnDate] AS (
    SELECT  [l].[container_num]
        ,   [items].[item_id]
        ,   [items].[item_type]
        ,   1 AS [level]
    FROM [dbo].[location] [l]
    CROSS APPLY (
        SELECT  [item].[item_id]
            ,   [item].[item_type]
        FROM [dbo].[GetItemFromLocOnDate]([l].[loc_id],@on_date) AS [item]
        WHERE [item].[item_id] IS NOT NULL
    ) AS [items]
    WHERE [l].[container_num] = @container_num
    UNION ALL
    SELECT  [l].[container_num]
        ,   [items].[item_id]
        ,   [items].[item_type]
        ,   [ItemsOnDate].[level] + 1 AS [level]
    FROM [ItemsOnDate]
    INNER JOIN [dbo].[location] [l] ON [ItemsOnDate].[item_id] = [l].[container_num]
    CROSS APPLY (
        SELECT  [item].[item_id]
            ,   [item].[item_type]
        FROM [dbo].[GetItemFromLocOnDate]([l].[loc_id],@on_date) AS [item]
        WHERE [item].[item_id] IS NOT NULL
    ) AS [items]
    WHERE   [ItemsOnDate].[item_type] = 'container'
)
SELECT  *
FROM [ItemsOnDate]

The iTVF called goes through a master audit table. The audit table is of schema change_id, change_date, user_id, table_id, primary_key, field_name, old_value, new_value

This audit table has 3 indexes, 1 is change_date, table_id, primary_key. 2 is table_id, field_name, new_value. And 3 is table_id, primary_key. The index with the most specificity in this case is 3, where table_id correlates to the location table and primary_key is the loc_id. Any key in this index would have on average 10 rows. The table is clustered on index 1 and is unique for our purposes.

This is the iTVF:

SELECT TOP 1 [t].[new_value] AS [item_id]
    ,   [t2].[new_value] AS [item_type]
FROM [dbo].[trans] [t]
INNER JOIN [dbo].[trans] [t2] ON [t].[change_date] = [t2].[change_date]
    AND [t].[user_id] = [t2].[user_id]
    AND [t].[primary_key] = [t2].[primary_key]
WHERE [t].[table_id] = 'LOC'
    AND [t].[primary_key] = CAST(@loc_id AS VARCHAR)
    AND [t2].[table_id] = 'LOC'
    AND [t].[field_name] = 'item_id'
    AND [t2].[field_name] = 'item_type'
    AND [t].[change_date] < @on_date
ORDER BY [t].[change_date] DESC 

I would expect it to use index 3 on table [t], and index 1 on table [t2]. This works in dev, this works on the first iteration in prod, then decides to do a full clustered index scan on the recursion. As I said, I've updated statistics and even added statistics relevant to index 1 and 3 for when the table_id is 'LOC'. I can't figure out for the life of me this behavior.

This is what the bad plan in production looks like: badplan

And here's the plan explorer profile for it: https://drive.google.com/file/d/1fkzPbdIjC74Itn2-uRW9Aqh3zpnuOr6D/view?usp=sharing

This is what the good plan in dev looks like: goodplan

And here's the plan explorer profile for it: https://drive.google.com/file/d/1wPvh3W_dIA3lHZOBcXjllYXWEBs-xnkb/view?usp=sharing

Thank you very much for looking at this, it helps to have a fresh set of eyes on it!

  • 1.Do you have the same schema between prod and dev , related to these objects? (data types and lengths) just to check. 2.It is possible to limit the date range of your search , change_date ? meaning to add something like AND [t].[change_date] >= DATEADD(,day,-30,@on_date) ? It's searching in the last 30 days, but could be any value ... – Sabin Bio Mar 5 '18 at 7:50
  • Would it be possible to try an index that covers all columns related to the query? I.e. (table_id, primary_key, field_name, change_date) INCLUDE (user_id, new_value)? – ypercubeᵀᴹ Mar 5 '18 at 9:21
  • To the first question: Yes the schema is the same for the objects in question. Unfortunately there's no way to put a lower bound on the date range since we're asking for the last time the field was changed before the given date, which could be years ago. To the second question, that's something we can consider but we should be able to get the desired performance with the indexes that exist already (as evidenced by dev). This trans table is the one table whose indexes can't be easily changed since it contains billions of rows (even a statistics rebuild takes ~20 hours). – Apneal Mar 5 '18 at 13:48
  • Looking back over the the query plans generated for each. The row estimate for the initial loop of the recursion between the two are very different. In the good plan, it has an operator cost of 470 for the initial index seek. For the bad plan, it has an operator cost of 180,000. The statistics of the table have been updated in both environments and contains nearly identical data, so how can it vary so much? – Apneal Mar 5 '18 at 15:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.