f63ef288   
   From: esquel@sommarskog.se   
      
   Absi (SQL Developer) (absiabso@gmail.com) writes:   
   > we have a big problem in our production optimizing a table. This table   
   > is a small table (usually 100s of items, max of around 5000 rows). But   
   > it is high-churn table - we insert and delete rows a lot from it. It   
   > also has a number of indexes.   
   >   
   > This table is causing many timeouts. is there a way to tell MS SQL to   
   > optimize this table for high-churn? (e.g. keep it in memory all the   
   > time, etc.).   
   >   
   > Our DBA tried to schedule re-index job frequently, statistics, we   
   > tried changing queries and use with(nolock) but that wasn't useful.   
   >   
   > The other teams in the company suggest we move to oracle! of course   
   > that is not an option (for now). and, I don't believe in magic.   
   >   
   > any ideas how we can optimize MS SQL to handle this table?   
      
   With the scant information you have offered, it is difficult to tell.   
   For these kind of problems there is rarely any magic bullet, but most   
   of the time you need to get your hands dirty and turn every stone.   
      
   You say the query causes timeouts? What sort of timeouts?   
      
   You say that the table has a lot of indexes. For a table that small,   
   indexes are not very important for read access, as a scan is not   
   expensive. On the other hand, the indexes can be a big burden for   
   all deletes and inserts. So there is all reason to drop as many indexes   
   as possible.   
      
   Obviously, scans will not be compatible with all that reading and   
   writing. Then again, scanning 5000 rows in memory does not take long.   
   So it may actually be OK - as long as you don't entail the table   
   in a more complex query. You could make the habit of always reading   
   into a temp table.   
      
   Or you could consider using any of the SNAPSHOT ISOLATION models.   
   (These require SQL 2005, please alsways post which version of SQL   
   Server you are using.)   
      
   By the way, how come this table has such high update frequency? Let   
   me guess, is it a queue table? In that case maybe you should investigate   
   using Service Broker if you are on SQL 2005 or later.   
      
      
   --   
   Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se   
      
   Links for SQL Server Books Online:   
   SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx   
   SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx   
   SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)