Autostats diabled in SQL server 2008

[Former Member]

Dear Gurus,

Our SAP version is ECC 6.0 EHP5, DB is SQL server 2008 and OS Windows 2008 R2

We have EWA generated for PRD system and it says -

Set the AUTOMATIC UPDATE STATISTICS option to "OFF"  for tables VBHDR, VBMOD, and VBDATA

Execute below commands in QUERY ANALYZER

EXEC sp_autostats VBHDR  

EXEC sp_autostats VBDATA  

EXEC sp_autostats VBMOD

I have few questions regarding this :

1. I opened Query analyser but have no idea where to paste this command and how to execute it ?

2. Can this be done while DB is UP ?

Please help as I havent worked much in SQL before and this is regarding PRD system.

Thanks,

Shobhit

[former_member227891]

Hello Shobith,

I've already made this fixed on the same problem as well with 2008 R2.

Just copy the following

Go to ST04 -----» Diagnostics ----------» SQL Command Editor

EXEC sp_autostats 'sid.VBHDR' 'OFF' 

EXEC sp_autostats 'sid.VBDATA' 'OFF'  

EXEC sp_autostats 'sid.VBMOD' 'OFF'

Execute

Regards,

Juan

[Former Member]

Hello Shobhit,

Please, check https://websmp130.sap-ag.de/sap/support/notes/1649078

"As of Microsoft SQL Server 2005 you do not need to run single commands anymore to adjust the autostats settings. Instead SAP provides the script sap_z_set_parameters which will set all required autostats options. This is also mentioned in SAP Note 879941 and SAP Note 1237682. To adjust the settings in your database please run the script. Depending on your SAP Basis release and support package you can run the script with:

1. Transaction ST04 / DBACockpit
2. Configuration
3. SQL Script Execution
4. Execute SQL Script
5. Enter select sap_z_set_parameters
6. Click Execute

or with:

1. Transaction SA38
2. Execute report MSSPROCS
3. Select your DB release
4. Lookup script sap_z_set_parameters in the list
5. Rightclick and select Execute script

"

Cheers,

Diego.

[Former Member]

Thanks for replying Diego, But I have checked all the related notes and did my RND and checked the forums.

As I said my DB is not 2005 but 2008 also I have no clue how to run this command in query analyser.

Also in DBACOCKPIT i dont see EXECUTE SQL QUERY rather there is SQL QUERY MAINTENENCE, can this be used ?

Shobhit

[Former Member]

Hi Shobhit

The note states:

"Microsoft SQL Server 2005 and higher

As of Microsoft SQL Server 2005 you do not need to run single commands anymore..."

So, it should apply to 2008 also.

Have you tried with ST04/SA38?

Cheers,

Diego.

[Former Member]

Yes diego, I tried with SA38, do I just need to select that parameter and click on Execute SQL query ?

I am little reluctant because it is PRD and that note also talks that it gets reenabled untill pagelocking query is run for it.

I would still ask if you can guide me to do this via Query Analyser.

Regards,

Shobhit

[Former Member]

Hi,

In SQL Server 2008 you have a new tool: SQL Server Management Studio.

You can create the new query there and execute it. just copy&paste the sentences separated by ";"

Anyway, is advisable to execute via ST04/SA38.

Cheers,

Diego.