1

Currently, I have a highly transactional database with appx 100,000 inserts daily. Do I need to be concerned if I start allowing a large number of concurrent reads from my main transaction table? I am not concerned about concurrency, so much as performance.

At present there are 110+ million transactions in this table, and I am using SQL 2005

unforgiven1987
  • 454
  • 1
  • 3
  • 18
  • 1
    Consider using a replica set and reading from that, as it would help distribute load between several servers. – HenryZhang Aug 16 '12 at 17:40

1 Answers1

1

In 2002, a dell server with 2 GB of RAM, and 1.3 GHz CPU served 25 concurrent users as a File Server, a Database Server, and ICR server (very CPU intensive). Users and ICR server continuously insert, read and update one data table with 80+ million records where each operation requires 25 to 50 insert or update statements. It worked like a charm for 24/7 for almost a year. If you use decent indexes, and your selects use these indexes, it will work.

As @huadianz proposed, a read-only copy will do even better.

Erdogan Kurtur
  • 3,630
  • 21
  • 39
  • A read-only copy will only do better if it sits on a different set of disks, or if the inserts are highly-blocking which is unlikely. – usr Aug 16 '12 at 18:03