January 9, 2020 at 11:58 am
Hi,
We have got a procedure on SQL Server agent jobs and this job started to hang on transaction for many hours with wait stats CXCONSUMER and never complete. The problem is after we noticed that it was hanging we kill the job and started manually during and the job was working fine and completing. But right now neither starting manually or on sql agent job seems never completing and hanging on waits with CXCONSUMER.
According to sp_who2, sp_whoisactive and sql server profiler there are no active locks it is waiting for. It keeps yielding CXCONSUMER and it's processor time and does basically nothing.
And the other symptoms is the tempdb is unaccesible through properties when this job runs I get this error "Property SpaceAvailable is not available for Database '[tempdb]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights" despite there are available space on tempdb.
I will appreciate your help in this matter.
January 9, 2020 at 1:13 pm
You're going to need more and deeper information here. CXCONSUMER is a standard wait that simply means parallelism is occurring. It's not in any way actionable. You can read more about it here and also here. I'd suggest capturing individual statement start and stops combined with wait info through extended events. Turn on causality tracking too. This will let you track exactly what's happening. Make sure you filter the session appropriately though or you'll get a lot of data you don't care about.
"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
January 9, 2020 at 4:02 pm
Following up on Grant's fine post, can you share the actual execution plan?
😎
At the first glance, this looks like a tempdb related problem, without knowing what is actually going on, one can only speculate 😉
January 10, 2020 at 8:38 am
we would need the explain plan file itself, not images of it.
January 10, 2020 at 9:52 am
I can not upload the file here . but here is the execution plan https://www.brentozar.com/pastetheplan/?id=rygp4aVxU
January 10, 2020 at 12:51 pm
Have you tried to run the query with "OPTION (MAXDOP 1) ?
😎
Looking at the execution plan, there are quite few opportunities for improvement, i.e. add indices to the #temp tables, limit the cardinality by filtering the output of the .[sap].[MSEG] table, eliminate the Hash Match inner join etc.
January 10, 2020 at 1:30 pm
In SSMS go to the first operator in the plan. Right click it. Select "Properties" from the list. A tab will open on the side. Pin that in place by clicking on the pin icon. Scroll to the bottom. Expand the thing that says "Warnings." See all those implicit and explicit conversions? Those are going to prevent good statistics and index usage. Fix those. No guarantee it will do everything for you, but it's sure going to help.
I agree with Eirikur, indexes on the temp tables can't hurt.
"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
January 15, 2020 at 12:59 pm
Hi Grant , When I followed your instructions I see this errors under warning :
Type conversion in expression (CONVERT(date,[m].[CPUDT],0)) may affect "CardinalityEstimate" in query plan choice; Type conversion in expression (CONVERT_IMPLICIT(nvarchar(3),[ngo].[BWART],0)=[ms].[BWART]) may affect "SeekPlan" in query plan choice; Type conversion in expression (CONVERT_IMPLICIT(nvarchar(10),[ngo].[CHARG],0)=[ms].[CHARG]) may affect "SeekPlan" in query plan choice; Type conversion in expression (CONVERT_IMPLICIT(nvarchar(1),[ngo].[SOBKZ],0)=[ms].[SOBKZ]) may affect "SeekPlan" in query plan choice; Type conversion in expression (CONVERT_IMPLICIT(nvarchar(1),[ngo].[SHKZG],0)=[ms].[SHKZG]) may affect "SeekPlan" in query plan choice; Type conversion in expression (CONVERT_IMPLICIT(nvarchar(4),[ngo].[BUSTM],0)=[ms].[BUSTM]) may affect "SeekPlan" in query plan choice; Type conversion in expression (CONVERT_IMPLICIT(nvarchar(4),[ngo].[BUSTW],0)=[ms].[BUSTW]) may affect "SeekPlan" in query plan choice
January 15, 2020 at 1:26 pm
Yes. That's the one. You're mixing data types such that you have to do CONVERT in some cases and SQL Server is covering for you in others using CONVERT_IMPLICIT. These types of functions against columns completely prevent good statistics use and good index use and lead to scans where seeks may be possible. This hurts performance.
"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
January 21, 2020 at 7:53 am
Despite the warnings are disappeared job hanging more than 16 hours in this stage : https://www.brentozar.com/pastetheplan/?id=BJqia74-8 . Can you see something different this time ?
Thank you
January 21, 2020 at 1:42 pm
I think we're back to the original suggestion from Eirikur, an index on your temp tables to assist in filtering that data. The one table #repositioning, says it has 1 million rows (according to the cardinality) and that's getting filtered to 500k, about half, through the query. I can't guarantee an index will help, but it may. It's where I would start to experiment.
Additionally, I would really want to get an actual execution plan in this case. The nested loops operation where you get one execution of an index seek on [IX_MSEG_MBLNR_MJAHR_MANDT] works because of the estimated single execution. Are you really only getting one? If not, that could be a major performance bottleneck. In fact, this is an example where estimated versus actual row counts could be vital. Don't try to capture the plan using SSMS. Set up Extended Events to capture it.
Other than that, I always question the need to move hundreds of thousands of rows of data around very often. Are you certain it's needed? Smaller data sets are easier to tune. Very large scale data sets are frequently better "tuned" by just throwing hardware at the problem. If you can better filter the data involved, performance can be radically enhanced.
"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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply