waittypes in sql server
-
Hi All,
We have a UAT sql instance running on SQL 2017 EE CU23.
16cpu's - 256GBRAM
max server memory set to 230GB
max degree of parallelism set to default 0.
At database level MAXDOP is set to 4.
Tempdb configuration : 8 files and 1 logfile.
When load testing is done, we are seeing below waits. PAGELATCH_UP on tempdb and CXPACKET waittypes.
Is anything can be improved here as far as SQL server configuration is concerned?
Thanks,
Sam
-
CXPACKET is not bad on its own - it may only be an indication that some queries are going parallel when they don't need/or could work better serially.
so those cxpacket are perfectly normal and I would ignore them on this case - only way to "solve" them is to force update statistics to run in single thread - so they would go away BUT your process runs a lot slower.
for the SGAM contention - you have been around long enough to know YOU should be searching on documentation for this type of info - had you read it you would have jumped upon MS recomendation at https://learn.microsoft.com/en-US/troubleshoot/sql/database-engine/performance/recommendations-reduce-allocation-contention
-
So... lemme get this straight... you actually have a 4.1 TERAbyte TempDB??? This suggests to me that your code has some real problems and that you grew tempdb to that size to try to fix it.
Your post also shows that you have 1 CPU with a 16 ratio for hyperthreading. That doesn't sound right. How many physical core does that 1 cpu actually have?
As for your "load testing", we have absolutely no clue what that entails.
For your graphic about TempDB latches... I see that they're all related to the same basic statement of SELECT ROWID OBJECT PARENT yada-yada. Why is the test hitting TempDB and why is it hitting it so hard??? Is it the code you're testing that's doing that? If so, I'm thinking the code that your testing has a real problem and the stress test is proving that fact.
--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) -
Go through this link looks like there are few CU's released by Microsoft.
=======================================================================
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply