Background:
We have an application which uses an SQL Server 2008 database heavily. We are trying to optimize this application. It was single threaded, and we noticed through logging/ profiling of the app that the largest bottleneck was db reads/writes. Some calls were taking up to 100ms, which is far too long for us to deal with. Among other optimizations, we split some of these calls off into a separate thread. From this change alone, we are seeing a large improvement in processing time (the code is almost exactly the same, just some has been moved into a different thread)
Question:
Assuming that the time spent running the code in memory is negligible, I would have guessed that performing some of the reads/writes in another thread would yield no performance gain. I feel that if SQL Server were simply queuing the requests, the reads/writes couldn't happen in parallel anyway so the processing time would be similar. This is not the case.
I'm wondering how SQL Server handles two simultaneous requests (any combo of read/write) from different threads. Is it somehow able to execute them at the same time?
In summary, I would have assumed that the total time of Query1 + Query2 called one after another would be similar to Query1 + Query2 called simultaneously.
This is running on a dual core server.
Best Answer
SQL Server is a multi-user database. It's primarily intended to handle multiple simultaneous requests.
Ideally, two queries that take A + B when executed serially will take MAX(A, B) when executed simultaneously.
In order to prevent corrupt data from being read or written, SQL Server uses Transactions and Locking. In addition to this, applications may be managing concurrency as well (optimistic, for example).