January 26, 2010 at 12:41 pm
Hi everyone,
we have some strange problem here, we have optimized a table valued function with a lot of table variables to a stored procedure.
until here all goes fine, but when we decided to use a xml ParameterSet as Parameter value the query optimizer throw out the parallelism in all subqueries.
My question, is there a way to say the query optimizer to strongly use the parallelism ? Like u can say maxdop 1 to use only one cpu?
greetz
Alex
January 27, 2010 at 10:06 am
If you can post the queries and the before and after query plans, we should be able to tell you what is going wrong.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 28, 2010 at 8:13 am
XML may prevent the use of parallelism by the query engine, just like scalar UDFs and other operators do.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 29, 2010 at 2:17 am
Hi guys,
thanks for reply.
I think we found the mistake. There was a @counter variable which was used in a join clause in dynamic sql - and so optimizer couldn't recognize the value of this variable before executing.
@sqlguru: i haven't said it well enough, we get a Parameter Set as XML - then putting these Values out of the XML into temp tables and afterwards use these temp tables in our usps...
@RBarryYoung: sry it's not possible to show the queries and the execution plans because of privacy...
topic closed.
greetings
Alex
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply