September 21, 2006 at 5:59 am
As GilaMonster says, check your perfmon counters on your disk i/o - I'd figure that was your problem.
My guess is memory is the primary bottleneck.
With frequent inserts, updates and selects of a 10 million row table and only 2GB of memory, I'd expect to see high paging and low buffer page lifetime.
If SQL often has to go to disk to get data then the queries will be much slower than if it could satisfy the queries from the buffer cache.
Another thought. If your transaction log and data file are on the same physical disk, then you're going to get contention between data file reads and transaction log writes, which will slow down your inserts. An insert is only considered complete once the log records for that transaction have been flushed to disk. This will be exasperated if there's memory pressure and frequent page swaps.
How many physical disks do you have? How many CPUs?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing post 16 (of 15 total)
You must be logged in to reply to this topic. Login to reply