slow processing

Question

Post reply

slow processing

bobrooney.81

SSC-Addicted

Points: 468
More actions
March 31, 2023 at 11:26 am
Go to Answer
#4167477

Hi Experts,
Yesterday, we ran a program and it has processed 1500 records in 2 min.
Today, it is running for past 90 minutes and processed only 350 records so far.
What should be checked apart from blocking?

Cheers,
Bob

Jeff Moden

SSC Guru

Points: 1003853
More actions
March 31, 2023 at 4:03 pm
Answer
#4167648

If you look at the number of reads, there's over 7 billion logical reads. That's more than 56 TERAbytes of I/O.
My first suggestion is to stop the query because it's going no where fast and it's beating the crap out of your server.
You might want to do an sp_Recompile on the offending proc because this looks like a pretty nasty case of parameter sniffing.
Also, you say it returned 1500 rows in 2 minutes. First, that sucks. A likely part of the problem is that the TOP 1 assignment of a value to a variable in the outer query suggests that RBAR on steroids is present in an out call of this code. That should be fixed.
Please study the article at the second link in my signature line below for more details on how to help us help you when it comes to such performance issues.
--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)

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

bobrooney.81 SSC-Addicted Points: 468 More actions · Answer 1

query is running over 2 hrs. What's runnable status mean? the cpu utilization in task mgr is around 30-40% only...

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

There are so many sleeping spids as well. 40 databases hosted on this server.

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

Y.B. SSChampion Points: 11642 More actions · Answer 2

There are a few things to unpack here. From your screenshot, I can't be certain which transaction it's referring to. However, you can clearly see that there is blocking occurring which is almost certainly contributing to your issue. You need to realize that adding NOLOCK hints will still create a schema lock on said tables. If you have another transaction doing a DDL statement on those tables you may very well end up with blocking.

You shouldn't really use NOLOCK hints everywhere. Setting your transaction level to READ UNCOMMITTED will do the same thing. That being said that's not a reason to use it without good reason. This might cause you more headaches than you think.

Lastly, depending on your table and usage a filtered index on CM.COMM_TYP_CD = 'Phone' could be useful. However, don't just add indexes without fully understanding the consequences.

SELECT quote FROM brain WHERE original = 1
0 rows returned

Jo Pattyn SSC-Dedicated Points: 32066 More actions · Answer 3

Can you post the matching DDL? Can you view the query plan?

Are there good indexes on c_b_party_comm

You can start by watching https://www.brentozar.com/training/think-like-sql-server-engine/ series

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 4

An index on the columns involved in ordering the data may also be useful. Testing is your buddy.

"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

ratbak Ten Centuries Points: 1328 More actions · Answer 5

How many rows does the CTE body (the subquery portion) return? I'm not sure if the query engine is smart enough to short-cut the query based on the top 1 selecting from it, or has to pull the entire result set first.

But it doesn't appear that the CTE is even necessary -- wouldn't this return the same result?

select TOP 1 
    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
ORDER BY 
    SNOW_STATUS, 
    SCORE DESC, 
    LAST_UPDATE_DATE DESC

Dennis Jensen Mr or Mrs. 500 Points: 583 More actions · Answer 6

Also another means of getting around all those WITH (NOLOCKS) is to set at the top of the stored procedure either:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

-- OR --

SET TRANSACTION ISOLATION READ UNCOMMITTED

However before using either of these or even using the WITH (NOLOCKS) be sure you fully understand all their PROS and CONS as an informed decision is a better decision.

Note: To use SNAPSHOT you mave to enable it first, I do not think it is available by default.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 7

Dennis Jensen wrote:

Also another means of getting around all those WITH (NOLOCKS) is to set at the top of the stored procedure either:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
-- OR --
SET TRANSACTION ISOLATION READ UNCOMMITTED
However before using either of these or even using the WITH (NOLOCKS) be sure you fully understand all their PROS and CONS as an informed decision is a better decision.
Note: To use SNAPSHOT you mave to enable it first, I do not think it is available by default.

Considering the number of reads the query has made, I seriously doubt that this has anything to do with blocking.

--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)

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 8

@Bob... you made the comment in your first posts of...

bobrooney.81 wrote:

What should be checked apart from blocking?

Did you check for blocking? If so, is there blocking associated with this issue?

--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)

Emperor100 SSCrazy Points: 2143 More actions · Answer 9

40 databases, how many physical CPU's or VCPUs on this server? Looks like querying a huge Contacts table...

If it is SP, as Jeff pointed out, it is a case of parameter sniffing.

=======================================================================

bobrooney.81 SSC-Addicted Points: 468 More actions · Answer 10

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 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.

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?

Cheers,

Bob

bobrooney.81 SSC-Addicted Points: 468 More actions · Answer 11

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 optimize 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.

Thanks!

Bob

This reply was modified 1 year ago by bobrooney.81.

frederico_fonseca SSCoach Points: 16008 More actions · Answer 12

if the proc is running then using sp_whoisactive MAY give you the current explain plan - to got it the command would be

exec sp_whoisactive @get_plans = 1 -- or = 2 if 1 does not return a plan

upload it to https://www.brentozar.com/pastetheplan/ and then give us the link to look at it.

if you don't have it installed on your server you should - get it from https://github.com/amachanic/sp_whoisactive/releases

with SQL 2017 you should also have Query Store enabled on the db so you could look at the "misbehaving queries". do you?

Jonathan AC Roberts SSCoach Points: 18257 More actions · Answer 13

I would guess one or maybe both of these indexes would fix your problem:

 CREATE INDEX IX_c_b_party_comm_1 
     ON dbo.c_b_party_comm(ROWID_OBJECT, COMM_TYP_CD) 
INCLUDE (COMM_TYP_CD, COMM_USG, PARTY_ID)
;

 CREATE INDEX IX_C_B_PARTY_COMM_XREF_1 
     ON dbo.C_B_PARTY_COMM_XREF(ROWID_OBJECT, LAST_UPDATE_DATE) 
INCLUDE (REF_PHONE_VERBOSE_STS, ROWID_SYSTEM)
;

It's worth just getting an estimated execution plan and seeing if SQL Server suggests any missing indexes.