update statistics entire database level

Last Post 13 Aug 2010 07:05 PM by russellb. 10 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
sql-tips
New Member
New Member

--
29 Jul 2010 06: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 11:52 AM
Take look at 'STATS_DATE (Transact-SQL)' in books online.
gunneyk
New Member
New Member

--
09 Aug 2010 03: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 07: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 09: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 10: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 01: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 04:52 AM
Not necessary unless you don't update stats on prod.
sql-tips
New Member
New Member

--
13 Aug 2010 12: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 01: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 07: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
You are not authorized to post a reply.

Acceptable Use Policy