update statistics entire database level

Last Post 13 Aug 2010 08:05 PM by russellb. 10 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
sql-tips
New Member
New Member

--
29 Jul 2010 07:03 AM
How do I see database statistics last updated. Basically, How do I find when sp_updatestats was ran last time. is there any dynamic views to find out?
rm
New Member
New Member

--
29 Jul 2010 12:52 PM
Take look at 'STATS_DATE (Transact-SQL)' in books online.
gunneyk
New Member
New Member

--
09 Aug 2010 04:37 PM
This will let you see when the stats were updated but not necessarily when sp_updatestats were run.

SELECT name AS stats_name,
STATS_DATE(object_id, stats_id) AS statistics_update_date, *
FROM sys.stats

sql-tips
New Member
New Member

--
11 Aug 2010 08:47 AM
Thanks.. Also, whenever I refresh the db I had to run sp_updatestats to run queries fast. If I don't run then my db response is very slow.
rm
New Member
New Member

--
11 Aug 2010 10:38 AM
What do you mean refresh db? Should update stats after large amount of data changing.
russellb
New Member
New Member

--
11 Aug 2010 11:06 AM
And if you're changing THAT much data, you probably should be rebuilding indexes too.
sql-tips
New Member
New Member

--
12 Aug 2010 02:32 PM
Refreshing is Restore db from production. Whenever, I restored db on dev, I had to run the statistics, which I am not changing any data.
rm
New Member
New Member

--
13 Aug 2010 05:52 AM
Not necessary unless you don't update stats on prod.
sql-tips
New Member
New Member

--
13 Aug 2010 01:51 PM
We have schedule to update daily in prod and prod is faster. Even though it is not necessary queries runs slow in dev as soon as restore, after update stats runs faster.
gunneyk
New Member
New Member

--
13 Aug 2010 02:30 PM
MS has always recommended updating the stats after a restore especially if the instance where it is restored is a later version or service pack than where the backup was taken.
russellb
New Member
New Member

--
13 Aug 2010 08:05 PM
Agree with gunneyk, want to add though, that it is highly likely that the hardware on your dev environment is inferior to that in production.

Run a few queries with statistics io on and see if there is a difference. Also review a few execution plans versus those in prod


Acceptable Use Policy
---