Skip to Content
0

Running manually index rebuild and update statistics for MS SQL server 2014 running SAP systems

May 01 at 02:46 PM

48

avatar image

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.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
Clas Hortien May 03 at 06:26 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Clas Hortien May 02 at 08:09 AM
1

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Thank you Clas for response.

one last question : Is it full update stats or selected tables ? If selected tables how to determine those tables ?

Thank you - Asif.

0
Asif Pasha Shaik May 03 at 01:17 PM
0

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 .

Share
10 |10000 characters needed characters left characters exceeded