interesting results with forceplan

Question

Post reply

interesting results with forceplan

Steve Croll

SSC-Addicted

Points: 488
More actions
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

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

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

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 1

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

0tter SSC Veteran Points: 280 More actions · Answer 2

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.

Steve Croll SSC-Addicted Points: 488 More actions · Answer 3

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

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 4

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

Steve Croll SSC-Addicted Points: 488 More actions · Answer 5

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

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 6

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

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 7

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/

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 8

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/

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 9

>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/

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 10

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/