on 05-01-2018 3:46 PM
Hi All,
We have SAP Net-weaver products ECC, BI and EP running on SQL server 2014 with huge DB size 4 TB, 3 TB and 5 TB respectively.
As we have no archiving solutions implemented hence the large DB size.
Auto update state and update stats job is present. But we are facing performance issue now and then.
Our SQL DBA is recommending to run full update stats monthly and yearly once index rebuild to overcome performance issues.
We have seen in SAP notes not to run manually any update stats and index rebuild .
Could you please advise under exception cases we can run the update stats and index rebuild ?
Moreover previously we have received specific tables in ECC system to run the update stats weekly basis to overcome performance issues.
Hello Asif,
that depends on your preferences and your database. Most of the customer that are doing manual update statistics are doing it for the complete DB, but if the size of the DB is too big you will not be able to do it in one night or over a weekend. Then you have to do it selectively. There are tools out there that can help you to determine which tables are good candidates for an update as the tools from Ola Hallengren (here) and others.
I do not recommend to rely on the automatic sample size for the statistics, but to specify either a fullscan of the table or at least 10% of the rows.
Best regards
Clas
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello,
the SAP doesn't recommend it to do, but you can do it if you want, it is not forbidden.
Many other customers are running update statistics on a regular basis as well.
Best regards
Clas Hortien
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi SAP replied. as below.
Running update statistics or reorg rebuild is not recommended by SAP as most of times if you are facing a performance issue is not due to non accurate statistics or fragmentation.
You should investigate in detail which is the root cause of those performace issues. Anyways you can run update statistics if you want to.
This is up to you and your specific problems that are fixed by running this update stats. Update statistics is an intensive IO operation .. if you run it bi-montly and you don't hit performance problems i would run it bi-monthly .. if you are hitting those problems every month i guess you should run in monthly .. I guess your SQL DBA that recommend to run full update should know how often is the best option .
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
88 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.