Index is only used for statistics in query plan

• michel.zdw

  SSC Eights!

  Points: 838

  More actions

  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.

• Site Owners

  SSC Guru

  Points: 80342

  More actions

  Follow

  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.