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.
Dennis Lee replied
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.