Skip to Content
avatar image
Former Member

Index maintenance

Dear Team,

Please help me with following doubts in Sql server (2005 onwards)

1) can we regularly go for index reorg/rebuild (depending upon fragmentation level) ?

2) After doing Index rebuild do we need to go for Full scan update statistics ?

3) Is it necessary that we must go for SP_RECOMPILE <Table> after every Full scan update-stat ?

4) can we schedule jobs like SP_UPDATESTAT and SP_RECOMPILE for all tables ? what are disadvantages ?

5) And last but not least ; in DB02 I see that there are two statistics (index Statistics and Column statistics - "Screenshot attached"); When we run full scan (SP_UPDATESTAT) will it update all the statistics or only index statistics ? In our case it can be seen that there is discrepancy about the latest date the statistics were updated. Kindly advise.

Regards,

Arthur

Capture.PNG (56.7 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Jan 27, 2014 at 09:52 AM

    Hi Arthur,

       After more than 10 years experience, I don't recommend you troubleshoot SQL server performance issue using index reorg/rebuild, update statistics, SP_RECOMPILE..etc. Just enable auto update statistics is fine and identify expensive queries using SQL statement and History in DBACockpit. Please read this information carefully.

    ---

    note 159316 - Reorganizing tables on SQL Server


    http://blogs.msdn.com/b/saponsqlserver/archive/2009/02/09/sql-execution-plans-part-1-sql-statement-execution-on-an-sap-system.aspx


    http://blogs.msdn.com/b/saponsqlserver/archive/2009/02/22/sql-execution-plans-part-2-sql-compilation.aspx


    https://blogs.msdn.com/b/saponsqlserver/archive/2009/05/10/sql-execution-plans-part-3-how-to-get-the-plan.aspx


    SAP with Microsoft SQL Server 2008 and SQL Server 2005: Best Practices for High Availability, Maximum Performance, and S…


    ----

    4) can we schedule jobs like SP_UPDATESTAT and SP_RECOMPILE for all tables ? what are disadvantages ?

    > You will clear performance data in DMV which is really useful to identify expensive SQL statement or parameter sniffing issues.

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Arthur,

      I think sp_recompile is just a short-term workaround because the problem is not really solved(You just force SQL server to choose a new execution plan on next run) and it will reoccur over and over again. So your “finally solution” might probably  sp_recompile a specific table every day or every week. It still does not solve the problem and impact performance.

      --- by Martin Merdes ---

      An existing execution plan may become invalid:

      • After updating the index (or column) statistics, which were relevant for the query optimizer during plan creation. It makes no difference here whether this is an automatic or manual update statistics.
      • Once the SP executing a query or a table accessed by the query is explicitly marked for recompilation. This can be done using the system stored procedure sp_recompile. It is not recommended to mark a table for recompilation, because a “sp_recompile <TABLE NAME>” holds and requests database locks. We have seen blocking situations for hours at SAP systems caused by this.

      In my opinion, the permanent solution is to identify which SQL statement is expensive and which table/index causes parameter sniffing, maybe you did not choose index properly(use DB05 to analyze, see a poor index below) or maybe you should use SQL hint. If it is a standard program, try note search or ask SAP via OSS.

      db05.jpg (109.0 kB)
  • avatar image
    Former Member
    Jan 23, 2014 at 11:08 PM

    Hi Arthur

    1) can we regularly go for index reorg/rebuild (depending upon fragmentation level)

    yes you should go for this mainly based on the fragmentation level .. Also you need to consider the online - offline options .

    Check this link

    http://www.brentozar.com/archive/2013/09/index-maintenance-sql-server-rebuild-reorganize/

    2) After doing Index rebuild do we need to go for Full scan update statistics ?

    yes - update statistics is needed after index reorganization.

    After a rebuilt , the index statistics are also updated ( but not the column statistics ). You need to run column statistics separately.  

    But in a SAP system , we will generally have the Update Stats job running weekly in DB13 over the weekends..

    See this nice blog

    http://sqlblog.com/blogs/ben_nevarez/archive/2009/10/06/rebuilding-indexes-vs-updating-

    statistics.aspx

    can we schedule jobs like SP_UPDATESTAT and SP_RECOMPILE for all tables ? what are disadvantages ?

    Please see the following link behind the logic of running Sp_UPDATESTAT.

    Understanding What sp_updatestats Really Updates

    Thanks

    Rishi

    Add comment
    10|10000 characters needed characters exceeded

    • Hi

      Could pl refer the SAP Note for SP_RECOMPILE -  159171 - Recompilation of Stored Procedures

      and also every month during off peak time you can schedule the full Database Consistency Checks (DBCC check) as part of SAP Note - 142731 - DBCC checks of SQL server

      Regards

      Sriram