July 14, 2015 at 6:30 am
When CPU usage is high about 85%, same query performs slow in Production database, and all of the query start performing slow. When i update statistics using exec sp_updatestats in production database , all the query performs well like back to normal state. Could you please suggest is it the good way to do ?
Could you please suggest permanent solution for this?
Even in same transaction, we have not faced these issue previously.
July 14, 2015 at 6:43 am
No, it's not a good solution. By updating the stats you're likely forcing all plans to recompile. It's probably only one or two queries which are causing the problem and which either need some modification to ensure they have stable execution plans, or one or two tables which need their stats updating.
Do some monitoring, see if you can identify the query which is the root cause of the problem, then apply a specific fix to the identified problem.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 14, 2015 at 10:42 am
Could you please let me know how do i find which tables stats needs to be updated instead of whole table's stats.
So that i do not have to update stats of all the tables using sp_updatestats.
Could you please provide me query to find which table we needs to apply stats or related some suggestions?
July 14, 2015 at 11:22 am
You can start by using some of the metrics built into SQL server using DMV's like sys.dm_exec_query_stats and sys.dm_exec_procedure_stats to look at most executed queries and longest running queries. Be aware that these DMV's are based on what queries have plans in the cache, so you can poll them and store the data for later use if you want.
You can also use Extended Events to identify long-running queries. This article will help set that up, https://redmondmag.com/articles/2013/12/11/slow-running-sql-queries.aspx
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply