September 10, 2018 at 3:51 am
Hello, i have a little question.
Is it possible that a query has CPU time greater than elapsed time without parallelism?
His execution plan doesn't have parallelism (gather stream). Compilations maybe?
Thanks for all.
September 10, 2018 at 5:40 am
It really depends on how you're capturing the elapsed time. There is a way in extended events to only capture the execution time of a procedure, skipping the compile time entirely. Depending on how you're measuring CPU time for a process, if you compare these, yeah, you're going to see radically different values where CPU time is higher than elapsed time. However, that's not the normal rpc_completed event. That includes compile time, so you're unlikely to see the two be different (again, barring parallelism).
Although, I suppose, if we were talking about a 2017 instance with a columnstore index involved and batch mode processing, we might see a difference there, again, depending on how we're measuring things.
Because of things like parallelism, etc., trying to get a perfect match between CPU time and elapsed time can be problematic. The question would be, why do you need a perfect match there?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 10, 2018 at 6:13 am
Hello Grant.
I use a extended event sqlserver.rpc_completed and the DMV sys.dm_exec_procedure_stats because the application executes procs, never adhoc. A lot of procs use sp_executesql.
I don't need a perfect match for my "ego" jejejejeje. In a instalation the DBAs changed with sp_configure the MAXDOP from 2 to 8, restarted the engine and a proc with average elapsed time around 1 second, now his duration is about 8 or 9 seconds.
Before the change, the CPU time was less than elapsed time and i'm studying the question, the instalation's dbas request my help, execution time is very high.
I thought that the execution plan changed to parallelism and i see that not.
What is the problem? do you think that to rewrite the proc is the solution?
The engine is a SQL SERVER 2014.
Thanks for all.
September 10, 2018 at 6:42 am
msimone - Monday, September 10, 2018 6:13 AMHello Grant.
I use a extended event sqlserver.rpc_completed and the DMV sys.dm_exec_procedure_stats because the application executes procs, never adhoc. A lot of procs use sp_executesql.
I don't need a perfect match for my "ego" jejejejeje. In a instalation the DBAs changed with sp_configure the MAXDOP from 2 to 8, restarted the engine and a proc with average elapsed time around 1 second, now his duration is about 8 or 9 seconds.
Before the change, the CPU time was less than elapsed time and i'm studying the question, the instalation's dbas request my help, execution time is very high.
I thought that the execution plan changed to parallelism and i see that not.
What is the problem? do you think that to rewrite the proc is the solution?
The engine is a SQL SERVER 2014.
Thanks for all.
This isn't a system problem. It's a code problem. In other words, yes... I believe you'll need to make some changes to the proc. A "cheater method" would be to us WITH (MAXDOP 2) to get you out of the woods while you're fixing the proc to run without that hint. Who knows? You might be able to get it down to a couple of hundred ms or less. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
September 10, 2018 at 7:17 am
msimone - Monday, September 10, 2018 6:13 AMHello Grant.
I use a extended event sqlserver.rpc_completed and the DMV sys.dm_exec_procedure_stats because the application executes procs, never adhoc. A lot of procs use sp_executesql.
I don't need a perfect match for my "ego" jejejejeje. In a instalation the DBAs changed with sp_configure the MAXDOP from 2 to 8, restarted the engine and a proc with average elapsed time around 1 second, now his duration is about 8 or 9 seconds.
Before the change, the CPU time was less than elapsed time and i'm studying the question, the instalation's dbas request my help, execution time is very high.
I thought that the execution plan changed to parallelism and i see that not.
What is the problem? do you think that to rewrite the proc is the solution?
The engine is a SQL SERVER 2014.
Thanks for all.
Maybe it's not this proc at all. Maybe this procedure is being blocked by others. You can use extended events to look at wait stats for a query. That's probably where I'd start here. Jeff's right, you'll probably need to rewrite the procedure, but in addition, I'd try to find out why things slowed down. Changing the MAXDOP from 2 to 8 wouldn't affect a query in isolation unless you see a change in the plan. That's why I think it may have affected other queries which are affecting this one. But, just guessing based on the information you provided.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 10, 2018 at 7:23 am
Grant Fritchey - Monday, September 10, 2018 7:16 AMmsimone - Monday, September 10, 2018 6:13 AMHello Grant.
I use a extended event sqlserver.rpc_completed and the DMV sys.dm_exec_procedure_stats because the application executes procs, never adhoc. A lot of procs use sp_executesql.
I don't need a perfect match for my "ego" jejejejeje. In a instalation the DBAs changed with sp_configure the MAXDOP from 2 to 8, restarted the engine and a proc with average elapsed time around 1 second, now his duration is about 8 or 9 seconds.
Before the change, the CPU time was less than elapsed time and i'm studying the question, the instalation's dbas request my help, execution time is very high.
I thought that the execution plan changed to parallelism and i see that not.
What is the problem? do you think that to rewrite the proc is the solution?
The engine is a SQL SERVER 2014.
Thanks for all.Maybe it's not this proc at all. Maybe this procedure is being blocked by others. You can use extended events to look at wait stats for a query. That's probably where I'd start here. Jeff's right, you'll probably need to rewrite the procedure, but in addition, I'd try to find out why things slowed down. Changing the MAXDOP from 2 to 8 wouldn't affect a query in isolation unless you see a change in the plan. That's why I think it may have affected other queries which are affecting this one. But, just guessing based on the information you provided.
Hmmmm... hadn't thought about other stuff running a lot faster and so providing more contention for the subject proc to wade through and wait it's turn. But, even if that's true, someone is going to have to rework the code. And, I have seen where someone changing the system to a higher MAXDOP has affected some queries (usually bad queries) because the additional parallelism became relatively more expensive for the split/gather than the original query itself.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 10, 2018 at 7:33 am
I will follow yours recommendations, wait stats in extended event and to rewrite de code.
Grant, Jeff, thanks for all.
September 10, 2018 at 7:51 am
msimone - Monday, September 10, 2018 7:33 AMI will follow yours recommendations, wait stats in extended event and to rewrite de code.
Grant, Jeff, thanks for all.
All looking at wait stats is going to do is tell you what Murphy's half brother, Sum Ting Wong, already knows. Wait stats are a symptom... not the problem and they won't necessarily tell you what the problem is for you to figure out how to fix the code. Overall, CPU, Logical Reads, and Duration are the key indicators of where the problem is in each section of code and, if you fix the first 2, Duration usually follows. You also see a huge improvement in wait stats unless there's a ton of other bad code making your code wait.
Remember, if you have 16 core and 100 users, the wait stats are going to show things waiting their turn in a round robin fashion. The longer it takes code to run, the more waiting there will be.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply