By Kendra Little on March 5, 2024
Last November, a puzzle was really bothering me. Some queries from an application were timing out frequently after running for 30 seconds, but they were halfway invisible in the SQL Server.
I say “halfway invisible” because I could see the queries while they were running in SQL Server’s dynamic management views using free tools (sp_WhoIsActive and sp_BlitzWho).
But the queries had some odd characteristics:
- They had a sql_handle, but never a query_hash
- They also never showed a plan_hash or execution plan
- I couldn’t find the timeouts in Query Store like a normal timeout
Query Store won’t see a query that doesn’t finish compiling
This all happened around the time of the PASS Data Community Summit in Seattle. I thought, surely if I ask enough people, someone will have an idea that sticks.
I ended up in the Expo Hall, babbling about half invisible queries to anyone who would listen. This was actually a good strategy.
Erik Darling and a group of fine Microsoft PMs listened to my tale of confusion. Erik suggested, “Maybe the query isn’t done compiling?” Erin Stellato thought it through and confirmed that yes, if the query did not finish the compilation process before the client cancelled it, then that run of the query wouldn’t be recorded in Query Store.
(After this chat, I eventually found and read the Learn article on How Query Store Collects Data, which gives more detail on Query Store and how it fits into query processing. Read that to learn more, it’s good.)
Identifying a long compiler
Before this, I hadn’t really thought about how a query appears in SQL Server DMVs if it is still compiling. I’d taken short compile times for granted. It turns out I shouldn’t.
As I mentioned above, some traits of a query in the compilation phase are that you’ll see a sql_handle, but not a query hash, query plan, or query plan hash.
There is another way you can monitor these and confirm that a query is compiling as well: you can query sys.dm_tran_active_transactions for transactions where the name column has the value sqlsource_transform.
select transaction_id, transaction_begin_time, datediff(second, transaction_begin_time, SYSDATETIME()) as seconds
from sys.dm_tran_active_transactions
where name = 'sqlsource_transform';
Thanks to the Troubleshoot queries that seem to never end in SQL Server article for this little tip, it helped me confirm that I was, in fact, seeing queries that were taking longer than 30 seconds to compile, and being cancelled due to a timeout before they ever got a query plan.
Why were they compiling so long? I’ve got some details, but some of it is still mysterious. Stay tuned for more in a future post.
And many thanks to Erik Darling, Erin Stellato, and others for their invaluable help and advice.