Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Had to reindex again
Message
De
15/04/2010 22:46:07
 
 
À
Tous
Information générale
Forum:
Microsoft SQL Server
Catégorie:
Autre
Titre:
Had to reindex again
Versions des environnements
SQL Server:
SQL Server 2008
Application:
Web
Divers
Thread ID:
01460466
Message ID:
01460466
Vues:
104
This is the second time it happens in less than a week. Tonight, an index started to not be used anymore. So, basically, the process moved up to 5 seconds instead of just a fraction of a second to be executed. To resolve the issue, I had to drop the index and reindex again. This is what I use for creating it:
DECLARE @indexName nvarchar(128)
DECLARE @dropIndexSql nvarchar(4000)
DECLARE @Constraint_Name nvarchar(128)
DECLARE @SQL varchar(4000)
DECLARE @Message varchar(400)

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
There has to be something that caused this. I cannot imagine that SQL Server would have a bug in there.
Michel Fournier
Level Extreme Inc.
Designer, architect, owner of the Level Extreme Platform
Subscribe to the site at https://www.levelextreme.com/Home/DataEntry?Activator=55&NoStore=303
Subscription benefits https://www.levelextreme.com/Home/ViewPage?Activator=7&ID=52
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform