February 5, 2023 at 4:29 am
Hi!
How\Steps to setup Extended events to capture query causing tempdb log getting full?
Can Query Store be used to use metric for Temp DB Memory Used? Read somewhere but was not able to locate, we are using SQL 2016 with databases of backward compatibility of 2012. I tried to look for TempDB metrics on SQL 2016 with DB compatibility of 2016 but could not find information for Tempdb.
Using DMV was able to capture few queries which was creating temp tables and table variables which might be the cause of it but was looking for capturing in extended events.
Thanks.
February 6, 2023 at 5:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
February 6, 2023 at 2:08 pm
There really isn't a direct mechanism from extended events to capture tempdb use by a given query, procedure or function.
While you could do what you've already done through the DMVs, filter your events (sql_batch_complete, rpc_completed, sql_statement_completed, sp_statement_completed would be used) to capture queries referencing table variables or temp tables, it's going to be extremely inefficient. Plus, it's going to miss tons of tempdb use. That's because tempdb is used when you put in ORDER BY in a query. It's also used when you don't put in any ORDER BY, but the optimizer decides it needs to order the data to satisfy your query. Hash tables used in aggregations and hash joins both go to tempdb. Some calculations go to tempdb. Spools. Snapshot isolation. Spills when the memory allocation isn't correct go to tempdb. Tons and tons of stuff goes into tempdb and just looking for table variables or temp tables won't cover it.
So, what can you do. Well, here's an older article, but it's 100% still applicable, that uses wait stats on tempdb to surface the queries causing those waits. Personally, if you pursue this approach, in order to reduce the amount of data you're collecting, I'd suggest filtering based on wait times or wait counts or something. Otherwise, you're going to be looking at metric tonnes of data, most of it not actionable.
If you really only care about table variables & temp tables, then use this method that Aaron Bertrand laid out.
One of these approaches should help you out.
Spills are automatically captured by the query metrics extended events I mentioned earlier, so if that's the problem, you can readily see that information with no extra work needed.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply