How busy is my tempdb ?

How can I tell if my tempdb is busy ?
Currently my tempdb is on the same drive as my user database data files and
want to know if i see some high IO on that drive, how do I contribute to
whether its tempdb activity or userdatabase activity..
In general, how can I tell how busy is my tempdb ?
I am using SQL 2005
Thanks
Hi
SQL Profiler will show you this if you look for a DBID of 2.
John
"Hassan" wrote:

> How can I tell if my tempdb is busy ?
> Currently my tempdb is on the same drive as my user database data files and
> want to know if i see some high IO on that drive, how do I contribute to
> whether its tempdb activity or userdatabase activity..
> In general, how can I tell how busy is my tempdb ?
> I am using SQL 2005
> Thanks
>
>
|||Well is that completely true ?
I mean if I am using temp tables or queries that may use temp worktables for
internal usage or row versioning info.. I dont think if I profile by DBid =
2, will I get it ..Will I ?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:49632454-27C4-429C-82F0-619E5347666B@.microsoft.com...[vbcol=seagreen]
> Hi
> SQL Profiler will show you this if you look for a DBID of 2.
> John
> "Hassan" wrote:
|||Hi Hassan
You should see something, I suggest you try it.
John
"Hassan" wrote:

> Well is that completely true ?
> I mean if I am using temp tables or queries that may use temp worktables for
> internal usage or row versioning info.. I dont think if I profile by DBid =
> 2, will I get it ..Will I ?
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:49632454-27C4-429C-82F0-619E5347666B@.microsoft.com...
>
>
|||Hi
I think Aaron wrote this script
To determine the space used by objects in TempDB:
SELECT
SPID = s.session_id,
s.[host_name],
s.[program_name],
s.status,
s.memory_usage,
granted_memory = CONVERT(INT, r.granted_query_memory*8.00),
t.text,
sourcedb = DB_NAME(r.database_id),
workdb = DB_NAME(dt.database_id),
mg.*,
su.*
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_db_session_space_usage su
ON s.session_id = su.session_id
AND su.database_id = DB_ID('tempdb')
INNER JOIN sys.dm_exec_connections c
ON s.session_id = c.most_recent_session_id
LEFT OUTER JOIN sys.dm_exec_requests r
ON r.session_id = s.session_id
LEFT OUTER JOIN (
SELECT
session_id,
database_id
FROM sys.dm_tran_session_transactions t
INNER JOIN sys.dm_tran_database_transactions dt
ON t.transaction_id = dt.transaction_id
WHERE dt.database_id = DB_ID('tempdb')
GROUP BY session_id, database_id
) dt
ON s.session_id = dt.session_id
CROSS APPLY sys.dm_exec_sql_text(COALESCE(r.sql_handle,
c.most_recent_sql_handle)) t
LEFT OUTER JOIN sys.dm_exec_query_memory_grants mg
ON s.session_id = mg.session_id
WHERE (r.database_id = DB_ID('tempdb')
OR dt.database_id = DB_ID('tempdb'))
AND s.status = 'running'
ORDER BY SPID;
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:0BDBB16F-2CCE-4947-9FDD-FC74C4C80D45@.microsoft.com...[vbcol=seagreen]
> Hi Hassan
> You should see something, I suggest you try it.
> John
> "Hassan" wrote:
|||You can also view the transactions per second counter in Perfmon for Tempdb
to get an idea how active it is. The dmv for IO stats will tell you how much
disk access you have for Tempdb.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Hassan" <hassan@.test.com> wrote in message
news:%23XgRSvCJIHA.4196@.TK2MSFTNGP04.phx.gbl...
> How can I tell if my tempdb is busy ?
> Currently my tempdb is on the same drive as my user database data files
> and want to know if i see some high IO on that drive, how do I contribute
> to whether its tempdb activity or userdatabase activity..
> In general, how can I tell how busy is my tempdb ?
> I am using SQL 2005
> Thanks
>
|||I have RPC and Batch completed and filtered for dbid = 2 and theres nothing
showing up... What events do I need ?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:0BDBB16F-2CCE-4947-9FDD-FC74C4C80D45@.microsoft.com...[vbcol=seagreen]
> Hi Hassan
> You should see something, I suggest you try it.
> John
> "Hassan" wrote:
|||If you have tempdb and your user database on the same drive and want to know
which one is contributing to the disk I/O activities on that drive, the
simplest way is to take snapshots of fn_virtualfilestats(NULL, NULL), and
compare the delta NumReads, delta NumWrites, delta BytesRead, and delta
ByteWritten. In particular, compare these delta's in terms of their relative
percentages. The I/O delta distribution among the databases tells you which
database accounts for how much of the I/O activities.
If your database has files on multiple drives, you have to look at the
percentage at the file level.
Linchi
"Hassan" wrote:

> How can I tell if my tempdb is busy ?
> Currently my tempdb is on the same drive as my user database data files and
> want to know if i see some high IO on that drive, how do I contribute to
> whether its tempdb activity or userdatabase activity..
> In general, how can I tell how busy is my tempdb ?
> I am using SQL 2005
> Thanks
>
>
|||Those things don't actually "execute" from within tempdb but rather from the
database they exist in or are executed from within as the spid's context.
The temporary OBJECTS they create, either explicitly or implicitly, will go
into tempdb.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Hassan" <hassan@.test.com> wrote in message
news:%23Iq2dqIJIHA.2064@.TK2MSFTNGP06.phx.gbl...
>I have RPC and Batch completed and filtered for dbid = 2 and theres nothing
>showing up... What events do I need ?
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:0BDBB16F-2CCE-4947-9FDD-FC74C4C80D45@.microsoft.com...
>
|||Thanks.. Thats what I thought too..
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13jfceci1b96g8d@.corp.supernews.com...
> Those things don't actually "execute" from within tempdb but rather from
> the database they exist in or are executed from within as the spid's
> context. The temporary OBJECTS they create, either explicitly or
> implicitly, will go into tempdb.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "Hassan" <hassan@.test.com> wrote in message
> news:%23Iq2dqIJIHA.2064@.TK2MSFTNGP06.phx.gbl...
>