The Moment Every DBA Eventually Faces

After 25 years working with databases — SQL Server, Oracle, PostgreSQL and MySQL — I can tell you something very important:

At some point in your career, you will face a system where:

• CPU looks fine
• Memory looks fine
• Disk seems normal
• Queries look correct

…and yet everything is slow.

Users complain.

Applications timeout.

Monitoring dashboards show nothing obvious.

This is the moment where many engineers start guessing.

But experienced DBAs know the truth:

The database is waiting for something.

And if you want to truly understand what SQL Server is doing internally, there is one concept you absolutely must master:

Wait Statistics.

---

The Truth About SQL Server Performance

SQL Server is essentially a scheduling engine.

Every request executed inside the engine must wait for resources such as:

• CPU
• memory
• disk
• locks
• network
• internal synchronization

Whenever SQL Server cannot proceed immediately, it records a wait event.

Those wait events accumulate over time in a DMV called:

sys.dm_os_wait_stats

This DMV tells you one of the most important truths about your server:

Where SQL Server is spending its time waiting.

And this information is incredibly powerful.

Because instead of guessing what is wrong, you can see exactly:

• if the system is CPU bound
• if storage is slow
• if queries are blocked by locks
• if the network is slow
• if parallelism is misconfigured

---

One of the Most Powerful Scripts for Wait Analysis

One of the most famous scripts used by SQL Server professionals was created by Paul Randal, one of the most respected experts in the SQL Server community and founder of SQLskills.

His wait statistics script analyzes the waits recorded by SQL Server and highlights the most relevant ones, filtering out background waits that normally do not represent performance problems.

The script also generates links pointing to the SQLskills documentation explaining each wait type.

This approach allows DBAs to quickly identify the top waits responsible for performance bottlenecks.

Below is the script used for this analysis.

WITH [Waits]AS ( SELECT [wait_type], [wait_time_ms] / 1000.0 AS [WaitS], ( [wait_time_ms] - [signal_wait_time_ms] ) / 1000.0 AS [ResourceS], [signal_wait_time_ms] / 1000.0 AS [SignalS], [waiting_tasks_count] AS [WaitCount], 100.0 * [wait_time_ms] / SUM([wait_time_ms]) OVER () AS [Percentage], ROW_NUMBER() OVER ( ORDER BY [wait_time_ms] DESC ) AS [RowNum] FROM sys.dm_os_wait_stats WHERE [wait_type] NOT IN ( N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR', N'CXCONSUMER', N'LAZYWRITER_SLEEP', N'SLEEP_TASK', N'XE_TIMER_EVENT', N'SQLTRACE_BUFFER_FLUSH') AND [waiting_tasks_count] > 0)SELECT MAX([W1].[wait_type]) AS [WaitType], CAST(MAX([W1].[WaitS]) AS DECIMAL(16, 2)) AS [Wait_S], CAST(MAX([W1].[ResourceS]) AS DECIMAL(16, 2)) AS [Resource_S], CAST(MAX([W1].[SignalS]) AS DECIMAL(16, 2)) AS [Signal_S], MAX([W1].[WaitCount]) AS [WaitCount], CAST(MAX([W1].[Percentage]) AS DECIMAL(5, 2)) AS [Percentage], CAST(( MAX([W1].[WaitS]) / MAX([W1].[WaitCount])) AS DECIMAL(16, 4)) AS [AvgWait_S], CAST(( MAX([W1].[ResourceS]) / MAX([W1].[WaitCount])) AS DECIMAL(16, 4)) AS [AvgRes_S], CAST(( MAX([W1].[SignalS]) / MAX([W1].[WaitCount])) AS DECIMAL(16, 4)) AS [AvgSig_S], CAST('https://www.sqlskills.com/help/waits/' + MAX([W1].[wait_type]) AS XML) AS [Help/Info URL]FROM [Waits] AS [W1]INNER JOIN [Waits] AS [W2] ON [W2].[RowNum] <= [W1].[RowNum]GROUP BY [W1].[RowNum]HAVING SUM([W2].[Percentage]) - MAX([W1].[Percentage]) < 95;

This script highlights the waits responsible for 95% of total waiting time, which is usually enough to identify the main bottlenecks.

---

Some Wait Types Every DBA Must Understand

Over the years, certain wait types appear again and again in real environments.

Let me walk you through some of the most common ones.

---

ASYNC_NETWORK_IO

Despite the name, this is rarely a network issue.

This wait usually means:

SQL Server is waiting for the client application to consume the results.

Typical causes include:

• applications fetching rows one by one
• poorly written ORM code
• RBAR processing (Row-By-Agonizing-Row)

In many cases the real problem is not SQL Server, but the application reading data too slowly.

---

CXPACKET

This wait is related to parallelism.

It happens when multiple worker threads execute a parallel query and some threads finish earlier than others.

High CXPACKET waits can indicate:

• unbalanced parallel workloads
• poor indexing
• inefficient query plans
• incorrect MAXDOP configuration

Parallelism is powerful, but it must be configured correctly.

---

PAGEIOLATCH

This wait indicates SQL Server is waiting for pages to be read from disk.

When this wait becomes dominant, common causes include:

• slow storage
• insufficient memory
• missing indexes
• large table scans

In many systems I’ve analyzed, the root cause was simply missing indexes causing large scans.

---

PAGELATCH

This wait is often associated with TempDB contention.

Heavy workloads inserting into TempDB can cause multiple threads to compete for the same allocation pages.

Typical solutions include:

• multiple TempDB data files
• improved TempDB configuration
• reducing temporary object usage

---

WRITELOG

WRITELOG occurs when SQL Server waits for transaction log writes.

Since commits depend on log durability, slow log disks can significantly affect performance.

Solutions include:

• faster storage for log files
• separating logs from data disks
• optimizing transaction size

---

SOS_SCHEDULER_YIELD

This wait indicates that a worker voluntarily yielded the CPU because other tasks needed processing.

This can happen when:

• CPU is overloaded
• queries are CPU-intensive
• parallelism is misconfigured

But it does not always mean a server-wide CPU problem. Sometimes a single expensive query is responsible.

---

Lock Waits (LCK_*)

These waits occur when transactions are blocked by locks held by other transactions.

This is extremely common in OLTP systems.

Common causes include:

• long transactions
• missing indexes
• poorly designed queries
• blocking chains

Many systems appear “slow” when in reality they are simply waiting for locks to be released.

---

Wait Analysis Changes How You Troubleshoot

One of the biggest mindset changes in a DBA career happens when you start analyzing waits.

Instead of asking:

“Why is SQL Server slow?”

You start asking:

“What is SQL Server waiting for?”

And once you answer that question, the path to solving the problem becomes much clearer.

---

Final Thoughts

Understanding wait statistics is one of the most powerful skills a database professional can develop.

Wait analysis allows you to:

• diagnose bottlenecks quickly
• identify resource contention
• understand workload behavior
• tune systems more effectively

After decades working with databases, I can say this confidently:

If you don’t understand waits, you’re troubleshooting SQL Server blindly.

But once you do understand them, performance tuning becomes much more scientific and predictable.

🚀 Ready to boost your career in data?

👉 DBAcademy – DBA & Data Analyst Training
Over 1,300 lessons and 412 hours of exclusive content.
Includes subtitles in English, Spanish, and French.

🔗 https://filiado.wixsite.com/dbacademy

💡 Start learning today and become a highly in-demand data professional.