Searching for Plans

Question

Searching for Plans

Viewing 15 posts - 16 through 30 (of 42 total)

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

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 1

Added my vote. That's a good suggestion, though I wonder how well people would know how to use it.

Mike Byrd Default port Points: 1458 More actions · Answer 2

At first reading I thought that was a neat idea, but on reflection (and sadly experience), what runs on one machine and one set of data does not mean it could come up with the "optimal" plan for another machine and its data. The whole purpose of recompile is take advantage of changing data and its statistics and change the query plan accordingly. Data normally not only changes in quantity, but usually in its distribution and relationship to each other. What works now may not be the optimal plan 10 minutes later.

Mike Byrd

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 3

Mike,

good points, although often we have the plans cached, so I'd expect that if I run a query regularly, I'm getting the same plan every time.

Perhaps this isn't a great idea, and it might be too complex. You'd have to be sure that your data distribution was similar, and that your test machine could duplicate production, or your test instance would have switches to set the # of CPUs, RAM, etc.

IceDread SSCertifiable Points: 5050 More actions · Answer 4

Jack Corbett (9/13/2010)
I think that this would be nice, but as many of us know, the best plan for one set of parameter values for a query is not always the best plan when the parameters values, so you may not actually get the best plan for your application. Now if this worked off of a workload file created using a trace where the optimization process takes into account the parameter values provided for each query in the workload and produces the best plan for that workload. So if you have a query where you are selecting customers by country, the optimizer takes into account that 60% of the time the query is for customers in the US, then you might have something.
Something I think is somewhat related, I'd love for MS to allow you to tell Developer Edition what mode to run in, i.e. Standard Edition/Workgroup/Enterprise, so I could be sure I'm not developing something using Enterprise only features when my production server is Standard. This might be something you need to select when you install.

Two very good points.

Dave Ballantyne SSC-Dedicated Points: 33566 More actions · Answer 5

Maybe one already exists....??

Not sure what "higher authority" Paul set his info from (such as undocumented flags used in http://sqlblog.com/blogs/paul_white/archive/2010/07/31/inside-the-optimiser-constructing-a-plan-part-4.aspx) but it doesnt go beyond the realms of reason that there *could* be a "set good enough costs" flag somewhere considering you can mess with the optimizer to this degree.

This is all pure speculation on my part.

Clear Sky SQL
My Blog[/url]

Eric M Russell SSC Guru Points: 125522 More actions · Answer 6

Mike Byrd (9/13/2010)
At first reading I thought that was a neat idea, but on reflection (and sadly experience), what runs on one machine and one set of data does not mean it could come up with the "optimal" plan for another machine and its data. The whole purpose of recompile is take advantage of changing data and its statistics and change the query plan accordingly. Data normally not only changes in quantity, but usually in its distribution and relationship to each other. What works now may not be the optimal plan 10 minutes later.

This conversation reminded me of something from a while back. It's actually possible to create a "clone" database that contains only the schemas and statistics scripted from a production database. You can then use the clone database to run queries and examine execution plans that would resemble the same on the production server. The Microsoft Customer Support Services team uses this technique to troubleshoot performance issues.

How to create a SQL Server Clone / Statistics Only Database

http://www.mssqltips.com/tip.asp?tip=1999

How to generate a script of the necessary database metadata to create a statistics-only database in SQL Server 2005 and in SQL Server 2008

http://support.microsoft.com/default.aspx?scid=kb;EN-US;914288

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

mtillman-921105 SSCertifiable Points: 7156 More actions · Answer 7

Steve, thanks for clarifying that.

I'm not saying that optimization isn't important, it is. But the SPs I write don't have to be scrutinized for optimum performance since they are mainly used in a warehouse rather than in a fast-paced production server. I'm still just trying to learn how to get the result I want in some cases, not so much how fast I can get it.

I pay just as much attention to readablilty as I do performance unless the process runs way too long for what it's doing.

The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking

Jack Corbett SSC Guru Points: 184393 More actions · Answer 8

Dave Ballantyne (9/13/2010)
Jack Corbett (9/13/2010)
Something I think is somewhat related, I'd love for MS to allow you to tell Developer Edition what mode to run in, i.e. Standard Edition/Workgroup/Enterprise, so I could be sure I'm not developing something using Enterprise only features when my production server is Standard. This might be something you need to select when you install.
Try upvoting this
https://connect.microsoft.com/SQLServer/feedback/details/496380/enable-sql-developer-edition-to-target-specific-sql-version

Thanks for the link Dave. I voted for it.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 9

Eric Russell 13013 (9/13/2010)
Jeff Moden (9/12/2010)
The "best plan" is dictated only by four things and only two of those really matter where performance is concerned...
4. Code - This is where the true performance lies. Without doing this correctly, nothing else matters. Not hardware, not design, and usually not indexes. Write code the right way at all times. If it takes you too much time, then you need to get better at writing good code. 😛 The only way you can do that is to practice. A LOT! 😉
In some cases, the Database Tuning Advisor will suggest minor revisions to the T-SQL it's analyzing, like replacing a paramerized sp_executesql call with a straight SQL statement. I'm sure it could be made smart enough to recognize when someone is using a cursor or while loop to do something like populate a column with a comma seperated list of values, and then suggest they use a FOR XML sub-query instead, or it could simply advise them to do it on the front end. It could also suggest the removal of hints that have proven unbenefitial or the removal of unneeded distinct clauses (by confirming the presence of unique keys and join types).

Personally, that would annoy me almost as much as Intellisense. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 10

Especially since you could ruin a lot of monitors by throwing pork chops through them.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 11

The Dixie Flatline (9/13/2010)
Especially since you could ruin a lot of monitors by throwing pork chops through them.

Ya know, the new thin screens just can't take a direct hit like the ol' fishbowls could. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 12

Jeff, you really have to let me buy you a drink at the PASS summit.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 13

The Dixie Flatline (9/15/2010)
Jeff, you really have to let me buy you a drink at the PASS summit.

That would be both a pleasure and an honor. Thanks, Bob.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 14

Jeff Moden (9/15/2010)
The Dixie Flatline (9/15/2010)
Jeff, you really have to let me buy you a drink at the PASS summit.
That would be both a pleasure and an honor. Thanks, Bob.

Where's that drink taking place?

I would like to launch a hailstorm of pork chops just for grins.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 15

CirquedeSQLeil (9/15/2010)
Jeff Moden (9/15/2010)
The Dixie Flatline (9/15/2010)
Jeff, you really have to let me buy you a drink at the PASS summit.
That would be both a pleasure and an honor. Thanks, Bob.
Where's that drink taking place?
I would like to launch a hailstorm of pork chops just for grins.

I don't know where... anyone know a good "chop house" in Seattle? 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)