Run table statisitics

[Former Member]

Hi all,

I need to run statisitcs on a single table. We're on ECC5/SQL2000. DB20 says it's not supported on this system. Any ideas?

Thanks,

Samer

[JPReyes · Accepted solution]

why don't you do if for the tablespace containing the table?.... statistics do not take long to run.

Regards

Juan

[Former Member · Accepted solution]

Hi Juan,

I need to run statistics on the table so the database will see the new index created. Do you think running a stat on the tablespace would do that too?

Thanks,

Samer

[JPReyes · Accepted solution]

Yes, it will... make sure you do it on the tablespace that contains the new index

Regards

Juan

[Former Member · Accepted solution]

Very good. Can you please tell me how to run stats on a tablespace, never had to do that.

Thanks again,

Samer

[JPReyes · Accepted solution]

Actually, you're using MSSQL so statistics are created automatically by the query optimizer.

Read,

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

Seems like theres no need to update stats after all.

will be a good idea to run DBCHECK in DB13 to see if theres any missing statistics, if theres none then you're fine.

Regards

Juan

[Former Member · Accepted solution]

Hello Sameer,

These is no concept of tablespaces in MSSQL server

so if you want to update stats you will have to do on single table

Here is the procedure:

go to DB02 and then click on single table statistics

then it will ask for the table name and then anaylze,then you can update statisitcs

Let me know of any issues

Rohit

[Former Member · Accepted solution]

Hi Rohit,

That's what I thought the right way too, but i can't find "single table statistics" under db02. At the bottom right i'm seeing "Detailed Analysis", "Missing indexes", "Space Statitiscs", "Check"s.

Would I find Single Table Statistics somehwhere else?

Thank you,

Samer

[Former Member · Accepted solution]

hi

when you go to DB02 you can find single table anaylsis in the left part of the screen(Please expore the screen a bit if you dont find it)

then give the name of table and the anaylze,then you will see five tabs coming in the screen

Size,Index,runtime stats, field. settings

click on indexes,click on each index and then update statistics in the above part of the screen,you will be able to do that

Rohit

[Former Member · Accepted solution]

Hi again Rohit,

I've uploaded a screenshot of my db02 to this http://www.barberfoods.com/images/samer/db02.jpg

Maybe it's because it's the end of the day here for me, but I can't seem to find the "Single Table Analysis"

Thanks again!

Samer

[Former Member · Accepted solution]

hey did you click on detailed anaysis and check,

also is this the full screen?

try detailed anaylsis you will find it

[Former Member · Accepted solution]

Sameer if you are not able to do that through DB02,you have one more option of doing that through SQL server

Just go through the following blog

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1278729_mem1,00.html?ShortReg=1&mboxConv...;

Rohit

[Former Member · Accepted solution]

Hi Rohit,

Sorry for the delayed message, got caught with something else.

I'm currently running statistics on the first index of this table using your recommendations in DB02. Will let you know how it goes after running it on all indexes.

Thanks man,

Samer

[Former Member · Accepted solution]

Hi Rohit,

I'm getting a runtime exceeded time allowed after having the statistics run for a while. Any way to go around that? Maybe run the statisicts as a background job?

[Former Member · Accepted solution]

Hai,

Try increasing your Dialog work process runtime parameter value.

rdisp/max_wprun_time.

Regards,

Yoganand.V

Edited by: Yoganand Vedagiri on Mar 25, 2009 3:30 PM

[Former Member · Accepted solution]

Hii

you can also increase the value of rdisp/max_wprun_time parameter in RZ11.just go to RZ11 and increase the value to around 7200 and then test.The system does not require restart after changing this parameter,just do it through RZ11

You can also update stats as a background job

Rohit

[Former Member · Accepted solution]

very good, it's been running now for about 10 minutes, will update you..

[Former Member · Accepted solution]

Hello

rdisp/max_wprun_time -DynamicallySwitchable , so no need to restart sap.

Thanks

Rajasekhar

[Former Member · Accepted solution]

still running

[Former Member · Accepted solution]

just ended with another "Maximum runtime exceeded". That's after changed the parameter to 7200 from 600. You guys think I should change it again or should I approach the problem in a different way?

Thanks,

Samer

[Former Member · Accepted solution]

ive just changed the parameter to 1000. And i'm running it now on a non-primary index now to see if that runs..

[Former Member · Accepted solution]

Hai,

Are you using AUTO UPDATE STATISTICS option in SQL server.

This option is present in SQL Server 2000.

You can try to update the statistics using the UPDATE STATISTICS (Transact-SQL).

Check below link.....

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

SAP Note: 327494.

Regards,

Yoganand.V

[Former Member · Accepted solution]

hello Sameer

7200 means 2 hrs i.e really long time...

as I posted in my earlier post also if you are not able to do through SAP,do it from SQL server

please follow the procedure mentioned in the following blog:

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1278729_mem1,00.html?ShortReg=1&mboxConv...;

Rohit

[Former Member · Accepted solution]

Hi Rohit,

Very good article thanks. I just ran UPDATE STATISTCS 'table' and it finished in less than 3 minutes. Knowing how long it failed on in SAP would that be a true result? Did i miss anything?

Thanks,

Samer

[Former Member · Accepted solution]

yes I guess there was some issues because generally it doesnt take so much time doing,we also do updates sometime in our BW system with very big tables but doesnt take too much of time

but the gud thing is that you were successful in that,congrats man!!!

Rohit

[Former Member · Accepted solution]

Wouldn't do it without you man!

I've let the developer who created the index know I'm done and he'll let users know to see if the performance has changed. We'll see what they say.

Thanks for all the time and quick responses!

Samer

[Former Member · Accepted solution]

looks like we're all set!

[Former Member]

Hi SK

Please check the following link:

http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.cc.doc/db2_udb/r...

I hope this helps

Regards

Chen

[JPReyes]

@Chetan,

That information is for DB2.... so its not related to this thread.

Regards

Juan