f536c877   
   From: hugo@perFact.REMOVETHIS.info.INVALID   
      
   On Tue, 11 Aug 2009 10:28:24 -0700 (PDT), Thomas R. Hummel wrote:   
      
   >I have a table in a SQL 2005 database which is brand new. As part of   
   >our application deployment we load the table with about 2.6M rows.   
   >Once that is done, the indexes on the table are all rebuilt. Then the   
   >users are let into the system and queries against that table time out.   
   >I can then rebuild the indexes (using the same exact script that was   
   >used after the import) and the queries are lightning fast.   
   >   
   >I've checked that there are no other major data changes to the table   
   >after the index rebuilds. Any ideas on what else might cause this   
   >behavior?   
   >   
   >Here's a sample of what the index rebuild script looks like:   
   >   
   >DROP INDEX dbo.My_Table.Index1   
   >DROP INDEX dbo.My_Table.Index2   
   >   
   >ALTER INDEX PK_My_Table ON dbo.My_Table REBUILD   
   >   
   >CREATE NONCLUSTERED INDEX Index1 ON dbo.My_Table (column_1 ASC)   
   >CREATE NONCLUSTERED INDEX Index2 ON dbo.My_Table (column_2 ASC)   
   >   
   >   Thanks for any advice!   
   >        -Tom.   
      
   Hi Tom,   
      
   The first two things that come to my mind are these:   
      
   - Is anything else done to the database after the first rebuild of the   
   indexes? For instance, are you shrinking the database?   
      
   - Was the server restarted between the first rebuild and the slow   
   queries? In that case, the cause might be that the data all had to be   
   read from disk to cache when the queries were first executed.   
      
   Also, for how long was the timeout set? Did you disable the timeout to   
   check how long they actually took before finishing and whether they   
   finished at all?   
      
   --   
   Hugo Kornelis, SQL Server MVP   
   My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)