May 20, 2014 at 10:13 am
Hello,
I am working on an existing infrastructure and i do not have liberty to change much right now. I am in a situation where app issues update statistics command quite often. So frequently that sometimes one blocks another. Is there any way i can do something like this
IF ( update_statistics going on)
dont do anything
else
run update statistics
This is temporary solution untill i fix bad inline SQL code (in app) and use SPs.
Thanks alot in advance.
May 20, 2014 at 10:18 am
ekant_alone (5/20/2014)
Hello,I am working on an existing infrastructure and i do not have liberty to change much right now. I am in a situation where app issues update statistics command quite often. So frequently that sometimes one blocks another. Is there any way i can do something like this
IF ( update_statistics going on)
dont do anything
else
run update statistics
This is temporary solution untill i fix bad inline SQL code (in app) and use SPs.
Thanks alot in advance.
Use Ola's solution:
You can customize the job, frequency, and what type of tables (based on pages) you can refresh.
But don't abuse of it. Not refreshing statistics can lead you to slow queries, due stalled query plans. But updating too frequently will create I/O issues and bottlenecks.
In my opinion, you should identify those tables or jobs that change data frequently so you can later use a solution like Ola's and refresh those according to that. It may be that just a fraction of your tables have that problem and you are refreshing everything.
May 20, 2014 at 1:39 pm
You'd have to query the DMV's to find requests that are doing update statistics.
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
May 21, 2014 at 6:41 am
Thank you for your reply. I am trying to find out which DMV can help
May 21, 2014 at 6:43 am
Ola solution is good, but that's something i cant implement right now. Due some complexities.
May 21, 2014 at 8:01 am
ekant_alone (5/21/2014)
Ola solution is good, but that's something i cant implement right now. Due some complexities.
I understand your situation but I think your workaround will create more problems than a real solution.
You need to explain your management team that they can't continue updating statistics.
Implementing something on your code to stop queries when they refresh the stats will delay your T-SQL queries. Moreover, if you don't know when they are actually refreshing those, you will introduce more delays and performance issues on your application code.
Find out where is the job that update statistics and get the schedule for it. Change it to something that makes sense and goes with your existing workload.
May 22, 2014 at 12:12 am
as the solution you asked....only...
you can put below code in your if else condition to avoid that situation
SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
WHERE req.command like '%update statistics%'
May 22, 2014 at 1:44 pm
Instead of changing the code like that, just remove the UPDATE STATS statement entirely. You shouldn't be doing that all throughout code anyway.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 22, 2014 at 5:18 pm
ScottPletcher (5/22/2014)
Instead of changing the code like that, just remove the UPDATE STATS statement entirely. You shouldn't be doing that all throughout code anyway.
Agree!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply