This post is actually less about parallel query plans and more about a recap of some lessons learned from a recent project.

I run an in-house T-SQL training class for new hires and other employees who either need to get up to speed with T-SQL or have a personal desire to learn.  In the very first class I show participants how to check the estimated plan cost for their queries.  The reason I do this, despite contrary opinions of other developers, is to possibly help them to prevent from running a query that brings the server to it’s knees.

I’ve written more than a few of these queries over the years and can confidently say that I usually check the plans of new queries.

I also indicate to participants that if a query has a high plan cost, it will run slow.  The corollary of that is not true; If a query has a low cost it may STILL run slow.

Why is that the case?  The reasons are too numerous to tell for sure… there may be foreign keys involved that are preventing a delete or update to complete as the keys are validated.  Statistics may be out of date.  Statistics samples may not represent the data.  Indexes can be fragmented.  Indexes can be missing. etc.

Recently I ran into a situation where a query with a fairly low cost, about 11, took forever to run.  It also consumed all resources making it challenging to get into the server even through the DAC.

Here’s my high level synopsis of what we did to correct the situation.

1.  Update the statistics on the tables involved in the query.  If possible, use the WITH FULLSCAN option to minimize the chance that the default sample size will not result in a misrepresented statistics.

2.  If running parallel query plans, it may end up taking more resources to combine the parallel threads then the benefit of running the threads in parallel in the first place.  If you suspect a problem with your parallel query set the MAXDOP = the number of physical processors.  Better yet, set the MAXDOP = 1 to see if the query will finish with a single thread.

3.  If #1 and #2 don’t solve your problem you may need to disable hyper-threading (HT) on your server.  There’s tons of expert info on HT, how it works, why it can impair performance, so just take some time and do your homework.  In order to disable HT you’ll need to shutdown the server and interrupt the boot up process to change the BIOS settings.

In my case it took a combination of all of the above events to get the query to run in a predictable, reasonable amount of time.  It still is not great but at least it is predictable.

The next step…  Capture the query / ETL trace data and run the trace results through DTA.

The servers I encountered my challenges with were circa 2006 machines running Windows 2003, SQL Server 2005 SP2.  One had 4 single core processors; the other had 2 single core processors.  Had we been running a new OS, applied SP3 to SQL Server or running SQL 2008, or had the latest generation processors installed, we might never have had any problems, so the life of this post may be short.

Regardless, the lessons learned were hard won and worthy of a 20 minute post… if for nothing else, to remind me that even the simplest queries may not be so simple after all.

Stay curious… Keep learning.

Dave