(The Curious Case of… used to be part of our bi-weekly newsletter but we decided to make it a regular blog post instead so it can sometimes be more frequent. It covers something interesting one of us encountered when working with a client, doing some testing, or were asked in a random question from the community.)
On Twitter today under the #sqlhelp hash tag, a former student asked what is the wait resource (0:0:0)?
This can show up in a couple of places:
- In the output from sys.dm_exec_requests in the wait_resource column when the last_wait_type is PAGELATCH_EX or PAGELATCH_SH
- In the output from sys.sysprocesses in the waitresource column when the lastwaittype is PAGELATCH_EX or PAGELATCH_SH
(as sys.sysprocesses is basically the same code as the DMV under the covers)
The simple answer is that in some cases of PAGELATCH_SH or PAGELATCH_EX waits in tempdb, the wait resource simply isn’t preserved by the code and so the wait resource for the last wait type is set to (0:0:0) in the output of those queries. It doesn’t mean anything special, and I’ve only ever seen it in tempdb. It’s been that way since SQL Server 2005 and is still there in all the latest versions today.
Simple answer: ignore it and troubleshoot the other waits.