5

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.

  • 1
    have you benchmarked your I/O subsystem? where are your files placed? what are the relevant RAID arays? – Mitch Wheat Oct 14 '11 at 13:23
  • @MitchWheat I haven't benchmarked it, and to be honest I'm not sure what the hard disk setup is in that machine. I think it has just a single SCSI drive. –  Oct 14 '11 at 13:33
  • "a single SCSI drive" - well there's your problem right there! – Mitch Wheat Oct 14 '11 at 13:35
  • 1
    [There is an overview of SQL Server Scheduling here](http://sqlserverdownanddirty.blogspot.com/2011/02/sql-server-schedulers.html). It is covered in more detail in [Professional SQL Server 2008 Internals and Troubleshooting](http://www.wrox.com/WileyCDA/WroxTitle/Professional-SQL-Server-2008-Internals-and-Troubleshooting.productCd-0470484284.html) – Martin Smith Oct 14 '11 at 13:59

3 Answers3

3

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).

  • "two queries that take A + B when executed serially will take MAX(A, B) when executed simultaneously." - that's an optimistic view. Things are often more complicated on an active server – Mitch Wheat Oct 14 '11 at 13:38
  • 1
    @Mitch Wheat, The statement included the word "Ideally", which allows for the complexity you mention. –  Oct 14 '11 at 13:45
  • again: it depends: If boths queries hit the same table and hold locks it might be as high as A + B (or at least higher than MAX(A,B) ) – Mitch Wheat Oct 14 '11 at 13:46
  • Granted, however that is not ideal. In fact, it could be much worse than A+B. –  Oct 14 '11 at 13:49
  • I think you'll find very few things are ideal when talking to a real-world server... – Mitch Wheat Oct 14 '11 at 13:50
  • 1
    The ideal is still worth describing, once in a while. –  Oct 14 '11 at 13:51
2

I think that David B provided a good answer, but I'm just going to expand it a bit here with an example.

Let's say that you have two tables in your database, Table_A and Table_B. The server has multiple processors. Further, the disk subsystem puts each table on a separate drive/LUN/spindle (whatever the correct terminology is, I'm not a big hardware guy). Further, the server has multiple NIC cards.

If two users both access the database and one wants to write to Table_A while another wants to read from Table_B then hopefully you can see how the requests could come in simultaneously on different network cards, be handled simultaneously by different CPUs/cores, and the IO activity could all occur simultaneously on the different disks.

Now, in the real world it's seldom this simple. You usually have bottlenecks in one or more of these areas depending on your hardware and how the application is being used. Also, if both users are trying to access the same rows in the same table it could cause a resource bottleneck. So, in real life there are people who spend much of their time trying to find which resources are causing these bottlenecks for an application and getting rid of those bottlenecks. At the very least though, there are usually some areas where things can be processed in parallel.

2

Your question implies that before the improvements, SQL access was sequential, i.e. subsequent queries are performed one after another, on the same connection, in the same code thread.

Moving the logic to separate threads will cause two distinct effects: firstly, your code no longer has to wait for each query to finish before continuing, and secondly, the SQL queries will be parallelized (to a degree), allowing SQL server to optimize data file access - which it is far, far better at than your code could ever be.

Assuming the code consists of more than pure SQL calls, the former can have as large an impact as the latter.

  • Good point about the front-end application being in parallel as well. –  Oct 14 '11 at 14:39