Performance Question

FlareFlare Member Posts: 2
edited 2007-06-18 in SQL Performance
Hi,

we work with navision 40SP1
Since we updated to 40SP1 we have some performanceissues.
The serverhardware is really fine and no leaks there.

First i will do is to set the indexes to clustered, i hope it will boost the performance a bit.

I reorganize data and index pages with a maintance plan daily. (fillfactor 30% )

Must i create a second maintanace plan for update statistcs ?

Greets Flare

Comments

  • SQLGuruSQLGuru Member Posts: 13
    Jesus, 30%?!?!
    This means that you leave 70% free space on all pages.
    I would consider this way too much!

    My tool SQLSunrise calulates fillfactors for a maintenance intervall pretty precisely, and for big tables the average fillfactor lies around 97%. Which is pretty obvious, if you look at it like this: 3% space in a table that has 10 million records would mean enough space for 300000 new records (if the keys are distributed evenly), which should be enough for one week, i guess...?

    Please adjust your fillfactors to around 95, and this will boost again.
    Bodo Michael Danitz
    MCT, MCITP SQL Server 2005

    Author of SQL Server 2005 MOC exam items
    and SQLSunrise NavTune:
    http://www.sqlsunrise.com
  • krikikriki Member, Moderator Posts: 9,115
    [Topic moved from Navision forum to SQL Performance forum]
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • WaldoWaldo Member Posts: 3,412
    Flare wrote:
    Must i create a second maintanace plan for update statistcs ?

    To give you a much-too-late answer: yes you should create a maintenance plan to update statistics (indexonly is enough).

    Best is to first rebuild the indexes, then update statistics.

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • toortoor Member Posts: 52
    Best is to first rebuild the indexes, then update statistics.

    I don't think that you need to update the statistics after rebuilding the indexes. The statistics will be updated while rebuilding the indexes.

    Am i wrong?


    tobias
  • krikikriki Member, Moderator Posts: 9,115
    toor wrote:
    Best is to first rebuild the indexes, then update statistics.

    I don't think that you need to update the statistics after rebuilding the indexes. The statistics will be updated while rebuilding the indexes.

    Am i wrong?


    tobias
    Good question. I think they are updated while rebuilding. I think this because with when creating a Database Maintenance plan, if you select to rebuild, you CAN'T select also to create statistics.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • WaldoWaldo Member Posts: 3,412
    Hm, you can update statistics with the "indexonly" property.
    To be sure, I would just do it... . It takes 30 seconds on our 40Gb database ... not worth not to do it :wink:

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • krikikriki Member, Moderator Posts: 9,115
    Waldo wrote:
    Hm, you can update statistics with the "indexonly" property.
    To be sure, I would just do it... . It takes 30 seconds on our 40Gb database ... not worth not to do it :wink:
    And what is the percentage of records that SQL samples on your machine?
    And what kind of machine is it?
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • WaldoWaldo Member Posts: 3,412
    It's a 4CPU 6GB server.
    first question :
    I do a sp_createstats 'indexonly' without the fullscan parameter.

    This is the "help" text:
    The SQL Server 2005 Database Engine makes sure a minimum number of values are sampled to ensure useful statistics

    It's always better to do a fullscan, then you're sure the stats are 100% useful, but if you don't specify it, SQL Server 2005 will make sure the stats are useful ... so in 30 seconds you have useful stats ... .

    That's why I say it's better do it, than to guess it's updated through index rebuild.

    You agree?

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • krikikriki Member, Moderator Posts: 9,115
    Waldo wrote:
    That's why I say it's better do it, than to guess it's updated through index rebuild.

    You agree?
    Yes.

    The reason I asked was that SQL2000 proposes 10% of sampling and it take about 10-15 minutes for a DB of about 20GB.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • NobodyNobody Member Posts: 93
    Data sampling is great for auto-updating systistics on the fly, but it is good to schedule a Stat update with FULLSCAN as part of a regular maint. schedule.
Sign In or Register to comment.