December 21, 2009 at 11:36 pm
Hi,
We have SQL server 2005 and I want to know whether DBAs use 'Update Statistics' maintenance plan task or not? As Auto update statistics feature is enabled by default, is it require to use Update Statistics maintenance plan task job?
And how to know whether statistics are up to date or not? Is there any direct query to get this info?
thanks
December 22, 2009 at 2:37 am
I preferably go with "Auto update Statistics" feature of sql server.
You can check the data and time of updated statistics with the following command:
DBCC SHOW_STATISTICS
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
December 22, 2009 at 5:44 am
gmamata7 (12/21/2009)
Hi,We have SQL server 2005 and I want to know whether DBAs use 'Update Statistics' maintenance plan task or not? As Auto update statistics feature is enabled by default, is it require to use Update Statistics maintenance plan task job?
Answer is it depends.
Please read the following Url:http://www.sqlskills.com/BLOGS/KIMBERLY/categoryStatistics.aspx
And how to know whether statistics are up to date or not? Is there any direct query to get this info?
Use the following Query to find the statistics last updated date
select object_name (i.id)as objectname,i.name as indexname,i.origfillfactor,i.rowcnt,i.rowmodctr ,STATS_DATE(i.id, i.indid) as ix_Statistics_Date,o.instrig,o.updtrig,o.deltrig,o.seltrig
from sysindexes i INNER JOIN dbo.sysobjects o ON i.id = o.id
where rowcnt >1000 and i.name not like'sys%'and object_name(i.id)not like'sys%'
order by rowcnt desc
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
December 22, 2009 at 9:07 am
I don't use Maintenance Plans because I don't feel they provide me with the granular level of control I need. However, I strongly recommend you update the statistics on a regular basis (the precise meaning of 'regular' depends on your environment). Statistics can be off despite having auto update stats enabled. You will need to update some of them on occasion. Better to have a regular maintenance job doing the work than not. Minimum would be once a week, but, again, depending on your environment, once a day would not be out of line.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 22, 2009 at 9:38 am
I agree with Grant, but there's nothing wrong with the maintenance plans if they work for you. The only place I've used this is where I have large loads of data on a schedule that might substantially change the selectivity of tables/keys. In those cases I've scheduled this, otherwise, I tend to just rely on auto update.
December 23, 2009 at 11:09 am
We don't have any very large databases so I typically schedule UPDATE STATS jobs weekly or monthly just to make sure stats are fully updated eventhough I have auto stats on.
December 23, 2009 at 11:22 am
I will run stat updates after index reorgs (not rebuilds, would be redundant).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 23, 2009 at 11:31 am
GSquared (12/23/2009)
I will run stat updates after index reorgs (not rebuilds, would be redundant).
This is typically what I do.
Grant
I don't use Maintenance Plans because I don't feel they provide me with the granular level of control I need.
Like Grant, I don't use Maint. Plans. I prefer custom scripted stuff that offers more control.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 23, 2009 at 6:46 pm
I often schedule a job to run sp_updatestats on each non-system database on a daily basis.
This procedure was greatly improved with SQL 2005 to only do updates when they are needed, and will usually run in just a few minutes, depending on the workload.
You may also want set the option to do asynchronous statistic updates to prevent the auto statistic update from blocking queries until it completes:
ALTER DATABASE [MyDatabase] SET UPDATE_STATISTICS_ASYNC ON
December 23, 2009 at 11:44 pm
I often schedule a job to run sp_updatestats on each non-system database on a daily basis.
This procedure was greatly improved with SQL 2005 to only do updates when they are needed, and will usually run in just a few minutes, depending on the workload.
Same thing we r doing in our environment.sp_updatestats twice per day using by script.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
December 24, 2009 at 9:58 am
Michael Valentine Jones (12/23/2009)
I often schedule a job to run sp_updatestats on each non-system database on a daily basis.This procedure was greatly improved with SQL 2005 to only do updates when they are needed, and will usually run in just a few minutes, depending on the workload.
You may also want set the option to do asynchronous statistic updates to prevent the auto statistic update from blocking queries until it completes:
ALTER DATABASE [MyDatabase] SET UPDATE_STATISTICS_ASYNC ON
I do this myself - but be careful as sp_updatestats uses a default sampling rate unless you specify the option to resample. When using resample, it will use the last sampling rate for that statistic - which may or may not be a full sampling rate (depends on whether or not the statistics were updated using auto update stats - which also uses a default sampling rate).
The default sampling rate is - for the most part - okay, but in some cases may not be good enough. In those cases, you'll want to schedule a process to update those specific statistics manually with a full scan.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 28, 2009 at 3:14 pm
If the Index rebuild maintenance plan run on weekly basis, then NO need to run Update statistics maintenance job?
thanks
December 29, 2009 at 2:12 am
klnsuddu (12/28/2009)
If the Index rebuild maintenance plan run on weekly basis, then NO need to run Update statistics maintenance job?thanks
How can u said no need ?
Always "it depends"
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
December 29, 2009 at 5:19 am
klnsuddu (12/28/2009)
If the Index rebuild maintenance plan run on weekly basis, then NO need to run Update statistics maintenance job?thanks
You will still need to update statistics. I'm pretty sure the rebuild job only rebuilds indexes based on their fragmentation level. This means that indexes that are not fragmented may have aging statistics. You'll need to do everything on any reasonably active production system.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 29, 2009 at 5:33 am
When migrating databases from SQL Server 2000 to 2005 using backup and restore, How do I verify that auto update statistics is turned on AND it is necessary to update statistics once the restore to 2005 is complete?? Wondering what the best practices for this are???
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply