Multi Threading
This week I am going to take a step way outside my comfort zone and talk about threading in SQL Server and Dot Net. My purpose here is not to provide instruction, but to raise awareness of the power and benefit of using threading in your applications.
Years ago I had the luxury of adding 3 CPUs to a Pentium 200 Server capable of hosting 4 CPUs. One of the first things I learned from this exercise is that the additional CPUs did not make my queries run any faster. The second thing I learned is that the additional CPUs allowed me to do more of the same work concurrently. That was long ago using SQL Server 6.0.
Today my laptop has more power than that 4 CPU server all in a single chip. It has two 64 bit cores with hyper threading running at 2.56 GHz. By comparison it screams. One thing that is interesting is that there are ways to make a single query outperform on a multi-CPU enabled system beyond that of my example above.
For example, in modern versions of SQL Server Server you have the option of setting the Max Degrees of Parallelism for an instance. What this does is determine if a query, when broken down into steps, may perform some of those steps concurrently on different threads using different CPUs. So, unlike my old experience of SQL Server, a single query can benefit from multiple CPUs if the query can be broken down into discrete units of work.
I’m not providing guidance that you should turn that setting on or increase the value. I have recently done some tests with the setting of the Max Degrees of Parallelism. I found scenarios where increasing multi-threading for a single query works fine when the load is light. However, when the load increases, single threading performs much better. One size does not fit all.
Why do we need to even consider this capability today? Primarily because the way CPUs are scaling is through adding more cores to a single chip rather than increasing the speed of a single core. I had a laptop that was clocked at 3.3 GHz that does not compete with my current multi-core model with a slower clock speed. On a single unit of work I’m sure it may have been faster. But, I do lots of things all the time. More CPUs or cores can result in better overall performance.
For those of you with true experience and guidance you would like to share when using multi-threading, please send your input to btaylor@sswug.org. Java is not even in my realm of experience with threading, so that kind of input would be welcome as well.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
UNIX for DBAs (Part 2)
UNIX is one of the most popular operating system for running database management systems. Of course, Windows is popular, too, as are z/OS and even OS/400, not to mention Linux (but that is a variant of UNIX). At any rate, UNIX is ubiquitous in the realm of database processing and it is important that DBAs know the basics of UNIX in order to effectively manage the databases on that platform. This article offers up a short tutorial on the basic UNIX operating system commands and features that are most important for DBAs to understand.
Featured White Paper(s)
Top 10 Tips for Optimizing SQL Server Performance
read more)