statsman query question

Bgestel
Bgestel Member Posts: 136
edited 2008-01-17 in SQL Performance
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
**********************
** SI ** Bert Van Gestel **
**********************

Comments

  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    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.
    Marije Brummel | Freelance Business Central Specialist
  • DenSter
    DenSter Member Posts: 8,307
    You mean update statistics right. Reindexing does not automatically update the statistics AFAIK.
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • kriki
    kriki Member, Moderator Posts: 9,121
    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.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • Nobody
    Nobody Member Posts: 93
    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
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    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.
    Marije Brummel | Freelance Business Central Specialist
  • DenSter
    DenSter Member Posts: 8,307
    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.
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • Bgestel
    Bgestel Member Posts: 136
    i turned it off and put maintenance plan in place,

    by by queries and welcome performance

    thnx for the information
    **********************
    ** SI ** Bert Van Gestel **
    **********************

Categories