July 20, 2004 at 9:49 am
I read that this is a default configuration, but where do I check to see if it is configured and how do I change it?
July 21, 2004 at 7:47 pm
As per BOL:
SQL Server automatically updates this statistical information periodically as the data in the tables changes. The sampling is random across data pages, and taken from the table or the smallest nonclustered index on the columns needed by the statistics. After a data page has been read from disk, all the rows on the data page are used to update the statistical information. The frequency at which the statistical information is updated is determined by the volume of data in the column or index and the amount of changing data. For example, the statistics for a table containing 10,000 rows may need updating when 1,000 index values have changed because 1,000 values may represent a significant percentage of the table. However, for a table containing 10 million index entries, 1,000 changing index values is less significant, and so the statistics may not be automatically updated. SQL Server, however, always ensures that a minimum number of rows are sampled; tables that are smaller than 8 megabytes (MB) are always fully scanned to gather statistics.
The cost of this automatic statistical update is minimized by sampling the data, rather than analyzing all of it. Under some circumstances, statistical sampling will not be able to accurately characterize the data in a table. You can control the amount of data that is sampled during manual statistics updates on a table-by-table basis by using the SAMPLE and FULLSCAN clauses of the UPDATE STATISTICS statement. The FULLSCAN clause specifies that all of the data in the table is scanned to gather statistics, whereas the SAMPLE clause can be used to specify either the percentage of rows to sample or the number of rows to sample.
The systems fine with no users loggged in. Can we keep it that way ?br>
July 26, 2004 at 8:02 pm
If you are referring to Auto Create Stats and Auto Update Stats, you can view this by right clicking your database in Enterprise Manager, click properties, and then choose the Options tab. If there are ticks in the corresponding boxes, they have been turned on. They are turned on by default.
Personally I uncheck the option on all of my databases and have a stored procedure that I schedule to update the statistics. This is because SQL Server wasn't updating the stats fast enough on our production system. Also, if you have auto stats checked, it could run anytime - like when your loading data, doing a backup, running a report and this slows things down.
Hope this helps,
Angela
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply