Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Searching for Plans

By Steve Jones,

http://www.penn-olson.com/wp-content/uploads/2009/10/searching-for-info.jpgI was re-reading Conor Cunningham's "Does Join Order Matter?" recently and found this quote in the post that stood out. As an aside, read this post. It's fantastic.

"...SQL Server’s QP is actually not set up to give you the best plan.  Instead, it is set up to give you a good enough plan quickly (where good enough is very close to “best”)."

I get that, and it makes sense. Why spend 4 hours optimizing a query that runs in 2 minutes to get it down to 1:50 (Conor's example) when the optimizer can spend 1 sec and have it execute in those two minutes. If the optimizer looked for the best plan, I'd be very angry as a customer. Especially if this happened with any frequency.

Or would I?

I'm not suggesting that we ought to optimize every query, or that I'd prefer the optimizer didn't try to normalize queries, or that I want to see the internal tree and try to figure out if I can rewrite the query. I might, however, want to do that for some queries. There are queries that I might want to find the "best" plan for, either because they take a long time to run, or they are run often. I could then use that in something like a USE PLAN.

Computers are cheap these days, as is disk. If I were to somehow able to copy my database onto an instance of SQL Server on a cheap PC, wouldn’t it be cool if I could set that optimizer loose on my query and let it try ALL possible plans? I'd be happy if it spent that 4 hours and gave me back an optimal plan that I could carry back over to my expensive, busy server, and shave that 10sec off a query that I run 10,000 times a day.

I could see some value in a "test" instance of SQL Server that just looked for optimal plans. Now if I could just get Microsoft to build it.

Steve Jones


The Voice of the DBA Podcasts

Everyday Jones

The podcast feeds are available at sqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there. Overall RSS Feed: or now on iTunes!

Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

You can also follow Steve Jones on Twitter:

Total article views: 142 | Views in the last 30 days: 1
 
Related Articles
FORUM

Optimizing SQL Query

Query optimization

BLOG

Understanding SQL Server Query Optimization Statistics

SQL Server query optimizer uses statistics to create query plans that improve query performance. For...

FORUM

query optimizer

OPTIMIZATION

FORUM

Query Optimizer

Query Optimizer

FORUM

Query Optimizer

Query Optimizer

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones