upgrade statistics

[Former Member]

From  long some tables are not statistics updated?how to  update statistics in ms sql server 2008 through sap application? I checked in  Db02-database options-statistics are active?but some tables are not updating ?

[former_member188883 · Accepted solution]

Hi Visu,

In SAP system go to DB20 and give table name in the field and use menu option.

goto Statistics->Create statistics

This will update the statistics to latest in your system

Hope this helps.

Regards,

Deepak Kori

[Former Member · Accepted solution]

when entering in to  Db20 i am getting Database system is not supported

[Sriram2009 · Accepted solution]

Hi Visu

Kindly refer the screen shot as per this have you enabled in MS SQL DB by using SQL Server Management Studio?

Since you are using MS Sql DB use the T-code ST04 to see the actions in DB02

Regards

Ram

[Former Member · Accepted solution]

how can i update statistics  through db02

[Former Member · Accepted solution]

i have checked in db02

[Former Member · Accepted solution]

one or two  tables are getting updated but so many tables are not updating from months

[Sriram2009 · Accepted solution]

Hi Visu

Kindly follow the steps

T code St04 or DB02 go to space, Select -'Single Table Analysis'  Enter table name '(Table name)'  Goto Index tab - Select the " Update statistics

Regards

Ram

[Former Member · Accepted solution]

thanks for your answer, one or two  tables are getting updated automatically by enabling statistics update but so many tables are not updating from months.

[Sriram2009 · Accepted solution]

Hi Visu

what is the size of DB? it possible you can run the DBCC check off peak time Kindly refer the SAP Note 142731: DBCC checks of SQL Server

Regards

Sriram

[xymanuel · Accepted solution]

Hi Ram,

do NOT use auto shrink!!! this is deadly for SQL Server because your datafiles will end up with thousands of extends. This will slow down your SQL Server.

Nevertheless topic here are update statistics.

Yes activate auto update auto create and auto update async statistics.

Thats the correct setting.

Also check Traceflags 2371,3604.

I always recommend the SAP on SQL Server Blog where these questions are answered and explained.

Running SAP Applications on SQL Server - Site Home - MSDN Blogs

http://blogs.msdn.com/b/saponsqlserver/archive/2012/01/13/auto-update-statistics-async-in-sap-netwea...

How Update Statistics really works in SAP BW - Running SAP Applications on SQL Server - Site Home -...

To check the date of the last statisticsupdate fo a table go to DBACOCKPIT -> Space -> Single Table Analysis -> Choose a table -> Indices --> Column "Last statistics upadate".

Here you can choose "update statistic now" if you want it.

Regards

Manuel

[Sriram2009 · Accepted solution]

Hi Manuel

Kindly refer the Microsoft link about Auto shrink

http://technet.microsoft.com/en-us/library/ms189080(v=sql.105).aspx

Regards

Ram

[xymanuel · Accepted solution]

Hi Ram,

this article only explains what auto shrink do. Its a normal feature aritcle about SQL Server. Its not a recommendation!

Autoshrink is NOT recommended for SAP Databases!!

I kindly refer you to the Best Practices of SQL Server Part I Article of Jürgen Thomas.

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/4ab89e84-0d01-0010-cda2-82ddc3548...

Page 38.

The problem is, as i said, the number of extends which will be created by the autoshrink feature.

Regards

Manuel

[former_member184473]

Hello Visu,

The row modification counter is used on table and index level. If a lot of data are changed, the automatic update statistics is executed more frequently.

The following links could be useful:

http://support.microsoft.com/kb/195565/en-us

http://msdn.microsoft.com/en-us/library/ms181055.aspx

Regards,

Eduardo Rezende

[Reagan]

To update individual table statistics you can use the Tx DB02.

I am not sure whether it will work on an SAP system running on MSSQL.

Regards

RB