Post reply

Index is only used for statistics in query plan

  • April 17, 2025 at 9:59 am

    #4580361

    Hi,

    I have very large table (6 * 10^9 records) where i have 4 indexes (primary included)

    Now i found out that 1 index is almost never used (50 reads/3 billion writes).

    Then i got a little bit deeper and i searched the queries that uses this index, maybe they are very important or can refactored to use another existing index).

    SELECT querystats.plan_handle
    , querystats.query_hash
    , SUBSTRING(sqltext.text, (querystats.statement_start_offset / 2) + 1,
    (CASE querystats.statement_end_offset
    WHEN -1 THEN DATALENGTH(sqltext.text)
    ELSE querystats.statement_end_offset END - querystats.statement_start_offset) / 2 + 1) AS sqltext
    , querystats.execution_count
    , querystats.total_logical_reads
    , querystats.total_logical_writes
    , querystats.creation_time
    , querystats.last_execution_time
    , CAST(query_plan AS XML) AS plan_xml
    FROM sys.dm_exec_query_stats AS querystats
    CROSS APPLY sys.dm_exec_text_query_plan
    (querystats.plan_handle, querystats.statement_start_offset, querystats.statement_end_offset)
    AS textplan
    CROSS APPLY sys.dm_exec_sql_text(querystats.sql_handle) AS sqltext
    WHERE textplan.query_plan LIKE '%IX_MyIndex%'
    ORDER BY querystats.last_execution_time DESC
    OPTION (RECOMPILE);
    GO

    This returned a lot of queries with e very high number of execution count. Then i checked the query plans from results and i saw that my index was used in statistics (see attached screenshot)

    Now my question: what might be the impact of dropping my index to the selected query where it is not used for a seek nor a scan?

    Thanks

    Attachments:
    You must be logged in to view attached files.

  • April 18, 2025 at 10:10 am

    #4580877

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply