Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How often SQL Update Statistics job recommend to run Expand / Collapse
Author
Message
Posted Sunday, June 26, 2011 5:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 13, 2012 9:16 PM
Points: 7, 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.
Post #1131846
Posted Sunday, June 26, 2011 7:30 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 3:34 PM
Points: 3,109, Visits: 11,516
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.





Post #1131856
Posted Sunday, June 26, 2011 7:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 13, 2012 9:16 PM
Points: 7, 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.
Post #1131862
Posted Monday, June 27, 2011 4:58 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 4:23 AM
Points: 20,584, Visits: 9,624
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.
Post #1131998
Posted Monday, June 27, 2011 5:12 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:42 AM
Points: 14,037, Visits: 28,407
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1132007
Posted Monday, June 27, 2011 5:18 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 4:23 AM
Points: 20,584, Visits: 9,624
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 .


Edited typo.
Post #1132009
Posted Monday, June 27, 2011 5:28 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 8:46 PM
Points: 1,786, Visits: 1,015
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
Post #1132016
Posted Monday, June 27, 2011 5:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:42 AM
Points: 14,037, Visits: 28,407
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 .


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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1132020
Posted Monday, June 27, 2011 5:33 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 4:23 AM
Points: 20,584, Visits: 9,624
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.
Post #1132021
Posted Monday, June 27, 2011 5:39 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 4:23 AM
Points: 20,584, Visits: 9,624
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/
Post #1132026
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse