Post reply

cannot add (maxdop 1) option to a sql2000 view

  • May 8, 2007 at 12:02 pm

    #116898

    I tuned a sql2000 query within a view by adding option (maxdop 1)

    When I try to incorporate this change back into the view it keeps giving me syntax errors ?

    This view has the with check option in it which I thought was the original problem but I even took it out and I still cannot add the maxdop option.

    I cannot find any documentation supporting or not supporting adding query hints to views

    So can you or can you not add query hints to views?

    If so what am I missing ??

  • May 9, 2007 at 12:12 am

    #705147

    http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/ebe3c2bb7149025/2d342ea4ece5aa27?lnk=st&q=create+view+maxdop&rnum=1#2d342ea4ece5aa27

     

    MohammedU
    Microsoft SQL Server MVP

  • May 9, 2007 at 2:47 am

    #705160

    an answer no would have probably been better than a link, however, views cannot take the maxdop hint, I sometimes wish they did because I've encountered some quite appalling views and as the number of cores increases in boxes so issues with parallelism will increase ( due to poor coding and/or design - not the number of cores )

    You might want to revisit the tuning of the view, e.g. useful indexes which will assist in decreasing the complexity of the query and maybe take it out of generating a parallel plan. Sometimes a view can be better represented by a table valued function, but be careful.  SQL 2005 has a few other options.

    Calls to views can take the maxdop hint, as noted in the link, but if your app generates sql on the fly you probably can't do this.

     

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • May 9, 2007 at 8:51 am

    #705252

    >>an answer no would have probably been better than a link

    I strongly disagree with that statement.  This is a forum, and thus (IMHO) a learning environment.  Not only does the poster get a more full explanation with that post, so does every other reader of the thread who uses the link. 

    >>issues with parallelism will increase

    This can be controlled by limiting the number of CPUs that can be used by parallelism and also the cost threshhold of parallelism.

    >>Calls to views can take the maxdop hint, as noted in the link, but if your app generates sql on the fly you probably can't do this.

    Why?  If you are dynamically generating a select statement that references a view, why can't that same generated code include OPTION(MAXDOP 1)  as part of the text?

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply