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 SQL Server

I believe we are reaching some limitations with SQL Server and I have been monitoring certain items in the Performance Monitor such as: pages/sec; Bytes received/sec; Bytes sent/sec; % disk read time; % disk write time; % processor time; Log growths; percent log used; and transactions/sec. I notice quite a few spikes in Bytes sent/sec and when the % disk read time spikes for more than a few seconds, users notice a delay.

My thoughts are that 1: We need more memory on our SQL Server box (we currently have 768 meg, need a faster SQL Server box and need to distribute the load of some databases to another SQL Server; and 2: We also have a bottleneck when users are connecting via Citrix to SQL Server via our Terminal2 server (which has been tracked down to simply a slow Terminal2 box with a slow nic card - This has been confirmed that our Terminal2 is definately taking a toll and will time-out when large queries are executed.)

We also have been monitoring each of the server boxes. Are there any other recommendations for SQL Server Performance monitor that anyone could see which would be good to monitor (there are several things which can be selected to monitor?)

We've also noticed that bound MSAccess forms seem to play a significant role in the long spikes for Bytes sent/sec. I'm assuming this might be normal for bound forms and the slow SQL Server box with limited memory. Unbound MSAccess forms do not seem to present any problem and show as quick spikes for the Performance monitor.

Another problem is that I also can't seem to tie back the Performance monitor spikes with specific transactions in the SQL Profiler. Is there any way to pinpoint a spike in the Performance monitor with a specific transaction other than trying to catch the spike and quickly switching to SQL Profiler?

We are planning on upgrading our SQL Server box and also adding in another SQL Server box to help distribute the load with certain databases. We are also getting a faster box for our Terminal2 (citrix) server as these slow-downs/time-outs do not happen internally or when we use Remote Desktop Connection to connect externally (only when we connect externally via Terminal2).

Any help would be greatly appreciated! Thank you in advance.Have you done a sql trace?

1/2 GB of memory?

That's kinda low...

MS Access and SQL Server...hmmmm|||I'm sorry, I meant running a SQL Trace and the Performance Monitor. Is there a way to tie these 2 together to find spikes in Performance Monitor related to a specific transaction in the SQL Trace?

Monitoring Disk activity

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

Monitoring Disk activity

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

Monitoring Disk activity

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

Monitoring a SSAS Server

When monitoring a SQL Server, the perfomanceindicators is something like:

Memory — Pages/Sec, Network Interface — Bytes Total/sec, PhysicalDisk — % Disk Time — _Total and so on.

Is there something else specific to monitor when looking at a Analysis Services Server ?

Yes, of course you can add a lot of counters about MDX, memory, locks, proactive caching and so on.

Try to have a look!

|||Ok, but you have a link or whitepaper telling a little more in detail ?|||

I don't remember a specific whitepaper.

You can find something more detailed in "AS2005 Performance Guide" that is available for download here:

http://download.microsoft.com/download/8/5/e/85eea4fa-b3bb-4426-97d0-7f7151b2011c/SSAS2005PerfGuide.doc