January 2, 2017 at 11:14 am
Comments posted to this topic are about the item Query details about objects allocated in TEMPDB.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 4, 2017 at 8:43 am
I'm not sure how to use the results yet. But it does appear to work on my 2008 system.
January 4, 2017 at 11:16 am
Iwas Bornready (1/4/2017)
I'm not sure how to use the results yet. But it does appear to work on my 2008 system.
How to use the results? It tells you what application, login, and objects have space allocated in TEMPDB, so if temp storage starts growing unexpectedly, you can narrow down the issue to specific stored procedures or queries.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 4, 2017 at 11:25 am
Hi, Eric nice query.
But one pull request: replace USE TEMPDB; on USE tempdb; for case sensitive instance.
January 5, 2017 at 7:41 am
kast218 (1/4/2017)
Hi, Eric nice query.But one pull request: replace
USE TEMPDB;onUSE tempdb;for case sensitive instance.
Ugh... I've actually never worked with a server that has case sensitivity enabled. That must be pain. What is the advantage or reasoning for this?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 6, 2017 at 1:15 am
Case sensitive server is not real pain - it is question about discipline. I don't criticize your code (conversely, I add your script - https://github.com/ktaranov/sqlserver-kit/blob/master/Scripts/tempdb_Information.sql, to compare it with Glenn Berry alternatives - https://github.com/ktaranov/sqlserver-kit/blob/master/Scripts/SQL%20Server%202016%20Diagnostic%20Information%20Queries.sql), I just want to point out the fact that the best practices for using the name of the system database is lowercase.
January 9, 2017 at 10:34 am
Based on feedback, I have submitted an updated version that uses tempdb (lowercase) to compensate for servers with case sensitivity enabled. Also I have extended it to include additional columns for containing the stored procedure name and SQL statement (if applicable) that allocated the object.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 10, 2017 at 12:07 pm
Hi, Eric. Great thanks for updated script - really good work.
But for CS instance still exist error: Msg 207, Level 16, State 1, Line 56
Invalid column name 'spid'.
Please, change on line 56 ON er.session_id = trace.spid on ON er.session_id = trace.SPID
January 30, 2017 at 4:30 pm
Your script is a military grade flashlight in what was a black box for me! Added to favorites, thank you.
February 8, 2017 at 1:34 pm
For my personal version of this I have removed the first 3 columns as they were taking up visual space for information I did not need. I have added the index usage statistics on the TempDB indexes if you want to merge into your copy:
-- Query details about objects allocated in TEMPDB. This must be run in context of SET
SET LOCK_TIMEOUT 10000;
SET DEADLOCK_PRIORITY LOW;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
USE tempdb;
SELECT * FROM
(
SELECT DISTINCT
o.name AS ObjectName
, si.name AS IndexName
, CASE si.index_id
WHEN 0 THEN 'HEAP'
WHEN 1 THEN 'CLUSTERED'
ELSE 'NONCLUSTERED'
END AS IndexType
, ius.user_seeks
, ius.user_scans
, ius.user_lookups
, ius.user_seeks + ius.user_scans + ius.user_lookups AS user_reads
, ius.user_updates AS user_writes
, ius.last_user_seek
, ius.last_user_scan
, ius.last_user_update
, ps.row_count AS RowsCount
, ((ps.reserved_page_count * 8024) / 1024 / 1024) AS ReservedMB
, trace.SPID
, er.start_time AS RequestStartTime
, trace.ApplicationName
, OBJECT_NAME( qt.objectid, qt.dbid ) AS ProcedureName
, SUBSTRING(CHAR(13) + SUBSTRING (qt.text,(er.statement_start_offset/2) + 1
,((CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE er.statement_end_offset
END - er.statement_start_offset)/2) + 1)
,1,8000) AS StatementText
, trace.HostName
, trace.LoginName
, o.create_date AS ObjectCreated
FROM sys.dm_db_partition_stats ps
JOIN sys.tables AS o ON o.object_id = ps.OBJECT_ID AND o.is_ms_shipped = 0
LEFT JOIN sys.indexes si ON si.object_id = o.object_id AND si.index_id = ps.index_id
LEFT JOIN sys.dm_db_index_usage_stats ius ON ius.OBJECT_ID = ps.OBJECT_ID AND ius.index_id = ps.index_id
LEFT JOIN
(
SELECT HostName, LoginName, SPID, ApplicationName, DatabaseName, ObjectID
, ROW_NUMBER() OVER (PARTITION BY ObjectID ORDER BY StartTime DESC) MostRecentObjectReference
FROM fn_trace_gettable(
(
SELECT LEFT(path, LEN(path)-CHARINDEX('\', REVERSE(path))) + '\Log.trc'
FROM sys.traces
WHERE is_default = 1
), DEFAULT)
WHERE ObjectID IS NOT NULL
) trace
ON trace.ObjectID = ps.object_id
AND trace.DatabaseName = 'tempdb'
AND MostRecentObjectReference = 1
LEFT JOIN sys.dm_exec_requests AS er
ON er.session_id = trace.spid
OUTER APPLY sys.dm_exec_sql_text( er.sql_handle) AS qt
) AS T
WHERE ReservedMB > 0
ORDER BY 4;
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply