Monitoring Transaction Time

How do I monitor how long a transaction/SQL Statement
takes in SQL Profiler ?. What is the unit of the
Duration? sec or ms ?
Thanks.
The unit of duration in SQL Profile is milliseconds.
The duration is the total duration of the transaction, form the moment the
request from the client came in until the moment that the receipt of the
last data has been acknowledged by the client. If you just want the
processor time, look at the CPU column in your profiler trace.
Jacco Schalkwijk
SQL Server MVP
"Jeff" <anonymous@.discussions.microsoft.com> wrote in message
news:f08101c43da1$f5682a50$a401280a@.phx.gbl...
> How do I monitor how long a transaction/SQL Statement
> takes in SQL Profiler ?. What is the unit of the
> Duration? sec or ms ?
> Thanks.
|||No. I just want to see how long each transaction/SQL
statement takes.
Thanks.

>--Original Message--
>The unit of duration in SQL Profile is milliseconds.
>The duration is the total duration of the transaction,
form the moment the
>request from the client came in until the moment that the
receipt of the
>last data has been acknowledged by the client. If you
just want the
>processor time, look at the CPU column in your profiler
trace.
>--
>Jacco Schalkwijk
>SQL Server MVP
>"Jeff" <anonymous@.discussions.microsoft.com> wrote in
message
>news:f08101c43da1$f5682a50$a401280a@.phx.gbl...
>
>.
>
|||As a standard I always put start time and end time as the first two columns in my traces, this helps me do other things too.
Hope this helps

Monitoring Transaction Time

How do I monitor how long a transaction/SQL Statement
takes in SQL Profiler '. What is the unit of the
Duration? sec or ms ?
Thanks.The unit of duration in SQL Profile is milliseconds.
The duration is the total duration of the transaction, form the moment the
request from the client came in until the moment that the receipt of the
last data has been acknowledged by the client. If you just want the
processor time, look at the CPU column in your profiler trace.
Jacco Schalkwijk
SQL Server MVP
"Jeff" <anonymous@.discussions.microsoft.com> wrote in message
news:f08101c43da1$f5682a50$a401280a@.phx.gbl...
> How do I monitor how long a transaction/SQL Statement
> takes in SQL Profiler '. What is the unit of the
> Duration? sec or ms ?
> Thanks.|||No. I just want to see how long each transaction/SQL
statement takes.
Thanks.

>--Original Message--
>The unit of duration in SQL Profile is milliseconds.
>The duration is the total duration of the transaction,
form the moment the
>request from the client came in until the moment that the
receipt of the
>last data has been acknowledged by the client. If you
just want the
>processor time, look at the CPU column in your profiler
trace.
>--
>Jacco Schalkwijk
>SQL Server MVP
>"Jeff" <anonymous@.discussions.microsoft.com> wrote in
message
>news:f08101c43da1$f5682a50$a401280a@.phx.gbl...
>
>.
>|||As a standard I always put start time and end time as the first two columns
in my traces, this helps me do other things too.
Hope this helps

Monitoring Transaction Time

How do I monitor how long a transaction/SQL Statement
takes in SQL Profiler '. What is the unit of the
Duration? sec or ms ?
Thanks.The unit of duration in SQL Profile is milliseconds.
The duration is the total duration of the transaction, form the moment the
request from the client came in until the moment that the receipt of the
last data has been acknowledged by the client. If you just want the
processor time, look at the CPU column in your profiler trace.
--
Jacco Schalkwijk
SQL Server MVP
"Jeff" <anonymous@.discussions.microsoft.com> wrote in message
news:f08101c43da1$f5682a50$a401280a@.phx.gbl...
> How do I monitor how long a transaction/SQL Statement
> takes in SQL Profiler '. What is the unit of the
> Duration? sec or ms ?
> Thanks.|||No. I just want to see how long each transaction/SQL
statement takes.
Thanks.
>--Original Message--
>The unit of duration in SQL Profile is milliseconds.
>The duration is the total duration of the transaction,
form the moment the
>request from the client came in until the moment that the
receipt of the
>last data has been acknowledged by the client. If you
just want the
>processor time, look at the CPU column in your profiler
trace.
>--
>Jacco Schalkwijk
>SQL Server MVP
>"Jeff" <anonymous@.discussions.microsoft.com> wrote in
message
>news:f08101c43da1$f5682a50$a401280a@.phx.gbl...
>> How do I monitor how long a transaction/SQL Statement
>> takes in SQL Profiler '. What is the unit of the
>> Duration? sec or ms ?
>> Thanks.
>
>.
>|||As a standard I always put start time and end time as the first two columns in my traces, this helps me do other things too
Hope this helpssql

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 ...

I want to monitor the activity on a specific table, but I can't seem to get
the Profiler to cooperate. I know this table is active because it is a
locking table that we use to lock interdependant modules from conflicts so
it is constantly being hit with inserts, updates and deletes. I tried
filtering on object name, but the gives me nothing.
Hints and clues most welcome.
Bob Castleman
DBA PoseurWhat events are you monitoring? Are you looking specifically for DELETES,
UPDATES and INSERTS?
"Bob Castleman" wrote:

> I want to monitor the activity on a specific table, but I can't seem to ge
t
> the Profiler to cooperate. I know this table is active because it is a
> locking table that we use to lock interdependant modules from conflicts so
> it is constantly being hit with inserts, updates and deletes. I tried
> filtering on object name, but the gives me nothing.
> Hints and clues most welcome.
> Bob Castleman
> DBA Poseur
>
>|||Yup. I want to know just how active this puppy is.
"Cris_Benge" <CrisBenge@.discussions.microsoft.com> wrote in message
news:B9D551D0-D21C-43DF-9212-BFD2AF1CF7AA@.microsoft.com...
> What events are you monitoring? Are you looking specifically for DELETES,
> UPDATES and INSERTS?
> "Bob Castleman" wrote:
>|||If you've got stored procedures which perform the updates these won't get
listed by profiler.
A possible alternative could be to add some triggers to the table, and use
them to populate an audit table.
That's by best shot on this.
"Bob Castleman" <nomail@.here> wrote in message
news:eVhML$FbFHA.2444@.TK2MSFTNGP15.phx.gbl...
> Yup. I want to know just how active this puppy is.
>
> "Cris_Benge" <CrisBenge@.discussions.microsoft.com> wrote in message
> news:B9D551D0-D21C-43DF-9212-BFD2AF1CF7AA@.microsoft.com...
>

Monitor only reads on just specific table

Can the profiler be set up to monitor only reads on just a specific
table?Hello Jim,
This is possible, you could add the following filter:
DatabaseName Like
ObjectName Like
Writes Less than 0
Reads Greater or equal 0
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: Jim Weiler <jim.weiler@.staples.com>
| Subject: Monitor only reads on just specific table
| Date: Thu, 1 Sep 2005 15:59:18 -0400
| Message-ID: <MPG.1d81260c38d3ad90989680@.msnews.microsoft.com>
| Organization: Staples
| MIME-Version: 1.0
| Content-Type: text/plain; charset="iso-8859-15"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: MicroPlanet Gravity v2.60
| Newsgroups: microsoft.public.sqlserver.security
| NNTP-Posting-Host: 66.181.92.2
| Lines: 1
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.security:5925
| X-Tomcat-NG: microsoft.public.sqlserver.security
|
| Can the profiler be set up to monitor only reads on just a specific
| table?
|

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

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 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:
>