Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 ? Update Stats on Database with Users Logged on?

Author  Topic 

viperbyte
Posting Yak Master

132 Posts

Posted - 2012-12-04 : 08:42:47
Good morning all.

A few days ago a fellow member showed me some sql code in helping me with speeding things up. Would it be a bad idea to execute this script on the Production(live) database with all users logged on? About 15 users btw. Will this bog the system down considerably? Or would it be best to do it before or after everyone is on the system? Will this take a long time? I ran the script on the devlopment server/database and it was a snap. The db has hundreds of tables. This is the script:

SELECT object_schema_name(s.object_id) + '.' + OBJECT_NAME(s.object_id) + '.' + s.name table_stats ,p.rows,
STATS_DATE(s.object_id,s.stats_id) stats_date, CONVERT(INT, INDEXPROPERTY(p.object_id, s.name, 'rowmodcnt80'))
modified_rows, 'UPDATE STATISTICS ' + QUOTENAME(object_schema_name(s.object_id)) + '.' +
QUOTENAME(OBJECT_NAME(s.object_id)) + QUOTENAME(QUOTENAME(s.name),'(') + ' -- ' + CAST(p.rows AS VARCHAR(20)) SQL
FROM sys.stats s INNER JOIN sys.partitions p ON s.object_id=p.object_id AND p.index_id<2
WHERE s.object_id>100
-- ignore system objects AND p.rows>100
-- change rowcount if needed AND STATS_DATE(s.object_id,s.stats_id)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-04 : 09:11:07
Running the query you posted only generates the script for doing the statistics update. That should not cause any peformance issues even on a production system with hundreds of tables. However, if you were to run the generated scripts to update the statistics, that can affect performance, so I would do that at off-peak hours.
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-12-04 : 09:33:31
UPDATE STATISTICS scans the data and causes an I/O hit, plus it runs in parallel and may cause CPU thread contention (this happens on my server anyway). It would be better to run it during quiet periods, especially on larger tables.
Go to Top of Page

viperbyte
Posting Yak Master

132 Posts

Posted - 2012-12-04 : 09:37:04
Oh, thanks. When I ran it on development I didn't realize that the script created a script. So I will run it again and look for the script it creates and of course I'll take your advice and run that newly created script in quiet times like you both suggested. Super thanks.
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-12-04 : 09:50:12
Yes, the generated script should be run when it's relatively quiet. The query that generates it is safe to run any time.
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-04 : 12:29:25
If you are doing for DB, it would be better with sp_updatestats coz it only does whichever is required. Also you have to consider that stats are itself updated if you have reindex job running.
Go to Top of Page

viperbyte
Posting Yak Master

132 Posts

Posted - 2012-12-05 : 09:11:34
Good morning all. Where is this script that is created from the above script in the original post? I don't see it in a mangement studio window.
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-12-05 : 11:17:01
It will show in the query results, the last column (SQL). Copy and paste that column into another query window to run it. Use grid mode for the results.
Go to Top of Page
   

- Advertisement -