none
Index seek vs index scan

    Question

  • I

    I have a stored procedure which is configured as "RECOMPILE" as follows,

    CREATE PROCEDURE [myProcedure] ( @in_parm1 datetime ) WITH RECOMPILE As BEGIN select * from table1 a left join table2 b on a.id = b.id left outer join ( select * from tableMain main join tableDetail detail on detail.main_id = main.main_id join tableResult result on result.detail_id = detail.detail_id

    and result.result_id = (select min(result2.result_id) from tableMain main2 join tableDetail detail2 on detail2.main_id = main2.main_id join tableResult result2 on result2.detail_id = detail2.detail_id

    and main2.case_id = main.case_id and (main.para1 is not null or main.para2 is not null or main3.para3 is not null) )) t1 on t1.case_id=a.case_id where a.createdate = @in_parm1 END

    I have a the following jobs which are configured to run daily

      • A SQL Agent job which run at 1 am to update the statistics of "table1", "table2", tableMain", "tableDetail", and "tableResult"
      • A window scheduled job which  run at 2 am to execute a console application which run stored procedure "myProcedure" and retrieve the result

    The stored procedure "myProcedure" normally took 17 seconds to complete but yesterday, it suddenly took more than 30 seconds and caused the console application to timeout.

    I run the stored procedure "myProcedure" manually and it took > 30 seconds.

    When I compare the Query execution plan of "myProcedure" against previous run, I noticed that the execution plan is slightly different and the increase in cost is due to it using "Index Scan" instead of "Index Seek" for table "tableMain", "tableDetail" and "tableResult".

    I checked the index fragmentation and the "Scan Density" and "Logical Scan Fragmentation" are low but the "Extent Scan Fragmentation" are high.

    The statistics are updated just before the stored procedure is executed and the query is set to to recompile every time it is run, therefore, the execution plan should be optimized with the latest statistics.

    Anyone has any idea why the SQL Server generate a sub-optimal execution plan? Thanks for any advice.

    Friday, September 07, 2012 11:00 AM

Answers

All replies

  • >The statistics are updated just before the stored procedure is executed

    Is it updated with FULLSCAN?

    For a night job to run 10 sec or 20 sec, usually not an issue. Why don't you schedule it with SQL Server Agent? That will not time out.

    Do you have indexes on all JOIN keys and colums in WHERE clause predicates?

    Are indexes rebuilt every weekend with FILLFACTOR in case the table is dynamic?

    I hope the tables are not named "table1" and "table2"?

    Optimization article: http://www.sqlusa.com/articles/query-optimization/


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

    Friday, September 07, 2012 11:40 AM
      • No, the update statistic is not updated with fullscan. Could this be a possible cause? But update statistics with fullscan will cause it to run longer? will this cause problem like blocking? Although it is a night job, there are still user applications accessing the database 24/7.
      •  We could increase the timeout period... but before that, we need to find out why it is suddenly taking so much longer (although double the duration) 
      • Yes, there are indexes on all JOIN key. 
      • All the indexes are rebuid monthly. However, if  "Extent Scan Fragmentation" is high whereas "Scan Density" is high and "Logical Scan Fragmentation" is low, is there still fragmentation with the index? 
      • No, the actual table names are not "table1", "table2", i tried to simply it in the example (as the actual query contains quite a number of table joins).


    Saturday, September 08, 2012 1:02 AM
  • To decrease fragmentation, generally, indexes should be rebuilt every weekend with FILLFACTOR for dynamic tables.

    Index REBUILD script: http://www.sqlusa.com/bestpractices2008/rebuild-all-indexes/

    FULLSCAN takes longer, it is more accurate.

    You don't mention indexes on the WHERE columns?

    For quick assistance, post the real query (if you can) and associated information.

    Also make sure that parameter sniffing is not an issue:

    http://www.sqlusa.com/bestpractices/parameter-sniffing/


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

    Saturday, September 08, 2012 1:21 AM
  • Thanks for the reply.

    As rebuilding of indexes will cause blocking, etc, it is not possible to do it every weekend... the database is in use everyday (even on weekend).

    Will FULLSCAN cause blocking, performance issues? This is because there are a number of tables involved and the database is used 24/7.

    No, in this case there are no indexes on the where clause. Have tried to introduce but it didn't reduce the cost of the query so can't justify adding the indexes (as more indexes will impact update/ insert performance...).

    Saturday, September 08, 2012 2:09 AM
  • How about parameter sniffing?  Make sure that is not the case.

    How about FILLFACTOR? That is very important if you can only do index REBUILD monthly. Set it to 70-90 for dynamic tables.

    In some SS editions, you can do index REBUILD online.

    UPDATE Statistics with FULLSCAN does not cause blocking nonetheless adds to the server load and takes longer than selective scan.


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

    Saturday, September 08, 2012 8:29 AM