0

I have SQL 2014 sp3 (Standard) With below CPU Details according to 8 CPU we have configured 8 Tempdb datafiles.

Based on my configuration could you please suggest what should be the CPU & Tempdb configuration for the same.

I am facing the Top 2 Wait type to the server.

  1. ASYNC_NETWORK_IO
  2. SOS_SCHEDULER_YIELD

enter image description here

enter image description here

1
  • Why do you feel these waits are related to tempdb performance problems? Commented Mar 3, 2020 at 20:52

2 Answers 2

2

ASYNC_NETWORK_IO has nothing to do with tempdb. ASYNC_NETWORK_IO is the wait that occurs when SQL Server is transmitting data to the client. So the only ways to address this are to upgrade the network to provide more bandwidth by either upgrading the network or, if the network is saturated, address the saturation in whatever way possible, or modify the queries or number of users to reduce the amount of data that has to be transmitted to clients.

For SOS_SCHEDULER_YIELD, giving the full treatment in an answer here is a bit overkill (IMHO), and I would refer you to SOS_SCHEDULER_YIELD at sqlskills.com to get started on your journey there. However, I might say that if you have one or more queries that are returning massive amounts of data, resulting in the ASYNC_NETWORK_IO waits, it's very likely that the same queries are also the cause of the SOS_SCHEDULER_YIELD waits.

These waits may be inevitable on data warehouse or big data systems where queries that return massive amounts of data are the norm.

3
  • Hello Tony, Thanks for your Input I have a question is it possible [ASYNC_NETWORK_IO] because I have replication between two different geolocation. also, I have two-node clusters and datafiles & logfiles on SAN. may this cause [ASYNC_NETWORK_IO]. [SOS_SCHEDULER_YIELD]:: My CPU allocation is wrong as its standard edition what I understood. if incase I reduce to 4 sockets and do I need to change my tempdb datafiles to 4 instead of 8 ??
    – Manish R
    Commented Mar 4, 2020 at 9:24
  • If clients, replication, and SAN are all using the same network, then the network may be saturated, causing all network traffic to be slow and resulting in ASYNC_NETWORK_IO waits. If the network utilization is 80% or higher, you need to reduce the load on that network or upgrade it. There could also be an issue with the network, such as the server NIC having mis-matched speed/duplex setting, causing slow network. NIC speed/duplex auto-detect is notorious for being faulty and setting to 100 Mbs when switch is 1 Gbs, for example. This will choke the network. Commented Mar 4, 2020 at 12:15
  • Look at the performance counters for network for current bandwidth to determine if speed/duplex is set correctly, and output queue length to see if applications are having to wait to send data. Commented Mar 4, 2020 at 12:18
1

For 8 cores, generally the TempDB should have 8 data files, however, that may not be the core of your performance issues.

Standard Edition supports the lesser of 4 sockets or 16 cores, and your results & screenshot shows 4 CPUs appear offline to SQL Server and your machine has 8 sockets. This means you're effectively only using 50% of the available CPU on the server.

If this is a Virtual Machine, you need to edit the configuration to map more than one vCPU to a socket so that you do not exceed the maximum of 4 sockets before you have 8 cores. This will make the other 4 cores VISIBLE ONLINE to SQL Server and may improve performance.

5
  • Hello HandyD, Thanks for your Input [SOS_SCHEDULER_YIELD]:: My CPU allocation is wrong as its standard edition what I understood. if incase I reduce to 4 sockets and do I need to change my tempdb datafiles to 4 instead of 8 ??
    – Manish R
    Commented Mar 4, 2020 at 9:26
  • If you reduce the number of sockets to 4 but increase the cores:sockets ratio (2 cores per socket) you will still have 8 visible cores so tempdb should remain at 8 files.
    – HandyD
    Commented Mar 4, 2020 at 22:23
  • Hello Handy D, What if I can configure 4 sockets with (4 cores per socket)
    – Manish R
    Commented Apr 1, 2020 at 15:54
  • 4 sockets with 4 cores per socket = 16 visible cores which is within Standard Edition limits, so SQL will see all available cores. You may or may not need to increase the number of tempdb files, it depends on whether you're encountering contention.
    – HandyD
    Commented Apr 2, 2020 at 0:28
  • Thanks, Handy D,
    – Manish R
    Commented Apr 2, 2020 at 6:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.