| Author |
Topic |
|
DBADave
Constraint Violating Yak Guru
366 Posts |
 Posted - 2008-10-08 : 19:13:59
|
| Rather then having a script update all statistics in a database I only want to update statistics that are out of date. I assume with 2005 I still run DBCC SHOW_STATISTICS to get index statistics, but how do I determine if an index should be updated? Is there a DMV I should be using instead of SHOW_STATISTICS?Thanks, Dave |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-08 : 20:11:14
|
| If you have enabled Auto update statistics feature turned on on your database,then you shouldn't worry about it. |
 |
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2008-10-08 : 23:42:16
|
| It's not enabled. The software vendor requires it be turned off. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-10-09 : 01:36:41
|
| Just schedule the sp_updatestats procedure to run in a job once per day.It automatically decides if statistics need to be updated, and only updates the ones that need it.CODO ERGO SUM |
|
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2008-10-09 : 01:55:54
|
| You can't specify a sampling ratio with sp_updatestats so I don't know how accurate the statistics will be vs DBCC UPDATE STATISCS WITH FULLSCAN. I still would like to know how to determine if statistics need updating. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-09 : 08:17:15
|
| If it requires frequent updating statistics,then why not Turn the feature on for Auto update statistics? |
|
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2008-10-09 : 10:17:10
|
| As I indicated above the vendor recommends AUTO_UPDATE_STATISTICS and AUTO_CREATE_STATISTICS be turned off. They site some Microsoft articles indicating that with some large and heavily used systems automatically updating and creating statistics can slow performance. Our databases will be about 500GB initially and grow by 250GB each year. It's a very large financial and reporting system for a financial company. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-09 : 10:19:56
|
| So how often you rebuild index ? How high is your transaction rate ? |
|
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2008-10-09 : 10:24:43
|
| We only rebuild indexes when fragmentation is at a certain level and that job runs every weekend. Not all indexes are rebuilt and therefore not all statistics are updated. Conditional logic determine if a rebuild or a reorg is performed.Dave |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-09 : 10:32:46
|
| You are going to Nail down your Server if you do AutoUpdate with FUll Scan . See why and when you should enable from Kimberly.http://www.sqlskills.com/blogs/kimberly/2008/08/24/AutoUpdateStatisticsAndAutoCreateStatisticsShouldYouLeaveThemOnAndorTurnThemOn.aspx |
|
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2008-10-09 : 10:46:01
|
| Everyone is missing my point. The vendor said not to do it. If we go against the vendor's recommendations they will always point to that, whenever we encounter production problems. We will lose their technical support. I agree that auto update statistics should probably be on. I've always kept in on for every system we support, but this system is different. I simply need to know if it is possible to identify when statistics are incorrect and should be updated. I'll call Microsoft for the answer.Dave |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-09 : 11:05:28
|
| Well, We have already said whatever we can. You will have to call microsoft for it. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-10-09 : 12:06:33
|
| If you leave AUTO_CREATE_STATISTICS off, you won't get any statistics to update, except for the indexes, unless the vendor has specifically created statistics. You could run the sp_createstats proc, but you will end up with lot of statistics that you probably do not need.You should be OK using sp_updatestats. The 2005 version uses the same algorithm that auto update statistics uses to determine if the statistics need updating. You can specify the RESAMPLE option if you want after manually running update statistics with the sample size you want. That is probably not necessary, since 2005 is designed to gather at least the minimum sample size needed to get useful statistics.Having AUTO_UPDATE_STATISTICS and AUTO_CREATE_STATISTICS off is likely to result in far worse performance than having them on. You could consider setting AUTO_UPDATE_STATISTICS_ASYNC on so that queries do not wait for automatic statistic updates to complete. You can’t auto create statistics asynchronously, but that should happen far less often and be less of a problem once the system is in production for a while. If you run sp_updatestats each night or when the system is fairly idle, you are less likely to have auto updates running when the system is busy.Maybe the place to start is to ask the vendor what their plan is for this, since they are going against the usual best practices. Ask them to explain their alternative and why it is better. It is possible that they are basing this on things that were true about prior versions, but don’t really apply to SQL 2005.CODO ERGO SUM |
|
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2008-10-09 : 12:30:08
|
| I agree completely about getting the vendor to explain their reasoning. In fact I've contacted the vendor for clarification since their technical documentation references Microsoft KB articles and links that no longer exist. They sent me an updated document, but only one of the links referenced in that document actually worked. That link took me to a 2000 article where Microsoft contained a link to a very nice 2005 article on statistics. I asked the vendor if their engineers have confirmed problems in 2005 or only in 2000 and am waiting for their response. I also pointed out to them the 2005 Microsoft article indicated that auto updates stats should be left on for 98% of all applications. We will see what they say.In regards to statistics not being created if AUTO_CREATE_STATISTICS is OFF are you sure about that? According to BOL...When you create an index, the query optimizer automatically stores statistical information about the indexed columns. Also, when the AUTO_CREATE_STATISTICS database option is set to ON (default), the Database Engine automatically creates statistics for columns without indexes that are used in a predicate.The second sentence does sound like statistics won't be created for columns without indexes when I have AUTO_CREATE_STATISTICS OFF. That's another question for the vendor.The 2005 link does give guidelines for determining when an index statistics should be updated. SQL Server 2005 determines whether to update statistics based on changes to column modification counters (colmodctrs).A statistics object is considered out of date in the following cases:• If the statistics is defined on a regular table, it is out of date if:1.The table size has gone from 0 to >0 rows. 2.The number of rows in the table when the statistics were gathered was 500 or less, and the colmodctr of the leading column of the statistics object has changed by more than 500 since then. 3.The table had more than 500 rows when the statistics were gathered, and the colmodctr of the leading column of the statistics object has changed by more than 500 + 20% of the number of rows in the table when the statistics were gathered. • If the statistics object is defined on a temporary table, it is out of date as discussed above, except that there is an additional threshold for recomputation at 6 rows, with a test otherwise identical to test 2 in the previous list. Table variables do not have statistics at all.Thanks, Dave |
|
|
|
iapetus
Starting Member
15 Posts |
Posted - 2008-12-15 : 11:41:15
|
| It is funny that your vendor is quoting Microsoft as the reason they have for not enabling these features.Even in Books Online, the recommendation is to leave AUTO_CREATE and AUTO_UPDATE enabled, but then to use the sp_autostats procedure to disable only the few indexes that may be adversely affected by the AUTO UPDATE features (i.e., very large tables with high modification counts).But then, you will definitely need to watch those few indexes for staleness as well as setting up an alert for the Lock Requests/sec counter. Baseline your system to determine what is typical, but I've seen operations in the 50,000 to 100,000's range, but performance degrades quickly as this number approaches or exceeds the 1 million mark.We had a vendor that used to recommend the same sort of thing until bad statistics brought the online banking system to its knees. Lock requests were overloading the CPUs. We re-enabled AUTO CREATE and disabled the one table using sp_autostats, but updated the statistics on that table manually 3 times a day during more favorable operational periods.Sincerely,Anthony Thomas |
|
|
|
|
How to determine if index statistics need updating