a lot of our users get Sql Server timeout errors from what appear to be
pretty basic queries run from our web app. the server should be well
equipped hardware wise to deal with server load (dual proc xeon with nearly
4 gb ram raid 5). concurrent user connections average around 300; it handles
an average of 4 transactions a second. running the queries themselves via QA
in some cases takes an extremely long time. otehr times, they run in a
timely fashion.
I notice on perfmon a something that raised an eyebrow - the database is
spread over 2 filegroups, primary is about 4.5GB, secondary index filegroup
is about 2.3 GB. but we are getting very high disk queue values - averaging
50 over an hour or so.
While I know that as general rule of thum the threshold for disk queue is 2
* physical drives, 2 of the drives are hardly used. One drive contains only
backups, the second contains Diskeeper files, the 3rd is the OS drive. In
theory, only 2 of the drives are being written to during normal database
usage.
My question - is THIS normal (can I rule out the queue as a bottleneck),
given that we arent separating the filegroups over the RAID?I notice the system gets tweaked during Page Reads... probably indexes then?
"Ick" <nospamplease> wrote in message
news:uFZMAsphDHA.4088@.tk2msftngp13.phx.gbl...
> a lot of our users get Sql Server timeout errors from what appear to be
> pretty basic queries run from our web app. the server should be well
> equipped hardware wise to deal with server load (dual proc xeon with
nearly
> 4 gb ram raid 5). concurrent user connections average around 300; it
handles
> an average of 4 transactions a second. running the queries themselves via
QA
> in some cases takes an extremely long time. otehr times, they run in a
> timely fashion.
> I notice on perfmon a something that raised an eyebrow - the database is
> spread over 2 filegroups, primary is about 4.5GB, secondary index
filegroup
> is about 2.3 GB. but we are getting very high disk queue values -
averaging
> 50 over an hour or so.
> While I know that as general rule of thum the threshold for disk queue is
2
> * physical drives, 2 of the drives are hardly used. One drive contains
only
> backups, the second contains Diskeeper files, the 3rd is the OS drive. In
> theory, only 2 of the drives are being written to during normal database
> usage.
> My question - is THIS normal (can I rule out the queue as a bottleneck),
> given that we arent separating the filegroups over the RAID?
>
>
Showing posts with label appear. Show all posts
Showing posts with label appear. Show all posts
Wednesday, March 21, 2012
Friday, March 9, 2012
Performance on SQL 2000
I have a SQL server with one file group that's about 600GB and two files
residing on a clariion array. Index jobs, etc. appear to run faster if I
create more files. I looked at some of microsoft's tpc server benchmarks and
see that they often break up the database into a number of files with 200GB
as the max file size I've seen.
Does anyone out there know of any articles, web sites or whatever that
specifically address this? Are users out there creating large numbers of
files and I'm in the dark? Articles by reputable sources (K. Delaney among
just a few) are what I'm looking for.
--
burt_king@.yahoo.comOK, since no one answered I'll beg for conjecture, random thoughts or
otherwise...
--
burt_king@.yahoo.com
"burt_king" wrote:
> I have a SQL server with one file group that's about 600GB and two files
> residing on a clariion array. Index jobs, etc. appear to run faster if I
> create more files. I looked at some of microsoft's tpc server benchmarks and
> see that they often break up the database into a number of files with 200GB
> as the max file size I've seen.
> Does anyone out there know of any articles, web sites or whatever that
> specifically address this? Are users out there creating large numbers of
> files and I'm in the dark? Articles by reputable sources (K. Delaney among
> just a few) are what I'm looking for.
> --
> burt_king@.yahoo.com|||It's generally not about more file but more disk spindles. When parts of
the database reside on different disk drives, seeks can be done in parallel
so the overall performance is improved. There are a few cases where
multiple files allow parallel operations which may help performance but this
generally is not as big a factor as splitting logs, indexes and data into
different disks.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"burt_king" <burt_king@.yahoo.com> wrote in message
news:6040A038-EB9E-4D03-8023-BA5A79A8F469@.microsoft.com...
> OK, since no one answered I'll beg for conjecture, random thoughts or
> otherwise...
> --
> burt_king@.yahoo.com
>
> "burt_king" wrote:
>> I have a SQL server with one file group that's about 600GB and two files
>> residing on a clariion array. Index jobs, etc. appear to run faster if
>> I
>> create more files. I looked at some of microsoft's tpc server benchmarks
>> and
>> see that they often break up the database into a number of files with
>> 200GB
>> as the max file size I've seen.
>> Does anyone out there know of any articles, web sites or whatever that
>> specifically address this? Are users out there creating large numbers
>> of
>> files and I'm in the dark? Articles by reputable sources (K. Delaney
>> among
>> just a few) are what I'm looking for.
>> --
>> burt_king@.yahoo.com
residing on a clariion array. Index jobs, etc. appear to run faster if I
create more files. I looked at some of microsoft's tpc server benchmarks and
see that they often break up the database into a number of files with 200GB
as the max file size I've seen.
Does anyone out there know of any articles, web sites or whatever that
specifically address this? Are users out there creating large numbers of
files and I'm in the dark? Articles by reputable sources (K. Delaney among
just a few) are what I'm looking for.
--
burt_king@.yahoo.comOK, since no one answered I'll beg for conjecture, random thoughts or
otherwise...
--
burt_king@.yahoo.com
"burt_king" wrote:
> I have a SQL server with one file group that's about 600GB and two files
> residing on a clariion array. Index jobs, etc. appear to run faster if I
> create more files. I looked at some of microsoft's tpc server benchmarks and
> see that they often break up the database into a number of files with 200GB
> as the max file size I've seen.
> Does anyone out there know of any articles, web sites or whatever that
> specifically address this? Are users out there creating large numbers of
> files and I'm in the dark? Articles by reputable sources (K. Delaney among
> just a few) are what I'm looking for.
> --
> burt_king@.yahoo.com|||It's generally not about more file but more disk spindles. When parts of
the database reside on different disk drives, seeks can be done in parallel
so the overall performance is improved. There are a few cases where
multiple files allow parallel operations which may help performance but this
generally is not as big a factor as splitting logs, indexes and data into
different disks.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"burt_king" <burt_king@.yahoo.com> wrote in message
news:6040A038-EB9E-4D03-8023-BA5A79A8F469@.microsoft.com...
> OK, since no one answered I'll beg for conjecture, random thoughts or
> otherwise...
> --
> burt_king@.yahoo.com
>
> "burt_king" wrote:
>> I have a SQL server with one file group that's about 600GB and two files
>> residing on a clariion array. Index jobs, etc. appear to run faster if
>> I
>> create more files. I looked at some of microsoft's tpc server benchmarks
>> and
>> see that they often break up the database into a number of files with
>> 200GB
>> as the max file size I've seen.
>> Does anyone out there know of any articles, web sites or whatever that
>> specifically address this? Are users out there creating large numbers
>> of
>> files and I'm in the dark? Articles by reputable sources (K. Delaney
>> among
>> just a few) are what I'm looking for.
>> --
>> burt_king@.yahoo.com
Performance on SQL 2000
I have a SQL server with one file group that's about 600GB and two files
residing on a clariion array. Index jobs, etc. appear to run faster if I
create more files. I looked at some of microsoft's tpc server benchmarks an
d
see that they often break up the database into a number of files with 200GB
as the max file size I've seen.
Does anyone out there know of any articles, web sites or whatever that
specifically address this? Are users out there creating large numbers of
files and I'm in the dark? Articles by reputable sources (K. Delaney among
just a few) are what I'm looking for.
--
burt_king@.yahoo.comOK, since no one answered I'll beg for conjecture, random thoughts or
otherwise...
--
burt_king@.yahoo.com
"burt_king" wrote:
> I have a SQL server with one file group that's about 600GB and two files
> residing on a clariion array. Index jobs, etc. appear to run faster if I
> create more files. I looked at some of microsoft's tpc server benchmarks
and
> see that they often break up the database into a number of files with 200G
B
> as the max file size I've seen.
> Does anyone out there know of any articles, web sites or whatever that
> specifically address this? Are users out there creating large numbers of
> files and I'm in the dark? Articles by reputable sources (K. Delaney amo
ng
> just a few) are what I'm looking for.
> --
> burt_king@.yahoo.com|||It's generally not about more file but more disk spindles. When parts of
the database reside on different disk drives, seeks can be done in parallel
so the overall performance is improved. There are a few cases where
multiple files allow parallel operations which may help performance but this
generally is not as big a factor as splitting logs, indexes and data into
different disks.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"burt_king" <burt_king@.yahoo.com> wrote in message
news:6040A038-EB9E-4D03-8023-BA5A79A8F469@.microsoft.com...[vbcol=seagreen]
> OK, since no one answered I'll beg for conjecture, random thoughts or
> otherwise...
> --
> burt_king@.yahoo.com
>
> "burt_king" wrote:
>
residing on a clariion array. Index jobs, etc. appear to run faster if I
create more files. I looked at some of microsoft's tpc server benchmarks an
d
see that they often break up the database into a number of files with 200GB
as the max file size I've seen.
Does anyone out there know of any articles, web sites or whatever that
specifically address this? Are users out there creating large numbers of
files and I'm in the dark? Articles by reputable sources (K. Delaney among
just a few) are what I'm looking for.
--
burt_king@.yahoo.comOK, since no one answered I'll beg for conjecture, random thoughts or
otherwise...
--
burt_king@.yahoo.com
"burt_king" wrote:
> I have a SQL server with one file group that's about 600GB and two files
> residing on a clariion array. Index jobs, etc. appear to run faster if I
> create more files. I looked at some of microsoft's tpc server benchmarks
and
> see that they often break up the database into a number of files with 200G
B
> as the max file size I've seen.
> Does anyone out there know of any articles, web sites or whatever that
> specifically address this? Are users out there creating large numbers of
> files and I'm in the dark? Articles by reputable sources (K. Delaney amo
ng
> just a few) are what I'm looking for.
> --
> burt_king@.yahoo.com|||It's generally not about more file but more disk spindles. When parts of
the database reside on different disk drives, seeks can be done in parallel
so the overall performance is improved. There are a few cases where
multiple files allow parallel operations which may help performance but this
generally is not as big a factor as splitting logs, indexes and data into
different disks.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"burt_king" <burt_king@.yahoo.com> wrote in message
news:6040A038-EB9E-4D03-8023-BA5A79A8F469@.microsoft.com...[vbcol=seagreen]
> OK, since no one answered I'll beg for conjecture, random thoughts or
> otherwise...
> --
> burt_king@.yahoo.com
>
> "burt_king" wrote:
>
Subscribe to:
Comments (Atom)