db performance on san mapped to one drive

I'm working on a performance problem on a SQL server 2000 W2K SP4
Active / Active cluster. The primary database (bigdb) is 140 Gb and
has 1200 users. Looking at perfmon I am suspecting an I/O bottleneck.
The 8 CPUs in the system run all under 50%, and the 4 Gb RAM shows
only 3.2 Gb in use. I'm an old Windows guy, but new to SQL
performance tuning. The system is all up to par with patches etc.
The disk is a screaming fast SAN with great cache. The databases are
all on a single drive mapped to H: for this instance. Here's some
results from a ~20 hr perfmon analysis with ~4500 values of drive H:
CounterAverageMinMax
Disk Bytes / sec: 4,406,00052,00039,912,000
Disk Queue Length:3.500255
%Disk Time:3440.1215,500
So, I'm wondering if separating the databases onto different drive
letters would help disk time and queue length. For example: tempdb on
H:, bigdb-data on I:, bigdb-index on J:, bigdb-logs on K: and master
etc. on J:. All of these would still on the same san fiber, but maybe
W2K would do a better job managing the data using multiple disks to
the same SAN? Thoughts?
Are there any tuning tweeks I can make to W2K to give more priority to
the SQL SAN drive? The internal drives are not have any issues...
Hi
The disk queues do not look too good.
What type of SAN is it? EMC? Depending on the SAN technology, the disk
layout can play a big role. RAID-10 preferably.
It does not help splitting up the volume into different volumes / drive
letters, unless the volumes are going to be on different spindles.
The Transaction log writes are generally sequential, whilst the database is
usually random. Having the same disks try to service both types of requests
leads to terrible disk queue lengths.
The OS can't do much more to help the SAN drives, they are the ones not
coping.
With 4GB or RAM and 32 Bit OS, SQL Server can only use up to 3GB and the OS
gets the other 1GB (assuming /3GB is in boot.ini) Considering the load on
the server, 4GB is a bit little. 8GB would start to sound right, 16 might
become very sweet. With more RAM, SQL Server can cache more, resulting in
less reads being required. In effect, you have too many processors for the
RAM.
There are other factors that can bury the best hardware. Bad database and
index design, and terrible queries. e.g bad indexing results in SQL Server
having to read though all the data in a table, this causes more read
requests. this slows down writes, which in turn slow down reads...on and on
and on.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<paulaner@.iname.com> wrote in message
news:0hdka111vmelvphc86a7rrpcopjpdjt8d9@.4ax.com...
> I'm working on a performance problem on a SQL server 2000 W2K SP4
> Active / Active cluster. The primary database (bigdb) is 140 Gb and
> has 1200 users. Looking at perfmon I am suspecting an I/O bottleneck.
> The 8 CPUs in the system run all under 50%, and the 4 Gb RAM shows
> only 3.2 Gb in use. I'm an old Windows guy, but new to SQL
> performance tuning. The system is all up to par with patches etc.
> The disk is a screaming fast SAN with great cache. The databases are
> all on a single drive mapped to H: for this instance. Here's some
> results from a ~20 hr perfmon analysis with ~4500 values of drive H:
> Counter Average Min Max
> Disk Bytes / sec: 4,406,000 52,000 39,912,000
> Disk Queue Length: 3.50 0 255
> %Disk Time: 344 0.12 15,500
> So, I'm wondering if separating the databases onto different drive
> letters would help disk time and queue length. For example: tempdb on
> H:, bigdb-data on I:, bigdb-index on J:, bigdb-logs on K: and master
> etc. on J:. All of these would still on the same san fiber, but maybe
> W2K would do a better job managing the data using multiple disks to
> the same SAN? Thoughts?
> Are there any tuning tweeks I can make to W2K to give more priority to
> the SQL SAN drive? The internal drives are not have any issues...
>
>

DB Maintenance Plan failure

