Monitoring tempdb usage.

I want to monitor the number of times that tempdb was accessed, wether it
grew or shrunk. What counters of perfmon can I use for this? Or should I use
the profiler instead ? There are db file related parameters over there.
Cheers
SQLCatZ
IN the database section of Perfmon there are shrink and growth counters, as
well as file size... I'd go there...
You could also take some code out of sp_spaceused and do your own thing for
size..
As to access, you could use physical reads/writes if the files are on their
own hard drive... otherwise it would be a profiler thing..
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:B6C93E0A-FC79-4627-ABF8-4A44AA5218DE@.microsoft.com...
> I want to monitor the number of times that tempdb was accessed, wether it
> grew or shrunk. What counters of perfmon can I use for this? Or should I
use
> the profiler instead ? There are db file related parameters over there.
> Cheers
> SQLCatZ
>
|||to add on Wayne's comment, you could use profiler to monitor events of
SP:starting, T-SQL:StmtStarting and RPC:SPStarting, filtering on database id
= 2.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:uhzwXRlSFHA.1384@.TK2MSFTNGP09.phx.gbl...
> IN the database section of Perfmon there are shrink and growth counters,
> as
> well as file size... I'd go there...
> You could also take some code out of sp_spaceused and do your own thing
> for
> size..
> As to access, you could use physical reads/writes if the files are on
> their
> own hard drive... otherwise it would be a profiler thing..
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
> news:B6C93E0A-FC79-4627-ABF8-4A44AA5218DE@.microsoft.com...
> use
>

Monitoring tempdb usage.

I want to monitor the number of times that tempdb was accessed, wether it
grew or shrunk. What counters of perfmon can I use for this? Or should I use
the profiler instead ? There are db file related parameters over there.
Cheers
SQLCatZIN the database section of Perfmon there are shrink and growth counters, as
well as file size... I'd go there...
You could also take some code out of sp_spaceused and do your own thing for
size..
As to access, you could use physical reads/writes if the files are on their
own hard drive... otherwise it would be a profiler thing..
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:B6C93E0A-FC79-4627-ABF8-4A44AA5218DE@.microsoft.com...
> I want to monitor the number of times that tempdb was accessed, wether it
> grew or shrunk. What counters of perfmon can I use for this? Or should I
use
> the profiler instead ? There are db file related parameters over there.
> Cheers
> SQLCatZ
>|||to add on Wayne's comment, you could use profiler to monitor events of
SP:starting, T-SQL:StmtStarting and RPC:SPStarting, filtering on database id
= 2.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:uhzwXRlSFHA.1384@.TK2MSFTNGP09.phx.gbl...
> IN the database section of Perfmon there are shrink and growth counters,
> as
> well as file size... I'd go there...
> You could also take some code out of sp_spaceused and do your own thing
> for
> size..
> As to access, you could use physical reads/writes if the files are on
> their
> own hard drive... otherwise it would be a profiler thing..
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
> news:B6C93E0A-FC79-4627-ABF8-4A44AA5218DE@.microsoft.com...
>> I want to monitor the number of times that tempdb was accessed, wether it
>> grew or shrunk. What counters of perfmon can I use for this? Or should I
> use
>> the profiler instead ? There are db file related parameters over there.
>> Cheers
>> SQLCatZ
>

Monitoring tempdb usage.

I want to monitor the number of times that tempdb was accessed, wether it
grew or shrunk. What counters of perfmon can I use for this? Or should I use
the profiler instead ? There are db file related parameters over there.
Cheers
SQLCatZIN the database section of Perfmon there are shrink and growth counters, as
well as file size... I'd go there...
You could also take some code out of sp_spaceused and do your own thing for
size..
As to access, you could use physical reads/writes if the files are on their
own hard drive... otherwise it would be a profiler thing..
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:B6C93E0A-FC79-4627-ABF8-4A44AA5218DE@.microsoft.com...
> I want to monitor the number of times that tempdb was accessed, wether it
> grew or shrunk. What counters of perfmon can I use for this? Or should I
use
> the profiler instead ? There are db file related parameters over there.
> Cheers
> SQLCatZ
>|||to add on Wayne's comment, you could use profiler to monitor events of
SP:starting, T-SQL:StmtStarting and RPC:SPStarting, filtering on database id
= 2.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:uhzwXRlSFHA.1384@.TK2MSFTNGP09.phx.gbl...
> IN the database section of Perfmon there are shrink and growth counters,
> as
> well as file size... I'd go there...
> You could also take some code out of sp_spaceused and do your own thing
> for
> size..
> As to access, you could use physical reads/writes if the files are on
> their
> own hard drive... otherwise it would be a profiler thing..
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
> news:B6C93E0A-FC79-4627-ABF8-4A44AA5218DE@.microsoft.com...
> use
>sql

Monitor Object Access

I would like to remove an index from my database, but I need to verify
that it is not being accessed before I do that.

I tried running Profiler, but it doesn't seem to be giving me the
information I need. I put in a filter for ObjectName, but it seems to
be ignoring it - i get several entries in the profile trace, but no
value in the ObjectName column.

Is there an easy way to monitor for object access?

Thanks so much!

SQL Servef 2000 SP4
(I also have 2005, SP2, if there's a better way to do this with 2005)In SQL Server 2005 I have used the sys.dm_db_index_usage_stats dynamic
management view to check for index usage. Essentially a new row is added for
the index when it is used. The catch is that the info in
sys.dm_db_index_usage_stats is reset when the SQL Server service is
restarted.

HTH,

Plamen Ratchev
http://www.SQLStudio.com|||That is PERFECT!! Thank you so much!

On Apr 26, 12:25 am, "Plamen Ratchev" <Pla...@.SQLStudio.comwrote:

Quote:

Originally Posted by

In SQL Server 2005 I have used the sys.dm_db_index_usage_stats dynamic
management view to check for index usage. Essentially a new row is added for
the index when it is used. The catch is that the info in
sys.dm_db_index_usage_stats is reset when the SQL Server service is
restarted.
>
HTH,
>
Plamen Ratchevhttp://www.SQLStudio.com

|||PERFECT!! Thank you so much!!

On Apr 26, 12:25 am, "Plamen Ratchev" <Pla...@.SQLStudio.comwrote:

Quote:

Originally Posted by

In SQL Server 2005 I have used the sys.dm_db_index_usage_stats dynamic
management view to check for index usage. Essentially a new row is added for
the index when it is used. The catch is that the info in
sys.dm_db_index_usage_stats is reset when the SQL Server service is
restarted.
>
HTH,
>
Plamen Ratchevhttp://www.SQLStudio.com