SQL Server Query Takes Long Time to Execute Despite Index Configuration

optimizationsql serversql-server-2016

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.

Best Answer

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.

Related Solutions

Mysql – Simple MySQL query randomly takes forever

To speed up the execution of your query, you need to create a compound index:

ALTER TABLE email_messages
ADD INDEX idx1(is_sent, is_cancelled, time_created);

More information: Multiple-Column Indexes

Mysql – Simple SELECT takes forever, but works as a subquery. Why

As your table is structured now, there are only two "good" options for satisfying your query, neither one ideal, and one of them isn't likely to be very good at all:

find all of the articles matching 'article_id' using the (article_id) index, and then sort their timestamps to find the largest one (the one shown in the question)... or
iterate through all the articles starting with the largest timestamp by reading row data in the order specified in the (timestamp) index and working backwards by timestamp until it finds one with the correct article_id... which would vary wildly in performance but could be a sensible strategy in some sort of parallel universe :) or if certain article_id values are particularly common, in which case a strategy like this might actually have been a good choice... but my guess is the optimizer might be incorrectly choosing this one on occasion, since the structures do support it.

The thing is, the execution plan can actually change for a given query depending on the specific argument in the WHERE clause. It could be that you're getting a different plan for particular values -- the question seems to imply that your EXPLAIN output may not have been from one of the problematic rows, so this may not be the plan chosen for the other article_id values.

If I'm correct that you haven't captured the query plan for one of the problematic values, it could be that the subquery makes your intentions somehow more obvious and changes the plan.

Really, though, if this is a common query, you need an index on both columns together.

ALTER TABLE my_table ADD KEY (article_id,timestamp);

This index should always be the obvious choice from the optimizer's perspective, since it knows exactly where to find the largest timestamp for any given article, so this index should eliminate "Using where" and "Using filesort" and result in all such queries being faster, though the difference may be less significant for some queries that are performing acceptably now.

Best Answer

Related Solutions

Mysql – Simple MySQL query randomly takes forever

Mysql – Simple SELECT takes forever, but works as a subquery. Why

Related Question