Query takes long time to execute while there is an index configured

Question

We have a table with 190M records. there is an index on a fairly unique column but still, if I run a query on the table selecting top 10 with only the indexed column in the where and select clause it still takes ages to complete.

If I turn on live query plan sql server shows the 'waiting for query plan" waiting spinner. If the query finally finishes and if I run it again it then completes instantly. Even if I run DBCC FREEPROCCACHE before So it feels like the creation of the query plan is taking all the time but then i cannot reproduce by running DBCC FREEPROCCACHE before the query. After a while the same issue will reoccur.

Note: The database has 2 filegroups. Indexes and system databases are stored on SSD disk while normal table data is stored on a non SSD disk.

Answer 1

Perhaps auto-update stats kicked in. AUTO_UPDATE_STATISTICS_ASYNC ON is your friend in that case. Check the index stats date to see if it coincides with the slowness:

SELECT 
    STATS_DATE(
          OBJECT_ID(N'YourSchema.YourTable')
        , INDEXPROPERTY(OBJECT_ID(N'YourSchema.YourTable'),N'YourIndexName','IndexID')
    ) AS LastStatsDate;

-- dan-guzman

If it isn't plan generation (SET STATISTICS TIME ON can be revealing), or the query processing per se (reading just a few rows with a pretty unique index), then the natural thing to suspect is blocking. - tibor-karaszi

---

There are several ways to monitor for statistics update events (profiler, extended events...). SQL Server 2019 introduces the WAIT_ON_SYNC_STATISTICS_REFRESH wait type, which can appear in the wait information section of 'actual' execution plans.

Sentry One Plan Explorer captures statistics building and refreshing (statman) and includes this information in the wait info.