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

Monitoring SQL Compact Edition Performance

Does SQL Compact Edition expose performance counters to tools like Perfmon as SQL Server does? For instance, can you view lock wait times, cache hit ratio, etc.?

Currently, SQL Compact does not expose any performance counters. It is a in-process database engine, not a service/server process like SQL Server. You can analyze query execution with the query analyzer in SQL Server Management Studio (Express SP2).

Monitoring Disk activity

Whats the best perfmon counter to use to check how many
bytes are being read and written to disk/sec ?
I know most of the read and writes take place within a
virtual buffer cache, but what triggers a read/write from
disk .. I am currently running SQL 2000 if that helps
The Physical Disk: Bytes Read(and Write) / Sec will give you just that. You
can also use fn_virtualfilestats() to get totals.
Andrew J. Kelly SQL MVP
"Jamie" <anonymous@.discussions.microsoft.com> wrote in message
news:23a5a01c45ecc$0b2044e0$a301280a@.phx.gbl...
> Whats the best perfmon counter to use to check how many
> bytes are being read and written to disk/sec ?
> I know most of the read and writes take place within a
> virtual buffer cache, but what triggers a read/write from
> disk .. I am currently running SQL 2000 if that helps
|||Hi,
Add on, Have a look into this link.
http://www.sql-server-performance.co...ounters_io.asp
Thanks
Hari
MCDBA
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:#bLPn2sXEHA.2816@.TK2MSFTNGP11.phx.gbl...
> The Physical Disk: Bytes Read(and Write) / Sec will give you just that.
You
> can also use fn_virtualfilestats() to get totals.
> --
> Andrew J. Kelly SQL MVP
>
> "Jamie" <anonymous@.discussions.microsoft.com> wrote in message
> news:23a5a01c45ecc$0b2044e0$a301280a@.phx.gbl...
>
|||I generally start off by observing Physical Disk queue length counters as
this tells you whether the disk subsystem is keeping up with the i/o
requests made by sql (or other apps on the same box) through windows. This
is because i/o requests are queued by windows against the i/o subsystem and
if that queue is too large (more than a few i/o's per phsyical disk) or
growing, you have a clear indication that the disk is a bottleneck (not
keeping up).
If you do see a problem here, you next need to ascertain whether the amount
of work being sent to the disk is excessive, due to inefficient query plans
etc.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Jamie" <anonymous@.discussions.microsoft.com> wrote in message
news:23a5a01c45ecc$0b2044e0$a301280a@.phx.gbl...
> Whats the best perfmon counter to use to check how many
> bytes are being read and written to disk/sec ?
> I know most of the read and writes take place within a
> virtual buffer cache, but what triggers a read/write from
> disk .. I am currently running SQL 2000 if that helps

Monitoring Disk activity

Whats the best perfmon counter to use to check how many
bytes are being read and written to disk/sec ?
I know most of the read and writes take place within a
virtual buffer cache, but what triggers a read/write from
disk .. I am currently running SQL 2000 if that helpsThe Physical Disk: Bytes Read(and Write) / Sec will give you just that. You
can also use fn_virtualfilestats() to get totals.
--
Andrew J. Kelly SQL MVP
"Jamie" <anonymous@.discussions.microsoft.com> wrote in message
news:23a5a01c45ecc$0b2044e0$a301280a@.phx.gbl...
> Whats the best perfmon counter to use to check how many
> bytes are being read and written to disk/sec ?
> I know most of the read and writes take place within a
> virtual buffer cache, but what triggers a read/write from
> disk .. I am currently running SQL 2000 if that helps|||Hi,
Add on, Have a look into this link.
http://www.sql-server-performance.com/performance_monitor_counters_io.asp
Thanks
Hari
MCDBA
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:#bLPn2sXEHA.2816@.TK2MSFTNGP11.phx.gbl...
> The Physical Disk: Bytes Read(and Write) / Sec will give you just that.
You
> can also use fn_virtualfilestats() to get totals.
> --
> Andrew J. Kelly SQL MVP
>
> "Jamie" <anonymous@.discussions.microsoft.com> wrote in message
> news:23a5a01c45ecc$0b2044e0$a301280a@.phx.gbl...
> > Whats the best perfmon counter to use to check how many
> > bytes are being read and written to disk/sec ?
> >
> > I know most of the read and writes take place within a
> > virtual buffer cache, but what triggers a read/write from
> > disk .. I am currently running SQL 2000 if that helps
>|||I generally start off by observing Physical Disk queue length counters as
this tells you whether the disk subsystem is keeping up with the i/o
requests made by sql (or other apps on the same box) through windows. This
is because i/o requests are queued by windows against the i/o subsystem and
if that queue is too large (more than a few i/o's per phsyical disk) or
growing, you have a clear indication that the disk is a bottleneck (not
keeping up).
If you do see a problem here, you next need to ascertain whether the amount
of work being sent to the disk is excessive, due to inefficient query plans
etc.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Jamie" <anonymous@.discussions.microsoft.com> wrote in message
news:23a5a01c45ecc$0b2044e0$a301280a@.phx.gbl...
> Whats the best perfmon counter to use to check how many
> bytes are being read and written to disk/sec ?
> I know most of the read and writes take place within a
> virtual buffer cache, but what triggers a read/write from
> disk .. I am currently running SQL 2000 if that helps

Monitoring Disk activity

Whats the best perfmon counter to use to check how many
bytes are being read and written to disk/sec ?
I know most of the read and writes take place within a
virtual buffer cache, but what triggers a read/write from
disk .. I am currently running SQL 2000 if that helpsThe Physical Disk: Bytes Read(and Write) / Sec will give you just that. You
can also use fn_virtualfilestats() to get totals.
Andrew J. Kelly SQL MVP
"Jamie" <anonymous@.discussions.microsoft.com> wrote in message
news:23a5a01c45ecc$0b2044e0$a301280a@.phx
.gbl...
> Whats the best perfmon counter to use to check how many
> bytes are being read and written to disk/sec ?
> I know most of the read and writes take place within a
> virtual buffer cache, but what triggers a read/write from
> disk .. I am currently running SQL 2000 if that helps|||Hi,
Add on, Have a look into this link.
http://www.sql-server-performance.c...counters_io.asp
Thanks
Hari
MCDBA
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:#bLPn2sXEHA.2816@.TK2MSFTNGP11.phx.gbl...
> The Physical Disk: Bytes Read(and Write) / Sec will give you just that.
You
> can also use fn_virtualfilestats() to get totals.
> --
> Andrew J. Kelly SQL MVP
>
> "Jamie" <anonymous@.discussions.microsoft.com> wrote in message
> news:23a5a01c45ecc$0b2044e0$a301280a@.phx
.gbl...
>|||I generally start off by observing Physical Disk queue length counters as
this tells you whether the disk subsystem is keeping up with the i/o
requests made by sql (or other apps on the same box) through windows. This
is because i/o requests are queued by windows against the i/o subsystem and
if that queue is too large (more than a few i/o's per phsyical disk) or
growing, you have a clear indication that the disk is a bottleneck (not
keeping up).
If you do see a problem here, you next need to ascertain whether the amount
of work being sent to the disk is excessive, due to inefficient query plans
etc.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Jamie" <anonymous@.discussions.microsoft.com> wrote in message
news:23a5a01c45ecc$0b2044e0$a301280a@.phx
.gbl...
> Whats the best perfmon counter to use to check how many
> bytes are being read and written to disk/sec ?
> I know most of the read and writes take place within a
> virtual buffer cache, but what triggers a read/write from
> disk .. I am currently running SQL 2000 if that helps