Performance degradation - Help troubleshooting

Question

Performance degradation - Help troubleshooting

September 6, 2022 at 6:44 am

This was removed by the editor as SPAM

Viewing 15 posts - 16 through 30 (of 32 total)

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

ramana3327 SSCoach Points: 19722 More actions · Answer 1

Update stats looks temporarily resolved the issue. I am again seeing overall performance maybe degraded because the system shows signs of being CPU bound message from the performance dashboard.

There is one query under current waiting requests showing CPU - Parallelism on the graph wait time (ms) greater than 10000000.

But I am not seeing that query from top queries by total CPU time or top queries by Avg CPU time.

This reply was modified 2 years, 2 months ago by ramana3327.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

So dig into that one query and find out what's going on with it and what's causing it to wait.

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

ramana3327 SSCoach Points: 19722 More actions · Answer 3

I am seeing some blocking on server. Select *from sys.dm_os_waiting_tasks where blocking _session_id is not null giving about 70 records. Most of the wait_type is CXPACKET & Few wait_type is resource _semaphore.

The session _id & blocking_session_id is same. Wait_type=cxpacket. The resource _description is exchange event I’d wait type=e_waitpipeget row mode Id=0

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

Ok, so now find the code that's doing the blocking. One way to do that is to look sp_WhoIsActive by Adam Machanic or Brent Ozar's sp_BlitzCache, read the instructions, download it, install it, and use it. Once you're determined the code that's doing the blocking with either of those tools, fix it.

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

ramana3327 SSCoach Points: 19722 More actions · Answer 5

Thank you. I am not seeing any blocking from sp_who2 or select * from sys.dm_exec_requests where blocking_session_id <>0. But select * from sys.dm_os_waiting_tasks where blocking_session_id is not null giving me few records with session _id & blocking _session_id same.

This reply was modified 2 years, 2 months ago by ramana3327.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 6

If you are seeing blocking where the session_id and blocking_session_id are the same - you are seeing parallelism. You will often see one of the parallel threads blocking other threads and that is normal.

With that said - what are your 'Cost Threshold for Parallelism' and 'Max Degree of Parallelism' settings at the server level? If these are set to the default then they are almost certainly incorrect. And if you do have a values defined, the MAXDOP setting may be too high for the specified workload.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

ramana3327 SSCoach Points: 19722 More actions · Answer 7

The Parallelism settings for the server is below

Processors: 8

Cost threshold for Parallelism: 25 Locks: 0 Max Degree of Parallelism:0 Query Wait:-1

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

ramana3327 wrote:

The Parallelism settings for the server is below
Processors: 8
Cost threshold for Parallelism: 25 Locks: 0 Max Degree of Parallelism:0 Query Wait:-1

For the processors, is that "Physical Core" or "Logical Processors"? If you don't know, the "8" is what SQL Server is showing, correct?

Mr. Williams may disagree with me but I'd boost CTOP to 50 and change MaxDop to 2 but 4 might be ok. "It Depends".

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

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 9

Jeff Moden wrote:

ramana3327 wrote:
The Parallelism settings for the server is below
Processors: 8
Cost threshold for Parallelism: 25 Locks: 0 Max Degree of Parallelism:0 Query Wait:-1
For the processors, is that "Physical Core" or "Logical Processors"? If you don't know, the "8" is what SQL Server is showing, correct?
Mr. Williams may disagree with me but I'd boost CTOP to 50 and change MaxDop to 2 but 4 might be ok. "It Depends".

I would agree with those settings.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 10

p.s. And don't stop trying to find out what the code is that's causing the blocking because, when these nifty hardware tricks don't work, you have to remember... "Performance is in the code... or not".

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

ramana3327 SSCoach Points: 19722 More actions · Answer 11

Yes, # 8 is what I am seeing from SQL.

Does changing Maxdop & cost of threshold will effect other queries performance? These setting changes require SQL server restart?

Ken McKelvey SSCoach Points: 18842 More actions · Answer 12

As a matter of interest, what values are returned by the following queries:

--1
SELECT CAST([value] AS int)/1024
FROM sys.configurations
WHERE [name] = 'max server memory (MB)'
ORDER BY [name]
OPTION (RECOMPILE);

--2
SELECT CAST(ROUND(total_physical_memory_kb/1048576.0, 0) AS int)
FROM sys.dm_os_sys_memory

--3
SELECT MAX(backup_finish_date)
FROM msdb.dbo.backupset;

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 13

ramana3327 wrote:

Yes, # 8 is what I am seeing from SQL.
Does changing Maxdop & cost of threshold will effect other queries performance? These setting changes require SQL server restart?

The change does not require a restart - and this changes how the optimizer determines when a parallel plan should be generated. It will affect all queries - but in general, if a query is not using a parallel plan now it won't use one under the new settings so there won't be any impact to those queries.

This will affect any queries that are currently using a parallel plan. Some of those will no longer use a parallel plan - any that still use a parallel plan will only utilize the number of CPUs configured for MAXDOP. The current settings allow up to 8 where the new settings would allow 2 or 4 (depending on which one you use).

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 14

Jeffrey Williams wrote:

ramana3327 wrote:
Yes, # 8 is what I am seeing from SQL.
Does changing Maxdop & cost of threshold will effect other queries performance? These setting changes require SQL server restart?
The change does not require a restart - and this changes how the optimizer determines when a parallel plan should be generated. It will affect all queries - but in general, if a query is not using a parallel plan now it won't use one under the new settings so there won't be any impact to those queries.
This will affect any queries that are currently using a parallel plan. Some of those will no longer use a parallel plan - any that still use a parallel plan will only utilize the number of CPUs configured for MAXDOP. The current settings allow up to 8 where the new settings would allow 2 or 4 (depending on which one you use).

To add to that, this will cause many smaller queries that are currently using parallelism to stop using parallelism. That's not a bad thing. In fact, it's a good thing. Running such queries in a single threaded manner does two things... it will frequently make them run faster and with less CPU usage and that also means more queries should be able to run in the same amount of time. It will also prevent larger queries that use all 8 CPUs from using so many CPUs. Large queries that use all the CPUs are going to block a whole lot of things. It does sometimes mean that the larger queries will take a bit longer but it shouldn't be a killer.

Parallelism <> guaranteed performance improvements.

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