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.