Post reply

interesting results with forceplan

  • April 7, 2005 at 9:50 am

    #152381

    Hi all,

    I've been working with Microsoft on a problem that I am having with a query that is being poorly optimized by the Query Processor.  One server will execuute it using nested loops and skipping index scans in favor of index seeks, the other server will Hash the entire column as a result of an index scan and return 406k row to the hash physical operator when it should just return one.

    Here is the rub.  When I use forceplan on the poorly performing server it returns the results roughly 50x faster.  In fact, it is faster than the server with the better execution plan.  I realize that costs are just one parameter that plays into how the Query Processor chooses a plan.  (I believe it looks at current disk stats and processor utilization...probably other variables as well.)

    I've tried using forceplan on a bunch of queries in different databases.  So far, the results are troubling.  I am getting faster returns by using forceplan than by trusting the optimizer to do its job.  Microsoft is a little bit baffled.

    Any ideas?

    Steve

  • April 8, 2005 at 3:17 am

    #551405

    No ideas (sorry) but please keep us posted as to what MS come back with

  • April 8, 2005 at 6:22 am

    #551443

    While I won't say this relates to your problem, I have run across similar types of odd behavior. On one occasion the presence of other indexes on the table was enough to cause a large performance hit. We discovered that when one index was present the query was very slow and the plan chosen was completely different, while when that index was removed performance increased by a factor greater the 30 times. Unfortunately I can't recall all that details, though I am certain I have old emails documenting the discussion we had when we tried to troubleshoot the problem. I do remember that changing a BETWEEN to an AND ultimately solved our problem.

  • April 18, 2005 at 1:43 pm

    #553262

    So I am still working this issue with MS Support, but I have a favor to ask anyone with a spare minute or two.  Try using:

    SET STATISTICS IO  ON

    GO

    SET STATISTICS PROFILE ON

    GO

    SET STATISTICS TIME ON

    GO

    SET FORCEPLAN ON

    GO

    --complex to semi-complex stored procedure or adhoc query

    GO

    SET STATISTICS IO  OFF

    GO

    SET STATISTICS PROFILE OFF

    GO

    SET STATISTICS TIME OFF

    GO

    SET FORCEPLAN OFF

    GO

    I am wondering if anyone else is seeing faster returns.  I would recommend trying this in larger databases.

    Thanks,

    steve

  • April 19, 2005 at 2:02 am

    #553349

    I gave it a go with a fairly complex SP and it took much longer to complete than just executing the SP by itself

  • April 19, 2005 at 9:41 am

    #553471

    Thanks!

    Couple of questions:

    1. Were there cross database joins?

    2. Were your PK/FK relationships based on char or int datatypes?

    3. Clustered or non-clustered pk's?

    Thanks,

    steve

  • April 19, 2005 at 9:44 am

    #553472

    1. nope, everything was in the one database

    2. int based

    3. Clustered PK's

    Also, the DB in question is >TB so I hope that meets your large requirement too.

    Hope this helps

  • April 19, 2005 at 2:01 pm

    #553581

    Depending on how a 'fairly complex SP or statement' is written, it will often be much slower when executed with SET FORCEPLAN ON. If you use that you are now the optimizer and must create the best plan (by structuring the joins in order).

    It's interesting to hear about the problems you are having here. Have you not been able to break it down into something smaller, which can 'easily' be tested?

    --
    Chris Hedgate http://www.hedgate.net/
    Contributor to the Best of SQL Server Central volumes
    Articles: http://www.sqlservercentral.com/columnists/chedgate/

  • April 20, 2005 at 5:42 am

    #553738

    I have some similar issues, still not fully resolved, where a different plan is chosen for the same proc on two servers ( absolutely identical databases ) In one the indexes on the join columns were used, in the other there was a table scan.

    I have wondered if the default stats sampling used ( 10% ) is not sufficient and I should use full sampling - I have yet to test this as the problems arise in production and I can't just go and change things - test systems generally seem to work fine.

    On a slightly different note I have some "horrible" views which work 100 times fater if I can turn off parallelism .. I have noted that complex sql will create parallel plans that often run slower - ( note that the sql in these cases is "less than perfect"  and rewriting the query usually solves the problem )

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

  • April 20, 2005 at 6:47 am

    #553756

    >On a slightly different note I have some "horrible" views which work 100 times fater if I can turn off parallelism

    http://blogs.technet.com/mat_stephen/archive/2005/02/08/369120.aspx

    --
    Chris Hedgate http://www.hedgate.net/
    Contributor to the Best of SQL Server Central volumes
    Articles: http://www.sqlservercentral.com/columnists/chedgate/

  • April 20, 2005 at 8:57 am

    #553806

    I've met Stephen and read his article. Sadly I can't imbed a hint in the bad queries because they're dynamically generated sql from an app ( sigh ) and it seems a view definition can't take a maxdop hint either!  I'm slightly reluctant to make the setting on the entire server, which has a number of databases, although it is a thought I've had ( mentioned by a ms consultant too ).

    I know this is slightly off the original post but I still have this similar problem to the original, now and again, with plans seeming off to one side for procs.

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

Viewing 11 posts - 1 through 10 (of 10 total)

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