July 31, 2011 at 8:24 pm
we had maintenance plan which will Check DB Integrity followed by rebuild indexes once weekly. the person who created this job does not include update statistics to the plan. what will be the reason and is it necessary to modify the plan to add update statistics step?IS it a issue not having update statistics step?
its sql server 2008r2 enterprise.
August 1, 2011 at 1:31 am
clarification (7/31/2011)
we had maintenance plan which will Check DB Integrity followed by rebuild indexes once weekly. the person who created this job does not include update statistics to the plan. what will be the reason and is it necessary to modify the plan to add update statistics step?IS it a issue not having update statistics step?its sql server 2008r2 enterprise.
Rebuild index will automatically update the statistics.No need to add.
I hope you enable the auto update statistics.Right?
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
August 1, 2011 at 2:31 am
It is a good idea to have a second update statistics step, set update the column statistics only with fullscan. While the index rebuilds will update the stats associated with those indexes, it won't update the other statistics.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 1, 2011 at 8:07 am
Gail could you please clarify:-
If a second update statistics should be added even if auto updates statistics is on?
August 1, 2011 at 8:09 am
jasonmorris (8/1/2011)
Gail could you please clarify:-If a second update statistics should be added even if auto updates statistics is on?
Definitely yes.
The server waits untill 20% of the rows have been modified (includes inserts / delete).
So on a 1 M rows table it takes 200K rows changed before that kicks in.
Not all, but a lot of stats benefit from being updated way before the 20%...
especially in that case : http://sqlinthewild.co.za/index.php/2011/03/22/statistics-row-estimations-and-the-ascending-date-column/
August 1, 2011 at 8:20 am
muthukkumaran Kaliyamoorthy
I hope you enable the auto update statistics.Right?
I disagree. Globally setting autoupdate statistics to on can have a deterimental effect on your system if it decides to update the statistics at the wrong time. I have seen internet apps slow way down because the system felt that the statistics needed updating based upon an internal rule of thumb when in fact the performance was not suffering. Over the years I have found that a sound maintenance plan has done better than using MS's settings globally. I had written many processes that review percentages of fragmentation or looking at how many records have changed in a table to determine is statistics need to be updated during off hours rather than take chances.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
August 1, 2011 at 8:23 am
sjimmo (8/1/2011)
muthukkumaran Kaliyamoorthy
I hope you enable the auto update statistics.Right?
I disagree. Globally setting autoupdate statistics to on can have a deterimental effect on your system if it decides to update the statistics at the wrong time. I have seen internet apps slow way down because the system felt that the statistics needed updating based upon an internal rule of thumb when in fact the performance was not suffering. Over the years I have found that a sound maintenance plan has done better than using MS's settings globally. I had written many processes that review percentages of fragmentation or looking at how many records have changed in a table to determine is statistics need to be updated during off hours rather than take chances.
Care to share those scripts?
August 1, 2011 at 8:32 am
Ninja,
I would but stopped using them when I found Ola Halingren's procedures which are all encompassing (http://ola.hallengren.com). I now use his system for alll of my maint on SS2K5 + boxes. (Yes, giving a plug)
Not only have I been able to use his update stats/maint but his backup piece as well, using our own SQLBackup from Redgate on our 2K5 boxes and the compression feature on 2K8.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
August 1, 2011 at 8:38 am
What are they doing that's so special on the stats?
August 1, 2011 at 8:50 am
Ninja,
One of the things I like is that there are plenty of parameters that can be passed to taylor the process to your requirements/needs. You can, in the execution string set a liit for low, medium or high fragmentation levels. You can specify if you want your indexes rebuilt on-line, off-line. Sort in tempdb if wanted. You can also tell it if you want all of the statistics updated or just those on a index or column. If you only want the statistics updated that changed since the last update stats you can do that as well.
His maint is also set up to do checkdb against the entire db or just the pieces that have changed.
It does a lot of testing to determine which indexes need to be worked on.
A lot of effort went into this. I played with it for awhile as I was getting ready to work on changes to mine when I found this one. Decided that since he had done all of this work and it does work well, that I would use it.
I placed it on a couple of OLTP servers and verified the database fragmentation each day before and after this ran. Found that the tool I was using (Capacity Manager from Quest Software) was not finding any significant issues after Ola's process ran. I rolled it out to the rest of my 2K5 servers.
Whenever I had a question, Ola responded within a short amount of time. Very pleasant to deal with.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
August 1, 2011 at 8:53 am
sjimmo (8/1/2011)
Ninja,One of the things I like is that there are plenty of parameters that can be passed to taylor the process to your requirements/needs. You can, in the execution string set a liit for low, medium or high fragmentation levels. You can specify if you want your indexes rebuilt on-line, off-line. Sort in tempdb if wanted. You can also tell it if you want all of the statistics updated or just those on a index or column. If you only want the statistics updated that changed since the last update stats you can do that as well.
His maint is also set up to do checkdb against the entire db or just the pieces that have changed.
I use Michelle's script and I get roughly the same benefits...
As for "His maint is also set up to do checkdb against the entire db or just the pieces that have changed."
I find that to be really dangerous. The poin of checkdb is to find errors when the drive fails and to find them before the server needs to access them. I would really be wary about that optio.
August 1, 2011 at 10:59 am
Ninja,
I find that to be really dangerous. The poin of checkdb is to find errors when the drive fails and to find them before the server needs to access them. I would really be wary about that optio.
Agreed completely, but having a minimal maint window on multiple 1+TB databases has proven to be challenging. Using Ola's feature has allowed for the maint to be performed more during the week, and we are able to do a full maint when we have a large enough window.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
August 1, 2011 at 11:03 am
sjimmo (8/1/2011)
Ninja,
I find that to be really dangerous. The poin of checkdb is to find errors when the drive fails and to find them before the server needs to access them. I would really be wary about that optio.
Agreed completely, but having a minimal maint window on multiple 1+TB databases has proven to be challenging. Using Ola's feature has allowed for the maint to be performed more during the week, and we are able to do a full maint when we have a large enough window.
Can't copy db to other server and run checks over there?? I'm sure someone in your offcie is screaming over those budget figures right about now ;-).
August 1, 2011 at 11:16 am
Can't copy db to other server and run checks over there?? I'm sure someone in your offcie is screaming over those budget figures right about now
I sure wish we could. Unfortunately the higher ups haven't found the need to be able to do this worth the cost. Thus I have to be at times overly creative rather than safe. Right now disk space is provided with an eye dropper.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
August 1, 2011 at 11:22 am
sjimmo (8/1/2011)
Can't copy db to other server and run checks over there?? I'm sure someone in your offcie is screaming over those budget figures right about now
I sure wish we could. Unfortunately the higher ups haven't found the need to be able to do this worth the cost. Thus I have to be at times overly creative rather than safe. Right now disk space is provided with an eye dropper.
Doesn't realy have to be big, nor fast. Just enough to do 2-3 restores per day + checkdb. That way you could do more than 1 check per week per DB.
But you already know that :-).
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply