SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 ? Update Stats on Database with Users Logged on?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

viperbyte
Posting Yak Master

USA
132 Posts

Posted - 12/04/2012 :  08:42:47  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/04/2012 :  09:11:07  Show Profile  Reply with Quote
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

USA
15665 Posts

Posted - 12/04/2012 :  09:33:31  Show Profile  Visit robvolk's Homepage  Reply with Quote
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

USA
132 Posts

Posted - 12/04/2012 :  09:37:04  Show Profile  Reply with Quote
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

USA
15665 Posts

Posted - 12/04/2012 :  09:50:12  Show Profile  Visit robvolk's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/04/2012 :  12:29:25  Show Profile  Reply with Quote
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

USA
132 Posts

Posted - 12/05/2012 :  09:11:34  Show Profile  Reply with Quote
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

USA
15665 Posts

Posted - 12/05/2012 :  11:17:01  Show Profile  Visit robvolk's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000