I have Auto Create Statistics and Auto Update Statistics ON in one of my databases.
How do I know what time the Auto Update Statistics Kicks on ?
I dont see it in the log.
Is there a way for me to find out when was the last time Auto Update Statistics kicked on?
TIA
You can run this to see when statistics were last updated on a particular table.
-- When were Statistics last updated on each index in a table
SELECT'Index Name'= i.name,'Statistics Date'=STATS_DATE(i.object_id, i.index_id)
FROMsys.objectsAS o WITH(NOLOCK)
JOINsys.indexesAS i WITH(NOLOCK)
ON o.name ='YourTableName'
AND o.object_id= i.object_id
ORDERBYSTATS_DATE(i.object_id, i.index_id);
|||Thanks Glenn
Yes, I Think -- this should do it.
I am thinking the date is updated when you run Update Statistics manually as well as by Audo Update.
Currently I am seeing dates -- which coincides with the Manual Update Statistics that I run -- every night. So the Audo Update has not been run since the last time it was run -- last night.
|||Statistics will only be updated by Auto Update when SQL Server thinks it needs to be done (which is typically when 20% of the rows have changed). If you are manually updating statistics pretty frequently, AutoUpdate may not fire very often, if at all.
BTW, please mark Answers as "Helpful" so I get credit for the answer. Thanks!
|||How? How do i mark it helpful ?
Am I missing something -- I dont see anything that lets me say that
|||Just to the left of the Reply button (for my posts) it should say, "Was this post helpful?" with a Yes and No button.
You cannot mark your own post helpful, just other people's posts. The idea is that if someone answers your question or helps you out, you can mark their post(s) in the thread as helpful.
|||Gotccha Glenn
Going forward into that same issue, I collected a sample trace file and ran it through DTA.
DTA throws at me a slew of CREATE STATISTCIS statement -- does this mean -- that the Statistics are getting whacked every night?
I did not tell you this earlier -- we do run a process that does a whole bunch of updates at night. And the way everything is scheduled -- that "Nightly Process" gets done at 7:30 AM -- and I do not get a chance to run Update Statistics after that -- but we dive right into using Production Data -- My Goal is to convince the Management -- Hey we cannot continue to do this -- and I need strong evidence to do that.
|||DTA is wanting to add statistics because it thinks they would be useful based on the workload you gave it to analyze. They are not getting getting whacked, they just are not there. It would be better to update statistics after the nightly process job. Maybe you could add a step to the job to UPDATE STATISTICS on a few key tables after the update part of the job is done.
You might try running this query:
-- Missing Indexes
SELECT user_seeks * avg_total_user_cost *(avg_user_impact * 0.01)AS index_advantage,
migs.*, mid.*
FROMsys.dm_db_missing_index_group_statsAS migs
INNERJOINsys.dm_db_missing_index_groupsAS mig
ON migs.group_handle = mig.index_group_handle
INNERJOINsys.dm_db_missing_index_detailsAS mid
ON mig.index_handle = mid.index_handle
--WHERE statement = '[ngservices].[dbo].[BuzzInfo]' -- Specify one table
ORDERBY index_advantage DESC;
|||Glenn
Going further on this discussion -- I thought every time you create an INdex -- Statistics are created AUTOMATICALLY if you have Auto Create Statistics ON and if you have Auto Update Statistics ON -- they get updated every so often.
So question is WHY do I have to EXPLICITLY create Statistics -- or more importantly under what situation you explicitly create statistics ? I do have Auto Create Statistics ON.
Thanks Glenn for staying on with me -- on this.
Stefano Stefani - MSFT wrote:
Stefano Stefani - MSFT wrote: