Parallelism in USP with XML

  • 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

  • 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]

  • 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

  • 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