August 13, 2014 at 1:41 pm
We have a SQL Server 2008 Enterprise Edition SP3 64 bit environment. We're migrating SQL Server 2000 and 2005 databases to it. It was recommended that once we move the databases on to the server (already done) to do the following:
1. Change the compatiblity level (100)
2. Change the Page Verify Option to CheckSum
3. Update all statistics with fullscan on the user databases
4. Run an integrity check on the older databases: DBCC CHECKDB WITH DATA_PURITY.
I'm concerned with the updating all statistics. I've considered sp_updatestats
or "use @dbname exec sp_MSforeachtable 'update statistics ? with fullscan' go".
Any suggestions on the statistics update? Comments / urls always appreciated. Thanks.
August 13, 2014 at 1:50 pm
Here is code I use. It may not be pretty, but it does what I want by building output of code to run.
with cte as (
select Object_name(object_id) AS TableName
, name as StatName
, STATS_DATE(object_id, stats_id) AS statistics_update_date
from sys.stats
where name not like '_W%'
AND name not like '_d%'
AND Object_name(object_id) NOT LIKE 'sys%'
AND Object_name(object_id) NOT LIKE '!OLD%'
UNION
select Object_name(object_id) AS TableName
, name as StatName
, STATS_DATE(object_id, index_id) AS statistics_update_date
from sys.indexes
where name not like '_W%'
AND name not like '_d%'
AND Object_name(object_id) NOT LIKE 'sys%'
AND Object_name(object_id) NOT LIKE '!OLD%'
--order by 3
)
SELECT DISTINCT 'UPDATE STATISTICS [' + TableName + '];'
from cte
August 15, 2014 at 6:01 am
Why are you worried about updating the statistics? They're going to get updated naturally by the internals process of SQL Server anyway. Some of those updates may be sampled, some may be scanned. I'd suggest doing the scan as part of an update in order to start the database off with the most accurate statistics you can get.
"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
August 15, 2014 at 2:08 pm
Grant,
Thanks for responding. You kind of hit the nail right on the head in that these databases are being moved from SQL 2000 and 2005 to SQL 2008. So I wanted to start them off with the most accurate statistics possible. After that I'd let maintenance take over. That was my thinking anyway.
Any issue with that? Thanks.
August 16, 2014 at 4:16 am
None at all. If you're only temporarily moving them to 2005, I'd skip the update stats step there, but in general it can't hurt at all to have a fresh set of statistics as part of the migration and it's likely to help.
Just remember, the optimizer changes with each release (and service pack and many cumulative updates). So you may see changes in behavior on queries that are already edge cases. Sometimes this may be for the better, but sometimes it will be worse. It's something to be aware of.
"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
August 20, 2014 at 6:49 am
Thanks Grant - appreciate the comments.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply