Press "Enter" to skip to content

Category: Wait Stats

Waits And Latches

Published 2016-05-10 by Kevin Feasel

Paul Randal has come out with his comprehensive wait and latch type library:

I present to the community a comprehensive library of all wait types and latch classes that have existed since SQL Server 2005 (yes, it includes 2016 waits and latches).

The idea is that over time, this website will have the following information about all wait types and latch classes:

  • What they mean

  • When they were added

  • How they map into Extended Events (complete for all entries already)

  • Troubleshooting information

  • Example call stacks of where they occur inside SQL Server

  • Email link for feedback and questions

It’s not complete yet, but entries are thorough.

Comments closed

Azure SQL Database Wait Stats

Published 2016-03-01 by Kevin Feasel

Grant Fritchey discusses Azure SQL Database wait stats:

You’ll notice that these results are wildly different from those above. What we’re looking is largely a server versus a database, but not completely. I mean that sys.dm_os_wait_stats is showing the waits for the instance on which my primary Azure SQL Database is currently running. Most of those waits are mine, but because it’s part of the management structure of Azure, sys.dm_os_wait_stats shows some information that’s not applicable, directly, to me. The “server” is not really that. It’s a logical container holding your database. There’s a lot more to it under the covers. To get the waits that are absolutely applicable to me and my databases, I have to go to sys.dm_db_wait_stats.

Azure SQL Database is going to behave a bit differently from on-premise SQL Server, so if you’ve got an Azure SQL Database, pay attention to those differences.

Comments closed

ASYNC_NETWORK_IO

Published 2016-02-29 by Kevin Feasel

Dave Ballantyne discusses the ASYNC_NETWORK_IO wait stat:

Simply put ASYNC_NETWORK_IO waits occur when SQL Server is waiting on the client to consume the output that it has ‘thrown’ down the wire.  SQL Server cannot run any faster, it has done the work required and is now waiting on the client to say that it has done with data.

Naturally there can be many reasons for why the client is not consuming the results fast enough , slow client application , network bandwidth saturation, to wide or to long result sets are the most common and in this blog I would like to show you how I go about diagnosing and demonstrating these issues.

Dave goes on to explain this using Management Studio examples, but the information also applies to other client applications.

Comments closed

Session Wait Stats

Published 2015-11-07 by Kevin Feasel

SQL Server 2016 has a per-session wait stats DMV:  sys.dm_exec_session_wait_stats.  That’s exciting; wait stats are extremely interesting, but until now, impossible to use on a per-item level in a busy production system (where you’d most want to use them).

Daniel Farina looks at how the new DMV relates to sys.dm_exec_wait_stats (via Database Weekly):

[R]esetting the data of sys.dm_os_wait_stats operating system view doesn’t affect the values of sys.dm_exec_session_wait_stats view.

Based on my MSDN reading, the sys.dm_exec_session_wait_stats DMV resets if the connection pool context is re-used or if the session closes.  This is why DBCC SQLPERF doesn’t include a reset option for session-specific wait stats.

Comments closed