"Disable auto update statistics"

[Former Member]

Hello,

In our SAP prod database we have "auto update statistics" set "on" - I want to disable this and run "update statistics" after hours. If I uncheck the flag, is it a dynamic change or do I have to stop/start SQL for the change to take affect ? We are running SQL 2000.

Thanks

Message was edited by:

Brian Anastasia

Message was edited by:

Brian Anastasia

[Former Member]

Ok - thanks. The "alter" statement is the same as going into the "Properties, Option" tab of the database using SQL Enterprise Manager and checking/unchecking the appropriate box.

[Former Member]

Yes - I understand that we need to review quieres, code and the whole process, but I also have read that having this option set "on" can also be detrimental to the system if there are large inefficient database queries - which I have seen using SQL Profiler (the update stats process runs constatntly at high duration times). I may turn this feature back on, but I just want to know if I need to stop/start SQL to disable the feature. I will also look to run auto stats all every evening, when the system is "quiet".

[clas_hortien]

No, you do not need to restart the SQL Server to get this option on. You can set this even when the R/3 system is up and running. You can switch this easily with

ALTER DATABASE <SID> SET AUTO_UPDATE_STATISTICS OFF

Please be aware that the SAP SQL Support might will ask you to switch on this option again, when you open cases for performance problems.

Regards

Clas

[clas_hortien]

Hello,

nearly all SAP System on SQL Server are running with 'auto update statistics' on without any problems, so why do you want to disable this feature ?

It makes more sense to get the problematic statements analyzed and optimized one by one than to update all statisitical information and to hope that this will solve the problem.

Regards

Clas