April 25, 2013 at 1:35 am
Hi,
I am having a little issue in understanding how to implement a good maintenance plan in updating statistics. I have more than one db with very big tables and modifications done every day. I know for sure that the Auto Upate Statistics is not enough for our performance, so i need to update them mannually.
From all the material found online, i managed to get two queries: one for updating all the statistics created for indexes and one for updating all the statistics auto-created.
For updating the statistics for indexes, i use a query from sys.sysindexes, sys.objects and sys.schemas, by finding a percentage between the rowmodctr and rowcnt columns. Based on this percentage, i update the statistics. Also, i use STATS_DATE to update all old statistics.
For updating the auto-created statistics, the column statistics, i use a query that checks the column modified_count from sys.system_internals_partition_columns. I did not found a lot of documentation on this system internal view, so i don't really know how exactly can i analyse the column modified_count to know which statistic need to be updated.
How can analyse the field modified_count from sys.system_internals_partition_columns so that i could find out which statistic needs to be updated?
Or, is there another way i could analyse every auto-created statistics (columns statistics) so i can know which one should be updated?
Thank you!
April 25, 2013 at 3:50 am
April 25, 2013 at 3:58 am
Yes, I know about that solution and that was my first option, but it seems to not be a good solution for my case. I know what it is required for my db maintenance and i would like to make a personalised solution for my dbs only.
April 25, 2013 at 7:24 am
SELECT
p.[object_id],
p.[index_id],
pc.[partition_column_id],
pc.[modified_count]
FROM sys.system_internals_partition_columns pc
JOIN sys.partitions p
ON pc.[partition_id] = p.[partition_id]
WHERE p.[object_id] = OBJECT_ID ('Yourtablename');
April 25, 2013 at 7:27 am
Thank you for your answer!
I already have a query to find modified_count field.
My question is how can i analyse it (modified_count ) to know wich statistics need update?
April 25, 2013 at 2:25 pm
Have a look at sys.dm_db_stats_properties
http://msdn.microsoft.com/en-us/library/jj553546.aspx
Ola Hallengren
April 26, 2013 at 1:36 am
Thank you for your answer!
Unfortunally i cannot use sys.dm_db_stats_properties, since our server does not have Service Pack 2. It will be installed, but for the moment i cannot use it.
From what I could see the modification_counter column represents the total number of modifications since the last time the statistics were updated.
My issues are with the column sys.system_internals_partition_columns.modified_count
I did dome tests in updating the statistics in a table and the column sys.system_internals_partition_columns.modified_count did not seem to change.
As an example:
The table has 176719385 rows
The statistics on the clustered index were last updated on 2011-04-21 13:34:34.183
The column sys.sysindexes.rowmodctr = 4694424
The column sys.system_internals_partition_columns.modified_count = 185944892
After the update with fullscan:
The table has 176719385 rows
The statistics on the clustered index were last updated on 2013-04-26 09:29:38.223
The column sys.sysindexes.rowmodctr = 0
The column sys.system_internals_partition_columns.modified_count = 185944892
I probably do not have all the information, or i am missing something obvious, but what i want is to understand how the sys.system_internals_partition_columns.modified_count is calculated.
It does not seem to change after the update of statistics, like the column sys.sysindexes.rowmodctr.
Also, the column sys.sysindexes.rowmodctr only refers to statistics for indexes, and i need something that would show me the same way the modifications on columns statistics, since the last time the statistics were updated.
Sorry for the long post,
Thank you!
April 26, 2013 at 5:35 am
April 26, 2013 at 6:15 am
Again, thank you for your answer and interest ! 🙂
For the article part, i have read it in the past to. I cannot use the DAC connection, but from what i understood the column sys.system_internals_partition_columns .modified_count is similar to sys.sysrscols.rcmodified column.
For the sysindexes hint, thank you..that was what i was missing.
In my query for analysing statistics i had a filter rowcnt > 0 (to only get the tables that have data), which eliminated all the columns statistics from the returning set. I read now, that the rowcnt column is actually the number of rows in the index.
Thank you for your interest!
April 30, 2013 at 8:16 am
Dropping my 2 cents here.
I actually contacted Ola just yesterday I think 🙂 ... for a very similar question (what a coincidence)
I've implemented his solution but found that some reports were running slow after our daily or weekly data upload. So I was told that by default, Ola's solution does not update statistics, the ReIndex job I mean. I added that option as part of Saturday's weekly plan.
Now, by experience, and like you said, updating statistics should be done after major data insertions, so the queries will have fresh statistics and execution plans will be based on that, minimizing the chance of picking the wrong execution plan or Index.
So ... my advice ... validate your data upload schedule. You can update statistics as part of your store procedure's logic or, you can run a daily job just after that data insertion.
May 2, 2013 at 9:17 am
Hi,
As you said you have big tables, then enabling trace flag 2371 can be of asset for your databases. See this
Regards,
IgorMi
Igor Micev,My blog: www.igormicev.com
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply