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 memory usage of particular database

We would like to individually monitor the memory usage of all the different
databases on a server. In Current Activity in Enterprise Manager I see Memor
y Usage for the databases - is there a way to log that information? I didn't
see Memory Usage as a data
column in the Trace Properties in the Profiler. Thank you.Could the Perfmon be of any help? You ahve database counters there, depends
on what information you need.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

monitoring memory usage of particular database

We would like to individually monitor the memory usage of all the different databases on a server. In Current Activity in Enterprise Manager I see Memory Usage for the databases - is there a way to log that information? I didn't see Memory Usage as a data
column in the Trace Properties in the Profiler. Thank you.
Could the Perfmon be of any help? You ahve database counters there, depends
on what information you need.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Monitoring log usage

Hi Everyone,
I'mn setting up a proposal for a new hard drive for one of our SQL servers.
In an effort to get supporting data, I've defined performance counter logs f
or both %Disk and %Idle. Currently, %Idle is a minimum of 74.161 and a maxi
mum of 100.081. After I get the new drive installed and move the logs over
to that drive, I expect the
se percentages to change.
Question: Are there any other counter(s) I should be using to get more exac
t data that exposes the benefits of having the log files on a different hard
drive?
Thanks in advance
LarryYou should always have the log on a separate physcial drive from the data
for 2 reasons ( irregardless of stats) if there is going to be anything more
than minimal use of the database.
1. separate random IO from Serial IO... Log is serial(mostly) and data files
are random (mostly).
2. If you need up to the minute recovery without loss of data, you'd better
have your log on a separate drive...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Larry" <Larry@.discussions.microsoft.com> wrote in message
news:8A16AAEB-D472-4037-90EE-1D9B9EB5A202@.microsoft.com...
> Hi Everyone,
> I'mn setting up a proposal for a new hard drive for one of our SQL
servers.
> In an effort to get supporting data, I've defined performance counter logs
for both %Disk and %Idle. Currently, %Idle is a minimum of 74.161 and a
maximum of 100.081. After I get the new drive installed and move the logs
over to that drive, I expect these percentages to change.
> Question: Are there any other counter(s) I should be using to get more
exact data that exposes the benefits of having the log files on a different
hard drive?
> Thanks in advance
> Larry

Monitoring log usage

Hi Everyone,
I'mn setting up a proposal for a new hard drive for one of our SQL servers.
In an effort to get supporting data, I've defined performance counter logs for both %Disk and %Idle. Currently, %Idle is a minimum of 74.161 and a maximum of 100.081. After I get the new drive installed and move the logs over to that drive, I expect the
se percentages to change.
Question: Are there any other counter(s) I should be using to get more exact data that exposes the benefits of having the log files on a different hard drive?
Thanks in advance
Larry
You should always have the log on a separate physcial drive from the data
for 2 reasons ( irregardless of stats) if there is going to be anything more
than minimal use of the database.
1. separate random IO from Serial IO... Log is serial(mostly) and data files
are random (mostly).
2. If you need up to the minute recovery without loss of data, you'd better
have your log on a separate drive...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Larry" <Larry@.discussions.microsoft.com> wrote in message
news:8A16AAEB-D472-4037-90EE-1D9B9EB5A202@.microsoft.com...
> Hi Everyone,
> I'mn setting up a proposal for a new hard drive for one of our SQL
servers.
> In an effort to get supporting data, I've defined performance counter logs
for both %Disk and %Idle. Currently, %Idle is a minimum of 74.161 and a
maximum of 100.081. After I get the new drive installed and move the logs
over to that drive, I expect these percentages to change.
> Question: Are there any other counter(s) I should be using to get more
exact data that exposes the benefits of having the log files on a different
hard drive?
> Thanks in advance
> Larry

Monitoring log usage

Hi Everyone,
I'mn setting up a proposal for a new hard drive for one of our SQL servers.
In an effort to get supporting data, I've defined performance counter logs for both %Disk and %Idle. Currently, %Idle is a minimum of 74.161 and a maximum of 100.081. After I get the new drive installed and move the logs over to that drive, I expect these percentages to change.
Question: Are there any other counter(s) I should be using to get more exact data that exposes the benefits of having the log files on a different hard drive?
Thanks in advance
LarryYou should always have the log on a separate physcial drive from the data
for 2 reasons ( irregardless of stats) if there is going to be anything more
than minimal use of the database.
1. separate random IO from Serial IO... Log is serial(mostly) and data files
are random (mostly).
2. If you need up to the minute recovery without loss of data, you'd better
have your log on a separate drive...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Larry" <Larry@.discussions.microsoft.com> wrote in message
news:8A16AAEB-D472-4037-90EE-1D9B9EB5A202@.microsoft.com...
> Hi Everyone,
> I'mn setting up a proposal for a new hard drive for one of our SQL
servers.
> In an effort to get supporting data, I've defined performance counter logs
for both %Disk and %Idle. Currently, %Idle is a minimum of 74.161 and a
maximum of 100.081. After I get the new drive installed and move the logs
over to that drive, I expect these percentages to change.
> Question: Are there any other counter(s) I should be using to get more
exact data that exposes the benefits of having the log files on a different
hard drive?
> Thanks in advance
> Larry

monitoring index usage with profiler

How can you setup the Profiler to monitor which indexes are being used in SQL Server 2000 ?. I'm trying with a simple ad-hoc, specifying the actual index but I can't output the object name, id ... etc anything that'll tell me which index(s) are being us
ed.
Need some help. Thanx.
You can catch the execution plan, there you can see indexes used. Just be
sure to filter events to only those you are really interested in, because
you can get a lot of info.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"cqlboy" <anonymous@.discussions.microsoft.com> wrote in message
news:C5206D68-A403-44DC-986C-305196E4CF6D@.microsoft.com...
> How can you setup the Profiler to monitor which indexes are being used in
SQL Server 2000 ?. I'm trying with a simple ad-hoc, specifying the actual
index but I can't output the object name, id ... etc anything that'll tell
me which index(s) are being used.
> Need some help. Thanx.
|||Take a look at the ITW, the docs below combined with BOL will tell you how
to gather a representative workload and then the ITW will help you with the
first pass at tuning.
311826 INF: Index Tuning Wizard Best Practices
http://support.microsoft.com/?id=311826
Index Tuning Wizard 2000,
http://msdn.microsoft.com/library/techart/itwforsql.htm
Index Tuning Wizard 7.0,
http://msdn.microsoft.com/library/te...n_sqlindex.htm
Troubleshooting the Index Tuning Wizard,
http://msdn.microsoft.com/library/ps...tools_48ro.htm
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

Monitoring disk space

I am trying to write a script to properly monitor disk space for MSSQL
server.
Is there a stored procedure that tells me the separate space usage of
the database and the log?
The procedure sp_spaceusage seems to give an "overall" figure.
However, is it not possible (albeit unwise) that the user could create
the log on a separate filesystem, maybe even with a fixed file size,
so the log could potentially run out of space whilst there is still
space available overall.
Any thoughts?
thanks,
NeilConsider a combination of DBCC SQLPERF(LOGPSACE) and DBCC SHOWFILESTATS. The later is not documented
so use at your own risk...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:3631594f-2ecb-44db-8dd5-c0e1346ed7e4@.m34g2000hsb.googlegroups.com...
>I am trying to write a script to properly monitor disk space for MSSQL
> server.
> Is there a stored procedure that tells me the separate space usage of
> the database and the log?
> The procedure sp_spaceusage seems to give an "overall" figure.
> However, is it not possible (albeit unwise) that the user could create
> the log on a separate filesystem, maybe even with a fixed file size,
> so the log could potentially run out of space whilst there is still
> space available overall.
> Any thoughts?
> thanks,
> Neil|||> Consider a combination of DBCC SQLPERF(LOGPSACE) and DBCC SHOWFILESTATS. The later is not documented
> so use at your own risk...
>
Thanks, that got it.
I am using DBCC SQLPERF(LOGSPACE) and a query on the SYSFILES table.

Monitoring database space usage

I'm interesting in monitoring database space usage in SQL Server 2005.
Ideally, once a day I'd like a job to run that looks for databases that are
within 20% of capacity. If one or more exists, I get an email. I don't
need/want it to monitor constantly as our business processes do not require
that. I do not want to have to manually monitor. For other business
reasons, our databases will be set to fixed size with autogrowth disabled,
hence our interest in monitoring.
What do you recommend?
Thanks,
Mark
Mark,
Take a look at the view sys.database_files, it should be fairly easy
to create a SP off of this data and then as long as you have your
database mail setup, you can e-mail yourself.
Here is an example to run:
use DBNAME
go
select
physical_name,
size * 8 AS [Current Size in KB],
max_size * 8 [Maximum Size in KB]
from sys.database_files
Note that size and max_size are the number of PAGES the files have,
and pages in SQL Server are 8K.
Hope this helps,
-Sean
On Mar 28, 10:58Xam, "Mark" <m...@.idonotlikespam.com> wrote:
> I'm interesting in monitoring database space usage in SQL Server 2005.
> Ideally, once a day I'd like a job to run that looks for databases that are
> within 20% of capacity. XIf one or more exists, I get an email. XI don't
> need/want it to monitor constantly as our business processes do not require
> that. XI do not want to have to manually monitor. XFor other business
> reasons, our databases will be set to fixed size with autogrowth disabled,
> hence our interest in monitoring.
> What do you recommend?
> Thanks,
> Mark
|||On Mar 28, 8:31Xam, Sean <ColdFusion...@.gmail.com> wrote:
> Mark,
> Take a look at the view sys.database_files, it should be fairly easy
> to create a SP off of this data and then as long as you have your
> database mail setup, you can e-mail yourself.
> Here is an example to run:
> use DBNAME
> go
> select
> physical_name,
> size * 8 AS [Current Size in KB],
> max_size * 8 [Maximum Size in KB]
> from sys.database_files
> Note that size and max_size are the number of PAGES the files have,
> and pages in SQL Server are 8K.
> Hope this helps,
> -Sean
> On Mar 28, 10:58Xam, "Mark" <m...@.idonotlikespam.com> wrote:
>
>
>
> - Show quoted text -
Mark, I don't think this is going to work. This will only tell you
what the size of the database file is - not how much space is actually
being used within that file.
There are a couple of options:
1) Look up FILEPROPERTY - this function has a property for returning
SpaceUsed.
2) Review the stored procedure sp_spaceused - create your own version
using the same logic
3) Look up DataSpaceUsage, IndexSpaceUsage and SpaceAvailable in
SMO. You can either create a program, or use Powershell to create a
script to capture the data.
I prefer the script method myself and use Powershell to pull this data
from all of our SQL Servers.
HTH,
Jeff

Monitoring database space usage

I'm interesting in monitoring database space usage in SQL Server 2005.
Ideally, once a day I'd like a job to run that looks for databases that are
within 20% of capacity. If one or more exists, I get an email. I don't
need/want it to monitor constantly as our business processes do not require
that. I do not want to have to manually monitor. For other business
reasons, our databases will be set to fixed size with autogrowth disabled,
hence our interest in monitoring.
What do you recommend?
Thanks,
MarkMark,
Take a look at the view sys.database_files, it should be fairly easy
to create a SP off of this data and then as long as you have your
database mail setup, you can e-mail yourself.
Here is an example to run:
use DBNAME
go
select
physical_name,
size * 8 AS [Current Size in KB],
max_size * 8 [Maximum Size in KB]
from sys.database_files
Note that size and max_size are the number of PAGES the files have,
and pages in SQL Server are 8K.
Hope this helps,
-Sean
On Mar 28, 10:58=A0am, "Mark" <m...@.idonotlikespam.com> wrote:
> I'm interesting in monitoring database space usage in SQL Server 2005.
> Ideally, once a day I'd like a job to run that looks for databases that ar=e
> within 20% of capacity. =A0If one or more exists, I get an email. =A0I don='t
> need/want it to monitor constantly as our business processes do not requir=e
> that. =A0I do not want to have to manually monitor. =A0For other business
> reasons, our databases will be set to fixed size with autogrowth disabled,=
> hence our interest in monitoring.
> What do you recommend?
> Thanks,
> Mark|||On Mar 28, 8:31=A0am, Sean <ColdFusion...@.gmail.com> wrote:
> Mark,
> Take a look at the view sys.database_files, it should be fairly easy
> to create a SP off of this data and then as long as you have your
> database mail setup, you can e-mail yourself.
> Here is an example to run:
> use DBNAME
> go
> select
> physical_name,
> size * 8 AS [Current Size in KB],
> max_size * 8 [Maximum Size in KB]
> from sys.database_files
> Note that size and max_size are the number of PAGES the files have,
> and pages in SQL Server are 8K.
> Hope this helps,
> -Sean
> On Mar 28, 10:58=A0am, "Mark" <m...@.idonotlikespam.com> wrote:
>
> > I'm interesting in monitoring database space usage in SQL Server 2005.
> > Ideally, once a day I'd like a job to run that looks for databases that =are
> > within 20% of capacity. =A0If one or more exists, I get an email. =A0I d=on't
> > need/want it to monitor constantly as our business processes do not requ=ire
> > that. =A0I do not want to have to manually monitor. =A0For other busines=s
> > reasons, our databases will be set to fixed size with autogrowth disable=d,
> > hence our interest in monitoring.
> > What do you recommend?
> > Thanks,
> > Mark- Hide quoted text -
> - Show quoted text -
Mark, I don't think this is going to work. This will only tell you
what the size of the database file is - not how much space is actually
being used within that file.
There are a couple of options:
1) Look up FILEPROPERTY - this function has a property for returning
SpaceUsed.
2) Review the stored procedure sp_spaceused - create your own version
using the same logic
3) Look up DataSpaceUsage, IndexSpaceUsage and SpaceAvailable in
SMO. You can either create a program, or use Powershell to create a
script to capture the data.
I prefer the script method myself and use Powershell to pull this data
from all of our SQL Servers.
HTH,
Jeff

Monitor MemToLeave usage

Hi,
I am interested in detecting possible memory leaks in extended store
procedures.
Is there a way to monitor MemToLeave usage ?
setup:
win2k advanced server sp4 (cluster)
sql server 2000 advanced server sp3a
AWE + PEA
Total mem 7Gb
Mem for sql server 6Gb
/Jan
We use a combination of VMSTAT.EXE to get the total free and xp_memory_size
(comes with SQL Litespeed) to get the max contiguous block. We use these to
populate 2 of the user settable SQL counters so that we can monitor the
levels and raise alerts if the max contiguous block falls below 5MB which
allows us enough time to schedule a restart of the instance.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jan Ahlbeck" <NoSpam@.dk.dk> wrote in message
news:OB%23ULJ8VFHA.2960@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I am interested in detecting possible memory leaks in extended store
> procedures.
> Is there a way to monitor MemToLeave usage ?
> setup:
> win2k advanced server sp4 (cluster)
> sql server 2000 advanced server sp3a
> AWE + PEA
> Total mem 7Gb
> Mem for sql server 6Gb
> /Jan
>

Monitor MemToLeave usage

Hi,
I am interested in detecting possible memory leaks in extended store
procedures.
Is there a way to monitor MemToLeave usage ?
setup:
win2k advanced server sp4 (cluster)
sql server 2000 advanced server sp3a
AWE + PEA
Total mem 7Gb
Mem for sql server 6Gb
/JanWe use a combination of VMSTAT.EXE to get the total free and xp_memory_size
(comes with SQL Litespeed) to get the max contiguous block. We use these to
populate 2 of the user settable SQL counters so that we can monitor the
levels and raise alerts if the max contiguous block falls below 5MB which
allows us enough time to schedule a restart of the instance.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jan Ahlbeck" <NoSpam@.dk.dk> wrote in message
news:OB%23ULJ8VFHA.2960@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I am interested in detecting possible memory leaks in extended store
> procedures.
> Is there a way to monitor MemToLeave usage ?
> setup:
> win2k advanced server sp4 (cluster)
> sql server 2000 advanced server sp3a
> AWE + PEA
> Total mem 7Gb
> Mem for sql server 6Gb
> /Jan
>

Monitor MemToLeave usage

Hi,
I am interested in detecting possible memory leaks in extended store
procedures.
Is there a way to monitor MemToLeave usage ?
setup:
win2k advanced server sp4 (cluster)
sql server 2000 advanced server sp3a
AWE + PEA
Total mem 7Gb
Mem for sql server 6Gb
/JanWe use a combination of VMSTAT.EXE to get the total free and xp_memory_size
(comes with SQL Litespeed) to get the max contiguous block. We use these to
populate 2 of the user settable SQL counters so that we can monitor the
levels and raise alerts if the max contiguous block falls below 5MB which
allows us enough time to schedule a restart of the instance.
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jan Ahlbeck" <NoSpam@.dk.dk> wrote in message
news:OB%23ULJ8VFHA.2960@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I am interested in detecting possible memory leaks in extended store
> procedures.
> Is there a way to monitor MemToLeave usage ?
> setup:
> win2k advanced server sp4 (cluster)
> sql server 2000 advanced server sp3a
> AWE + PEA
> Total mem 7Gb
> Mem for sql server 6Gb
> /Jan
>

Monitor Index Usage.

Hi,
Is there any tool out there that can monitor what indexes are being used in
SQL Server? Example a filter in SQL Server Profiler for index usage?
Marcel
mpighin wrote:
> Hi,
> Is there any tool out there that can monitor what indexes are being
> used in SQL Server? Example a filter in SQL Server Profiler for index
> usage?
I don't know a direct way at the moment, but you can get Profiler to write
text execution plans and search them for your index's name. HTH
Kind regards
robert

Monitor DB Usage at Column Level

I'm looking for a tool that will compile historical usage information for a
specific SQL Server 2000 database down to the column-level. This is a
read-only database and I need to know how many times each column of each
table has been included in a query over a period of time. This is a
production database, so it has to be a tool that won't have a major impact on
performance.
Any suggestions?
Thanks,
Hari
Hari Seldon (HariSeldon@.discussions.microsoft.com) writes:
> I'm looking for a tool that will compile historical usage information
> for a specific SQL Server 2000 database down to the column-level. This
> is a read-only database and I need to know how many times each column of
> each table has been included in a query over a period of time. This is
> a production database, so it has to be a tool that won't have a major
> impact on performance.
You would have to run a server-side trace that captures the SP:StmtCompleted
and SQL:StmtCompleted events. Exactly what columns you should include
in the trace depends on your needs, but a minimum you would have to
include TextData. The trace should save data to a file.
The performance impact on such a query is not negligible, particularly if
users run many small queries. (If they main run long-running queries, the
number of events to track is much smaller.)
The easiest way to set up such a trace is to use Profiler, and then save
the trace as a script.
That was the easy part. Once you have the trace data, you need to analyse
it, one way or another. Unless you application generates command in a way
that makes things easy for you, for instance it always uses
tablename.columnname, this is a difficult task, and I don't know
of any tool that does this, thereby not said that it does not exist.
Presumably, a program would have to read the file, and parse each query to
determine which columns that are referred to in the query.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||"Erland Sommarskog" wrote:

> Hari Seldon (HariSeldon@.discussions.microsoft.com) writes:
> You would have to run a server-side trace that captures the SP:StmtCompleted
> and SQL:StmtCompleted events. Exactly what columns you should include
> in the trace depends on your needs, but a minimum you would have to
> include TextData. The trace should save data to a file.
> The performance impact on such a query is not negligible, particularly if
> users run many small queries. (If they main run long-running queries, the
> number of events to track is much smaller.)
> The easiest way to set up such a trace is to use Profiler, and then save
> the trace as a script.
> That was the easy part. Once you have the trace data, you need to analyse
> it, one way or another. Unless you application generates command in a way
> that makes things easy for you, for instance it always uses
> tablename.columnname, this is a difficult task, and I don't know
> of any tool that does this, thereby not said that it does not exist.
> Presumably, a program would have to read the file, and parse each query to
> determine which columns that are referred to in the query.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
|||Thanks. I really appreciate the info. I had a feeling this wasn't going to
be easy.
Hari
"Erland Sommarskog" wrote:

> Hari Seldon (HariSeldon@.discussions.microsoft.com) writes:
> You would have to run a server-side trace that captures the SP:StmtCompleted
> and SQL:StmtCompleted events. Exactly what columns you should include
> in the trace depends on your needs, but a minimum you would have to
> include TextData. The trace should save data to a file.
> The performance impact on such a query is not negligible, particularly if
> users run many small queries. (If they main run long-running queries, the
> number of events to track is much smaller.)
> The easiest way to set up such a trace is to use Profiler, and then save
> the trace as a script.
> That was the easy part. Once you have the trace data, you need to analyse
> it, one way or another. Unless you application generates command in a way
> that makes things easy for you, for instance it always uses
> tablename.columnname, this is a difficult task, and I don't know
> of any tool that does this, thereby not said that it does not exist.
> Presumably, a program would have to read the file, and parse each query to
> determine which columns that are referred to in the query.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>

Monitor CPU Usage by Query?

We are currently running MS SQL 8.0 on Windows 2003. I've been tasked
setting up a monitor that can identify queries that are causing CPU
spikes.
Does anyone have any suggestions about going about this? I'm at a
complete loss.The Profiler can do this. Create a new trace using the SQLProfilerStandard
trace template and add the CPU column. This will show both the query and the
CPU time, plus some other stuff. If this is a super busy production server
this can degrade performance so you might want to play with it on a test db
instance first. I typically run it from my desktop or from a remote desktop
and save the results to a local file cause if you write the result to the db
you are monitoring it too can impact your findings.
Netmon
"quixster@.gmail.com" wrote:
> We are currently running MS SQL 8.0 on Windows 2003. I've been tasked
> setting up a monitor that can identify queries that are causing CPU
> spikes.
> Does anyone have any suggestions about going about this? I'm at a
> complete loss.
>|||Thanks for the tip! Do you know of any way to use this or some other
utility to create an alert for administrators to receive when a query
has risen above a certain threshold? I can see how I can filter the
trace for CPU usage of a certain level, but I don't see how I could use
that to create an alert. Any thoughts?
On Oct 31, 2:21 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
> The Profiler can do this. Create a new trace using the SQLProfilerStandard
> trace template and add the CPU column. This will show both the query and the
> CPU time, plus some other stuff. If this is a super busy production server
> this can degrade performance so you might want to play with it on a test db
> instance first. I typically run it from my desktop or from a remote desktop
> and save the results to a local file cause if you write the result to the db
> you are monitoring it too can impact your findings.
> Netmon
>
> "quixs...@.gmail.com" wrote:
> > We are currently running MS SQL 8.0 on Windows 2003. I've been tasked
> > setting up a monitor that can identify queries that are causing CPU
> > spikes.
> > Does anyone have any suggestions about going about this? I'm at a
> > complete loss.- Hide quoted text -- Show quoted text -|||You can also use this query to see what's using the CPU - highest to
lowest usage:
SELECT * FROM master..sysprocesses ORDER BY cpu DESC
Then, run this to see the actual query:
DBCC INPUTBUFFER (spid) -- spid being the # in the spid column from the
above query
hth
quixster@.gmail.com wrote:
> We are currently running MS SQL 8.0 on Windows 2003. I've been tasked
> setting up a monitor that can identify queries that are causing CPU
> spikes.
> Does anyone have any suggestions about going about this? I'm at a
> complete loss.|||You can also use the windows tool PerfMon on the server and set up your
own custom traces which can viewed realtime or logged to files and
replayed.
quixster@.gmail.com wrote:
> We are currently running MS SQL 8.0 on Windows 2003. I've been tasked
> setting up a monitor that can identify queries that are causing CPU
> spikes.
> Does anyone have any suggestions about going about this? I'm at a
> complete loss.|||No the trace is not going to do that. I'v heard that NetIQ has a product
that monitors for query execution but I'm not sure of the details on how it
works.
Like the other person (tootsuite) posted you could use perfmon however it
does not take sustained thresholds, that is if CPU > 90% over 60 seconds then
alert. So anytime the CPU is above your threshold it will alert even if it
is a short lived spike you get alerted.
I'm not sure the context within which you want to filter the trace to
generate an alert. If you have any enterprise server monitoring tools like
ProIT, MOM or Nimbus available you could just monitor the CPU via those and
have it email your admin.
Are you hoping to find the rogue/cpu intense queries in action? Or are you
desiring to know when the CPU gets hammered so an admin can log in and kill
the offending query or find out the user running it?
"quixster@.gmail.com" wrote:
> Thanks for the tip! Do you know of any way to use this or some other
> utility to create an alert for administrators to receive when a query
> has risen above a certain threshold? I can see how I can filter the
> trace for CPU usage of a certain level, but I don't see how I could use
> that to create an alert. Any thoughts?
>
> On Oct 31, 2:21 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
> > The Profiler can do this. Create a new trace using the SQLProfilerStandard
> > trace template and add the CPU column. This will show both the query and the
> > CPU time, plus some other stuff. If this is a super busy production server
> > this can degrade performance so you might want to play with it on a test db
> > instance first. I typically run it from my desktop or from a remote desktop
> > and save the results to a local file cause if you write the result to the db
> > you are monitoring it too can impact your findings.
> >
> > Netmon
> >
> >
> >
> > "quixs...@.gmail.com" wrote:
> > > We are currently running MS SQL 8.0 on Windows 2003. I've been tasked
> > > setting up a monitor that can identify queries that are causing CPU
> > > spikes.
> >
> > > Does anyone have any suggestions about going about this? I'm at a
> > > complete loss.- Hide quoted text -- Show quoted text -
>|||Perfmon is just a general monitoring tool... you cannot see individual
queries.
Usually it works like this:
1. end users complain
2. look at overall picture using tool like perfmon or Quest Performance
Analysis, or even profiler (I find profiler to be flaky/unpredictable
at times, at least in 2000 it was terrible)
3. isolate queries using Quest based on various factors
4. most of our performance problems in the past have stemmed from
inadequate memory, which then in turns affects cpu, disk i/o, which
leads to blocking locks, etc - fortunately this has been resolved with
the purchase of higher performance servers + memory
so it's not enough to just isolate cpu performance, as many factors are
usually involved
so your cpu might be hammered, but the real underlying problem could be
lack of memory, etc but you wouldn't know that unless you can look at
all the various possible factors
Netmon wrote:
> No the trace is not going to do that. I'v heard that NetIQ has a product
> that monitors for query execution but I'm not sure of the details on how it
> works.
> Like the other person (tootsuite) posted you could use perfmon however it
> does not take sustained thresholds, that is if CPU > 90% over 60 seconds then
> alert. So anytime the CPU is above your threshold it will alert even if it
> is a short lived spike you get alerted.
> I'm not sure the context within which you want to filter the trace to
> generate an alert. If you have any enterprise server monitoring tools like
> ProIT, MOM or Nimbus available you could just monitor the CPU via those and
> have it email your admin.
> Are you hoping to find the rogue/cpu intense queries in action? Or are you
> desiring to know when the CPU gets hammered so an admin can log in and kill
> the offending query or find out the user running it?
> "quixster@.gmail.com" wrote:
> >
> > Thanks for the tip! Do you know of any way to use this or some other
> > utility to create an alert for administrators to receive when a query
> > has risen above a certain threshold? I can see how I can filter the
> > trace for CPU usage of a certain level, but I don't see how I could use
> > that to create an alert. Any thoughts?
> >
> >
> > On Oct 31, 2:21 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
> > > The Profiler can do this. Create a new trace using the SQLProfilerStandard
> > > trace template and add the CPU column. This will show both the query and the
> > > CPU time, plus some other stuff. If this is a super busy production server
> > > this can degrade performance so you might want to play with it on a test db
> > > instance first. I typically run it from my desktop or from a remote desktop
> > > and save the results to a local file cause if you write the result to the db
> > > you are monitoring it too can impact your findings.
> > >
> > > Netmon
> > >
> > >
> > >
> > > "quixs...@.gmail.com" wrote:
> > > > We are currently running MS SQL 8.0 on Windows 2003. I've been tasked
> > > > setting up a monitor that can identify queries that are causing CPU
> > > > spikes.
> > >
> > > > Does anyone have any suggestions about going about this? I'm at a
> > > > complete loss.- Hide quoted text -- Show quoted text -
> >
> >|||The first; trying to find rogue/cpu intense queries in action so we can
identify not only the query causing the problem, but perhaps what else
is occuring simultaneously that could be contributing to the high CPU
situation. We have MOM, and it can alert us to CPU incidents, but I
was hoping to find something that could single out the individual
queries that are running to create the incident.
On Oct 31, 4:30 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
> No the trace is not going to do that. I'v heard that NetIQ has a product
> that monitors for query execution but I'm not sure of the details on how it
> works.
> Like the other person (tootsuite) posted you could use perfmon however it
> does not take sustained thresholds, that is if CPU > 90% over 60 seconds then
> alert. So anytime the CPU is above your threshold it will alert even if it
> is a short lived spike you get alerted.
> I'm not sure the context within which you want to filter the trace to
> generate an alert. If you have any enterprise server monitoring tools like
> ProIT, MOM or Nimbus available you could just monitor the CPU via those and
> have it email your admin.
> Are you hoping to find the rogue/cpu intense queries in action? Or are you
> desiring to know when the CPU gets hammered so an admin can log in and kill
> the offending query or find out the user running it?
>
> "quixs...@.gmail.com" wrote:
> > Thanks for the tip! Do you know of any way to use this or some other
> > utility to create an alert for administrators to receive when a query
> > has risen above a certain threshold? I can see how I can filter the
> > trace for CPU usage of a certain level, but I don't see how I could use
> > that to create an alert. Any thoughts?
> > On Oct 31, 2:21 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
> > > The Profiler can do this. Create a new trace using the SQLProfilerStandard
> > > trace template and add the CPU column. This will show both the query and the
> > > CPU time, plus some other stuff. If this is a super busy production server
> > > this can degrade performance so you might want to play with it on a test db
> > > instance first. I typically run it from my desktop or from a remote desktop
> > > and save the results to a local file cause if you write the result to the db
> > > you are monitoring it too can impact your findings.
> > > Netmon
> > > "quixs...@.gmail.com" wrote:
> > > > We are currently running MS SQL 8.0 on Windows 2003. I've been tasked
> > > > setting up a monitor that can identify queries that are causing CPU
> > > > spikes.
> > > > Does anyone have any suggestions about going about this? I'm at a
> > > > complete loss.- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -|||Yes, you can do this easily using the query I provided in an earlier
post.
SELECT * FROM master..sysprocesses ORDER BY cpu DESC
DBCC INPUTBUFFER (spid) -- shows query
it can't get much clearer than this - it will list processes by CPU
usage, highest to lowest, at the given point in time you run the stmt
Or you can invest in some type of monitoring tool, like Quest
(performance analysis) or some other tool that you like if you need to
examine various performance indicators - this is what I would recommend
for a long term solution
hth
quixster@.gmail.com wrote:
> The first; trying to find rogue/cpu intense queries in action so we can
> identify not only the query causing the problem, but perhaps what else
> is occuring simultaneously that could be contributing to the high CPU
> situation. We have MOM, and it can alert us to CPU incidents, but I
> was hoping to find something that could single out the individual
> queries that are running to create the incident.
>
> On Oct 31, 4:30 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
> > No the trace is not going to do that. I'v heard that NetIQ has a product
> > that monitors for query execution but I'm not sure of the details on how it
> > works.
> >
> > Like the other person (tootsuite) posted you could use perfmon however it
> > does not take sustained thresholds, that is if CPU > 90% over 60 seconds then
> > alert. So anytime the CPU is above your threshold it will alert even if it
> > is a short lived spike you get alerted.
> >
> > I'm not sure the context within which you want to filter the trace to
> > generate an alert. If you have any enterprise server monitoring tools like
> > ProIT, MOM or Nimbus available you could just monitor the CPU via those and
> > have it email your admin.
> >
> > Are you hoping to find the rogue/cpu intense queries in action? Or are you
> > desiring to know when the CPU gets hammered so an admin can log in and kill
> > the offending query or find out the user running it?
> >
> >
> >
> > "quixs...@.gmail.com" wrote:
> >
> > > Thanks for the tip! Do you know of any way to use this or some other
> > > utility to create an alert for administrators to receive when a query
> > > has risen above a certain threshold? I can see how I can filter the
> > > trace for CPU usage of a certain level, but I don't see how I could use
> > > that to create an alert. Any thoughts?
> >
> > > On Oct 31, 2:21 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
> > > > The Profiler can do this. Create a new trace using the SQLProfilerStandard
> > > > trace template and add the CPU column. This will show both the query and the
> > > > CPU time, plus some other stuff. If this is a super busy production server
> > > > this can degrade performance so you might want to play with it on a test db
> > > > instance first. I typically run it from my desktop or from a remote desktop
> > > > and save the results to a local file cause if you write the result to the db
> > > > you are monitoring it too can impact your findings.
> >
> > > > Netmon
> >
> > > > "quixs...@.gmail.com" wrote:
> > > > > We are currently running MS SQL 8.0 on Windows 2003. I've been tasked
> > > > > setting up a monitor that can identify queries that are causing CPU
> > > > > spikes.
> >
> > > > > Does anyone have any suggestions about going about this? I'm at a
> > > > > complete loss.- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -|||Thanks for all the help! I'm taking the tips mentioned on this post
and also talking to a Quest rep about "Spotlight on SQL Server
Enterprise" http://www.quest.com/spotlight_on_sql_server_enterprise/
This has been immensely helpful!
On Nov 1, 11:20 am, tootsu...@.gmail.com wrote:
> Yes, you can do this easily using the query I provided in an earlier
> post.
> SELECT * FROM master..sysprocesses ORDER BY cpu DESC
> DBCC INPUTBUFFER (spid) -- shows query
> it can't get much clearer than this - it will list processes by CPU
> usage, highest to lowest, at the given point in time you run the stmt
> Or you can invest in some type of monitoring tool, like Quest
> (performance analysis) or some other tool that you like if you need to
> examine various performance indicators - this is what I would recommend
> for a long term solution
> hth
>
> quixs...@.gmail.com wrote:
> > The first; trying to find rogue/cpu intense queries in action so we can
> > identify not only the query causing the problem, but perhaps what else
> > is occuring simultaneously that could be contributing to the high CPU
> > situation. We have MOM, and it can alert us to CPU incidents, but I
> > was hoping to find something that could single out the individual
> > queries that are running to create the incident.
> > On Oct 31, 4:30 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
> > > No the trace is not going to do that. I'v heard that NetIQ has a product
> > > that monitors for query execution but I'm not sure of the details on how it
> > > works.
> > > Like the other person (tootsuite) posted you could use perfmon however it
> > > does not take sustained thresholds, that is if CPU > 90% over 60 seconds then
> > > alert. So anytime the CPU is above your threshold it will alert even if it
> > > is a short lived spike you get alerted.
> > > I'm not sure the context within which you want to filter the trace to
> > > generate an alert. If you have any enterprise server monitoring tools like
> > > ProIT, MOM or Nimbus available you could just monitor the CPU via those and
> > > have it email your admin.
> > > Are you hoping to find the rogue/cpu intense queries in action? Or are you
> > > desiring to know when the CPU gets hammered so an admin can log in and kill
> > > the offending query or find out the user running it?
> > > "quixs...@.gmail.com" wrote:
> > > > Thanks for the tip! Do you know of any way to use this or some other
> > > > utility to create an alert for administrators to receive when a query
> > > > has risen above a certain threshold? I can see how I can filter the
> > > > trace for CPU usage of a certain level, but I don't see how I could use
> > > > that to create an alert. Any thoughts?
> > > > On Oct 31, 2:21 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
> > > > > The Profiler can do this. Create a new trace using the SQLProfilerStandard
> > > > > trace template and add the CPU column. This will show both the query and the
> > > > > CPU time, plus some other stuff. If this is a super busy production server
> > > > > this can degrade performance so you might want to play with it on a test db
> > > > > instance first. I typically run it from my desktop or from a remote desktop
> > > > > and save the results to a local file cause if you write the result to the db
> > > > > you are monitoring it too can impact your findings.
> > > > > Netmon
> > > > > "quixs...@.gmail.com" wrote:
> > > > > > We are currently running MS SQL 8.0 on Windows 2003. I've been tasked
> > > > > > setting up a monitor that can identify queries that are causing CPU
> > > > > > spikes.
> > > > > > Does anyone have any suggestions about going about this? I'm at a
> > > > > > complete loss.- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -|||Another product that you might want to consider is Idera Diagnostic Manager
http://www.idera.com/Products/SQLdm/Default.aspx
Lucas
"quixster@.gmail.com" wrote:
> Thanks for all the help! I'm taking the tips mentioned on this post
> and also talking to a Quest rep about "Spotlight on SQL Server
> Enterprise" http://www.quest.com/spotlight_on_sql_server_enterprise/
> This has been immensely helpful!
> On Nov 1, 11:20 am, tootsu...@.gmail.com wrote:
> > Yes, you can do this easily using the query I provided in an earlier
> > post.
> >
> > SELECT * FROM master..sysprocesses ORDER BY cpu DESC
> > DBCC INPUTBUFFER (spid) -- shows query
> >
> > it can't get much clearer than this - it will list processes by CPU
> > usage, highest to lowest, at the given point in time you run the stmt
> >
> > Or you can invest in some type of monitoring tool, like Quest
> > (performance analysis) or some other tool that you like if you need to
> > examine various performance indicators - this is what I would recommend
> > for a long term solution
> >
> > hth
> >
> >
> >
> > quixs...@.gmail.com wrote:
> > > The first; trying to find rogue/cpu intense queries in action so we can
> > > identify not only the query causing the problem, but perhaps what else
> > > is occuring simultaneously that could be contributing to the high CPU
> > > situation. We have MOM, and it can alert us to CPU incidents, but I
> > > was hoping to find something that could single out the individual
> > > queries that are running to create the incident.
> >
> > > On Oct 31, 4:30 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
> > > > No the trace is not going to do that. I'v heard that NetIQ has a product
> > > > that monitors for query execution but I'm not sure of the details on how it
> > > > works.
> >
> > > > Like the other person (tootsuite) posted you could use perfmon however it
> > > > does not take sustained thresholds, that is if CPU > 90% over 60 seconds then
> > > > alert. So anytime the CPU is above your threshold it will alert even if it
> > > > is a short lived spike you get alerted.
> >
> > > > I'm not sure the context within which you want to filter the trace to
> > > > generate an alert. If you have any enterprise server monitoring tools like
> > > > ProIT, MOM or Nimbus available you could just monitor the CPU via those and
> > > > have it email your admin.
> >
> > > > Are you hoping to find the rogue/cpu intense queries in action? Or are you
> > > > desiring to know when the CPU gets hammered so an admin can log in and kill
> > > > the offending query or find out the user running it?
> >
> > > > "quixs...@.gmail.com" wrote:
> >
> > > > > Thanks for the tip! Do you know of any way to use this or some other
> > > > > utility to create an alert for administrators to receive when a query
> > > > > has risen above a certain threshold? I can see how I can filter the
> > > > > trace for CPU usage of a certain level, but I don't see how I could use
> > > > > that to create an alert. Any thoughts?
> >
> > > > > On Oct 31, 2:21 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
> > > > > > The Profiler can do this. Create a new trace using the SQLProfilerStandard
> > > > > > trace template and add the CPU column. This will show both the query and the
> > > > > > CPU time, plus some other stuff. If this is a super busy production server
> > > > > > this can degrade performance so you might want to play with it on a test db
> > > > > > instance first. I typically run it from my desktop or from a remote desktop
> > > > > > and save the results to a local file cause if you write the result to the db
> > > > > > you are monitoring it too can impact your findings.
> >
> > > > > > Netmon
> >
> > > > > > "quixs...@.gmail.com" wrote:
> > > > > > > We are currently running MS SQL 8.0 on Windows 2003. I've been tasked
> > > > > > > setting up a monitor that can identify queries that are causing CPU
> > > > > > > spikes.
> >
> > > > > > > Does anyone have any suggestions about going about this? I'm at a
> > > > > > > complete loss.- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -
>

Monitor CPU Usage by Query?

We are currently running MS SQL 8.0 on Windows 2003. I've been tasked
setting up a monitor that can identify queries that are causing CPU
spikes.
Does anyone have any suggestions about going about this? I'm at a
complete loss.The Profiler can do this. Create a new trace using the SQLProfilerStandard
trace template and add the CPU column. This will show both the query and the
CPU time, plus some other stuff. If this is a super busy production server
this can degrade performance so you might want to play with it on a test db
instance first. I typically run it from my desktop or from a remote desktop
and save the results to a local file cause if you write the result to the db
you are monitoring it too can impact your findings.
Netmon
"quixster@.gmail.com" wrote:

> We are currently running MS SQL 8.0 on Windows 2003. I've been tasked
> setting up a monitor that can identify queries that are causing CPU
> spikes.
> Does anyone have any suggestions about going about this? I'm at a
> complete loss.
>|||Thanks for the tip! Do you know of any way to use this or some other
utility to create an alert for administrators to receive when a query
has risen above a certain threshold? I can see how I can filter the
trace for CPU usage of a certain level, but I don't see how I could use
that to create an alert. Any thoughts?
On Oct 31, 2:21 pm, Netmon <Net...@.discussions.microsoft.com> wrote:[vbcol=seagreen]
> The Profiler can do this. Create a new trace using the SQLProfilerStandar
d
> trace template and add the CPU column. This will show both the query and t
he
> CPU time, plus some other stuff. If this is a super busy production serve
r
> this can degrade performance so you might want to play with it on a test d
b
> instance first. I typically run it from my desktop or from a remote deskt
op
> and save the results to a local file cause if you write the result to the
db
> you are monitoring it too can impact your findings.
> Netmon
>
> "quixs...@.gmail.com" wrote:
>|||You can also use this query to see what's using the CPU - highest to
lowest usage:
SELECT * FROM master..sysprocesses ORDER BY cpu DESC
Then, run this to see the actual query:
DBCC INPUTBUFFER (spid) -- spid being the # in the spid column from the
above query
hth
quixster@.gmail.com wrote:
> We are currently running MS SQL 8.0 on Windows 2003. I've been tasked
> setting up a monitor that can identify queries that are causing CPU
> spikes.
> Does anyone have any suggestions about going about this? I'm at a
> complete loss.|||You can also use the windows tool PerfMon on the server and set up your
own custom traces which can viewed realtime or logged to files and
replayed.
quixster@.gmail.com wrote:
> We are currently running MS SQL 8.0 on Windows 2003. I've been tasked
> setting up a monitor that can identify queries that are causing CPU
> spikes.
> Does anyone have any suggestions about going about this? I'm at a
> complete loss.|||No the trace is not going to do that. I'v heard that NetIQ has a product
that monitors for query execution but I'm not sure of the details on how it
works.
Like the other person (tootsuite) posted you could use perfmon however it
does not take sustained thresholds, that is if CPU > 90% over 60 seconds the
n
alert. So anytime the CPU is above your threshold it will alert even if it
is a short lived spike you get alerted.
I'm not sure the context within which you want to filter the trace to
generate an alert. If you have any enterprise server monitoring tools like
ProIT, MOM or Nimbus available you could just monitor the CPU via those and
have it email your admin.
Are you hoping to find the rogue/cpu intense queries in action? Or are you
desiring to know when the CPU gets hammered so an admin can log in and kill
the offending query or find out the user running it?
"quixster@.gmail.com" wrote:

