Disk I/O

I would like to confirm what Perfmon counters I should be looking at to esta
blish if the system running our Production Instance of SQL Server, has an I/
O bottleneck. I have been focusing on Current Disk Queue Length counter whic
h during peak times (a 2.5
hour interval) peaks around 200+ for extended intervals. We have storage on
a SAN (EMC disk array with RAID 10). The databases reside (both data and tra
nsaction logs) on a single logical drive that comprises of 4 physical disks
on the EMC. Current queue l
ength of 200+ (for extended periods of time) seems like a problem, but Micro
soft expert says that we should be looking at Avg Queue Length counter. I ne
ed to know what counters can help us determine if in fact there is I/O bottl
eneck
Thanks, ShehlaI agree that looking at average disk queue might give a better overall
picture but if your getting current disk queues of 200+ for extended periods
of time that will be an issue. You should also check with who ever
maintains the SAN to see what tools they have for monitoring the disk
activity.
Andrew J. Kelly SQL MVP
"Shehla Arshad" <anonymous@.discussions.microsoft.com> wrote in message
news:D46831D9-D706-4944-ADC4-FEBCBE13CFE8@.microsoft.com...
> I would like to confirm what Perfmon counters I should be looking at to
establish if the system running our Production Instance of SQL Server, has
an I/O bottleneck. I have been focusing on Current Disk Queue Length counter
which during peak times (a 2.5 hour interval) peaks around 200+ for extended
intervals. We have storage on a SAN (EMC disk array with RAID 10). The
databases reside (both data and transaction logs) on a single logical drive
that comprises of 4 physical disks on the EMC. Current queue length of 200+
(for extended periods of time) seems like a problem, but Microsoft expert
says that we should be looking at Avg Queue Length counter. I need to know
what counters can help us determine if in fact there is I/O bottleneck
> Thanks, Shehla|||In addition to Andrew's comments... average disk q is better, and average
queue > 2 is bad... However the number that comes from perfmon is for the
drive letter... you must divide the perfmon number (200) by the number of
physical drives behind the letter, (4), so your average queueing is still
50( way too high.)
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"Shehla Arshad" <anonymous@.discussions.microsoft.com> wrote in message
news:D46831D9-D706-4944-ADC4-FEBCBE13CFE8@.microsoft.com...
> I would like to confirm what Perfmon counters I should be looking at to
establish if the system running our Production Instance of SQL Server, has
an I/O bottleneck. I have been focusing on Current Disk Queue Length counter
which during peak times (a 2.5 hour interval) peaks around 200+ for extended
intervals. We have storage on a SAN (EMC disk array with RAID 10). The
databases reside (both data and transaction logs) on a single logical drive
that comprises of 4 physical disks on the EMC. Current queue length of 200+
(for extended periods of time) seems like a problem, but Microsoft expert
says that we should be looking at Avg Queue Length counter. I need to know
what counters can help us determine if in fact there is I/O bottleneck
> Thanks, Shehla

Disk I/O

I would like to confirm what Perfmon counters I should be looking at to establish if the system running our Production Instance of SQL Server, has an I/O bottleneck. I have been focusing on Current Disk Queue Length counter which during peak times (a 2.5 hour interval) peaks around 200+ for extended intervals. We have storage on a SAN (EMC disk array with RAID 10). The databases reside (both data and transaction logs) on a single logical drive that comprises of 4 physical disks on the EMC. Current queue length of 200+ (for extended periods of time) seems like a problem, but Microsoft expert says that we should be looking at Avg Queue Length counter. I need to know what counters can help us determine if in fact there is I/O bottlenec
Thanks, ShehlaI agree that looking at average disk queue might give a better overall
picture but if your getting current disk queues of 200+ for extended periods
of time that will be an issue. You should also check with who ever
maintains the SAN to see what tools they have for monitoring the disk
activity.
--
Andrew J. Kelly SQL MVP
"Shehla Arshad" <anonymous@.discussions.microsoft.com> wrote in message
news:D46831D9-D706-4944-ADC4-FEBCBE13CFE8@.microsoft.com...
> I would like to confirm what Perfmon counters I should be looking at to
establish if the system running our Production Instance of SQL Server, has
an I/O bottleneck. I have been focusing on Current Disk Queue Length counter
which during peak times (a 2.5 hour interval) peaks around 200+ for extended
intervals. We have storage on a SAN (EMC disk array with RAID 10). The
databases reside (both data and transaction logs) on a single logical drive
that comprises of 4 physical disks on the EMC. Current queue length of 200+
(for extended periods of time) seems like a problem, but Microsoft expert
says that we should be looking at Avg Queue Length counter. I need to know
what counters can help us determine if in fact there is I/O bottleneck
> Thanks, Shehla|||In addition to Andrew's comments... average disk q is better, and average
queue > 2 is bad... However the number that comes from perfmon is for the
drive letter... you must divide the perfmon number (200) by the number of
physical drives behind the letter, (4), so your average queueing is still
50( way too high.)
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"Shehla Arshad" <anonymous@.discussions.microsoft.com> wrote in message
news:D46831D9-D706-4944-ADC4-FEBCBE13CFE8@.microsoft.com...
> I would like to confirm what Perfmon counters I should be looking at to
establish if the system running our Production Instance of SQL Server, has
an I/O bottleneck. I have been focusing on Current Disk Queue Length counter
which during peak times (a 2.5 hour interval) peaks around 200+ for extended
intervals. We have storage on a SAN (EMC disk array with RAID 10). The
databases reside (both data and transaction logs) on a single logical drive
that comprises of 4 physical disks on the EMC. Current queue length of 200+
(for extended periods of time) seems like a problem, but Microsoft expert
says that we should be looking at Avg Queue Length counter. I need to know
what counters can help us determine if in fact there is I/O bottleneck
> Thanks, Shehlasql

DisallowAdhocAccess or DisallowAdHocAccess ?

Greetings,
My understanding is that registry items are case sensitive. Please confirm.
If registry entries are case sensitive and I want to use manually disable ad
hoc
access then which key should I use (I've seen both forms on MS and in
e-mails)?
DisallowAdhocAccess or DisallowAdHocAccess
Thanks,
StanI don't think it matters - I've seen it both ways as well.
The Knowledge Base article for the addressing ad hoc issues
has it listed as DisallowAdHocAccess:
http://support.microsoft.com/?id=327489
One easy way to deal with the issue is to make sure you are
on SP3 for SQL Server 2000. With that service pack, the
absence of the key would mean that ad hoc access is not
allowed. Consequently, you don't need to create the key to
disallow ad hoc access if you are running SQL 2000 SP3. If
one exists, you need to make sure it's set to 0.
-Sue
On Fri, 19 Nov 2004 08:03:00 -0500, "Stan Kasper"
<kasper@.wharton.upenn.edu> wrote:

>Greetings,
>My understanding is that registry items are case sensitive. Please confirm
.
>If registry entries are case sensitive and I want to use manually disable a
d
>hoc
>access then which key should I use (I've seen both forms on MS and in
>e-mails)?
>DisallowAdhocAccess or DisallowAdHocAccess
>
>Thanks,
>Stan
>