December 7, 2012 at 7:09 am
How can we update Stats of system created indexes Manually.
Auto Update Stats should set to TRUE and Sp_UpdateStats does it automatically.
But I need some clues in developing a new Code where I can only update the stats of system created indexes using some Colmodifiedcounter values.
How Does a database Engine Does this in background.?
Any ideas..
December 7, 2012 at 8:08 am
if you mean on user tables, SQL Server does not generate system indexes on user tables. It does create statistics though. Maybe you mean that ?
December 7, 2012 at 8:13 am
Thanks for your reply.
Yes I mean user tables only.
I am talking about indexex(Starts With _WA_) these are automatically created right?
So I want to update them manually.
hope you get me know.
December 7, 2012 at 8:34 am
mani@123 (12/7/2012)
I am talking about indexex(Starts With _WA_) these are automatically created right?
Those aren't indexes. They're automatically created statistics.
You can update then using the UPDATE STATISTICS statement
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
December 7, 2012 at 8:42 am
They're not indexes - they're column level statistics. They are created automatically by the query processor, and are linked to the table stats. I cannot imagine why you would ever want to update those stats by yourself, but you could if you wanted to like this:
UPDATE STATISTICS MYTABLE _WA_Sys_00000002_00200768
December 7, 2012 at 8:44 am
GilaMonster (12/7/2012)
mani@123 (12/7/2012)
I am talking about indexex(Starts With _WA_) these are automatically created right?Those aren't indexes. They're automatically created statistics.
You can update then using the UPDATE STATISTICS statement
Yeah there are not indexes.I am wrong.
so if I have to update only system created statstics on all tables from a particular database
I will extract all the system created statistics from sys.sysindexes and all the system views.
I just wanted to use [modified_count] from sys.system_internals_column and update stats according to it.
but when i update stats [modified_count] is not becoming zero again.
December 7, 2012 at 8:47 am
Be wary of running sp_UpdateStats if you are regularly rebuilding your indexes. Rebuilding an index causes the statistics to be updated with a FULL SCAN. If you then run sp_UpdateStats after and the rowmodctr value is greater than zero for the index you just rebuilt, your statistics will be updated with a sample unless you override the default behavior for sp_UpdateStats.
December 7, 2012 at 8:51 am
George M Parker (12/7/2012)
Be wary of running sp_UpdateStats if you are regularly rebuilding your indexes. Rebuilding an index causes the statistics to be updated with a FULL SCAN. If you then run sp_UpdateStats after and the rowmodctr value is greater than zero for the index you just rebuilt, your statistics will be updated with a sample unless you override the default behavior for sp_UpdateStats.
I agree with you George.But for now I am only concerned for system created statistics.
It is automatically updated but I wanted it to be done by on my by determing some percentage conditions over the modified count value.
December 7, 2012 at 8:54 am
mani@123 (12/7/2012)
It is automatically updated but I wanted it to be done by on my by determing some percentage conditions over the modified count value.
Why?
Have you tested and determined that updating them all at 10% is better than the auto update at 20? Or maybe 5%, or some other value that applies to every single one of them?
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
December 7, 2012 at 8:57 am
GilaMonster (12/7/2012)
mani@123 (12/7/2012)
It is automatically updated but I wanted it to be done by on my by determing some percentage conditions over the modified count value.Why?
Have you tested and determined that updating them all at 10% is better than the auto update at 20? Or maybe 5%, or some other value that applies to every single one of them?
Alright This is my last question for this post.
SO on what basis sql engine is automatically updating system created stats by turning ON auto update stats.
is it updating at 20% or 10% of some value.
At any cost engine has to compare the values in order to update right.What can be those values.
I am sorry if my question is crazy.Thanks for patience.
December 7, 2012 at 9:01 am
mani@123 (12/7/2012)
SO on what basis sql engine is automatically updating system created stats by turning ON auto update stats.
20% of the values for that column have changed since the last stats update.
Now I'm not saying that's perfect, it's often far from perfect, but in the same vein, there's no other % that is perfectly correct for all stats and all tables.
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
December 7, 2012 at 9:06 am
GilaMonster (12/7/2012)
mani@123 (12/7/2012)
SO on what basis sql engine is automatically updating system created stats by turning ON auto update stats.
20% of the values for that column have changed since the last stats update.
Do we have any separate views to track those changes.
How does sql engine know like its time to update stats?
December 7, 2012 at 9:06 am
mani@123 (12/7/2012)
GilaMonster (12/7/2012)
mani@123 (12/7/2012)
It is automatically updated but I wanted it to be done by on my by determing some percentage conditions over the modified count value.Why?
Have you tested and determined that updating them all at 10% is better than the auto update at 20? Or maybe 5%, or some other value that applies to every single one of them?
Alright This is my last question for this post.
SO on what basis sql engine is automatically updating system created stats by turning ON auto update stats.
is it updating at 20% or 10% of some value.
At any cost engine has to compare the values in order to update right.What can be those values.
I am sorry if my question is crazy.Thanks for patience.
The easiest way to find out is to run DBCC SHOWSTATISTICS and compare the rows to the rows sampled. If they are equal, then the sample was 100%. Just do the calculations and you'll know if the sample was 5%, 10%, etc...
You can run something like this to generate the DBCC SHOW_STATISTICS for all of th auto_created stats in your database:
SELECT 'DBCC SHOW_STATISTICS (' + '''' + object_name(object_id) + ''', [' + name + ']' + ');'
from sys.stats
where STATS_DATE( OBJECT_ID, stats_id) is not null
and auto_created = 1
order by 1
--**********************************************************************
The output will look something like this:
DBCC SHOW_STATISTICS ('AdApplicationExternalMap', [_WA_Sys_00000002_31E50CEF]);
December 7, 2012 at 9:17 am
Thank you all..
December 7, 2012 at 9:19 am
mani@123 (12/7/2012)
Do we have any separate views to track those changes.How does sql engine know like its time to update stats?
No views that you can access. It's buried in the internal system tables.
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
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply