Update Rate perfomance

• Frank Nostro

  Mr or Mrs. 500

  Points: 596

  More actions

  March 2, 2005 at 5:55 pm

  #545075

  Success!!

  Thank you Veteran and my thanks to all of you that offered your advice.  After reading up on pretty much everything you all suggested I now advance from "Newbie" to "Advanced Newbie."  Seems like index clustering and table pinning was the ticket.

  After pinning the 4 tables in question and adding clustered indexing to 3 of the 4 tables (more on that later) we maintained an update rate of 400-420 sustained updates/sec on this mornings trading activity. And our burst updates dropped to 800+ from 1000+.  Overall a 100% performance improvement from a few days ago.

  Enthusiast:  I will certainly look into those Disk I/O performance metrics.

  For those of you wondering about the RAM Disk.  I'm told by the developers of the RAM Disk software that tables in a RAM Disk update 6x to 8x faster than on a RAID.  In our configuration we kept these Tables in the RAM disk hoping to take advantage of that performance improvement when MSSQLServer decides to write the updates from the pinned pages back to disk.

  Also - because we were unsure of how much of the buffer cache we'd be stealing from SQLServer, we upped the memory in sql server from 700MB to 1.0GB.  We figured the extra 300MB should be enough to cover the 30 MB of information in the 4 tables

  About clustering.  It turns out that 1 of the 4 tables was already clustered - kind of.  But I'll throw those questions to a new topic.

  Thanx again everyone for your help.

  --Frank