SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Can we execute sp_updatestats in heavy transaction time


Can we execute sp_updatestats in heavy transaction time

Author
Message
keshab.basnet
keshab.basnet
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1055 Visits: 136
Our database becomes very slow in heavy transaction day after being transaction about 7000 with in 2 hours even i update database stats using sp_updatestats at night. We have 2 days specially heavy transaction per month. After executing sp_updatestats in heavy transaction time around mid day, same query will be executed in 1 seconds where it used to about 2 minutes just before executing sp_updatestats. We have database about 50GB. It takes about 3-4 minutes to execute sp_updatestats in our database.

1. Can we execute sp_updatestats during heavy transaction while database performing slow
2. Could you please suggest if there is any better way to overcome slow performance during heavy transaction.

Currently, During maintenance plan, backup both database and log, update stats will be done every night.
Mostly twice a month, Index reorganize and index rebuild currently being done.
Gianluca Sartori
Gianluca Sartori
SSC Guru
SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)

Group: General Forum Members
Points: 100181 Visits: 13373
You should identify which stats need updating rather than updating all the stats. You probably have some queries that perform poorly due to stats out of date and you should start your investigation from those queries.
Take the query and look at the actual execution plan: do you see wildly inaccurate estimates? Then you need to understand where they come from. Do you have new data in the tables which is not accounted for? Maybe you should enable trace flag 2389 and/or 2390 to enable statistics branding (useful for ever increasing columns).
If you have huge tables with stats that are not updated due to the inability to reach the 20% threshold of modified rows, maybe enabling the trace flag 2371 will help you (it enables an adaptive threshold for big tables).
If you have stats that are not representative of the data unless based on a 100% sample, you should disable auto updating them by using NORECOMPUTE and set up a manual update process using a SQL Server Agent job.
You can read more about TF 2371, 2389 and 2390 here: https://www.simple-talk.com/sql/database-administration/statistics-on-ascending-columns/

-- Gianluca Sartori
How to post T-SQL questions
spaghettidba.com
@spaghettidba
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (376K reputation)SSC Guru (376K reputation)SSC Guru (376K reputation)SSC Guru (376K reputation)SSC Guru (376K reputation)SSC Guru (376K reputation)SSC Guru (376K reputation)SSC Guru (376K reputation)

Group: General Forum Members
Points: 376092 Visits: 34642
1. Can you? Yes. Should you? Probably not. sp_updatestats is extremely undisciplined in which statistics it updates. Remember, when stats get updated, not only are you adding the load to the system for the statistics update process, but, after that process completes, any plans in cache that reference those stats is going to be recompiled the next time it gets referenced. That's going to add quite a bit of locking and resource contention, especially around your CPU.

2. Gianluca has already made a number of great suggestions and I strongly support you following those. I'd add the idea of targeting statistics updates where needed using UPDATE STATISTICS, not the generic approach of sp_updatestats. You might also want to test whether or not setting your automatic update of statistics to async is helpful. That one is not a sure thing. It helps in some cases, it doesn't in others.

Now, should you run the individual UPDATE STATS commands during high load on the system? Maybe. There's no hard and fast answer there. Experimentation and testing will be the key. I've done it to great success in some situations. It really depends on if the cost incurred from a targeted statistics update offsets the cost of bad execution plans causing poor performance. In many cases, it can, but not all.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
Product Evangelist for Red Gate Software
keshab.basnet
keshab.basnet
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1055 Visits: 136
I was checking the statistics objects in my Microsoft SQL Server database and found some statistics with strange names. Could you please explain what the _WA_Sys... statistics are? Why are they created? Can I safely delete them? Do they differ from the other statistics objects?
and recreate with following commands help to boost performance?
EXEC sp_createstats @indexonly = 'NO', @fullscan = 'FULLSCAN', @norecompute ='NO'
Could you please suggest?
Gianluca Sartori
Gianluca Sartori
SSC Guru
SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)

