about the option (fast n) and acutal execution plan

  • 892717952

    SSC-Addicted

    Points: 477

    1.  what kind of situation  can I use "option (fast 1)"  when I execute a Select/update/delete  statement ? such as: select *from tablename where f1='test' option (fast 1)
    2. when I execute 2 select sql statement, the 2 sql statement is same ,but the difference is  the first sql statment doesn't have "option (fast 1) but the second sql statement has "option (fast 1), when I show their actual execution plan,  the first one Query cost(relative to the batch) 62%, the second Query cost(relative to the batch) 38% , but I double check  the second sql statement takes more IO reads and more CPU time ?elapsed time , why the query cost of second statement is much lower than 1st statement?

    3. there are some parameters of execution plan such as cached plan size?Memory grant?Estimated subtree cost ...,

    if we compare which sql statemest is better as the said sql statements with option (fast 1) and the sql without option (fast 1), so which parameter(s) is the critical index for us to decide which sql statement is better ?

     

    exeparas

  • ScottPletcher

    SSC Guru

    Points: 98608

    Frankly the percentages from an estimated query plan are often so inaccurate they should just be ignored.

    Look at the actual operations in the query plan, not the percentages.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • 892717952

    SSC-Addicted

    Points: 477

    hmm, I saw some books or article says the execution plan is inaccurate , if like this how we can compare which sql statement is better ?

    I used actual execution plan in my test. thanks!

  • Sue_H

    SSC Guru

    Points: 90814

    Execution plans and the stats can look just like what you are seeing when you use option fast. And if you run the different queries together and see the costs relative to the batch, it will look like the no hint is the highest cost relative to the batch. You are telling the optimizer to worry about getting that fast number of rows - you can see that in the execution plans estimated rows if you hover on the select for each query. But you are still going to retrieve all the rows so the query plan is based on an incorrect number of rows.  That's how you end up with an inefficient plan. That's why you see the higher reads, CPU time when using with option fast. That hint is really more for when you need to see some results rather than waiting for all the data before you see anything. It's not about overall performance or being fast, it generally hurts overall performance even if you see the first number of rows sooner.

    Sue

  • 892717952

    SSC-Addicted

    Points: 477

    thanks !

    but  there are some parameters of actual execution plan such as cached plan size/Memory grant/Estimated subtree cost ...,

    if we compare which sql statemest is better as the said sql statements with option (fast 1) and the sql without option (fast 1), so which parameter(s) is the critical index for us to decide which sql statement is better ?

  • Sue_H

    SSC Guru

    Points: 90814

    You don't want to run them together to compare them the way you are comparing them though - it's just not accurate. And you want to be sure you really, really, really need to use option fast. If you don't need it, don't use it. It's not going to make the query faster.

    Other than that, look at the operators in the plans as Scott already suggested and look at stats io and time. Pretty much what you are doing.

    Sue

  • Jeff Moden

    SSC Guru

    Points: 997308

    892717952 wrote:

    hmm, I saw some books or article says the execution plan is inaccurate , if like this how we can compare which sql statement is better ?

    I used actual execution plan in my test. thanks!

    Execution plans accurately portray "estimates" (and some actuals) if you understand the irony in that statement, which can be wildly different than what actually happens.  Even the "Actual" Execution Plan is riddled with estimates.

    You can use execution plans to see things like which indexes are going to be or have been used and are a very effective development tool BUT... the only way to tell which code is actually "better" for the likes of CPU, Duration, Reads, Writes, and Memory Usage is to actually run the code and measure it using SQL Profiler, Extended Events, or, if you're careful with what you're measuring (shouldn't be used when things like Scalar UDFs or mTVFs are present, for example), SET STATISTICS.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

Viewing 7 posts - 1 through 7 (of 7 total)

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