SQL Server T-SQL query running indefinitely | Not if its batched

5cnsuln7 于 2023-02-28 发布在其他

关注(0)|答案(1)|浏览(129)

I hit queit a strange issue here working with a SQL Server table.

With following query, I'm checking if an entry exists that was created between 2023-02-01T04:10:18 and 2023-02-05T04:55:44 (4 days).

This query runs forever:

SELECT
    TOP 1 1
FROM
    tablexyz t1 (nolock)
WHERE
    t1.col1 = 1
    AND t1.col <= '2023-01-31'
    AND t1.knowledge_begin_date >= '2023-02-01T04:10:18'
    AND t1.knowledge_begin_date <= '2023-02-05T04:10:18'
OPTION(RECOMPILE)

While, if I check for a 2 day period, both the queries execute in under 200 ms:

-- Executes in 200ms
SELECT
    TOP 1 1
FROM
    tablexyz  t1 (nolock)
WHERE
    t1.col1 = 1
    AND t1.col2 <= '2023-01-31'
    AND t1.knowledge_begin_date >= '2023-02-01T04:10:18'
    AND t1.knowledge_begin_date <= '2023-02-03T04:10:18'
OPTION(RECOMPILE)

and

-- Executes in 200ms
SELECT
    TOP 1 1
FROM
    tablexyz  t1 (nolock)
WHERE
    t1.col1 = 1
    AND t1.col2 <= '2023-01-31'
    AND t1.knowledge_begin_date >= '2023-02-03T04:10:18'
    AND t1.knowledge_begin_date <= '2023-02-05T04:10:18'
OPTION(RECOMPILE)

Any idea what could be the reason here? Note that this view(over 3 tables) has over 3 billion rows.

Indexes on the tables

Non-clustered index col1_col2_IX on (col1, col2)
Non-clustered index kdb_IX on (knowledge_begin_date)

Execution plans:

I'm not able to get the actual execution plan of long running query as it is not completing execution. Is there any way to access this ?
Looking at query plans of batched queries, it is doing an index lookup on kdb_IX for all 3 tables the view is over

It seems reasonable to believe that query optimizer should take of this, but strangely that is not the case.

sql-server

来源：https://stackoverflow.com/questions/75422604/t-sql-query-running-indefinitely-not-if-its-batched

1条答案

按热度按时间

nuypyhwy1#

You can try to combine the 2 indexes in one query using CROSS APPLY, and see if it helps... something like

SELECT
    TOP 1 1
FROM
    tablexyz t1 (nolock)
CROSS APPLY ( 
    SELECT
    TOP 1 1 x
    FROM tablexyz t2 (nolock)
    WHERE 
        t1.col1 = 1
        AND t1.col <= '2023-01-31'
        AND (t1. ID  = t2.ID)
    ) t2
WHERE
    t1.knowledge_begin_date >= '2023-02-01T04:10:18'
    AND t1.knowledge_begin_date <= '2023-02-05T04:10:18'
OPTION(RECOMPILE)

This is just a strech, depends a lot on the distributions and how will create the execution plan. The idea is that CROSS APPLY should be applied for each row (as a set) and in this case can use the second index.

赞(0）回复(0）举报 2023-02-28

我来回答

SQL Server T-SQL query running indefinitely | Not if its batched

1条答案

相关问题

热门标签

最新问答