Group: General Forum Members
Points: 100181 Visits: 13373
Those stats are created automatically by SQL Server. If you want to replace them with custom statistics, you can do it.

-- Gianluca Sartori
How to post T-SQL questions
spaghettidba.com
@spaghettidba
keshab.basnet
keshab.basnet
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1055 Visits: 136
Dear Gianluca Sartori,
Is that beneficial to drop and recreate a more user-friendly name? Does it help in performance of insert, update, select operation? Could you please suggest?
Following block say it is beneficial to boost performance.

http://www.mssqltips.com/sqlservertip/2734/what-are-the-sql-server-wasys-statistics/
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (376K reputation)SSC Guru (376K reputation)SSC Guru (376K reputation)SSC Guru (376K reputation)SSC Guru (376K reputation)SSC Guru (376K reputation)SSC Guru (376K reputation)SSC Guru (376K reputation)

Group: General Forum Members
Points: 376092 Visits: 34642
Those stats get created when someone does a filter operation like a WHERE clause or a JOIN on columns that don't have either an index or custom statistics. The optimizer will then create statistics. They're usually helpful. Generally, I never touch them. Could you remove them all just to see which ones get recreated? Sure. Will that help your system out? A tiny amount. It's actually not a good use of your time. I'd just leave them alone until you're looking to tune a query, then refer to them to see where people are referencing columns where you don't have indexes. It might prove beneficial then.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
Product Evangelist for Red Gate Software
Summer90
Summer90
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30368 Visits: 4310
I would find out which queries perform poorly, look at their execution plans when it runs good and when it runs poorly. If no other tuning indexes can help it run better than you can run update stats on the specific tables that have queries running poorly on. That way you are spending the resources updating stats on only the tables you need updated. Benefit to you will the stats job will run quicker and cause less CPU consumption.

I found 4 tables here in a specific db that had that same senario happening. The indexes were good, the problem became that stats during certain data operations became stale so I just put together a job that runs right after the mass data change to update stats on those specific tables. Fixed the issue.
SQL ADMIN 79
SQL ADMIN 79
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1909 Visits: 673
Gianluca Sartori / Grant Fritchey,
There is a daily sp_update stats (on db which is 4TB) that is causing high CPU. To reduce the CPU, it is requiring us to perform Update Statistics of 5 specific tables manually. Query causing high CPU after the sp_update stats has been identified. It is performing an index scan instead of index seek. Once the Update Statistics on 5 tables is performed, query performs an index seek. Why is SP_update stats causing this issue and Update statistics is not? Shouldn't they both be doing the same?
It is a high OLTP system with 2.5 million transactions per hour with heavy reads/writes.
How do we find a balance between SP_update stats versus Update Statistics?
We want to make sure statistics on the database is up to date for optimal query optimization.
Thoughts??

Thanks!

[font=Arial]Prakash Bhojegowda MCSE, MCSA (SQL2012)[/font]
Jeffrey Williams 3188
Jeffrey Williams 3188
SSC Guru
SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)

Group: General Forum Members
Points: 83086 Visits: 11826
SQL ADMIN 79 - Saturday, February 24, 2018 6:54 AM
Gianluca Sartori / Grant Fritchey,
There is a daily sp_update stats (on db which is 4TB) that is causing high CPU. To reduce the CPU, it is requiring us to perform Update Statistics of 5 specific tables manually. Query causing high CPU after the sp_update stats has been identified. It is performing an index scan instead of index seek. Once the Update Statistics on 5 tables is performed, query performs an index seek. Why is SP_update stats causing this issue and Update statistics is not? Shouldn't they both be doing the same?
It is a high OLTP system with 2.5 million transactions per hour with heavy reads/writes.
How do we find a balance between SP_update stats versus Update Statistics?
We want to make sure statistics on the database is up to date for optimal query optimization.
Thoughts??

Thanks!

You posted this to a thread that is 3 years old - it would be better if you posted this as a separate question.


Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search