Check the wait statistics in SQL Server

use the sys.dm_os_wait_stats DMV to get information about wait types and their associated statistics.

SELECT wait_type, wait_time_ms FROM sys.dm_os_wait_stats ORDER BY wait_time_ms 
DESC

OR

SELECT 
 wait_type,
 wait_time_ms,
 max_wait_time_ms,
 signal_wait_time_ms,
 wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC;

Using SQL Server Management Studio (SSMS):
o Open SSMS and connect to the SQL Server instance.
o Navigate to Reports > Standard Reports > Server Dashboard.
o The Server Dashboard report provides information about wait statistics and other
performance metrics.

Using Performance Monitor (PerfMon):
o You can use Windows Performance Monitor (PerfMon) to track wait statistics
and related performance metrics. Add counters for SQL Server: Wait Statistics to
monitor different wait types

Using SQL Server Profiler:
o SQL Server Profiler allows you to trace and monitor wait events.
o Open SQL Server Profiler and create a new trace.
o Choose the appropriate events (e.g., WaitInfo, WaitInfoExternal) to capture
details about wait types and durations.

Wait types in SQL Server:

1. CXPACKET:
   o Happens during a parallel query when threads process at different speeds.
   o Common in highly parallel environments.
2. PAGEIOLATCH_ and PAGELATCH_**:
   o PAGEIOLATCH_SH, PAGEIOLATCH_UP, and PAGEIOLATCH_EX are wait
   types that occur when waiting for a page to be read from disk into the buffer pool.
   o PAGELATCH_SH, PAGELATCH_UP, and PAGELATCH_EX occur when
   there is contention on in-memory pages.
3. LCK_M_: o LCK_M_X, LCK_M_S, and other LCK_M_ wait types indicate that a session is
   waiting for a lock to be released by another session.
   o These waits are common in scenarios with high concurrency and locking
   contention.
4. ASYNC_NETWORK_IO:
   o Occurs when SQL Server is waiting for the client application to process the
   results of a query and acknowledge that it can receive more data.
   o This wait type is common in scenarios with slow client applications or network
   latency.
5. WRITELOG:
   o Occurs when SQL Server is waiting for a transaction log flush to disk.
   o This wait type is common in scenarios with high transactional activity and disk
   latency.
6. SOS_SCHEDULER_YIELD:
   o Occurs when a worker thread voluntarily yields the CPU after reaching its
   quantum limit.
   o This wait type is common in CPU-bound scenarios.
7. RESOURCE_SEMAPHORE:
   o Occurs when a query is waiting for memory grants.
   o This wait type is common in scenarios with memory-intensive queries and
   insufficient available memory.
8. HADR_SYNC_COMMIT:
   o Occurs in Always On Availability Groups when a transaction is waiting for
   confirmation that it has been committed on the secondary replica.
   o This wait type is common in environments with synchronous replication.
9. BACKUPIO:
   o Occurs when waiting for I/O during a backup operation.
   o This wait type is common in environments with frequent or large backup
   operations.
10. NETWORK_IO:
    o Occurs when waiting for network I/O.
    o This wait type is common in scenarios with high network traffic or latency.