Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Maintaining Statistics Expand / Collapse
Author
Message
Posted Thursday, April 25, 2013 1:35 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 8:24 AM
Points: 411, Visits: 265
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!
Post #1446325
Posted Thursday, April 25, 2013 3:50 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 31, 2014 10:27 PM
Points: 116, Visits: 347
Check out Ola Hallengren's SQL Server Maintenance Solution. A lot of people out there use it!

Greetz
SQL Pizza



sometimes you dont see the pizza for the toppings...
seek and ya shall find...
Post #1446378
Posted Thursday, April 25, 2013 3:58 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 8:24 AM
Points: 411, Visits: 265
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.
Post #1446383
Posted Thursday, April 25, 2013 7:24 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, March 21, 2014 9:46 AM
Points: 387, Visits: 1,078
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');
Post #1446486
Posted Thursday, April 25, 2013 7:27 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 8:24 AM
Points: 411, Visits: 265
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?
Post #1446487
Posted Thursday, April 25, 2013 2:25 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 18, 2014 11:57 AM
Points: 169, Visits: 1,869
Have a look at sys.dm_db_stats_properties

http://msdn.microsoft.com/en-us/library/jj553546.aspx

Ola Hallengren
http://ola.hallengren.com
Post #1446713
Posted Friday, April 26, 2013 1:36 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 8:24 AM
Points: 411, Visits: 265
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!
Post #1446828
Posted Friday, April 26, 2013 5:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 18, 2014 11:57 AM
Points: 169, Visits: 1,869
Here is an interesting blog post about sys.system_internals_partition_columns.
http://www.sqlskills.com/blogs/paul/how-are-per-column-modification-counts-tracked/

Regarding sys.sysindexes that contains information not only about indexes, but also about column-statistics.

Ola Hallengren
http://ola.hallengren.com
Post #1446910
Posted Friday, April 26, 2013 6:15 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 8:24 AM
Points: 411, Visits: 265
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!
Post #1446921
Posted Tuesday, April 30, 2013 8:16 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 2:56 PM
Points: 384, Visits: 1,260
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.
Post #1448055
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse