The combo index failed again today and was no longer optimized. For a 3rd time in the last 6 weeks, once every two weeks, I had to remove all indexes and recreate all indexes.

Basically, the regular indexes are not a factor. This problem is caused since I created combo indexes. This time, I found out that if I only recreate those two indexes, that it will not resolve the issue. So, there is a conflict with the base indexes when combined with those two indexes. Here is the full script of the indexing:

DECLARE @indexName nvarchar(128)
DECLARE @dropIndexSql nvarchar(4000)
DECLARE @Constraint_Name nvarchar(128)
DECLARE @SQL varchar(4000)
DECLARE @Message varchar(400)

Raiserror('Removing primary key...',0,1) With Nowait

SELECT @Constraint_Name = Constraint_Name FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE Table_Name='Reply'
if @@rowcount = 1
   BEGIN
      SET @SQL='ALTER TABLE Reply DROP Constraint '+@Constraint_Name
      BEGIN TRY
         EXECUTE(@SQL)
      END TRY
      BEGIN CATCH
         SET @Message='Error: Problem removing index '+@Constraint_Name
         Raiserror(@Message,0,1) With Nowait
         SET @Message=ERROR_MESSAGE()
         Raiserror(@Message,0,1) With Nowait
      END CATCH
   END

DECLARE tableIndexes CURSOR LOCAL FOR
SELECT name from sys.indexes where is_primary_key=0 and object_name(object_id)='Reply'

OPEN tableIndexes
FETCH NEXT FROM tableIndexes INTO @indexName
WHILE @@fetch_status = 0
BEGIN
   SET @dropIndexSql = N'DROP INDEX [Reply].[' + @indexName+']'
   BEGIN TRY
      SET @Message=@dropIndexSql
      Raiserror(@Message,0,1) With Nowait
      EXEC sp_executesql @dropIndexSql
   END TRY
   BEGIN CATCH
         SET @Message='Error: Problem removing index '+@indexName+' from Reply'
         Raiserror(@Message,0,1) With Nowait
         SET @Message=ERROR_MESSAGE()
         Raiserror(@Message,0,1) With Nowait
   END CATCH
   FETCH NEXT FROM tableIndexes INTO @indexName
END

CLOSE tableIndexes
DEALLOCATE tableIndexes

Raiserror('Creating primary key Numero...',0,1) With Nowait

BEGIN TRY
  ALTER TABLE [Reply]
  ADD CONSTRAINT ReplyNumero PRIMARY KEY (Numero)
END TRY
BEGIN CATCH
   Raiserror('Error: Problem creating primary key Numero...',0,1) With Nowait
   SET @Message=ERROR_MESSAGE()
   Raiserror(@Message,0,1) With Nowait
END CATCH

Raiserror('Creating index AddUser...',0,1) With Nowait

BEGIN TRY
   CREATE INDEX [AddUser] ON [Reply] ([AddUser])
END TRY
BEGIN CATCH
   Raiserror('Error: Problem creating index AddUser...',0,1) With Nowait
   SET @Message=ERROR_MESSAGE()
   Raiserror(@Message,0,1) With Nowait
END CATCH

Raiserror('Creating index AddDate...',0,1) With Nowait

BEGIN TRY
   CREATE INDEX [AddDate] ON [Reply] ([AddDate])
END TRY
BEGIN CATCH
   Raiserror('Error: Problem creating index AddDate...',0,1) With Nowait
   SET @Message=ERROR_MESSAGE()
   Raiserror(@Message,0,1) With Nowait
END CATCH

Raiserror('Creating index ModUser...',0,1) With Nowait

BEGIN TRY
   CREATE INDEX [ModUser] ON [Reply] ([ModUser])
END TRY
BEGIN CATCH
   Raiserror('Error: Problem creating index ModUser...',0,1) With Nowait
   SET @Message=ERROR_MESSAGE()
   Raiserror(@Message,0,1) With Nowait
END CATCH

Raiserror('Creating index ModDate...',0,1) With Nowait

BEGIN TRY
   CREATE INDEX [ModDate] ON [Reply] ([ModDate])
