SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How often SQL Update Statistics job recommend to run


How often SQL Update Statistics job recommend to run

Author
Message
dakshiw
dakshiw
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 44
Hi,

Just a quick question. Probably would hve answered earlier in this forum, but I couldnt found an answer which relate to me.

In a SQL server (SQL2005) which runs financial systems of an orgnization, how often it is recommend to run the Update Statistics job?

Microsoft recommend not to run th job often as it clears query cache. Im just wondering whether it should be an overnight job or a job which runs once in a monthly/ weekly.

Appreciate your suggestions/ recommendations in advance.

Thanks.
Michael Valentine Jones
Michael Valentine Jones
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5758 Visits: 11771
If you mean UPDATE STATISTICS WITH FULLSCAN, weekly or monthly is probably enough for most situations.

However, I often setup a job to run nightly to run EXECUTE SP_UPDATESTATS in active user databases, because it only updates the stats that are needed. It takes much less time and resources to run.
dakshiw
dakshiw
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 44
Yes, WITH FULLSCAN

I decided to go for once a month job at this stage. Feel that's sufficient, Might keep weekly as an option.

Thanks Michael.
Ninja's_RGR'us
Ninja's_RGR'us
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28967 Visits: 9671
dakshiw (6/26/2011)

Yes, WITH FULLSCAN

I decided to go for once a month job at this stage. Feel that's sufficient, Might keep weekly as an option.

Thanks Michael.


Not nearly enough.

The 1 issue that's going to bit you in the arse is when you have a [small]datetime column that is insterted with the value of today or getdate().

The stats will soon become stale and cause you to have bad plans >> the server will expect 0-1 rows to be returned instead of 1000s.

Here's the full story :

http://sqlinthewild.co.za/index.php/2011/03/22/statistics-row-estimations-and-the-ascending-date-column/

I'm working on something to solve this issue but it's not completed yet. Ping back if you don't hear from me in the next couple of days.
Grant Fritchey
Grant Fritchey
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40016 Visits: 32653
It really depends on the volatility of the data, the structure of the indexes, and the types of queries run against it. We updated statistics on an insurance system I used to manage nightly for most databases. A few were weekly. We even had one table that, due to very poor construction, had it's statistics updated with a full scan once every 15 minutes. So there's no one right answer.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Ninja's_RGR'us
Ninja's_RGR'us
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28967 Visits: 9671
Grant Fritchey (6/27/2011)
It really depends on the volatility of the data, the structure of the indexes, and the types of queries run against it. We updated statistics on an insurance system I used to manage nightly for most databases. A few were weekly. We even had one table that, due to very poor construction, had it's statistics updated with a full scan once every 15 minutes. So there's no one right answer.


Full scan every 15 mintues????????????????


I sure hope it didn't take 14 minutes to complete that scan w00t.


Edited typo.
Jayanth_Kurup
Jayanth_Kurup
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2917 Visits: 1351
Is it worth the effort to identify table with high index fragmentation and then update stats for those tables more frequently instead of doing a full scan

Jayanth Kurup
Grant Fritchey
Grant Fritchey
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40016 Visits: 32653
Ninja's_RGR'us (6/27/2011)
Grant Fritchey (6/27/2011)
It really depends on the volatility of the data, the structure of the indexes, and the types of queries run against it. We updated statistics on an insurance system I used to manage nightly for most databases. A few were weekly. We even had one table that, due to very poor construction, had it's statistics updated with a full scan once every 15 minutes. So there's no one right answer.


Full scan every 15 mintues????????????????


I sure hopre it didn't take 14 minutes to complete that scan w00t.


Ha! No, it took about 2 minutes though, but it was absolutely necessary. The data in the table would skew so badly and changed so frequently that if we didn't update the stats, we were almost guaranteed a bad execution plan 3-4 times a day due to bad parameter sniffing. It was messed up to say the least, but the constant update on the stats didn't hurt the system, it helped.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Ninja's_RGR'us
Ninja's_RGR'us
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28967 Visits: 9671
Jayanth_Kurup (6/27/2011)
Is it worth the effort to identify table with high index fragmentation and then update stats for those tables more frequently instead of doing a full scan


I actually do both. Reindex only what needs to be worked on. Then update all stats with full scan (I can fit this in my window so I just do it).

Yes I know this causes an overlap on some stats because I do rebuilds on a couple indexes daily but since that's well under 0.1 of the indexes I don't care.


Never had a single parameter sniffing issue in the 6 months+ doing that.
Ninja's_RGR'us
Ninja's_RGR'us
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28967 Visits: 9671
And here's what I use to reindex... works great out of the box. No tuning necessary at first. I only recommend starting with higher than 10% fragmentation because in the first few run(s) you'll have more index to work on.



http://sqlfool.com/2010/04/index-defrag-script-v4-0/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search