This one is starting to get on my nerves. I have been trying to find a workaround since two months. Basically, if I index the entire table with the following script, the index in regards to the SQL command shown after the script will get optimized. Then, after a while, it starts to take about 1 to 4 seconds to return the query. That query should be instant. Before, I was running that script once every two weeks. So, I decided to add it into my robot to have this to be executed at midnight. Even by executing that script every night, it still get non optimized after a few hours. I do not want to have to run this script every hour. This doesn't make any sense. Something is not done right. I am trying to figure out what I do wrong in that way I index the table.

Here is the script to index:

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

Here is the command that is affected by the index when it becomes non optimized:

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 

Right now, the result is:

Duration: 1.7187 Count: 10

When I index again, for a few hours after that, the result would be optimized such as:

Duration: 0.0156 Count: 10

The index that is affected is as follow and is at the end of the script just before the last index that is being built with the INCLUDE syntax:

CREATE INDEX [IndexCombo1] ON [Reply] ([NoMember]) INCLUDE ([NoThread]) ON [Reply]