END TRY
BEGIN CATCH
   Raiserror('Error: Problem creating index ModDate...',0,1) With Nowait
   SET @Message=ERROR_MESSAGE()
   Raiserror(@Message,0,1) With Nowait
END CATCH

Raiserror('Creating index NoThread...',0,1) With Nowait

BEGIN TRY
   CREATE INDEX [NoThread] ON [Reply] ([NoThread])
END TRY
BEGIN CATCH
   Raiserror('Error: Problem creating index NoThread...',0,1) With Nowait
   SET @Message=ERROR_MESSAGE()
   Raiserror(@Message,0,1) With Nowait
END CATCH

Raiserror('Creating index NoMember...',0,1) With Nowait

BEGIN TRY
   CREATE INDEX [NoMember] ON [Reply] ([NoMember])
END TRY
BEGIN CATCH
   Raiserror('Error: Problem creating index NoMember...',0,1) With Nowait
   SET @Message=ERROR_MESSAGE()
   Raiserror(@Message,0,1) With Nowait
END CATCH

Raiserror('Creating index Read...',0,1) With Nowait

BEGIN TRY
   CREATE INDEX [Read] ON [Reply] ([Read])
END TRY
BEGIN CATCH
   Raiserror('Error: Problem creating index Read...',0,1) With Nowait
   SET @Message=ERROR_MESSAGE()
   Raiserror(@Message,0,1) With Nowait
END CATCH

Raiserror('Creating index combo IndexCombo1...',0,1) With Nowait

BEGIN TRY
   IF NOT EXISTS (SELECT * FROM sys.filegroups WHERE Name='Reply')
      ALTER DATABASE UniversalThread ADD FILEGROUP Reply
   IF NOT EXISTS (SELECT * FROM sysfiles WHERE Name='Reply')
      ALTER DATABASE UniversalThread ADD FILE (Name='Reply',FileName='d:\Data\Universal Thread\Reply.ndf') To FileGroup Reply
   CREATE INDEX [IndexCombo1] ON [Reply] ([NoMember]) INCLUDE ([NoThread]) ON [Reply]
END TRY
BEGIN CATCH
   Raiserror('Error: Problem creating index combo IndexCombo1...',0,1) With Nowait
   SET @Message=ERROR_MESSAGE()
   Raiserror(@Message,0,1) With Nowait
END CATCH

Raiserror('Creating index combo IndexCombo2...',0,1) With Nowait

BEGIN TRY
   IF NOT EXISTS (SELECT * FROM sys.filegroups WHERE Name='Reply')
      ALTER DATABASE UniversalThread ADD FILEGROUP Reply
   IF NOT EXISTS (SELECT * FROM sysfiles WHERE Name='Reply')
      ALTER DATABASE UniversalThread ADD FILE (Name='Reply',FileName='d:\Data\Universal Thread\Reply.ndf') To FileGroup Reply
   CREATE INDEX [IndexCombo2] ON [Reply] ([NoMember],[Read]) INCLUDE ([NoThread]) ON [Reply]
END TRY
BEGIN CATCH
   Raiserror('Error: Problem creating index combo IndexCombo2...',0,1) With Nowait
   SET @Message=ERROR_MESSAGE()
   Raiserror(@Message,0,1) With Nowait
END CATCH

The last two indexes are the one that are no longer optimized after about every two weeks. Whenever this happens, I have to delete all indexes and recreate them again so they will be optimized. If I do not have the last two indexes, the ones with the INCLUDE syntax, everything works as is. Before implementing those two indexes, I never had to recreate the indexes on the Reply table. This has worked for about 16 months as is.

The query that is affected by that is as follow:

DECLARE @NoMember Integer

SET @NoMember=1

SELECT Thread.Numero,Thread.Title,Thread.Url,Thread.AddDate
 FROM (SELECT TOP 10 Reply.NoThread FROM Reply (NOLOCK) WHERE Reply.NoMember=@NoMember ORDER BY 1 DESC) Temp 
 INNER JOIN Thread ON Temp.NoThread=Thread.Numero 

This is the stats:

Duration: 1.9377 Count: 10

As is, since I recreated all the indexes, this SQL is instant result and returns in:

Duration: 0.0156 Count: 10