For those of you readers who've been wondering whatever happened to me, I've been rather busy. Among other activities, I've been writing a chapter for Kalen Delaney's upcoming fourth book in the Inside SQL Server 2005 series: Query Tuning and Optimization.
I am hoping to resurrect my blog and to get things started, I'm posting a presentation that I recently delivered to the Pacific Northwest SQL Server User's Group. Several attendees asked whether they could have copies of this presentation, so here it is.
The Non-Parallelizable “Stuff” slide in the attached presentation contained a somewhat ambiguous statement that "TSQL UDFs and CLR UDFs with data access" force a serial plan. This statement made it sound like TSQL UDFs without data access are compatible with parallel plans. In fact, ALL TSQL UDFs force serial plans. CLR UDFs with data access force serial plans. CLR UDFs without data access are compatible with parallel plans. I apologize for any confusion and I've updated the slide deck to make it clearer.
I've received a couple of questions in email and in comments about deadlocks involving mysterious-sounding
Hey Craig. I am a SQL Server MVP and I just stumbled across your Parallelism presentation. Would you mind if I gave this talk to various user groups and SQL Saturdays? I live in the southeast US and most of the activity would be there. I would actually like to submit it to present at the 2010 European PASS Conference if you would allow that too.
Please drop me an email at kgboles at earthlink dt net.
Thanks in advance!
Kevin G. Boles
SQL Server MVP
If you have a stocked procedure that contains lots of queries (update, delete, insert) on some tables, do you think that the optimizer allows some threads for execute all queries with parallelisme?
I'm not sure I follow your question. The "write cursor" (i.e., the update operator) in the plan for a data modification statement (like update, delete, and insert) is always executed serially though the "read cursor" (i.e., the operators below the update operator) may execute in parallel. Two statements within a stored procedure are always executed one after another -- never concurrently.
Hello Mr. Freedman,
I'm curious if you have a script that can reliably generate the Intra-Query Parallel Threads deadlock? If so, any chance of sharing it?
I'm afraid I do not have a script to generate an intra-query parallel deadlock. Sorry.
Thank you for the pdf. I found it very informative. However, could I suggest that you also include a section, or warning, that query plans can be quite different for a parallelised query versus the same query not parallelised. This can mean that the non parallelised query actually runs much faster than the parallelised query. I'm guessing that you have seen this. I have found it to be an issue on several occasions, which resulted in a maxdop 1 hint on the queries. Perhaps you could include a section on plan selection in your whitepaper. I have written on this issue a while back - see richardlees.blogspot.com.au/.../parallelised-queries-are-selfish.html.