> Thanks for the tip! Do you know of any way to use this or some other
> utility to create an alert for administrators to receive when a query
> has risen above a certain threshold? I can see how I can filter the
> trace for CPU usage of a certain level, but I don't see how I could use
> that to create an alert. Any thoughts?
>
> On Oct 31, 2:21 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
>|||Perfmon is just a general monitoring tool... you cannot see individual
queries.
Usually it works like this:
1. end users complain
2. look at overall picture using tool like perfmon or Quest Performance
Analysis, or even profiler (I find profiler to be flaky/unpredictable
at times, at least in 2000 it was terrible)
3. isolate queries using Quest based on various factors
4. most of our performance problems in the past have stemmed from
inadequate memory, which then in turns affects cpu, disk i/o, which
leads to blocking locks, etc - fortunately this has been resolved with
the purchase of higher performance servers + memory
so it's not enough to just isolate cpu performance, as many factors are
usually involved
so your cpu might be hammered, but the real underlying problem could be
lack of memory, etc but you wouldn't know that unless you can look at
all the various possible factors
Netmon wrote:[vbcol=seagreen]
> No the trace is not going to do that. I'v heard that NetIQ has a product
> that monitors for query execution but I'm not sure of the details on how i
t
> works.
> Like the other person (tootsuite) posted you could use perfmon however it
> does not take sustained thresholds, that is if CPU > 90% over 60 seconds t
hen
> alert. So anytime the CPU is above your threshold it will alert even if i
t
> is a short lived spike you get alerted.
> I'm not sure the context within which you want to filter the trace to
> generate an alert. If you have any enterprise server monitoring tools lik
e
> ProIT, MOM or Nimbus available you could just monitor the CPU via those an
d
> have it email your admin.
> Are you hoping to find the rogue/cpu intense queries in action? Or are you
> desiring to know when the CPU gets hammered so an admin can log in and kil
l
> the offending query or find out the user running it?
> "quixster@.gmail.com" wrote:
>|||The first; trying to find rogue/cpu intense queries in action so we can
identify not only the query causing the problem, but perhaps what else
is occuring simultaneously that could be contributing to the high CPU
situation. We have MOM, and it can alert us to CPU incidents, but I
was hoping to find something that could single out the individual
queries that are running to create the incident.
On Oct 31, 4:30 pm, Netmon <Net...@.discussions.microsoft.com> wrote:[vbcol=seagreen]
> No the trace is not going to do that. I'v heard that NetIQ has a product
> that monitors for query execution but I'm not sure of the details on how i
t
> works.
> Like the other person (tootsuite) posted you could use perfmon however it
> does not take sustained thresholds, that is if CPU > 90% over 60 seconds t
hen
> alert. So anytime the CPU is above your threshold it will alert even if i
t
> is a short lived spike you get alerted.
> I'm not sure the context within which you want to filter the trace to
> generate an alert. If you have any enterprise server monitoring tools lik
e
> ProIT, MOM or Nimbus available you could just monitor the CPU via those an
d
> have it email your admin.
> Are you hoping to find the rogue/cpu intense queries in action? Or are you
> desiring to know when the CPU gets hammered so an admin can log in and kil
l
> the offending query or find out the user running it?
>
> "quixs...@.gmail.com" wrote:
>
>
>
>
>|||Yes, you can do this easily using the query I provided in an earlier
post.
SELECT * FROM master..sysprocesses ORDER BY cpu DESC
DBCC INPUTBUFFER (spid) -- shows query
it can't get much clearer than this - it will list processes by CPU
usage, highest to lowest, at the given point in time you run the stmt
Or you can invest in some type of monitoring tool, like Quest
(performance analysis) or some other tool that you like if you need to
examine various performance indicators - this is what I would recommend
for a long term solution
hth
quixster@.gmail.com wrote:[vbcol=seagreen]
> The first; trying to find rogue/cpu intense queries in action so we can
> identify not only the query causing the problem, but perhaps what else
> is occuring simultaneously that could be contributing to the high CPU
> situation. We have MOM, and it can alert us to CPU incidents, but I
> was hoping to find something that could single out the individual
> queries that are running to create the incident.
>
> On Oct 31, 4:30 pm, Netmon <Net...@.discussions.microsoft.com> wrote:|||Thanks for all the help! I'm taking the tips mentioned on this post
and also talking to a Quest rep about "Spotlight on SQL Server
Enterprise" http://www.quest.com/spotlight_on_s...ver_enterprise/
This has been immensely helpful!
On Nov 1, 11:20 am, tootsu...@.gmail.com wrote:[vbcol=seagreen]
> Yes, you can do this easily using the query I provided in an earlier
> post.
> SELECT * FROM master..sysprocesses ORDER BY cpu DESC
> DBCC INPUTBUFFER (spid) -- shows query
> it can't get much clearer than this - it will list processes by CPU
> usage, highest to lowest, at the given point in time you run the stmt
> Or you can invest in some type of monitoring tool, like Quest
> (performance analysis) or some other tool that you like if you need to
> examine various performance indicators - this is what I would recommend
> for a long term solution
> hth
>
> quixs...@.gmail.com wrote:
>
>
>
>
>
>
>
>
>
>

Monitor CPU Usage by Query?

The Profiler can do this. Create a new trace using the SQLProfilerStandard
trace template and add the CPU column. This will show both the query and the
CPU time, plus some other stuff. If this is a super busy production server
this can degrade performance so you might want to play with it on a test db
instance first. I typically run it from my desktop or from a remote desktop
and save the results to a local file cause if you write the result to the db
you are monitoring it too can impact your findings.
Netmon
"quixster@.gmail.com" wrote:

> We are currently running MS SQL 8.0 on Windows 2003. I've been tasked
> setting up a monitor that can identify queries that are causing CPU
> spikes.
> Does anyone have any suggestions about going about this? I'm at a
> complete loss.
>
Thanks for the tip! Do you know of any way to use this or some other
utility to create an alert for administrators to receive when a query
has risen above a certain threshold? I can see how I can filter the
trace for CPU usage of a certain level, but I don't see how I could use
that to create an alert. Any thoughts?
On Oct 31, 2:21 pm, Netmon <Net...@.discussions.microsoft.com> wrote:[vbcol=seagreen]
> The Profiler can do this. Create a new trace using the SQLProfilerStandard
> trace template and add the CPU column. This will show both the query and the
> CPU time, plus some other stuff. If this is a super busy production server
> this can degrade performance so you might want to play with it on a test db
> instance first. I typically run it from my desktop or from a remote desktop
> and save the results to a local file cause if you write the result to the db
> you are monitoring it too can impact your findings.
> Netmon
>
> "quixs...@.gmail.com" wrote:
|||No the trace is not going to do that. I'v heard that NetIQ has a product
that monitors for query execution but I'm not sure of the details on how it
works.
Like the other person (tootsuite) posted you could use perfmon however it
does not take sustained thresholds, that is if CPU > 90% over 60 seconds then
alert. So anytime the CPU is above your threshold it will alert even if it
is a short lived spike you get alerted.
I'm not sure the context within which you want to filter the trace to
generate an alert. If you have any enterprise server monitoring tools like
ProIT, MOM or Nimbus available you could just monitor the CPU via those and
have it email your admin.
Are you hoping to find the rogue/cpu intense queries in action? Or are you
desiring to know when the CPU gets hammered so an admin can log in and kill
the offending query or find out the user running it?
"quixster@.gmail.com" wrote:

> Thanks for the tip! Do you know of any way to use this or some other
> utility to create an alert for administrators to receive when a query
> has risen above a certain threshold? I can see how I can filter the
> trace for CPU usage of a certain level, but I don't see how I could use
> that to create an alert. Any thoughts?
>
> On Oct 31, 2:21 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
>
|||Perfmon is just a general monitoring tool... you cannot see individual
queries.
Usually it works like this:
1. end users complain
2. look at overall picture using tool like perfmon or Quest Performance
Analysis, or even profiler (I find profiler to be flaky/unpredictable
at times, at least in 2000 it was terrible)
3. isolate queries using Quest based on various factors
4. most of our performance problems in the past have stemmed from
inadequate memory, which then in turns affects cpu, disk i/o, which
leads to blocking locks, etc - fortunately this has been resolved with
the purchase of higher performance servers + memory
so it's not enough to just isolate cpu performance, as many factors are
usually involved
so your cpu might be hammered, but the real underlying problem could be
lack of memory, etc but you wouldn't know that unless you can look at
all the various possible factors
Netmon wrote:[vbcol=seagreen]
> No the trace is not going to do that. I'v heard that NetIQ has a product
> that monitors for query execution but I'm not sure of the details on how it
> works.
> Like the other person (tootsuite) posted you could use perfmon however it
> does not take sustained thresholds, that is if CPU > 90% over 60 seconds then
> alert. So anytime the CPU is above your threshold it will alert even if it
> is a short lived spike you get alerted.
> I'm not sure the context within which you want to filter the trace to
> generate an alert. If you have any enterprise server monitoring tools like
> ProIT, MOM or Nimbus available you could just monitor the CPU via those and
> have it email your admin.
> Are you hoping to find the rogue/cpu intense queries in action? Or are you
> desiring to know when the CPU gets hammered so an admin can log in and kill
> the offending query or find out the user running it?
> "quixster@.gmail.com" wrote:
|||The first; trying to find rogue/cpu intense queries in action so we can
identify not only the query causing the problem, but perhaps what else
is occuring simultaneously that could be contributing to the high CPU
situation. We have MOM, and it can alert us to CPU incidents, but I
was hoping to find something that could single out the individual
queries that are running to create the incident.
On Oct 31, 4:30 pm, Netmon <Net...@.discussions.microsoft.com> wrote:[vbcol=seagreen]
> No the trace is not going to do that. I'v heard that NetIQ has a product
> that monitors for query execution but I'm not sure of the details on how it
> works.
> Like the other person (tootsuite) posted you could use perfmon however it
> does not take sustained thresholds, that is if CPU > 90% over 60 seconds then
> alert. So anytime the CPU is above your threshold it will alert even if it
> is a short lived spike you get alerted.
> I'm not sure the context within which you want to filter the trace to
> generate an alert. If you have any enterprise server monitoring tools like
> ProIT, MOM or Nimbus available you could just monitor the CPU via those and
> have it email your admin.
> Are you hoping to find the rogue/cpu intense queries in action? Or are you
> desiring to know when the CPU gets hammered so an admin can log in and kill
> the offending query or find out the user running it?
>
> "quixs...@.gmail.com" wrote:
>
>
|||Yes, you can do this easily using the query I provided in an earlier
post.
SELECT * FROM master..sysprocesses ORDER BY cpu DESC
DBCC INPUTBUFFER (spid) -- shows query
it can't get much clearer than this - it will list processes by CPU
usage, highest to lowest, at the given point in time you run the stmt
Or you can invest in some type of monitoring tool, like Quest
(performance analysis) or some other tool that you like if you need to
examine various performance indicators - this is what I would recommend
for a long term solution
hth
quixster@.gmail.com wrote:[vbcol=seagreen]
> The first; trying to find rogue/cpu intense queries in action so we can
> identify not only the query causing the problem, but perhaps what else
> is occuring simultaneously that could be contributing to the high CPU
> situation. We have MOM, and it can alert us to CPU incidents, but I
> was hoping to find something that could single out the individual
> queries that are running to create the incident.
>
> On Oct 31, 4:30 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
|||Thanks for all the help! I'm taking the tips mentioned on this post
and also talking to a Quest rep about "Spotlight on SQL Server
Enterprise" http://www.quest.com/spotlight_on_sql_server_enterprise/
This has been immensely helpful!
On Nov 1, 11:20 am, tootsu...@.gmail.com wrote:[vbcol=seagreen]
> Yes, you can do this easily using the query I provided in an earlier
> post.
> SELECT * FROM master..sysprocesses ORDER BY cpu DESC
> DBCC INPUTBUFFER (spid) -- shows query
> it can't get much clearer than this - it will list processes by CPU
> usage, highest to lowest, at the given point in time you run the stmt
> Or you can invest in some type of monitoring tool, like Quest
> (performance analysis) or some other tool that you like if you need to
> examine various performance indicators - this is what I would recommend
> for a long term solution
> hth
>
> quixs...@.gmail.com wrote:
>
>
>
>
>
|||Another product that you might want to consider is Idera Diagnostic Manager
http://www.idera.com/Products/SQLdm/Default.aspx
Lucas
"quixster@.gmail.com" wrote:

> Thanks for all the help! I'm taking the tips mentioned on this post
> and also talking to a Quest rep about "Spotlight on SQL Server
> Enterprise" http://www.quest.com/spotlight_on_sql_server_enterprise/
> This has been immensely helpful!
> On Nov 1, 11:20 am, tootsu...@.gmail.com wrote:
>