May 28, 2012 at 4:32 am
Hi All,
I just wanted to know how to check and Apply Update Stats on a DB.
I know how to check the last modified date for stats .....and My Data base is running ok as of now .
Last week I also did some rebuilding for the indexes whose fragmentation lebel were more than 30.
Is there a Specific requirement that Update Stats needs to be run ...
Is that by TIme?
is that by size of DB?
What could be the Best possible way to find the best time to run update STATS:-D
May 28, 2012 at 5:40 am
Jai-SQL DBA (5/28/2012)
Hi All,I just wanted to know how to check and Apply Update Stats on a DB.
I know how to check the last modified date for stats .....and My Data base is running ok as of now .
Last week I also did some rebuilding for the indexes whose fragmentation lebel were more than 30.
Is there a Specific requirement that Update Stats needs to be run ...
Is that by TIme?
is that by size of DB?
What could be the Best possible way to find the best time to run update STATS:-D
If auto_update_stats is enabled, SQL Server automatically updates the statistics whenever it is required. Whenever 20% of the rows are inserted/changed auto_update_stats starts updating the statistics. Some times this 20% can be a very high number e.g. If your table has 10,000000 records then SQL Serevr will not start auto_update_stats process to update the statistics untill atleast 20,00000 records are inserted/changed. In these cases you need to manually update the statistics rather than waiting for 2000000 records changes to happen.
Gail has written an excellent article on this which will explain you better:
http://sqlinthewild.co.za/index.php/2008/11/04/what-are-statistics-and-why-do-we-need-them/
May 28, 2012 at 10:02 am
to get the date of the latest stats updates for a given database, use the following
SELECTt.name
, i.name AS index_name
, STATS_DATE(i.object_id, i.index_id) AS statistics_update_date
FROM sys.objects t inner join sys.indexes i
on t.object_id = i.object_id
where t.is_ms_shipped <> 1
order by statistics_update_date desc
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply