%Disk Time value over 100

Hello!
I have collected counters using perfmon from our Production SQL Server. I
have noticed that %Disk Time counter averaging 111 with maximum value being
2249 for particular drive. I was wondering how I detect whether this value
indicates a bottleneck. Drive in question is RAID 10. Is there conversion
formula I should use?
I know that value over 60 indicates a potential problem. Average Disk Queue
lenght is 1 which means to me that disk is OK. I am confused by high %Disk
Time value.
Any advice is appreciated,
Igor
Used 100 - %disk idle time to get your utilization.
"imarchenko" wrote:

> Hello!
> I have collected counters using perfmon from our Production SQL Server. I
> have noticed that %Disk Time counter averaging 111 with maximum value being
> 2249 for particular drive. I was wondering how I detect whether this value
> indicates a bottleneck. Drive in question is RAID 10. Is there conversion
> formula I should use?
> I know that value over 60 indicates a potential problem. Average Disk Queue
> lenght is 1 which means to me that disk is OK. I am confused by high %Disk
> Time value.
>
> Any advice is appreciated,
> Igor
>
>
|||%Disk time is a useless counter on high-end IO systems. It was designed for
sequential command IO systems, not current SCSI systems that support Command
Tag Queuing or the new SATA Native Command Queuing options. These are the
device-level commands that support scatter-gather IO. Short description is
that they full-duplex command and response for IO. The device can queue a
large number of IO requests, sort them optimally, and respond to them
asychronously and asequentially. RAID subsystems further reduced the
effectiveness of this counter buy abstracting a large number of physical
devices into one logical device presented to the OS..
I use Physical disk | Disk Read Bytes/sec, Disk Write Bytes/sec, Disk
Reads/sec, Disk Writes/sec, and Disk Queue Length to determine if I am IO
bound. Of course, you need to know the maximum capabilities of your IO
subsystem to use these numbers effectively. But since you benchmarked the
IO system with IOMeter when you built the server that is an easy comparison.

Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:eKaIHNaqFHA.4044@.TK2MSFTNGP09.phx.gbl...
> Hello!
> I have collected counters using perfmon from our Production SQL Server.
> I have noticed that %Disk Time counter averaging 111 with maximum value
> being 2249 for particular drive. I was wondering how I detect whether this
> value indicates a bottleneck. Drive in question is RAID 10. Is there
> conversion formula I should use?
> I know that value over 60 indicates a potential problem. Average Disk
> Queue lenght is 1 which means to me that disk is OK. I am confused by high
> %Disk Time value.
>
> Any advice is appreciated,
> Igor
>
|||Jeffrey,
Thanks a lot!
Igor
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:22B02C35-4D78-47A1-B273-88ED2F5EDACA@.microsoft.com...[vbcol=seagreen]
> Used 100 - %disk idle time to get your utilization.
> "imarchenko" wrote:
|||Thanks, Geoff. I really appreciate your elaborate reply.
Igor
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:eSP65saqFHA.272@.TK2MSFTNGP15.phx.gbl...
> %Disk time is a useless counter on high-end IO systems. It was designed
> for sequential command IO systems, not current SCSI systems that support
> Command Tag Queuing or the new SATA Native Command Queuing options. These
> are the device-level commands that support scatter-gather IO. Short
> description is that they full-duplex command and response for IO. The
> device can queue a large number of IO requests, sort them optimally, and
> respond to them asychronously and asequentially. RAID subsystems further
> reduced the effectiveness of this counter buy abstracting a large number
> of physical devices into one logical device presented to the OS..
> I use Physical disk | Disk Read Bytes/sec, Disk Write Bytes/sec, Disk
> Reads/sec, Disk Writes/sec, and Disk Queue Length to determine if I am IO
> bound. Of course, you need to know the maximum capabilities of your IO
> subsystem to use these numbers effectively. But since you benchmarked the
> IO system with IOMeter when you built the server that is an easy
> comparison.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
> news:eKaIHNaqFHA.4044@.TK2MSFTNGP09.phx.gbl...
>

%Disk Time value over 100

Hello!
I have collected counters using perfmon from our Production SQL Server. I
have noticed that %Disk Time counter averaging 111 with maximum value being
2249 for particular drive. I was wondering how I detect whether this value
indicates a bottleneck. Drive in question is RAID 10. Is there conversion
formula I should use?
I know that value over 60 indicates a potential problem. Average Disk Queue
lenght is 1 which means to me that disk is OK. I am confused by high %Disk
Time value.
Any advice is appreciated,
IgorUsed 100 - %disk idle time to get your utilization.
"imarchenko" wrote:

> Hello!
> I have collected counters using perfmon from our Production SQL Server.
I
> have noticed that %Disk Time counter averaging 111 with maximum value bein
g
> 2249 for particular drive. I was wondering how I detect whether this value
> indicates a bottleneck. Drive in question is RAID 10. Is there conversion
> formula I should use?
> I know that value over 60 indicates a potential problem. Average Disk Queu
e
> lenght is 1 which means to me that disk is OK. I am confused by high %Disk
> Time value.
>
> Any advice is appreciated,
> Igor
>
>|||%Disk time is a useless counter on high-end IO systems. It was designed for
sequential command IO systems, not current SCSI systems that support Command
Tag Queuing or the new SATA Native Command Queuing options. These are the
device-level commands that support scatter-gather IO. Short description is
that they full-duplex command and response for IO. The device can queue a
large number of IO requests, sort them optimally, and respond to them
asychronously and asequentially. RAID subsystems further reduced the
effectiveness of this counter buy abstracting a large number of physical
devices into one logical device presented to the OS..
I use Physical disk | Disk Read Bytes/sec, Disk Write Bytes/sec, Disk
Reads/sec, Disk Writes/sec, and Disk Queue Length to determine if I am IO
bound. Of course, you need to know the maximum capabilities of your IO
subsystem to use these numbers effectively. But since you benchmarked the
IO system with IOMeter when you built the server that is an easy comparison.

Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:eKaIHNaqFHA.4044@.TK2MSFTNGP09.phx.gbl...
> Hello!
> I have collected counters using perfmon from our Production SQL Server.
> I have noticed that %Disk Time counter averaging 111 with maximum value
> being 2249 for particular drive. I was wondering how I detect whether this
> value indicates a bottleneck. Drive in question is RAID 10. Is there
> conversion formula I should use?
> I know that value over 60 indicates a potential problem. Average Disk
> Queue lenght is 1 which means to me that disk is OK. I am confused by high
> %Disk Time value.
>
> Any advice is appreciated,
> Igor
>

##temp table already exists problem

Hello

I am using a temp table called ##temp in an SProc but often get the
message that the table already exists. Could this be because the SProc
is being run by more than 2 webpages at the same time?
Or is it because the sProc has an error and is not getting to the drop
table line?

I have tried adding a line to test if the object exists and to drop the
table before I create it. If I drop it will it affect another instance
of the sProc that is using the same table name?

If so is there a way around this?

Many thanks
NigelGlobal temp tables are visible to all connections, so if you have
multiple processes, then you can easily get errors like that. And if
you drop the table explicitly, one connection could drop the table
which was created by another connection.

Is there some specific reason why you can't use a local temp table? Or
perhaps you can use a permanent table, with @.@.SPID as part of the key?
If you can explain some more about what you're trying to do, then
someone may be able to suggest an alternative solution.

Simon|||Thanks Simon.
So If I use # instead of ## the table is connection based rather than
global. Id did sort of know that but you have made it clear. I will
change to # instead of ##.

The situation is that I have a website that runs an SProc to get the
content of a banner. This runs for most pages on the site.
nigel