slow query processing

Question

slow query processing

Bob sql 476

Hi Experts,

Yesterday, we ran a program and it has processed 1500 records in 2 min.

Today, it is running for past 90 minutus and processed only 350 records so far.

What should be checked apart from blocking?

Cheers,

Bob

Bob sql 476

its running for 2 hrs.

What's runnable status? the cpu utilization in task mgr is around 30-40% only...

Reads are more. What can be done to fasten this query.

query

with phone 
 as
 (
	 select CASE WHEN SI.SNOW_VLDTN_MESSAGE = 'Valid' THEN 1  
		ELSE 2 END SNOW_STATUS, CM.ROWID_OBJECT, MT.SCORE, CX.LAST_UPDATE_DATE  
	 from c_b_party_comm (nolock) cm  
	 INNER JOIN C_B_PARTY_COMM_XREF CX (NOLOCK) ON CM.ROWID_OBJECT = CX.ROWID_OBJECT  
	 left outer join C_B_LU_PHONE_VLDTN_STS SI (NOLOCK) ON CX.REF_PHONE_VERBOSE_STS = SI.REF_PHONE_VLDTN_STATUS  
	 LEFT OUTER JOIN MDM_TRUST_SCORE MT (NOLOCK) ON CX.ROWID_SYSTEM = MT.SOURCE_SYSTEM AND MT.TYPE = @ph_type  
	 where CM.COMM_TYP_CD = 'Phone'  
	 AND CM.COMM_USG = @comm_usg  
	 AND CM.PARTY_ID = @party_id  
  )
 --IF (SELECT COUNT(*) FROM @phone) > 0  
 --BEGIN  
	 -- Get top rowid  
	  SELECT TOP 1 @SUGGEST_ROWID = ROWID_OBJECT FROM phone  
	  ORDER BY SNOW_STATUS, SCORE DESC, LAST_UPDATE_DATE DESC
--?>

Cheers,

Bob

Bob sql 476 Reputation points

2023-03-31T11:59:35.55+00:00

There are total 40 databases and other spids are mostly in 'sleeping' state.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-03-31T21:17:39.8366667+00:00

It could help to see the actual execution plan for the query above. You can upload it on https://www.pastetheplan.com.
Anonymous

2023-04-03T06:05:56.5466667+00:00

Hi @Bob sql

You can open the actual execution plan in SSMS.

Click on 'Include Actual Execution Plan'.

Best regards,

Percy Tang
Bob sql 476 Reputation points

2023-04-03T20:01:15.8666667+00:00

This query is not getting blocked but sometimes going to runnable state and suspended state. I tried to capture the actual plan but gave up after it ran for almost hours .. it is doing 9,896,185,716 reads and still going on and on. I even tried to get estimated plan but it was throwing some #tmp_tbl error. This query is running in some kind of loop within a func. Asked the dev team to look into it. The SQL Server Box has 256 GB RAM and max server memory set to 204GB. With these high number of reads [ 9,896,185,716] ,what kind of performance impact would be there on other spid's which are running at the same time? will it have less data to cache in memory and everything gets flushed out? On the other hand, what is this runnable and suspended tell? Also, is each read in sp_whoisactive = one 8K page? -Bob
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-04-03T21:36:39.09+00:00

I even tried to get estimated plan but it was throwing some #tmp_tbl error.

There is no #tmp_tbl in the query you posted.

it is doing 9,896,185,716 reads and still going on and on

It's probably time to take a closer look at the query and ask yourself: is the query correctly written? That insane number of reads suggest that there are insufficient join conditions which results in cartesian joins and row explosions. I don't know if your problem actually is the query above (since it does not have that temp table), But an interesting question is how many rows can the two left joins in the CTE produce for each row in C_B_PARTY_COMM_XREF? If the answer is > 1 for both of them, someone needs to give it more thought what this query is supposed to achieve.

Bob sql 476


This is a big stored procedure which has multiple operations SELECTs, INSERTs within it and bunch of tempdb operations? The temp table error was something related to another operation within that stored proc which when throw ed an error when I clicked on Estimated plan.

If I can one question, here in the query, does table join happen first or filtering is done first?
How can the join operation become a problem in this case. Is it 1-1 or 1-many relationshio can cause the issue? Could you pls provide more insight with numbers how the cartesian product is being an issue? I am not able to visualize how  this query is has bad performance impact w.r.t I/Os it is doing ? Can you please unfold in a little detailed manner?

Row count information:

        select count(ROWID_OBJECT) from C_B_PARTY_COMM; --11924789
        select count(ROWID_OBJECT) from  C_B_PARTY_COMM_XREF; --17767184
        select count(REF_PHONE_VLDTN_STATUS) from C_B_LU_PHONE_VLDTN_STS; --6
        select count(SOURCE_SYSTEM) from MDM_TRUST_SCORE; --26

Any tips to optmize this query where you can a direct performance impact?
or changing the join order will help ? or filtering anything first would help? 
what kind of indexes might help in the case. 
Looking for some advise.

again pasting code which is taking time.

with phone 
 as
 (
	 select CASE WHEN SI.SNOW_VLDTN_MESSAGE = 'Valid' THEN 1  
		ELSE 2 END SNOW_STATUS, CM.ROWID_OBJECT, MT.SCORE, CX.LAST_UPDATE_DATE  
	 from c_b_party_comm (nolock) cm  
	 INNER JOIN C_B_PARTY_COMM_XREF CX (NOLOCK) ON CM.ROWID_OBJECT = CX.ROWID_OBJECT  
	 left outer join C_B_LU_PHONE_VLDTN_STS SI (NOLOCK) ON CX.REF_PHONE_VERBOSE_STS = SI.REF_PHONE_VLDTN_STATUS  
	 LEFT OUTER JOIN MDM_TRUST_SCORE MT (NOLOCK) ON CX.ROWID_SYSTEM = MT.SOURCE_SYSTEM AND MT.TYPE = @ph_type  
	 where CM.COMM_TYP_CD = 'Phone'  
	 AND CM.COMM_USG = @comm_usg  
	 AND CM.PARTY_ID = @party_id  
  )
 --IF (SELECT COUNT(*) FROM @phone) > 0  
 --BEGIN  
	 -- Get top rowid  
	  SELECT TOP 1 @SUGGEST_ROWID = ROWID_OBJECT FROM phone  
	  ORDER BY SNOW_STATUS, SCORE DESC, LAST_UPDATE_DATE DESC
--?>

Thanks!
Bob

Oded 0 Reputation points

2023-04-04T12:15:39.4933333+00:00

@Bob sql , You can try and use TuneMyQuery.com .
SQL Server support is still new and in BETA phase, but it is getting better everyday.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-04-04T21:21:13.4166667+00:00

Logically, JOIN is processed before WHERE. The actual implementation is up to the optimizer. It may recast the computation order. It seems from your row counts that the two left-joined tables are small lookup tables. But can you extract the CREATE TABLE statements for all four tables, including index definitions, and attach those in a file?
Bob sql 476 Reputation points

2023-04-05T11:03:37.54+00:00

ddl.txt PFA ddl script.

Accepted answer

0 additional answers

Your answer

Bob sql 476 Reputation points

2023-03-31T11:55:35.3266667+00:00

its running for 2 hrs.

What's runnable status? the cpu utilization in task mgr is around 30-40% only...

Reads are more. What can be done to fasten this query.

query

with phone as ( select CASE WHEN SI.SNOW_VLDTN_MESSAGE = 'Valid' THEN 1 ELSE 2 END SNOW_STATUS, CM.ROWID_OBJECT, MT.SCORE, CX.LAST_UPDATE_DATE from c_b_party_comm (nolock) cm INNER JOIN C_B_PARTY_COMM_XREF CX (NOLOCK) ON CM.ROWID_OBJECT = CX.ROWID_OBJECT left outer join C_B_LU_PHONE_VLDTN_STS SI (NOLOCK) ON CX.REF_PHONE_VERBOSE_STS = SI.REF_PHONE_VLDTN_STATUS LEFT OUTER JOIN MDM_TRUST_SCORE MT (NOLOCK) ON CX.ROWID_SYSTEM = MT.SOURCE_SYSTEM AND MT.TYPE = @ph_type where CM.COMM_TYP_CD = 'Phone' AND CM.COMM_USG = @comm_usg AND CM.PARTY_ID = @party_id ) --IF (SELECT COUNT(*) FROM @phone) > 0 --BEGIN -- Get top rowid SELECT TOP 1 @SUGGEST_ROWID = ROWID_OBJECT FROM phone ORDER BY SNOW_STATUS, SCORE DESC, LAST_UPDATE_DATE DESC --?>

Cheers,

Bob
Bob sql 476 Reputation points

2023-03-31T11:59:35.55+00:00

There are total 40 databases and other spids are mostly in 'sleeping' state.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-03-31T21:17:39.8366667+00:00

It could help to see the actual execution plan for the query above. You can upload it on https://www.pastetheplan.com.
Anonymous

2023-04-03T06:05:56.5466667+00:00

Hi @Bob sql

You can open the actual execution plan in SSMS.

Click on 'Include Actual Execution Plan'.

Best regards,

Percy Tang
Bob sql 476 Reputation points

2023-04-03T20:01:15.8666667+00:00

This query is not getting blocked but sometimes going to runnable state and suspended state. I tried to capture the actual plan but gave up after it ran for almost hours .. it is doing 9,896,185,716 reads and still going on and on. I even tried to get estimated plan but it was throwing some #tmp_tbl error. This query is running in some kind of loop within a func. Asked the dev team to look into it. The SQL Server Box has 256 GB RAM and max server memory set to 204GB. With these high number of reads [ 9,896,185,716] ,what kind of performance impact would be there on other spid's which are running at the same time? will it have less data to cache in memory and everything gets flushed out? On the other hand, what is this runnable and suspended tell? Also, is each read in sp_whoisactive = one 8K page? -Bob
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-04-03T21:36:39.09+00:00

I even tried to get estimated plan but it was throwing some #tmp_tbl error.

There is no #tmp_tbl in the query you posted.

it is doing 9,896,185,716 reads and still going on and on

It's probably time to take a closer look at the query and ask yourself: is the query correctly written? That insane number of reads suggest that there are insufficient join conditions which results in cartesian joins and row explosions. I don't know if your problem actually is the query above (since it does not have that temp table), But an interesting question is how many rows can the two left joins in the CTE produce for each row in C_B_PARTY_COMM_XREF? If the answer is > 1 for both of them, someone needs to give it more thought what this query is supposed to achieve.
Oded 0 Reputation points

2023-04-04T12:15:39.4933333+00:00

@Bob sql , You can try and use TuneMyQuery.com .
SQL Server support is still new and in BETA phase, but it is getting better everyday.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-04-04T21:21:13.4166667+00:00

Logically, JOIN is processed before WHERE. The actual implementation is up to the optimizer. It may recast the computation order. It seems from your row counts that the two left-joined tables are small lookup tables. But can you extract the CREATE TABLE statements for all four tables, including index definitions, and attach those in a file?
Bob sql 476 Reputation points

2023-04-05T11:03:37.54+00:00

ddl.txt PFA ddl script.

Answer 1

Erland Sommarskog 121.4K MVP Volunteer Moderator

I note that the table MDM_TRUST_SCORE does not have any primary key defined. What happens if you do:

ALTER TABLE MDM_TRUST_SCORE ALTER COLUMN SOURCE_SYSTEM nvarchar(50) NOT NULL
ALTER TABLE MDM_TRUST_SCORE ALTER COLUMN TYPE nvarchar(50) NOT NULL
ALTER TABLE MSM_TRUST_SCORE ADD CONSTRAINT pk_MDM_TRUST_SCORE PRIMARY KEY (TYPE, SOURCE_SYSTEM)

If the ALTER COLUMN operations fails because of NULL values, try this instead:

CREATE UNIQUE INDEX type_source_system_ix ON MDM_TRUST_SCORE (TYPE, SOURCE_SYSTEM) WHERE TYPE IS NOT NULL AND SOURCE_SYSTEM IS NOT NULL

If any of these fails because values are not unique, you have something to investigate, because the query seems to be written with the assumption that this combo is unique.

The table C_B_PARTY_COMM could benefit from a composite index on (PARTY_ID, COMM_USG, COMM_TYP_CD). (There are single-column indexes on all three columns, but that is not really the same thing.) Of these columns, you should put the most selective column first. That is, the column with the highest number of distinct values.

The index SVR1_1XSE on C_B_PARTY_COMM_XREF could be augmented to include the columns LAST_UPDATE_DATE and REF_PHONE_VERBOSE_STS.

Bob sql 476 Reputation points

2023-04-06T03:55:06.09+00:00

Thanks for taking time . Just trying to sync in what you have written for one of the table. Regarding MDM_TRUST_SCORE table

Making two columns "Type","Source_system" as not null and then adding a primary key to the table to have a clustered idx instead of HEAP. If that fails, creating a unique filtered index on type and Source_system columns. [MDM_TRUST_SCORE] table has very less rows(~26), making these changes to table structure how much performance gain will be there or is it like we are making sure to have a clustered or unique index on all tables instead of HEAPS? What was the thinking behind making [TYPE] and [SOURCE_SYSTEM] columns should be clustered idx and not the "ID" column which has ever increasing values and narrow one? I'll definitely try this on non-production environment. On Prod, need to see if everyone agrees to these structural changes. Thank you.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-04-06T21:04:56.9533333+00:00

The important part here is not what is clustered or not. What I'm after is to reassure that (TYPE, SOURCE_SYSTEM) is a unique key. It if is not, the number of rows may start balooning. But since there was no primary key on this table at all, I figured that you could just as well use this one. Overall, I prefer natural keys over surrogate keys with IDENTITY, although it is not always possible to avoid them.
Bob sql 476 Reputation points

2023-04-08T10:26:16.0233333+00:00

Thank you very much Erland. App team has incorpaorated some additional filters to lesson the rows and as you suggested the uniqueness of (TYPE, SOURCE_SYSTEM) columns also helped. Many thanks. Can u please suggest any book(s) / video resources which talks about identifying such join related issues, how to write the wite joins otherwise endup in ballooon effect of rows / temp db bloating , indexes etc.... .
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-04-08T12:28:55.69+00:00

There are many resources out there, but I don't have a good track of them. And in the end, it is a lot about experience. Like always try to identify natural keys for your tables. And try to think how you would solve the query by hand, given the existing indexes. That does not always lead you right, but it works suprisingly often.

Share via

slow query processing

Thanks for taking time . Just trying to sync in what you have written for one of the table. Regarding MDM_TRUST_SCORE table

0 additional answers

Your answer