5

What causes Microsoft SQL Server to remove manually created indexes automatically? Every index I create it removes within a few hours.

Here is the code I use to create the indexes. Affected views run much faster. The index disappears / is removed within a few hours. The affected views run impossibly slow or fail.

CREATE NONCLUSTERED INDEX IX_Tablename_fieldname 
ON dbo.Tablename (fieldname) 
WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
      ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
6
  • 11
    SQL itself wouldn't do this - there's another process at play. Commented Apr 25, 2018 at 14:45
  • @George.Palacios Is there some SQL I could run periodically to catch the drop index command or log these actions to a file? Commented Apr 25, 2018 at 14:51
  • You could potentially use XEvents or SQL Profiler to catch this. Commented Apr 25, 2018 at 14:57
  • 4
    Some applications store the database schema, and periodically "sync" the database to conform with the schema that it has stored. So in order to add an index, you have to create in the application, and then sync the database. Dynamics AX is an example of an application that works like this, but it doesn't automatically sync (at least the versions I've worked with didn't). Commented Apr 25, 2018 at 14:59
  • @TonyHinkle I was thinking of AX when I saw the question title...good call
    – Kevin3NF
    Commented Apr 25, 2018 at 16:41

4 Answers 4

18

SQL Server does not automatically* remove indexes - neither does automatically execute any other DDL statement that alters the structure of your tables, views, functions, indexes, etc.

If your indexes are truly dropped, then some user or application or job is doing it.

*: SQL Server on Azure can automatically remove indexes if the option is enabled. it does so if they are duplicates or have been unused for 6 months or so. Not on version 2008 though or any other desktop version.


To find out who or what, you have various options:

  • write a DDL trigger to catch the DROP INDEX statements (and even disallow them from deleting your index). See Ken Kim's answer.
  • SQL Trace
  • enable Audit
  • use XEvents (Extended Events)
  • use SQL Profiler
  • read the transaction log
3
  • SQL Server on Azure can automatically remove indexes if the option is enabled. it does so if they are duplicates or have been unused for 6 months or so. Not on the OP's version though... Commented Apr 25, 2018 at 18:02
  • @MartinSmith I actually was wondering a few minutes ago about that. Is there a 2008 Azure? (which would make this plausible). Thnx thoug, updated the answer with that info. Commented Apr 25, 2018 at 18:46
  • No, the feature requires QueryStore which was a 2016 on prem addition. Commented Apr 25, 2018 at 20:44
8

You can setup a DDL trigger to capture create/drop index.

CREATE TRIGGER [DDLAuditTrigger] ON ALL SERVER
    FOR CREATE_INDEX, DROP_INDEX

Here is an article on howto. https://www.mssqltips.com/sqlservertip/2085/sql-server-ddl-triggers-to-track-all-database-changes/

0
2

You could try running the Schema Changes History Report by right clicking on the database name in SSMS and choosing reports from the menu, it will tell you the DDL operation, time login name and user name that performed the change on the object.

It could be System policies or alert responses that may be in place and set to perform and actions as well as database level triggers or jobs; or it may be as simple as when a sync is done they are being dropped if they don't exist in the source database schema.

0

This has already been suggested, but just to re-emphasise - I'd say by far the simplest method to find the culprit is to run the profiler.

Tweak the trace to search specifically for the name of one of your indexes, and you'll be able to find the exact process that's causing them to be dropped.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.