Good morning all...
SQL Server 2000, SP3 running on a W2K Server Cluster.
All other DB Maint Plans run fine (12 DBs). But on one,
we get the following error:
"[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934:
[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC
failed because the following SET options have incorrect
settings: 'QUOTED_IDENTIFIER'
I've searched and read everything I can find, but nothing
seems to be "our" problem. Suggestions ranged from
priviledge accounts to "are you running SP3".
Anyone seen this and/or have suggestions?
Thanks,
TonyGood afternoon Tony,
This error can occur if you have indexed views in your database, and you
have the "Reorganize indexes and data pages"check box checked in your
maintenance plan. Indexed views require a number of options to be set in a
certain way when they are build or reindexed, and you have to make sure that
the login under which the maintenance plan is running has them set
correctly. The two options most likely to be set wrongly are
QUOTED_IDENTIFIER and ARITH_ABORT, which should both be ON.
--
Jacco Schalkwijk
SQL Server MVP
"Tony Bowman" <anonymous@.discussions.microsoft.com> wrote in message
news:e72901c3f0b9$664ca1a0$a001280a@.phx.gbl...
> Good morning all...
> SQL Server 2000, SP3 running on a W2K Server Cluster.
> All other DB Maint Plans run fine (12 DBs). But on one,
> we get the following error:
> "[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934:
> [Microsoft][ODBC SQL Server Driver][SQL Server]DBCC
> failed because the following SET options have incorrect
> settings: 'QUOTED_IDENTIFIER'
> I've searched and read everything I can find, but nothing
> seems to be "our" problem. Suggestions ranged from
> priviledge accounts to "are you running SP3".
> Anyone seen this and/or have suggestions?
> Thanks,
> Tony|||Unless they fixed something I don't think this is something you can adjust.
I believe the only way around it is to create your own scheduled job that
does the reorgs and don't use the MP.
--
Andrew J. Kelly
SQL Server MVP
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:u0QDhwL8DHA.2796@.TK2MSFTNGP09.phx.gbl...
> Good afternoon Tony,
> This error can occur if you have indexed views in your database, and you
> have the "Reorganize indexes and data pages"check box checked in your
> maintenance plan. Indexed views require a number of options to be set in a
> certain way when they are build or reindexed, and you have to make sure
that
> the login under which the maintenance plan is running has them set
> correctly. The two options most likely to be set wrongly are
> QUOTED_IDENTIFIER and ARITH_ABORT, which should both be ON.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Tony Bowman" <anonymous@.discussions.microsoft.com> wrote in message
> news:e72901c3f0b9$664ca1a0$a001280a@.phx.gbl...
> > Good morning all...
> > SQL Server 2000, SP3 running on a W2K Server Cluster.
> > All other DB Maint Plans run fine (12 DBs). But on one,
> > we get the following error:
> > "[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934:
> > [Microsoft][ODBC SQL Server Driver][SQL Server]DBCC
> > failed because the following SET options have incorrect
> > settings: 'QUOTED_IDENTIFIER'
> >
> > I've searched and read everything I can find, but nothing
> > seems to be "our" problem. Suggestions ranged from
> > priviledge accounts to "are you running SP3".
> >
> > Anyone seen this and/or have suggestions?
> >
> > Thanks,
> > Tony
>

DB Maintenance Plan failure

Good morning all...
SQL Server 2000, SP3 running on a W2K Server Cluster.
All other DB Maint Plans run fine (12 DBs). But on one,
we get the following error:
"[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934:
[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC
failed because the following SET options have incorrect
settings: 'QUOTED_IDENTIFIER'
I've searched and read everything I can find, but nothing
seems to be "our" problem. Suggestions ranged from
priviledge accounts to "are you running SP3".
Anyone seen this and/or have suggestions?
Thanks,
TonyGood afternoon Tony,
This error can occur if you have indexed views in your database, and you
have the "Reorganize indexes and data pages"check box checked in your
maintenance plan. Indexed views require a number of options to be set in a
certain way when they are build or reindexed, and you have to make sure that
the login under which the maintenance plan is running has them set
correctly. The two options most likely to be set wrongly are
QUOTED_IDENTIFIER and ARITH_ABORT, which should both be ON.
Jacco Schalkwijk
SQL Server MVP
"Tony Bowman" <anonymous@.discussions.microsoft.com> wrote in message
news:e72901c3f0b9$664ca1a0$a001280a@.phx.gbl...
> Good morning all...
> SQL Server 2000, SP3 running on a W2K Server Cluster.
> All other DB Maint Plans run fine (12 DBs). But on one,
> we get the following error:
> "[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934:
> [Microsoft][ODBC SQL Server Driver][SQL Server]DBCC
> failed because the following SET options have incorrect
> settings: 'QUOTED_IDENTIFIER'
> I've searched and read everything I can find, but nothing
> seems to be "our" problem. Suggestions ranged from
> priviledge accounts to "are you running SP3".
> Anyone seen this and/or have suggestions?
> Thanks,
> Tony|||Unless they fixed something I don't think this is something you can adjust.
I believe the only way around it is to create your own scheduled job that
does the reorgs and don't use the MP.
Andrew J. Kelly
SQL Server MVP
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:u0QDhwL8DHA.2796@.TK2MSFTNGP09.phx.gbl...
> Good afternoon Tony,
> This error can occur if you have indexed views in your database, and you
> have the "Reorganize indexes and data pages"check box checked in your
> maintenance plan. Indexed views require a number of options to be set in a
> certain way when they are build or reindexed, and you have to make sure
that
> the login under which the maintenance plan is running has them set
> correctly. The two options most likely to be set wrongly are
> QUOTED_IDENTIFIER and ARITH_ABORT, which should both be ON.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Tony Bowman" <anonymous@.discussions.microsoft.com> wrote in message
> news:e72901c3f0b9$664ca1a0$a001280a@.phx.gbl...
>