how to track SP's to tempdb usage

Question

how to track SP's to tempdb usage

Viewing 2 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 399332 More actions · Answer 1

SQLRNNR (4/13/2015)
Grant Fritchey (4/13/2015)
SQLRNNR (4/13/2015)
Grant Fritchey (4/13/2015)
SQLRNNR (4/13/2015)
Grant Fritchey (4/11/2015)
I'd suggest checking out Jonathan Kehayias article in Simple Talk on monitoring tempdb usage through extended events[/url]. If it doesn't include capturing the queries, you can just add them in and use the ability to relate events together to group the query with the tempdb usage. That's how I'd do it.
The session in that article does not capture the sql_text. But you can just add the sql_text action to the event session. Then it becomes very easy to figure out which query is causing which tempdb use.
total side question: Which do you think is less intrusive, an added action or another event?
The action.
But the addition of another event would not be terrible either. There would be a little observer overhead from either one - but should be pretty low in both cases.
It would likely be a good experiment to show the observer overhead from both configs.
Thanks. I've always been pretty leery about actions, but maybe I'm being overly conservative.
Maybe a bit over-simplified, but think about the DB_NAME() function. A lot of people run that without concern because it is so quick. The database_name action is much the same. Sure, sql_text will likely be bigger, but a lot of actions are just like that DB_NAME function.
On the flip side, a bunch of the events already provide the sql_text or database name, so the action would be overkill there.

Yeah, that's over-simplified 😛

However, I get the point. The one action I tend to include is getting the query & plan hash where I can.

"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

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 2

Brandie Tarvin (4/13/2015)
edyeh (4/13/2015)
I did an analysis showing procs that use high I/O and cpu resources, but I'd like to isolate the procs using the tempDB the most without going through all the procs. I have the list of the top stressed procs, but I'd like to further refine it to the one's hitting the tempDB.
I may be mistaken, but I think there are an awful lot of queries that can and do hit tempdb without the use of temp tables. I think you're limiting yourself if you're just concentrating on procs that create temp tables.

+1 on that. I can just see someone fixing all the perceived "heavy usage" in TempDB and it not really having any effect on the big picture.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)