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.
| 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. |
 |
|
|
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. |
|
|
|
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. |
|
|
|
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. |
|
|
|
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. |
|
|
|
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. |
|
|
|
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. |
|
|
|
|
|
|
|
|
? Update Stats on Database with Users Logged on?