Share via

seeing these Waits in sql server

Sam 1,476 Reputation points
2023-03-03T13:30:19.07+00:00

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?

16 cpus

2 tempdb

waits

waits2

Thanks,

Sam

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,366 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Bjoern Peters 8,856 Reputation points
    2023-03-03T16:01:28.9033333+00:00

    Hi Samantha

    do you already had read this articel on how to troubleshooting performance issues with tempdb?

    https://learn.microsoft.com/en-US/troubleshoot/sql/database-engine/performance/recommendations-reduce-allocation-contention

    1.) they recommend to use the latest version of SQL Server 2017 ;-)

    2.) You already followed the recommendations regarding 8 datafiles for the tempdb, but maybe you should increase them.

    3.) Or consider upgrading to SQL 2019, as that version has an big improvement for tempdb page contention.

    I hope my answer is helpful to you,

    Your

    Bjoern Peters

    If the reply was helpful, please upvote and/or accept it as an answer, as this helps others in the community with similar questions. Thanks!

    0 comments
  2. LiHongMSFT-4306 25,651 Reputation points
    2023-03-06T09:06:45.6733333+00:00

    Hi @Samantha r

    PAGELATCH_UP on tempdb and CXPACKET waittypes.

    PAGELATCH_UP or PAGELATCH_EX don’t have an "IO" in them. And it’s not necessarily 100% proof that the issue is in tempdb. You need to dig deeper:

    • Identify when the PAGELATCH_% waits are growing the most, and dig into those periods with sp_whoisactive. You can log sp_whoisactive results to a table
    • Look at your top queries. The queries that are part of the issue might be suitable for caching in an application tier.

    CXPACKET indicates waits for parallel processes to complete.

    Some points:

    1. Correctly adding data files to tempdb.
    2. Enable Instant File Initialization.
    3. Enable TF 1118 - Use Full Extents
    4. Enable TF 1117 - Grow all files in a filegroup equally
    5. All tempdb data files should be equally sized.
    6. Monitor tempdb contention and follow above advise to add tempdb data files until tempdb contention is addressed.

    References:

    How to Tell if You Need More Tempdb File.

    How can I tell if my tempdb database has a performance issue/need to add more data files?

    Best regards,

    Cosmog Hong

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments