SQL Server Performance

Which plan is the most efficient?

Discussion in 'Performance Tuning for DBAs' started by Unsure, Aug 6, 2008.

  1. Unsure New Member


    I have been trying to fine tune a query that involves this one table. The table contains 725,027 rows.

    Plan 1
    The original query completes in 6.2 seconds. The optimizer makes use of the clustered index. Performs a clustered index scan. The primary key is not used in the query.
    Physical operation: Clustered Index Scan
    Row Count: 725,027
    I/O cost: 6.73
    CPU cost: 0.39
    Number of executes: 2
    Cost: 3%
    Estimated row count: 723,244

    Plan 2
    I created a covering index. Completes in 1.72 seconds. Uses the covering index but performs an index scan.
    Physical operation: Index Scan
    Row Count: 725,027
    I/O cost: 3.09
    CPU cost: 0.79
    Number of executes: 1
    Cost: 11%
    Estimated row count: 723,244

    Plan 3
    I used 'set forceplan'. Completes in 2.31 seconds. Uses the covering index. Performs an Index Seek.
    Physical operation: Index Seek
    Row Count: 20,215
    I/O cost: 0.00320
    CPU cost: 0.000080
    Number of executes: 10449
    Cost: 22%
    Estimated row count: 1

    Plan 3 performs a seek, I/O and CPU is good BUT the number of executes is too high. Reads are high.

    Would like to know which plan is ideal with regard to this table?

    Thank you.
  2. preethi Member

    Welcome to the forum! I aso should state that your question is a Great Question to the forum. First option is obviously out as It is not at all efficient. (Even though that is the best plan when the covering index was not there) Out of the second and third options, (Where either you do a single Scan of all rows or a selected seeks of some rows) you need to balance.Single scan is faster and will always go through all rows in a single pass. But the IO cost is high.Multiple seek is slower but it consumes less resources.
    In this case, you should not worry about the number of executions as for index seek, (which basically seeks a single row) the number of executions will be high but the cost of each execution will be extremely low.First of all, SQL Server chose the Index scan option as it will be faster. In your case you need to decide on what do you exactly try to achieve through this query. If you need the query to complete faster, go with that option. Nevertheless, the time taken is not an important factor, when compared with the IO cost you should seriously consider Index Seek. SQL Server places lock on table for Index Scan, which prevents you from updating any row during the execution of option 2. In option 3, as it touches single page at each execution, the locks will be at page/row level (even though number of locks will be high) and allows multiple users to work with the table
    Finally, when you go with the forceplan option, you will not give a chance to SQL Server to take the best plan. When the parameters change (for the same query) you may need to do more seeks (even 600,000 out of 725,000 rows). On the other hand, SQL Server has the ability to use a different plan when the parameters are changing (for example if you are touching only 100 rows) In summary, leave it to the SQL Server's option unless, (1) you need to achieve the beat query in terms of IO cost and not on duration; (2) You are sure of the data you always going to touch.
  3. Unsure New Member

    Thank you for your reply. Appreciate it.
    The Query Stats that I posted pertain to a procedure that is a part of this huge, huge query. I do avoid the use of 'set forceplan' but in this case it seemed to help to speed up the execution time. Based on the application, the number of rows returned by this procedure will always be between 25,000 - 50,000.
    This procedure, 'proc_level4' is invoked from another procedure, 'proc_level3'. I store the rows returned by 'proc_level4' to a temp table and use that table in 'proc_level3'.
    I have 2 questions, 1 is I created an index on the temp table. My 'create index' statement is in 'proc_level3'. The index keys are based on the columns used in the join statement that uses the temp table. However, the index is not being used. So, a table scan of the temp table is taking place. I even created the index in 'proc_level4' instead of 'proc_level3', still wasn't used. Do you have any suggestions? The performance I am looking for is speed. I know I need to keep in mind I/O.
    I have to mention that 'proc_level3' contains just joins statements. It has 2 inner join and 12 outer join statements. As a result only Table Scans are taking place. Also have a few 'Table/Lazy Spools'. Not sure if the 'Table/Lazy Spools' are being helpful or not in this case.
    'Number of executes' are high?

    Physical operation: Table Spool
    Logical Operation: Lazy Spool
    Rowcount: 255,672
    I/O Cost: 0.01
    CPU Cost: 0.000001
    Number of executes: 42612
    Cost: 0%
    Thank you.
  4. satya Moderator

    Welcome to the forums.
    As preethi covered bits and bobs on the scan/seeks I would like to go with option 3 based on the number of rows on the table, say if this table will have huge inserts & updates then you nee dto deploy an overnight update stats of tables for optimum performance.
  5. Unsure New Member

    Thanks for your reply.
    Update stats takes place everyday. I also have DBCC REINDEX scheduled for twice a week.
  6. Adriaan New Member

    Seeing that SET FORCEPLAN improves performance, you're probably suffering from "parameter sniffing".
    The standard work-around for this is to add local variables to the sproc, into which you pass the parameters. You also need to replace all references to the parameters inside the sproc, with references to the local variables.
  7. preethi Member

    I dont think FORCEPLAN increases performance in "Microsoft terms". SQL Server Considers only the duration to get the better plan.
    As I said before, If you are sure that your data is always within a range, you value IO usage more than duration, and availability is more important than faster execution go with forceplan.
  8. Unsure New Member

    I did try that out too as I want to avoid using 'set forceplan". It did not help.
    Thank you.

Share This Page