Naomi, nothing personal, but your posts seem to increasingly boil down to "I don't know the answer." If a question has been open for a while and no one has given a solution, I can understand referring the original poster elsewhere, but as a first response? 12 minutes after the question is asked?

>I suggest asking this question here
>
>http://social.msdn.microsoft.com/Forums/en-US/transactsql/threads
>
>Check also http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/3b2b23dc-be6c-4a06-86af-c582aee81e41 on a very similar topic which may apply to your case.
>
>>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