Parallel Programming In TSQL: Is It In Our Future?

I saw an interesting videopod from Steve Jones the other day where he noted that upcoming processors would have many more cores than now and hoping that DBAs won't have to learn to write multiprocessor-style code. What was also interesting was the way he described that he learned some of this style of coding but later when he came back to it, he realised how much he thought he knew but didn't.

For languages like T-SQL, we don't have inherent support for multi-threading. In fact, the only trace I can see of this in T-SQL today is the ability to have multiple readers on a service broker queue. In general, we haven't needed this because SQL Server systems constantly have many requests thrown at them concurrently anyway and there is a natural style of parallelism happening.

But if languages need to evolve, I think it's important that wherever possible, that it's the languages that work out how to parallelise the code, not the developer. I often mention to people that in one interview I did with the late Dr Jim Gray, he noted that people learning to write multithreaded code tend to go through three phases:

1. when it all just looks like magic and you don't understand any of it.

2. when you think you understand it.

3. when you get wise.

That's very insightful and has stuck with me ever since. I've written a bunch of multithreaded code over the years and my experience exactly parallels (pun intended) the phases he described. Getting it right is much, much harder than it looks.

6 thoughts on “Parallel Programming In TSQL: Is It In Our Future?”

  1. I think it is inevitable that multi-threads style code will bleed into SQL. I do hope that future procs will be asymmetric, that is, a few really fast cores, and a great many slower cores.
    In such a case, the SQL support could just be to hint the fast or slow cores. We do have parallelism within a query today, but I would like to see this extended to write operations.
    Yes it is very difficult to learn to write effective MT C/C++, even C#. But my view is providing support to SQL be simpler.
    Suppose we have a simple SQL script with multiple statements, A provision for providing a directive that certain statements can be executed in parallel is helpful. now I open multiple windows and execute each segment in its own windows. It might also be nice to pass a big chunk of data to a parallel processing engine, like a graphics chip, Larrabee or other

  2. "For languages like T-SQL, we don't have inherent support for multi-threading"
    Yes, we do: parallelism.  It's just that it's declarative; we have little or no control over it (and little or no need to control it).  I can only assume that won't change much; perhaps a few more hints here and there, but by and large the query engine does a good job of controlling multithreading for us, and has been doing a good job for several years.

  3. Greg, I think Oracle has been capable of running blocks of PL/SQL in parallel for quite a while already: DBMS_JOB and DBMS_PIPE can do it.

  4. Hi Adam,
    I was thinking more along the lines of the lack of direct language support. What SQL Server does now is what I'd hope it'd do in the future. 99% of the time, I'd hope we don't need to specify how to parallelize our queries and that the engine does it for us.
    Regards,
    Greg

  5. I would like to see an option for parallelism in stored procedures.  I have many situations where the next X statments can be run parallel, but instead I have to wait for each block.  I am looking to SSIS and Service Broker to give me this ability.

Leave a Reply

Your email address will not be published. Required fields are marked *