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]
Best Answer
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:
DROP INDEX
statements (and even disallow them from deleting your index). See Ken Kim's answer.