performance counters on a SAN

Hello,
Recently we moved our database servers onto a emc cx600 san. Since
the move I ran perfmon against the server for a week to try to get a
baseline of the server's performance. I am getting some curious
metrics from perfmon on the avg. disk queue lenth and %disk time for
the data drive for the sql server database. We have the same
dedicated spindle config as we did on our former system with the
difference being the greatly increased throughput of the new system.
Avg. disk queue lenth is reporting an avg. of over 30 for lengthy
periods of time(> 10 minutes) and %disk time is reporting an avg of
over 300 around the same time . The former system was not reporting
any performance bottlenecks. I wonder if the counters are skewed
somehow. Has anyone experienced any oddities with disk related
performance counter metrics on a san or am I suddenly looking at a
disk bottleneck? Is it possible that the increased throughput to the
san is now causing a disk bottleneck?
Let me know if more details are needed.
Thanks,
GregThanks for the reply. The logical drive is a 10 spindle raid 5, so
the avg. disk queue length would be 3. If I'm not mistaken an avg.
disk queue length of 2 or more for any extended period of time would
be considered a bottleneck. Also, a % disk time of 300 seems rather
high to me as well. I am just curious if these disk counters report
accurately on a san as there weren't any bottlenecks reported on the
former system which was much slower than this system all the way
around. To my knowledge there hasn't been a substantial change in
workload to suddenly cause this.
Greg
On Tue, 12 Aug 2003 05:26:31 -0400, "Wayne Snyder"
<wsnyder@.computeredservices.com> wrote:
>Remember that disk queue lengths are for the logical drive... and to get
>*real* number you care about divide 30 by the number of physical drives
> which are likely to be a larger number in the SAN)...
>"Greg" <gjackson@.thq.com> wrote in message
>news:gjagjvgsp8ogelufmret27pdddi7vi4sh4@.4ax.com...
>> Hello,
>> Recently we moved our database servers onto a emc cx600 san. Since
>> the move I ran perfmon against the server for a week to try to get a
>> baseline of the server's performance. I am getting some curious
>> metrics from perfmon on the avg. disk queue lenth and %disk time for
>> the data drive for the sql server database. We have the same
>> dedicated spindle config as we did on our former system with the
>> difference being the greatly increased throughput of the new system.
>> Avg. disk queue lenth is reporting an avg. of over 30 for lengthy
>> periods of time(> 10 minutes) and %disk time is reporting an avg of
>> over 300 around the same time . The former system was not reporting
>> any performance bottlenecks. I wonder if the counters are skewed
>> somehow. Has anyone experienced any oddities with disk related
>> performance counter metrics on a san or am I suddenly looking at a
>> disk bottleneck? Is it possible that the increased throughput to the
>> san is now causing a disk bottleneck?
>> Let me know if more details are needed.
>> Thanks,
>> Greg
>sql

Performance counters on 64 bit SSIS

On 32 bit SSIS installations, both of the following performance counter objects are visible in perfmon.

SQLServer:SSIS Service

SQLServer:SSIS Pipeline

On 64 bit SSIS installations, only the following is available.

SQLServer:SSIS Service

The SQlServer:SSIS Pipeline counters are nowhere to be found.

Should I re-install? Is this a known issue with 64 bit SSIS?

P.S. Remote or local access administrative access with perfmon makes no difference, the "SQLServer:SSIS Pipeline" performance counters don't appear in the listbox when connecting to Windows 2003 x64 server.

Try running perfmon w/ 32-bit switch like so:

mmc.exe /32 perfmon.msc

Counters should show up.

|||

Halfway answered.

Local perfmon access to x64 "SQlServer:SSIS Pipeline" counters: yes

Remote perfmon access to x64 "SQlServer:SSIS Pipeline" counters: no

As to the local SSIS performance counter access on x64, using the SysWOW64 version of perfmon.exe does pick up the"SQlServer:SSIS Pipeline" counters.(e.g. C\WINDOWS\SysWOW64\perfmon.exe), so thanks for that.

Performance Counters not displayed

SQL Server 2000 EE SP3a.
Through PerfMon the sql server specific perfomance
counters are not displayed. I have revisited the
installation CD and per the installation SQL Server
Components/Performance Counters SubComponent are installed.
Any Ideas on how I can see these counters. This is a
production machine so all maintenance is late night.
TIA,hi,
take a look at this
article
http://support.microsoft.com/?id=152513
>--Original Message--
>SQL Server 2000 EE SP3a.
>Through PerfMon the sql server specific perfomance
>counters are not displayed. I have revisited the
>installation CD and per the installation SQL Server
>Components/Performance Counters SubComponent are
installed.
>Any Ideas on how I can see these counters. This is a
>production machine so all maintenance is late night.
>TIA,
>.
>|||I have looked at this article. Again the only counters
that do not show up are the SQL Server specific counters.
Joe
>--Original Message--
>hi,
>take a look at this
>article
>http://support.microsoft.com/?id=152513
>>--Original Message--
>>SQL Server 2000 EE SP3a.
>>Through PerfMon the sql server specific perfomance
>>counters are not displayed. I have revisited the
>>installation CD and per the installation SQL Server
>>Components/Performance Counters SubComponent are
>installed.
>>Any Ideas on how I can see these counters. This is a
>>production machine so all maintenance is late night.
>>TIA,
>>.
>.
>|||Did you restart the server before looking at the perfmon,
To get u'r counters back just restart the entire system
and look at it
>--Original Message--
>I have looked at this article. Again the only counters
>that do not show up are the SQL Server specific counters.
>Joe
>>--Original Message--
>>hi,
>>take a look at this
>>article
>>http://support.microsoft.com/?id=152513
>>--Original Message--
>>SQL Server 2000 EE SP3a.
>>Through PerfMon the sql server specific perfomance
>>counters are not displayed. I have revisited the
>>installation CD and per the installation SQL Server
>>Components/Performance Counters SubComponent are
>>installed.
>>Any Ideas on how I can see these counters. This is a
>>production machine so all maintenance is late night.
>>TIA,
>>.
>>.
>.
>

Performance counter : PhysicalDisk: % Disk Time

We have a DataWarehouse process that, at times, causes this Perfmon counter to exceed > 1800 (for several minutes at a time. Not good).

Question: How can that counter exceed 100 * (number of spindles in array)? (I suspect that numbers in that range are meaningless except to point out a disk bottleneck.)

TIA,

barkingdog

P.S. We use sql 2005, x64 edition, with SP1.

http://www.demandtech.com/FAQsdisk.htm#DiskFAQ1

Frequently Asked Questions about Disk
1. The Logical/Physical Disk % Disk Time counters look wrong. What gives?

http://support.microsoft.com/kb/269481

not about:: We use sql 2005, x64 edition, with SP1.

performance bottleneck

I am looking at the performance of one of our production servers and have
done Perfmon. I am seeing a definite bottleneck on the following drives
I, L, H and J
What do you thing and is there anything else I am missing here.
Thanks for your help.
PhysicalDisk
E
T
O
I
L
H
J
% Disk Time
0.693
56.483
1.917
379.04
326.234
470.186
149.234
Avg.Disk Queue Length
0.007
0.565
0.019
3.79
3.262
4.702
1.492
Current Disk Queue Length
0.01
0.181
0.014
27.186
2.664
3.443
2.935
Disk Writes/sec
1.234
112.324
22.064
5.734
5.535
8.989
4.184
1) how does a % Disk Time counter get to be over 100? :-)
2) I don't think we have enough information to help you. What is the
capabilities and number of spindles of each drive system? What was CPU
utilization? Memory situation and usage? Were things running 'slowly'?
TheSQLGuru
President
Indicium Resources, Inc.
<msnews.microsoft.com> wrote in message
news:O83SEE6oHHA.4552@.TK2MSFTNGP04.phx.gbl...
>I am looking at the performance of one of our production servers and have
>done Perfmon. I am seeing a definite bottleneck on the following drives
> I, L, H and J
> What do you thing and is there anything else I am missing here.
> Thanks for your help.
>
> PhysicalDisk
> E
> T
> O
> I
> L
> H
> J
> % Disk Time
> 0.693
> 56.483
> 1.917
> 379.04
> 326.234
> 470.186
> 149.234
> Avg.Disk Queue Length
> 0.007
> 0.565
> 0.019
> 3.79
> 3.262
> 4.702
> 1.492
> Current Disk Queue Length
> 0.01
> 0.181
> 0.014
> 27.186
> 2.664
> 3.443
> 2.935
> Disk Writes/sec
> 1.234
> 112.324
> 22.064
> 5.734
> 5.535
> 8.989
> 4.184
>
>
|||These numbers don't seem all that out of line. The queueing on the I drive
is a little high, but without knowing how many spindles are behind the I
drive and what else those spindles are doing I can't say for sure.
What is the Avg Disk Sec/Write number for that drive?
Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
Microsoft Office SharePoint Server 2007: Configuration)
MCITP (dbadmin, dbdev)
"TheSQLGuru" wrote:

> 1) how does a % Disk Time counter get to be over 100? :-)
> 2) I don't think we have enough information to help you. What is the
> capabilities and number of spindles of each drive system? What was CPU
> utilization? Memory situation and usage? Were things running 'slowly'?
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <msnews.microsoft.com> wrote in message
> news:O83SEE6oHHA.4552@.TK2MSFTNGP04.phx.gbl...
>
>
|||Hi
"msnews.microsoft.com" wrote:

> I am looking at the performance of one of our production servers and have
> done Perfmon. I am seeing a definite bottleneck on the following drives
> I, L, H and J
> What do you thing and is there anything else I am missing here.
> Thanks for your help.
>
> PhysicalDisk
> E
> T
> O
> I
> L
> H
> J
> % Disk Time
> 0.693
> 56.483
> 1.917
> 379.04
> 326.234
> 470.186
> 149.234
> Avg.Disk Queue Length
> 0.007
> 0.565
> 0.019
> 3.79
> 3.262
> 4.702
> 1.492
> Current Disk Queue Length
> 0.01
> 0.181
> 0.014
> 27.186
> 2.664
> 3.443
> 2.935
> Disk Writes/sec
> 1.234
> 112.324
> 22.064
> 5.734
> 5.535
> 8.989
> 4.184
>
You may want to look at some previous posts such as
http://tinyurl.com/2ysoya which point you to resource that tell you about
what you should be monitoring and what values you can expect.
Also check out the articles such as
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx which
will still be relivant to SQL 2000, http://support.microsoft.com/kb/319942/
and
http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/Performance_Tuning_Waits_Queues.doc
John

performance bottleneck

I am looking at the performance of one of our production servers and have
done Perfmon. I am seeing a definite bottleneck on the following drives
I, L, H and J
What do you thing and is there anything else I am missing here.
Thanks for your help.
PhysicalDisk
E
T
O
I
L
H
J
% Disk Time
0.693
56.483
1.917
379.04
326.234
470.186
149.234
Avg.Disk Queue Length
0.007
0.565
0.019
3.79
3.262
4.702
1.492
Current Disk Queue Length
0.01
0.181
0.014
27.186
2.664
3.443
2.935
Disk Writes/sec
1.234
112.324
22.064
5.734
5.535
8.989
4.1841) how does a % Disk Time counter get to be over 100? :-)
2) I don't think we have enough information to help you. What is the
capabilities and number of spindles of each drive system? What was CPU
utilization? Memory situation and usage? Were things running 'slowly''
TheSQLGuru
President
Indicium Resources, Inc.
<msnews.microsoft.com> wrote in message
news:O83SEE6oHHA.4552@.TK2MSFTNGP04.phx.gbl...
>I am looking at the performance of one of our production servers and have
>done Perfmon. I am seeing a definite bottleneck on the following drives
> I, L, H and J
> What do you thing and is there anything else I am missing here.
> Thanks for your help.
>
> PhysicalDisk
> E
> T
> O
> I
> L
> H
> J
> % Disk Time
> 0.693
> 56.483
> 1.917
> 379.04
> 326.234
> 470.186
> 149.234
> Avg.Disk Queue Length
> 0.007
> 0.565
> 0.019
> 3.79
> 3.262
> 4.702
> 1.492
> Current Disk Queue Length
> 0.01
> 0.181
> 0.014
> 27.186
> 2.664
> 3.443
> 2.935
> Disk Writes/sec
> 1.234
> 112.324
> 22.064
> 5.734
> 5.535
> 8.989
> 4.184
>
>|||These numbers don't seem all that out of line. The queueing on the I drive
is a little high, but without knowing how many spindles are behind the I
drive and what else those spindles are doing I can't say for sure.
What is the Avg Disk Sec/Write number for that drive?
--
Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
Microsoft Office SharePoint Server 2007: Configuration)
MCITP (dbadmin, dbdev)
"TheSQLGuru" wrote:

> 1) how does a % Disk Time counter get to be over 100? :-)
> 2) I don't think we have enough information to help you. What is the
> capabilities and number of spindles of each drive system? What was CPU
> utilization? Memory situation and usage? Were things running 'slowly''
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <msnews.microsoft.com> wrote in message
> news:O83SEE6oHHA.4552@.TK2MSFTNGP04.phx.gbl...
>
>|||Hi
"msnews.microsoft.com" wrote:

> I am looking at the performance of one of our production servers and have
> done Perfmon. I am seeing a definite bottleneck on the following drives
> I, L, H and J
> What do you thing and is there anything else I am missing here.
> Thanks for your help.
>
> PhysicalDisk
> E
> T
> O
> I
> L
> H
> J
> % Disk Time
> 0.693
> 56.483
> 1.917
> 379.04
> 326.234
> 470.186
> 149.234
> Avg.Disk Queue Length
> 0.007
> 0.565
> 0.019
> 3.79
> 3.262
> 4.702
> 1.492
> Current Disk Queue Length
> 0.01
> 0.181
> 0.014
> 27.186
> 2.664
> 3.443
> 2.935
> Disk Writes/sec
> 1.234
> 112.324
> 22.064
> 5.734
> 5.535
> 8.989
> 4.184
>
You may want to look at some previous posts such as
http://tinyurl.com/2ysoya which point you to resource that tell you about
what you should be monitoring and what values you can expect.
Also check out the articles such as
http://www.microsoft.com/technet/pr...5/tsprfprb.mspx which
will still be relivant to SQL 2000, http://support.microsoft.com/kb/319942/
and
http://download.microsoft.com/downl...aits_Queues.doc
John

performance bottleneck

I am looking at the performance of one of our production servers and have
done Perfmon. I am seeing a definite bottleneck on the following drives
I, L, H and J
What do you thing and is there anything else I am missing here.
Thanks for your help.
PhysicalDisk
E
T
O
I
L
H
J
% Disk Time
0.693
56.483
1.917
379.04
326.234
470.186
149.234
Avg.Disk Queue Length
0.007
0.565
0.019
3.79
3.262
4.702
1.492
Current Disk Queue Length
0.01
0.181
0.014
27.186
2.664
3.443
2.935
Disk Writes/sec
1.234
112.324
22.064
5.734
5.535
8.989
4.1841) how does a % Disk Time counter get to be over 100? :-)
2) I don't think we have enough information to help you. What is the
capabilities and number of spindles of each drive system? What was CPU
utilization? Memory situation and usage? Were things running 'slowly''
--
TheSQLGuru
President
Indicium Resources, Inc.
<msnews.microsoft.com> wrote in message
news:O83SEE6oHHA.4552@.TK2MSFTNGP04.phx.gbl...
>I am looking at the performance of one of our production servers and have
>done Perfmon. I am seeing a definite bottleneck on the following drives
> I, L, H and J
> What do you thing and is there anything else I am missing here.
> Thanks for your help.
>
> PhysicalDisk
> E
> T
> O
> I
> L
> H
> J
> % Disk Time
> 0.693
> 56.483
> 1.917
> 379.04
> 326.234
> 470.186
> 149.234
> Avg.Disk Queue Length
> 0.007
> 0.565
> 0.019
> 3.79
> 3.262
> 4.702
> 1.492
> Current Disk Queue Length
> 0.01
> 0.181
> 0.014
> 27.186
> 2.664
> 3.443
> 2.935
> Disk Writes/sec
> 1.234
> 112.324
> 22.064
> 5.734
> 5.535
> 8.989
> 4.184
>
>|||These numbers don't seem all that out of line. The queueing on the I drive
is a little high, but without knowing how many spindles are behind the I
drive and what else those spindles are doing I can't say for sure.
What is the Avg Disk Sec/Write number for that drive?
--
Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
Microsoft Office SharePoint Server 2007: Configuration)
MCITP (dbadmin, dbdev)
"TheSQLGuru" wrote:
> 1) how does a % Disk Time counter get to be over 100? :-)
> 2) I don't think we have enough information to help you. What is the
> capabilities and number of spindles of each drive system? What was CPU
> utilization? Memory situation and usage? Were things running 'slowly''
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <msnews.microsoft.com> wrote in message
> news:O83SEE6oHHA.4552@.TK2MSFTNGP04.phx.gbl...
> >I am looking at the performance of one of our production servers and have
> >done Perfmon. I am seeing a definite bottleneck on the following drives
> >
> > I, L, H and J
> >
> > What do you thing and is there anything else I am missing here.
> >
> > Thanks for your help.
> >
> >
> >
> > PhysicalDisk
> > E
> > T
> > O
> > I
> > L
> > H
> > J
> >
> > % Disk Time
> > 0.693
> > 56.483
> > 1.917
> > 379.04
> > 326.234
> > 470.186
> > 149.234
> >
> > Avg.Disk Queue Length
> > 0.007
> > 0.565
> > 0.019
> > 3.79
> > 3.262
> > 4.702
> > 1.492
> >
> > Current Disk Queue Length
> > 0.01
> > 0.181
> > 0.014
> > 27.186
> > 2.664
> > 3.443
> > 2.935
> >
> > Disk Writes/sec
> > 1.234
> > 112.324
> > 22.064
> > 5.734
> > 5.535
> > 8.989
> > 4.184
> >
> >
> >
>
>|||Hi
"msnews.microsoft.com" wrote:
> I am looking at the performance of one of our production servers and have
> done Perfmon. I am seeing a definite bottleneck on the following drives
> I, L, H and J
> What do you thing and is there anything else I am missing here.
> Thanks for your help.
>
> PhysicalDisk
> E
> T
> O
> I
> L
> H
> J
> % Disk Time
> 0.693
> 56.483
> 1.917
> 379.04
> 326.234
> 470.186
> 149.234
> Avg.Disk Queue Length
> 0.007
> 0.565
> 0.019
> 3.79
> 3.262
> 4.702
> 1.492
> Current Disk Queue Length
> 0.01
> 0.181
> 0.014
> 27.186
> 2.664
> 3.443
> 2.935
> Disk Writes/sec
> 1.234
> 112.324
> 22.064
> 5.734
> 5.535
> 8.989
> 4.184
>
You may want to look at some previous posts such as
http://tinyurl.com/2ysoya which point you to resource that tell you about
what you should be monitoring and what values you can expect.
Also check out the articles such as
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx which
will still be relivant to SQL 2000, http://support.microsoft.com/kb/319942/
and
http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/Performance_Tuning_Waits_Queues.doc
John

Performace Monitor in Windows 2000

PerfMon under NT has a 'Always on top' option but the same
option can not be found in Windows 2000. Any ideas?
thanksHello,
My name is Michael and I would like to thank you for using Microsoft
newsgroup.
Under Windows NT, this feature is application-specific. So far as I know,
the option 'Always on top' does not exist in the performance application in
Windows 2000. That is by design so what we are able to do unfortunately is
very limited in Windows 2000.
Thanks for using Microsoft newsgroup.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Hello,
Due to the anture of this issue, I think the current newsgroup is not the
best one for this problem. I would suggest that you post this question in
the microsoft.public.win2000.general newsgroup, which is primarily for
issues involving windows 2000 problem. The reason why we recommend posting
appropriately is you will get the most qualified pool of respondents, and
other partners who read the newsgroups regularly can either share their
knowledge or learn from your interaction with us. I hope the problem can be
resolved quickly.
Thank you for using our newsgroup.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

Perfmon/Profiler confusion

I am trying to troubleshoot crazy values for SQL Statistics/SQL
Compilations/sec in Perfmon. So I started up the Profiler,removed all
the default events and added Stored Procedures/SP:Recompile event class.
I am seeing numbers like 20 SQL Compilations/sec average, however, the
Profiler only records a few over a 10 minute period. What am I missing
here? Could it be that SQL Server is not really doing recompilations,
but actual compilations (as if it sees the stored proc for the first
time)? Anyway, I am lost.
I am on SQL Server 2000 (v. 8.00.2039 - i think, sp4).
Hi Frank
These counters are measuring completely different things.
Although I don't know everything that is included in the Perfmon SQL
Statistics/SQL
Compilations/sec value, I know it is much more than just stored procedure
compilations.
Also, the SP:Recompile event in Profiler does not measure ALL
recompilations. It only counts those that occur after the procedure has
already started executing, and something in the proc forces SQL Server to
stop and compile the proc again. It does not count those cases where a proc
is recompiled before it even starts executing.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Frank Rizzo" <none@.none.com> wrote in message
news:OiS5lc05GHA.2104@.TK2MSFTNGP06.phx.gbl...
>I am trying to troubleshoot crazy values for SQL Statistics/SQL
>Compilations/sec in Perfmon. So I started up the Profiler,removed all the
>default events and added Stored Procedures/SP:Recompile event class.
> I am seeing numbers like 20 SQL Compilations/sec average, however, the
> Profiler only records a few over a 10 minute period. What am I missing
> here? Could it be that SQL Server is not really doing recompilations, but
> actual compilations (as if it sees the stored proc for the first time)?
> Anyway, I am lost.
> I am on SQL Server 2000 (v. 8.00.2039 - i think, sp4).
|||Kalen Delaney wrote:
> Hi Frank
> These counters are measuring completely different things.
> Although I don't know everything that is included in the Perfmon SQL
> Statistics/SQL
> Compilations/sec value, I know it is much more than just stored procedure
> compilations.
I've looked around but I can't find a good definition of what SQL
Statistics/SQL Compilations/sec in PerfMon measures. Any idea of where
I can find it?
Also, is it generally a bad thing to have a high number (like 30-40) in
SQL Statistics/SQL Compilations/sec value?
Regards.

> Also, the SP:Recompile event in Profiler does not measure ALL
> recompilations. It only counts those that occur after the procedure has
> already started executing, and something in the proc forces SQL Server to
> stop and compile the proc again. It does not count those cases where a proc
> is recompiled before it even starts executing.
>
|||On Wed, 04 Oct 2006 10:15:53 -0700, Frank Rizzo <none@.none.com> wrote:
>Also, is it generally a bad thing to have a high number (like 30-40) in
>SQL Statistics/SQL Compilations/sec value?
Well they're not free.
J.

Perfmon/Profiler confusion

I am trying to troubleshoot crazy values for SQL Statistics/SQL
Compilations/sec in Perfmon. So I started up the Profiler,removed all
the default events and added Stored Procedures/SP:Recompile event class.
I am seeing numbers like 20 SQL Compilations/sec average, however, the
Profiler only records a few over a 10 minute period. What am I missing
here? Could it be that SQL Server is not really doing recompilations,
but actual compilations (as if it sees the stored proc for the first
time)? Anyway, I am lost.
I am on SQL Server 2000 (v. 8.00.2039 - i think, sp4).Hi Frank
These counters are measuring completely different things.
Although I don't know everything that is included in the Perfmon SQL
Statistics/SQL
Compilations/sec value, I know it is much more than just stored procedure
compilations.
Also, the SP:Recompile event in Profiler does not measure ALL
recompilations. It only counts those that occur after the procedure has
already started executing, and something in the proc forces SQL Server to
stop and compile the proc again. It does not count those cases where a proc
is recompiled before it even starts executing.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Frank Rizzo" <none@.none.com> wrote in message
news:OiS5lc05GHA.2104@.TK2MSFTNGP06.phx.gbl...
>I am trying to troubleshoot crazy values for SQL Statistics/SQL
>Compilations/sec in Perfmon. So I started up the Profiler,removed all the
>default events and added Stored Procedures/SP:Recompile event class.
> I am seeing numbers like 20 SQL Compilations/sec average, however, the
> Profiler only records a few over a 10 minute period. What am I missing
> here? Could it be that SQL Server is not really doing recompilations, but
> actual compilations (as if it sees the stored proc for the first time)?
> Anyway, I am lost.
> I am on SQL Server 2000 (v. 8.00.2039 - i think, sp4).|||Kalen Delaney wrote:
> Hi Frank
> These counters are measuring completely different things.
> Although I don't know everything that is included in the Perfmon SQL
> Statistics/SQL
> Compilations/sec value, I know it is much more than just stored procedure
> compilations.
I've looked around but I can't find a good definition of what SQL
Statistics/SQL Compilations/sec in PerfMon measures. Any idea of where
I can find it?
Also, is it generally a bad thing to have a high number (like 30-40) in
SQL Statistics/SQL Compilations/sec value?
Regards.
> Also, the SP:Recompile event in Profiler does not measure ALL
> recompilations. It only counts those that occur after the procedure has
> already started executing, and something in the proc forces SQL Server to
> stop and compile the proc again. It does not count those cases where a proc
> is recompiled before it even starts executing.
>|||On Wed, 04 Oct 2006 10:15:53 -0700, Frank Rizzo <none@.none.com> wrote:
>Also, is it generally a bad thing to have a high number (like 30-40) in
>SQL Statistics/SQL Compilations/sec value?
Well they're not free.
J.

Perfmon/Profiler confusion

I am trying to troubleshoot crazy values for SQL Statistics/SQL
Compilations/sec in Perfmon. So I started up the Profiler,removed all
the default events and added Stored Procedures/SP:Recompile event class.
I am seeing numbers like 20 SQL Compilations/sec average, however, the
Profiler only records a few over a 10 minute period. What am I missing
here? Could it be that SQL Server is not really doing recompilations,
but actual compilations (as if it sees the stored proc for the first
time)? Anyway, I am lost.
I am on SQL Server 2000 (v. 8.00.2039 - i think, sp4).Hi Frank
These counters are measuring completely different things.
Although I don't know everything that is included in the Perfmon SQL
Statistics/SQL
Compilations/sec value, I know it is much more than just stored procedure
compilations.
Also, the SP:Recompile event in Profiler does not measure ALL
recompilations. It only counts those that occur after the procedure has
already started executing, and something in the proc forces SQL Server to
stop and compile the proc again. It does not count those cases where a proc
is recompiled before it even starts executing.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Frank Rizzo" <none@.none.com> wrote in message
news:OiS5lc05GHA.2104@.TK2MSFTNGP06.phx.gbl...
>I am trying to troubleshoot crazy values for SQL Statistics/SQL
>Compilations/sec in Perfmon. So I started up the Profiler,removed all the
>default events and added Stored Procedures/SP:Recompile event class.
> I am seeing numbers like 20 SQL Compilations/sec average, however, the
> Profiler only records a few over a 10 minute period. What am I missing
> here? Could it be that SQL Server is not really doing recompilations, but
> actual compilations (as if it sees the stored proc for the first time)?
> Anyway, I am lost.
> I am on SQL Server 2000 (v. 8.00.2039 - i think, sp4).|||Kalen Delaney wrote:
> Hi Frank
> These counters are measuring completely different things.
> Although I don't know everything that is included in the Perfmon SQL
> Statistics/SQL
> Compilations/sec value, I know it is much more than just stored procedure
> compilations.
I've looked around but I can't find a good definition of what SQL
Statistics/SQL Compilations/sec in PerfMon measures. Any idea of where
I can find it?
Also, is it generally a bad thing to have a high number (like 30-40) in
SQL Statistics/SQL Compilations/sec value?
Regards.

> Also, the SP:Recompile event in Profiler does not measure ALL
> recompilations. It only counts those that occur after the procedure has
> already started executing, and something in the proc forces SQL Server to
> stop and compile the proc again. It does not count those cases where a pro
c
> is recompiled before it even starts executing.
>|||On Wed, 04 Oct 2006 10:15:53 -0700, Frank Rizzo <none@.none.com> wrote:
>Also, is it generally a bad thing to have a high number (like 30-40) in
>SQL Statistics/SQL Compilations/sec value?
Well they're not free.
J.

perfmon.exe doesn't have sqlserver object...

I am trying to use Windows Performance monitor to find
trouble areas of our sql server. When I run perfmon.exe I
do not have a SQLServer object from which I can select
counters. If this is a sql server how do I add the
sqlserver object to perfmon?Tim,
there are many posibilities here.
Have a look at these articles, each of which detail reasons why the SQL
performance monitor counters may be missing and how to fix them:
http://support.microsoft.com/defaul...kb;en-us;227662
http://support.microsoft.com/defaul...kb;en-us;246328
http://www.extremeexperts.com/sql/f...erCounters.aspx
HTH,
Paul Ibison

perfmon.exe doesn't have sqlserver object...

I am trying to use Windows Performance monitor to find
trouble areas of our sql server. When I run perfmon.exe I
do not have a SQLServer object from which I can select
counters. If this is a sql server how do I add the
sqlserver object to perfmon?Tim,
there are many posibilities here.
Have a look at these articles, each of which detail reasons why the SQL
performance monitor counters may be missing and how to fix them:
http://support.microsoft.com/default.aspx?scid=kb;en-us;227662
http://support.microsoft.com/default.aspx?scid=kb;en-us;246328
http://www.extremeexperts.com/sql/faq/EnablingPerCounters.aspx
HTH,
Paul Ibison

Perfmon.exe doesn't have SQLserver object...

I am trying to use Windows Performance monitor to find
trouble areas of our sql server. When I run perfmon.exe I
do not have a SQLServer object from which I can select
counters. If this is a sql server how do I add the
sqlserver object to perfmon?
"Tim" <anonymous@.discussions.microsoft.com> wrote in message
news:1c53b01c4524b$06f48710$a401280a@.phx.gbl...
> I am trying to use Windows Performance monitor to find
> trouble areas of our sql server. When I run perfmon.exe I
> do not have a SQLServer object from which I can select
> counters. If this is a sql server how do I add the
> sqlserver object to perfmon?
If you are talking about SQL Server 2000, you may want to re-apply SP3a.
Failing this step, you may want to give this a read
http://support.microsoft.com/default...&Product=sql2k
Steve

perfmon.exe doesn't have sqlserver object...

I am trying to use Windows Performance monitor to find
trouble areas of our sql server. When I run perfmon.exe I
do not have a SQLServer object from which I can select
counters. If this is a sql server how do I add the
sqlserver object to perfmon?
Tim,
there are many posibilities here.
Have a look at these articles, each of which detail reasons why the SQL
performance monitor counters may be missing and how to fix them:
http://support.microsoft.com/default...b;en-us;227662
http://support.microsoft.com/default...b;en-us;246328
http://www.extremeexperts.com/sql/fa...rCounters.aspx
HTH,
Paul Ibison

perfmon tools

Hi,
I want to use perfmon.exe tools provided by win2000 to monitor performance
of SQL server, but it is strange, i cannot find SQL related counter from
drop-down performance object list. it is used to be there, but i can't find
it anymore, does someone know the reason?
I know I probably post this msg on Windows newsgroup, but i use it to
monitor SQL and I do SQL related jobs.
Thanks for your help in advance.
Jack
The following issue may apply to what you are seeing:
FIX: "Performance monitor shared memory setup failed: -1"
error message when you start SQL Server
http://support.microsoft.com/?id=812915
-Sue
On Mon, 6 Dec 2004 16:29:00 +0800, "Jack Hwang"
<jack_hc@.hotmail.com> wrote:

>Hi,
>I want to use perfmon.exe tools provided by win2000 to monitor performance
>of SQL server, but it is strange, i cannot find SQL related counter from
>drop-down performance object list. it is used to be there, but i can't find
>it anymore, does someone know the reason?
>I know I probably post this msg on Windows newsgroup, but i use it to
>monitor SQL and I do SQL related jobs.
>Thanks for your help in advance.
>Jack
>

perfmon tools

Hi,
I want to use perfmon.exe tools provided by win2000 to monitor performance
of SQL server, but it is strange, i cannot find SQL related counter from
drop-down performance object list. it is used to be there, but i can't find
it anymore, does someone know the reason?
I know I probably post this msg on Windows newsgroup, but i use it to
monitor SQL and I do SQL related jobs.
Thanks for your help in advance.
JackThe following issue may apply to what you are seeing:
FIX: "Performance monitor shared memory setup failed: -1"
error message when you start SQL Server
http://support.microsoft.com/?id=812915
-Sue
On Mon, 6 Dec 2004 16:29:00 +0800, "Jack Hwang"
<jack_hc@.hotmail.com> wrote:
>Hi,
>I want to use perfmon.exe tools provided by win2000 to monitor performance
>of SQL server, but it is strange, i cannot find SQL related counter from
>drop-down performance object list. it is used to be there, but i can't find
>it anymore, does someone know the reason?
>I know I probably post this msg on Windows newsgroup, but i use it to
>monitor SQL and I do SQL related jobs.
>Thanks for your help in advance.
>Jack
>

perfmon to remote server

I'm not running SQLServer on my own workstation.
I am running perfmon, just as a matter of course, showing CPU and disk
and net traffic. I'd also like to monitor some remote SQLServers.
When I first clicked on the Add Counters dialog and typed in the name
of a SQLServer (say, "MySQLServer1"), it didn't connect, nothing
happened. But after I opened a fresh connection to MySQLServer1 for
the query analyzer, when I went back to perfmon, now it does list
\\MySQLServer1. But, it shows no properties for it.
Do I need some more advanced admin privileges on the remote boxes (or
network generally) for this to work?
Thanks.
JoshLocal Admins to read performance counters. Probably more specialized
security could be given, but Local Admins have the rights.
Sincerely,
Anthony Thomas
"jxstern" wrote:
> I'm not running SQLServer on my own workstation.
> I am running perfmon, just as a matter of course, showing CPU and disk
> and net traffic. I'd also like to monitor some remote SQLServers.
> When I first clicked on the Add Counters dialog and typed in the name
> of a SQLServer (say, "MySQLServer1"), it didn't connect, nothing
> happened. But after I opened a fresh connection to MySQLServer1 for
> the query analyzer, when I went back to perfmon, now it does list
> \\MySQLServer1. But, it shows no properties for it.
> Do I need some more advanced admin privileges on the remote boxes (or
> network generally) for this to work?
> Thanks.
> Josh
>

perfmon to remote server

I'm not running SQLServer on my own workstation.
I am running perfmon, just as a matter of course, showing CPU and disk
and net traffic. I'd also like to monitor some remote SQLServers.
When I first clicked on the Add Counters dialog and typed in the name
of a SQLServer (say, "MySQLServer1"), it didn't connect, nothing
happened. But after I opened a fresh connection to MySQLServer1 for
the query analyzer, when I went back to perfmon, now it does list
\\MySQLServer1. But, it shows no properties for it.
Do I need some more advanced admin privileges on the remote boxes (or
network generally) for this to work?
Thanks.
JoshLocal Admins to read performance counters. Probably more specialized
security could be given, but Local Admins have the rights.
Sincerely,
Anthony Thomas
"jxstern" wrote:

> I'm not running SQLServer on my own workstation.
> I am running perfmon, just as a matter of course, showing CPU and disk
> and net traffic. I'd also like to monitor some remote SQLServers.
> When I first clicked on the Add Counters dialog and typed in the name
> of a SQLServer (say, "MySQLServer1"), it didn't connect, nothing
> happened. But after I opened a fresh connection to MySQLServer1 for
> the query analyzer, when I went back to perfmon, now it does list
> \\MySQLServer1. But, it shows no properties for it.
> Do I need some more advanced admin privileges on the remote boxes (or
> network generally) for this to work?
> Thanks.
> Josh
>

perfmon to remote server

I'm not running SQLServer on my own workstation.
I am running perfmon, just as a matter of course, showing CPU and disk
and net traffic. I'd also like to monitor some remote SQLServers.
When I first clicked on the Add Counters dialog and typed in the name
of a SQLServer (say, "MySQLServer1"), it didn't connect, nothing
happened. But after I opened a fresh connection to MySQLServer1 for
the query analyzer, when I went back to perfmon, now it does list
\\MySQLServer1. But, it shows no properties for it.
Do I need some more advanced admin privileges on the remote boxes (or
network generally) for this to work?
Thanks.
Josh
Local Admins to read performance counters. Probably more specialized
security could be given, but Local Admins have the rights.
Sincerely,
Anthony Thomas
"jxstern" wrote:

> I'm not running SQLServer on my own workstation.
> I am running perfmon, just as a matter of course, showing CPU and disk
> and net traffic. I'd also like to monitor some remote SQLServers.
> When I first clicked on the Add Counters dialog and typed in the name
> of a SQLServer (say, "MySQLServer1"), it didn't connect, nothing
> happened. But after I opened a fresh connection to MySQLServer1 for
> the query analyzer, when I went back to perfmon, now it does list
> \\MySQLServer1. But, it shows no properties for it.
> Do I need some more advanced admin privileges on the remote boxes (or
> network generally) for this to work?
> Thanks.
> Josh
>