SQL Server Performance

Update Stats

Discussion in 'General DBA Questions' started by Anil, Sep 11, 2006.

  1. Anil New Member

    Hi All,

    We are having performance issues in our production server. Billing query usally take 1 Hr to execute but it's taking nearly 4 Hrs in these days.

    We did faced this issue in dev server, we did a update stats and issue fixed there.

    In production we had a schedule job which will update the stats daily.

    I want to know How best to determine if statistics are being sufficiently updated. When sp_updatestats runs, it samples a subset of the records in the table. I reviewed the query that's run as part of the billing process to see what tables are included. I ran dbcc show_statistics against the index of the table. I found that only 4% of the records are being sampled. How can I determine from the information returned by this command and other potential sources of information whether this is a large enough sampling.

    I found in one site that "execute the sp_recompile command using the table name after statistics are updated". Is it works?

    Your inputs would be greatly appritiated!!!

    Thanks in advance!!!

    Regards,
    Anil Kumar
  2. Luis Martin Moderator

    What kind of update statistics do you run?

    Check BOL because:

    UPDATE STATISTICS

    is different of:

    UPDATE STATISTICS .......WITH FULLSCAN

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  3. Anil New Member

    Thanks for looking into it!!!

    We are just using sp_updatestats as we don't have proper experience at UPDATE STATISTICS .......WITH FULLSCAN.

    Regards,
    Anil Kumar
  4. Luis Martin Moderator

    In that case from BOL:<br /><br />sp_updatestats [[@resample =] 'resample']<br /><br />Return Code Values<br />0 (success) or 1 (failure)<br /><br />Arguments<br />[@resample=] 'resample'<br /><br />Specifies that sp_updatestats will use the RESAMPLE option of the UPDATE STATISTICS command. New statistics will inherit the sampling ratio from the old statistics. If 'resample' is not specified, sp_updatestats updates statistics using the default sampling. This parameter is varchar(<img src='/community/emoticons/emotion-11.gif' alt='8)' /> with a default value of 'NO'.<br /><br /><br /><br />Luis Martin<br />Moderator<br />SQL-Server-Performance.com<br /><br /><font size="1">All in Love is Fair <br />Stevie Wonder<br /></font id="size1"><br /><br /><font size="1">All postings are provided “AS IS” with no warranties for accuracy.</font id="size1"><br /><br /><br /><br />
  5. satya Moderator

    I found in one site that "execute the sp_recompile command using the table name after statistics are updated". Is it works?
    Yes it is a best practice to get performance gain, if the system is not addressing the stats effectively.


    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.

Share This Page