Kevin Kline recently said:
“SQL Server wait stats are, at their highest conceptual level, grouped into two broad categories: signal waits and resource waits. A signal wait is accumulated by processes running on SQL Server which are waiting for a CPU to become available (so called because the process has “signaled” that it is ready for processing). A resource wait is accumulated by processes running on SQL Server which are waiting for a specific resource to become available, such as waiting for the release of a lock on a specific record.”

“Waits analysis can reveal the symptoms of a SQL Server performance problem, but they rarely point directly to the root-cause of the performance problem.”

“When performance troubleshooting SQL Server, you want as much actionable information as quickly as possible. “Actionable” is the key word.”

“Under the default READ_COMMITTED isolation level, locks covering changes are held until the transaction commits, and will block reads and other updates to the same row(s). If anything prevents a transaction from committing, then blocking could show up.”

“ASYNC_NETWORK_IO WAITS
The name of this one causes lots of confusion. What word do you focus in on? NETWORK. The cause of this wait type usually has nothing to do with the network. It should really be called WAITING_FOR_APP_ACK (nowledgment), or something similar, as that is exactly what is happening: SQL Server has sent some data to a client and is waiting for the client to acknowledge that it has consumed the data. One of my favorite demos to do when teaching about wait statistics is to run a query that returns a large result set in Management Studio and watch the server rack up ASYNC_NETWORK_IO waits. There is clearly no network involved – it is just SSMS taking a long time to reply to SQL Server. It is doing what is known as RBAR (Row-By-Agonizing-Row), where only one row at a time is pulled from the results and processed, instead of caching all the results and then immediately replying to SQL Server and proceeding to process the cached rows. This is the major cause of ASYNC_NETWORK_IO waits – poor application design. Occasionally, it is the network, but that is rare in my experience.”

“OLEDB
The common knee-jerk reaction here is to equate this wait type with linked servers. However, this wait time became more common to see when SQL Server 2005 shipped, because 2005 contained a raft of new DMVs, and DMVs mostly use OLE DB under the covers. Before looking for linked server problems, I would check whether a monitoring tool is running DMVs constantly on the server. If you do have linked servers, continue troubleshooting by going to the linked server and looking at the wait statistics there to see what the most prevalent issue is, and then continue the same analysis. One other thing that can cause OLEDB waits is DBCC CHECKDB (and related commands). It uses an OLE DB rowset to communicate information between its Query Processor and Storage Engine subsystems.”

Adam Machanic said:
@Aaron: Totally agree that avoiding BETWEEN for most date/time comparisons is a best practice. I’m lucky in a sense as most of my work these days centers around DW type projects. Very rarely is a time component carried in; the data is almost always aggregated at least to the day if not the month. So in general I deal with only DATE and I don’t have to worry about a lot of these edge cases.

It’s really interesting to see the various mixed perspectives on many of these features. “One man’s trash…”

Andy Leonard said:
My friend Kevin Hazzard refers to JSON as “hipster XML.”

Maximum Degree of Parallelism

By default, SQL Server will use all available CPUs during query execution. While this is great for large queries, it can cause performance problems and limit concurrency. A better approach is to limit parallelism to the number of physical cores in a single CPU socket.

If you’re saying “What?” right now, don’t feel bad. Basically – on a SQL Server with two CPU sockets and a 4 core CPU that does hyperthreading, the maximum degree of parallelism option should be set to 4.