Databases Reference
In-Depth Information
SQL Server Wait Types
Now that you have a good idea of what's going on at the server level using System Monitor, you
can start to look at what's going on within SQL Server. Think about what happens when you issue
a query to update a row, for example:
1.
The optimizer uses statistics to help decide on the best way to execute the query.
2.
The query is executed.
3.
The row will be read from disk into memory.
4.
The update will be written to the transaction log.
5.
The row will be changed in memory.
6.
Confirmation will be sent back to the client.
Imagine if you could measure the amount of time that was spent waiting for things within each
of these stages. It probably won't surprise you to read that this chapter doesn't end here and you
can. Not only are monitoring waits possible, but they can actually form a very effective part of a
performance troubleshooting strategy.
Wait time is effectively dead time, and if you can reduce the amount of time you spend waiting,
you'll be able to achieve better overall performance. This chapter covers how SQL Server waits
work, what types there are, and how to view and track them. It also covers common scenarios and
their associated wait types. Because SQL Server waits represent just a single view of performance,
you will also see when to correlate the results with the system monitor counters discussed in the
previous chapter.
The chapter concludes with a closer look at a very common waiting scenario: locking and blocking.
Search WWH ::
Custom Search