Varying CPU usage for identical statements
-
Hi
At a customer we are experiencing varying CPU duration for a single statement (with changing values in where part though - so not a fully identical statement, i.e new execution plan for each, and yes should have been parameterized).
It is part of a long running task that usually runs a few hours, but the last few months it has started to take 12+ hours at this particular customer.
I've traced it to being the statement below that in periods takes ~450.000 microseconds in CPU to complete. And in other periods executes as expected.
I am curious on what can cause this change in CPU use. As the query itself is pretty simple.
We have changed the code now, to attack the task a bit different (one larger resultset from db and use parameterized sql - instead of soooo many calls) so asking primarily to learn what can be the culprit for this behaviour at this one client.
In a slow period the trace data looks like this.
And then 1½ hour later, it's 0 CPU.
When it is running fast, then once in a while a query taking 16.000 microseconds appear.
I assume it is something related to load on the server.
Has anyone seen behaviour like this before?
Best regards
/Anders
-
Yes... Most have seen it happen many times before. There are a thousand different causes of a few reasons that can usually be explained by an actual execution plan. Any way of capturing at least for the bad and one for the good times and attaching them here?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I will try to capture it during the next days.
Today and yesterday the task was quickly done and only ran outside office hours. So when the customer was there to let me in - it was done.
I have suggested they test SQL Server 2016 CU17 in their QA environment as they ran SP2 GDR in production. And then upgrade production to CU17 if nothing shows up.
CU5 included this that seems to potentially could be related https://support.microsoft.com/en-us/topic/kb4480635-fix-high-cpu-usage-when-there-are-many-batch-requests-in-sql-server-7c6b5c6d-5c29-962c-0bd6-2c5ef18d6168.
Were closing in on summer holidays here in Denmark - so the thread might be quiet until august... 🙂
/Anders
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply