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
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.
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
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 />
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.
