April 10, 2015 at 11:51 pm
Hello Forum,
We have a busy system, and the developers have fallen in love with temporary tables.
Too much love....
I can get a snapshot of tables in tempDB, but I would like to track which procs are causing the load in the tempDB.
I think I can sample and record objects in the tempdb, but I would like to record the proc creating the most tempDB usage, and disk read/writes associated with those procs.
The DMV's give usage in the individual DB's, but what's a good way to correlate procs in the DB's to tempdb usage?
thanks,
Ed
April 11, 2015 at 1:30 am
Quick thought, it is going to be somewhat tricky to correlate the actual tempdb I/O to an execution, here is an example alternative that checks for # or tempdb in the query text, not certain that this is sufficient for you though.
😎
I included a test procedure that generates some tempdb traffic, just in case that someone comes up with a better idea;-)
USE Test;
GO
SET NOCOUNT ON;
SELECT
EST.objectid
,OBJECT_NAME(EST.objectid) AS OBJ_NAME
,OBJECT_SCHEMA_NAME(EST.objectid) AS OBJ_SCHEMA_NAME
,DEQS.execution_count
,DEQS.last_elapsed_time
,DEQS.last_execution_time
,DEQS.last_logical_reads
,DEQS.last_logical_writes
,DEQS.last_physical_reads
,DEQS.last_rows
,DEQS.last_worker_time
,DEQS.max_elapsed_time
,DEQS.max_logical_reads
,DEQS.max_logical_writes
,DEQS.max_physical_reads
,DEQS.max_rows
,DEQS.max_worker_time
,DEQS.min_elapsed_time
,DEQS.min_logical_reads
,DEQS.min_logical_writes
,DEQS.min_physical_reads
,DEQS.min_rows
,DEQS.min_worker_time
,DEQS.total_elapsed_time
,DEQS.total_logical_reads
,DEQS.total_logical_writes
,DEQS.total_physical_reads
,DEQS.total_rows
,DEQS.total_worker_time
FROM sys.dm_exec_query_stats DEQS
CROSS APPLY sys.dm_exec_sql_text(DEQS.sql_handle) EST
WHERE EST.dbid = DB_ID()
AND (
CHARINDEX(N'#',EST.text,1) > 0
OR
CHARINDEX(N'tempdb',EST.text,1) > 0
)
--ORDER BY DEQS.last_execution_time DESC
;
Sample procedure to generate tempdb traffic
USE Test;
GO
SET NOCOUNT ON;
GO
CREATE PROCEDURE dbo.USP_DO_TEMP_TRAFFIC
AS
SET NOCOUNT ON;
DECLARE @MSG NVARCHAR(100) = CONVERT(NVARCHAR(50),SYSDATETIME(),127) + N' START OF BATCH';
RAISERROR (@MSG,0,0) WITH NOWAIT;
DECLARE @ID INT = 0;
CREATE TABLE #mytemp(id_val INT NOT NULL)
INSERT INTO #mytemp(id_val)
SELECT
SO.object_id
FROM sys.objects SO
WHERE SO.object_id % 2 = 0;
SELECT
@ID = T.id_val
FROM #mytemp T;
DROP TABLE #mytemp;
GO
and around 5 min worth of traffic
USE Test;
GO
SET NOCOUNT ON;
GO
EXEC dbo.USP_DO_TEMP_TRAFFIC;
GO 50000
April 11, 2015 at 2:39 am
yeah, I have access to the SP's, so I can see all the tempdb creations. The other tempDB usage (spools, aggregations) may also be a problem.
Ideally, I would like to generate a list of [db].[#proc#] and the amount of R/W's the proc calls with avg/min/max/total.
It would be great if I could do it with DMV's or Extended Events ... sampling is just not accurate. An event driven process would be ideal.
Anyone else run into a similar situation?
thx again,
Ed
April 11, 2015 at 4:40 am
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 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
April 12, 2015 at 7:18 pm
edyeh (4/10/2015)
Hello Forum,We have a busy system, and the developers have fallen in love with temporary tables.
Too much love....
I have to ask what makes you think so and what type of problems you're experiencing on the system as a result?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 12, 2015 at 7:36 pm
it's pretty much a single purpose database and it has crazy numbers on the write waits on the tempdb and high workfile numbers.
ed
April 12, 2015 at 8:35 pm
edyeh (4/12/2015)
it's pretty much a single purpose database and it has crazy numbers on the write waits on the tempdb and high workfile numbers.ed
Understood. My next question is why do you think it's because of any form of developer "love" for temp tables (meaning some form of intentional use) rather than just crap code that inherently overuses TempDB resources? The reason I'm asking such odd questions is because it's a whole lot easier to find such performance challenged code than it is to isolate which code is using TempDB resources and, normally, fixing the such code will also fix the high TempDB resource usage.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 13, 2015 at 3:11 am
I think it's a combination of explicit and implicit tempDB usage. A while ago, the DBA's showed the developers the magic of using staging tables in their procs, and the use of tempDB has become standard practice for all the procs.
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.
thanks,
ed
April 13, 2015 at 8:19 am
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.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 13, 2015 at 8:54 am
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 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
April 13, 2015 at 10:42 am
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.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 13, 2015 at 10:48 am
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.
"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
April 13, 2015 at 11:08 am
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.
April 13, 2015 at 11:26 am
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.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 13, 2015 at 12:05 pm
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.
ORDER BY just to start. Also when you HASH joins you're likely also seeing the possibility of tempdb use. Lots of other examples.
"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 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply