Recently, we observed something unusual things in our system, like the database was up, but nothing was responding. New connections were hanging, queries were not progressing, and even basic monitoring tools were struggling to connect. Team puzzled by seeing no significant CPU and memory pressure. The obvious solution was "Just reboot it!". But this time, we hold our nerve and decided to investigate it further. As the delving down started, different issues popped up — blocking, slow queries, even network issues. But the real problem was something less visible and often overlooked, the THREADPOOL starvation.
[More]
Sometimes SQL Server feels slow even when CPU and memory look perfectly fine. In many such cases, the hidden culprit is TempDB contention. Since TempDB is heavily used for internal operations, any bottleneck there can impact overall performance. The real challenge is identifying this contention quickly. In this part of the article series, we will use sp_WhoIsActive to detect TempDB related waits. Let's start.
[More]
In SQL Server, performance issues are usually the result of waiting. It is not due to CPU or memory shortages. Every query, at some point, waits for resources like CPU, disk I/O, locks, or memory. The real challenge for a DBA is identifying which wait actually matters. While SQL Server provides DMVs like sys.dm_os_wait_stats, they show aggregated data. What we often need instead is a real-time view of waits at the session level. In this part, we will explore how to use sp_WhoIsActive to identify top waits those really matters. Let's start.
[More]