February 20, 2017 at 2:13 am
I have observed that for some tables the statistics have not been updated for a long time but the rowmodctr value is very high. Can anyone help me understand why this behaviour? We have Ola maintenance task and the index fragmentation are under check.
stats_last_updated_time rowmodctr
2017-02-12 14:37:53.490 1560298
2016-11-04 01:01:01.003 571813
2016-11-04 01:01:05.610 571813
2016-11-04 01:01:23.843 571813
2016-11-24 13:16:15.550 449629
2016-11-04 01:02:15.633 390993
2016-12-14 17:38:22.253 340247
2016-11-30 11:29:52.470 281436
2016-11-04 01:00:50.560 218732
2017-01-09 12:19:28.500 187580
February 20, 2017 at 2:27 am
Take a look at this article. There are several reasons why your statistics may not have updated. May be auto update stats isn't turned on. Maybe your table is so large that the rowmodctr values you quoted don't take it past the 20% + 500 threshold. Or maybe the threshold has been crossed but no query has run that uses the statistics.
John
February 20, 2017 at 3:40 am
Rechana Rajan - Monday, February 20, 2017 2:13 AMI have observed that for some tables the statistics have not been updated for a long time but the rowmodctr value is very high. Can anyone help me understand why this behaviour? We have Ola maintenance task and the index fragmentation are under check.stats_last_updated_time rowmodctr
2017-02-12 14:37:53.490 1560298
2016-11-04 01:01:01.003 571813
2016-11-04 01:01:05.610 571813
2016-11-04 01:01:23.843 571813
2016-11-24 13:16:15.550 449629
2016-11-04 01:02:15.633 390993
2016-12-14 17:38:22.253 340247
2016-11-30 11:29:52.470 281436
2016-11-04 01:00:50.560 218732
2017-01-09 12:19:28.500 187580
have you scheduled a stats update task with Ola's scripts
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 20, 2017 at 6:49 am
Check out the traceflag 2371. If you're not hitting automatic statistics updates with the default settings, you may benefit from setting this traceflag.
"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
February 21, 2017 at 1:31 am
John Mitchell-245523 - Monday, February 20, 2017 2:27 AMTake a look at this article. There are several reasons why your statistics may not have updated. May be auto update stats isn't turned on. Maybe your table is so large that the rowmodctr values you quoted don't take it past the 20% + 500 threshold. Or maybe the threshold has been crossed but no query has run that uses the statistics.John
Thanks John,
We have enabled Trace Flag 2371 ,may be the later 2 points are valid for me but how to make sure that?
February 21, 2017 at 1:33 am
Perry Whittle - Monday, February 20, 2017 3:40 AMRechana Rajan - Monday, February 20, 2017 2:13 AMI have observed that for some tables the statistics have not been updated for a long time but the rowmodctr value is very high. Can anyone help me understand why this behaviour? We have Ola maintenance task and the index fragmentation are under check.stats_last_updated_time rowmodctr
2017-02-12 14:37:53.490 1560298
2016-11-04 01:01:01.003 571813
2016-11-04 01:01:05.610 571813
2016-11-04 01:01:23.843 571813
2016-11-24 13:16:15.550 449629
2016-11-04 01:02:15.633 390993
2016-12-14 17:38:22.253 340247
2016-11-30 11:29:52.470 281436
2016-11-04 01:00:50.560 218732
2017-01-09 12:19:28.500 187580have you scheduled a stats update task with Ola's scripts
Thanks Perry,
I have Index Maintenance Task for User Databases, I hope that will take care of statistics as well. Is there a seperate stats update task from Ola?
February 21, 2017 at 1:34 am
Grant Fritchey - Monday, February 20, 2017 6:49 AMCheck out the traceflag 2371. If you're not hitting automatic statistics updates with the default settings, you may benefit from setting this traceflag.
Thanks Grant,
We have Trace Flag 2371 enabled on our servers.
February 21, 2017 at 2:29 am
Rechana Rajan - Tuesday, February 21, 2017 1:31 AMJohn Mitchell-245523 - Monday, February 20, 2017 2:27 AMTake a look at this article. There are several reasons why your statistics may not have updated. May be auto update stats isn't turned on. Maybe your table is so large that the rowmodctr values you quoted don't take it past the 20% + 500 threshold. Or maybe the threshold has been crossed but no query has run that uses the statistics.John
Thanks John,
We have enabled Trace Flag 2371 ,may be the later 2 points are valid for me but how to make sure that?
I'm sure you don't need me to show you how to check how many rows are in your table. As for whether a query has run, you could try querying the plan cache to see whether the name of the table appears in cache. There's a link in the article I mentioned that tells you how to do that. It's best to look in the query plan XML rather than the query text, since the table may be referenced through a view or stored procedure or synonym.
By the way, what is the current value of rowmodctr for the indexes on that table?
SELECT leaf_insert_count
+ leaf_delete_count
+ leaf_update_count
+ leaf_ghost_count AS rowmodctr
FROM sys.dm_db_index_operational_stats(DB_ID('MyDatabase'),OBJECT_ID('MyTable'),NULL,NULL)
John
February 21, 2017 at 3:26 am
Grant Fritchey - Monday, February 20, 2017 6:49 AMCheck out the traceflag 2371. If you're not hitting automatic statistics updates with the default settings, you may benefit from setting this traceflag.
Thanks Grant,
We have Trace Flag 2371 enabled on our servers.
John Mitchell-245523 - Tuesday, February 21, 2017 2:29 AMRechana Rajan - Tuesday, February 21, 2017 1:31 AMJohn Mitchell-245523 - Monday, February 20, 2017 2:27 AMTake a look at this article. There are several reasons why your statistics may not have updated. May be auto update stats isn't turned on. Maybe your table is so large that the rowmodctr values you quoted don't take it past the 20% + 500 threshold. Or maybe the threshold has been crossed but no query has run that uses the statistics.John
Thanks John,
We have enabled Trace Flag 2371 ,may be the later 2 points are valid for me but how to make sure that?
I'm sure you don't need me to show you how to check how many rows are in your table. As for whether a query has run, you could try querying the plan cache to see whether the name of the table appears in cache. There's a link in the article I mentioned that tells you how to do that. It's best to look in the query plan XML rather than the query text, since the table may be referenced through a view or stored procedure or synonym.
By the way, what is the current value of rowmodctr for the indexes on that table?
SELECT leaf_insert_count
+ leaf_delete_count
+ leaf_update_count
+ leaf_ghost_count AS rowmodctr
FROM sys.dm_db_index_operational_stats(DB_ID('MyDatabase'),OBJECT_ID('MyTable'),NULL,NULL)John
Thanks John,
Query Provided gave
rowmodctr
188635
and the below query gave
select a.id as 'ObjectID', isnull(a.name,'Heap') as 'IndexName', b.name as 'TableName', stats_date (id,indid)
as stats_last_updated_time,a.rowmodctr from sysindexes as a inner join sys.objects as b on a.id = b.object_id
where b.type = 'U' and id=object_id(N'msn.logs')order by rowmodctr desc
IndexName TableName rowmodctr
PK_Log Logs 578162
_WA_Sys_00000006_40C49C62 Logs 578162
_WA_Sys_00000007_40C49C62 Logs 578162
_WA_Sys_00000004_40C49C62 Logs 455978
_WA_Sys_00000003_40C49C62 Logs 346596
_WA_Sys_00000002_40C49C62 Logs 59132
_WA_Sys_00000005_40C49C62 Logs 59132
February 21, 2017 at 3:39 am
OK, so your rowmodctr isn't anywhere near the threshold at which it updated last time. How many rows are there in your table?
Leaving all that aside, you'll still want to investigate why your regular maintenance job isn't updating these statistics. You asked earlier whether the index maintenance task takes care of statistics as well. You'll need to consult the documentation about that, or pore through the stored procedure code.
John
February 21, 2017 at 4:26 am
Rechana Rajan - Tuesday, February 21, 2017 1:33 AMI have Index Maintenance Task for User Databases, I hope that will take care of statistics as well. Is there a seperate stats update task from Ola?
No, it won't.
Check the documentation for Ola's maintenance SP and it details how to schedule the SP to performs just stats updates without index maintenance
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 21, 2017 at 5:10 am
John Mitchell-245523 - Tuesday, February 21, 2017 3:39 AMOK, so your rowmodctr isn't anywhere near the threshold at which it updated last time. How many rows are there in your table?Leaving all that aside, you'll still want to investigate why your regular maintenance job isn't updating these statistics. You asked earlier whether the index maintenance task takes care of statistics as well. You'll need to consult the documentation about that, or pore through the stored procedure code.
John
Thanks John,
As pointed out these tables are logs and are not queried frequently. Yes the Index Maintenance take care of statistics as well .
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply