statsman query question

Bgestel

Hi Nav community,

SELECT StatMan([SC0]) FROM (SELECT TOP 100 PERCENT [f11018207] AS [SC0] FROM [dbo].[Performance Test Company$337$2] WITH (READUNCOMMITTED,SAMPLE 1.000000e+002 PERCENT)  ORDER BY [SC0] ) AS _MS_UPDSTATS_TBL

i'm running the application benchmark toolkit and the sql server profiler is scanning for queries that take longer then 50 msec and is see a lot off queries like this. Does this indicate i made a mistake optimizing sift levels or are these queries a result of the profiler. the application name that comes with this query is Microsoft Dynamics NAV client

thanx in advance

Marije_Brummel

This is the great result of activating auto update and create statistics.

As you see, in some cases it causes huge overhead.

You can turn it of, but only if you run a reindex regularly.

DenSter

You mean update statistics right. Reindexing does not automatically update the statistics AFAIK.

kriki

DenSter wrote:

You mean update statistics right. Reindexing does not automatically update the statistics AFAIK.

-Rebuilding indexes updates the statistic (default), but you can give a parameter to NOT update the statistics (I don't know when this would be useful)
-Index defrag does NOT update the statistics.

Nobody

I would never recommend to any customer to turn off Autoupdate and Autocreate statistics. I have seen WAY to many customer turn it off on Monday and by Friday their perfomance is in the trash, and of course they never bother to manually update the stats or the job fails and they nobody ever notices. Personal opionion if they can not handle the overhead of STATMAN they do not have adaquate hardware.

Just me 2 cents.

Marije_Brummel

Nobody wrote:

I would never recommend to any customer to turn off Autoupdate and Autocreate statistics. I have seen WAY to many customer turn it off on Monday and by Friday their perfomance is in the trash, and of course they never bother to manually update the stats or the job fails and they nobody ever notices. Personal opionion if they can not handle the overhead of STATMAN they do not have adaquate hardware.

Just me 2 cents.

This is correct. Never, ever turn it off without a proper replacement and if you are not sure leave it on.

DenSter

I would always recommend they turn it off AND put in place a maintenance plan to update stats reularly. With all queries in NAV being cursors, SQL Server gets flooded with unnecessary statistics.

Bgestel

i turned it off and put maintenance plan in place,

by by queries and welcome performance

thnx for the information