Poor SQL Server Performance on High End System

microsoft.public.sqlserver.server

14 Nov 2007 5:50 PM

Jimmy

I have a dual core xeon 3.0 ghz system with a dell DAS disk array that
contains the following disk layout:

Status Name State Read Cache Write Cache Cache Policy Layout Size

Used for Data Files

Virtual Disk 2 Ready Enabled-Read Ahead Adaptive Write Back Direct I/O
RAID 10 838.12 GB

Used for Transaction Log Files

Virtual Disk 1 Ready Enabled-Read Ahead Adaptive Write Back Direct I/O
RAID 5 1,676.25 GB

The hardware is:

OS Name    Microsoft(R) Windows(R) Server 2003, Standard Edition
Version    5.2.3790 Service Pack 2 Build 3790
Other OS Description     Not Available
OS Manufacturer    Microsoft Corporation
System Name    --
System Manufacturer    Dell Computer Corporation
System Model    PowerEdge 1850
System Type    X86-based PC
Processor    x86 Family 15 Model 4 Stepping 3 GenuineIntel ~2992 Mhz
Processor    x86 Family 15 Model 4 Stepping 3 GenuineIntel ~2992 Mhz
Processor    x86 Family 15 Model 4 Stepping 3 GenuineIntel ~2992 Mhz
Processor    x86 Family 15 Model 4 Stepping 3 GenuineIntel ~2992 Mhz
BIOS Version/Date    Dell Computer Corporation A04, 9/22/2005
SMBIOS Version    2.3
Windows Directory    C:\WINDOWS
System Directory    C:\WINDOWS\system32
Boot Device    \Device\HarddiskVolume2
Locale    United States
Hardware Abstraction Layer    Version = "5.2.3790.3959
(srv03_sp2_rtm.070216-1710)"
User Name    Not Available
Time Zone    Central Standard Time
Total Physical Memory    2,047.27 MB
Available Physical Memory    158.00 MB
Total Virtual Memory    3.85 GB
Available Virtual Memory    1.98 GB
Page File Space    2.00 GB
Page File    C:\pagefile.sys

My problem is queries take a long time to run... of course, there's a lot of
data in there, and as far as I know, the indexes are somewhat optimal, I've
spent alot of time tuning them to get them to be somewhat acceptable in terms
of performance, but I can't help feel I'm missing something that's really
slowing SQL server down as a whole..

My disk queue length is mostly 0, and my processor usage is 1-2%, but
queries still take 30 seconds to 2 minutes to return, and running batches of
sproc commands yield about 6 batches per second.. sometimes this number goes
up to 30, sometimes it goes down to 1, but all the while, the processor and
disk queue remain low...

Any suggestions?

14 Nov 2007 6:52 PM

Linchi Shea

Poor performance as compared to what? Did it run faster on a different
platform? Note that a Dell PowerEdge 1850 can hardly be considered as high
end.

Linchi

Show quoteHide quote

"Jimmy" wrote:

> I have a dual core xeon 3.0 ghz system with a dell DAS disk array that
> contains the following disk layout:
>
> Status Name State Read Cache Write Cache Cache Policy Layout Size
>
> Used for Data Files
>
>   Virtual Disk 2 Ready Enabled-Read Ahead Adaptive Write Back Direct I/O
> RAID 10 838.12 GB
>
>
> Used for Transaction Log Files
>
>   Virtual Disk 1 Ready Enabled-Read Ahead Adaptive Write Back Direct I/O
> RAID 5 1,676.25 GB
>
> The hardware is:
>
> OS Name    Microsoft(R) Windows(R) Server 2003, Standard Edition
> Version    5.2.3790 Service Pack 2 Build 3790
> Other OS Description     Not Available
> OS Manufacturer    Microsoft Corporation
> System Name    --
> System Manufacturer    Dell Computer Corporation
> System Model    PowerEdge 1850
> System Type    X86-based PC
> Processor    x86 Family 15 Model 4 Stepping 3 GenuineIntel ~2992 Mhz
> Processor    x86 Family 15 Model 4 Stepping 3 GenuineIntel ~2992 Mhz
> Processor    x86 Family 15 Model 4 Stepping 3 GenuineIntel ~2992 Mhz
> Processor    x86 Family 15 Model 4 Stepping 3 GenuineIntel ~2992 Mhz
> BIOS Version/Date    Dell Computer Corporation A04, 9/22/2005
> SMBIOS Version    2.3
> Windows Directory    C:\WINDOWS
> System Directory    C:\WINDOWS\system32
> Boot Device    \Device\HarddiskVolume2
> Locale    United States
> Hardware Abstraction Layer    Version = "5.2.3790.3959
> (srv03_sp2_rtm.070216-1710)"
> User Name    Not Available
> Time Zone    Central Standard Time
> Total Physical Memory    2,047.27 MB
> Available Physical Memory    158.00 MB
> Total Virtual Memory    3.85 GB
> Available Virtual Memory    1.98 GB
> Page File Space    2.00 GB
> Page File    C:\pagefile.sys
>
> My problem is queries take a long time to run... of course, there's a lot of
> data in there, and as far as I know, the indexes are somewhat optimal, I've
> spent alot of time tuning them to get them to be somewhat acceptable in terms
> of performance, but I can't help feel I'm missing something that's really
> slowing SQL server down as a whole..
>
> My disk queue length is mostly 0, and my processor usage is 1-2%, but
> queries still take 30 seconds to 2 minutes to return, and running batches of
> sproc commands yield about 6 batches per second.. sometimes this number goes
> up to 30, sometimes it goes down to 1, but all the while, the processor and
> disk queue remain low...
>
> Any suggestions?

Are all your drivers up to date? click for free checkup

14 Nov 2007 8:04 PM

Gert-Jan Strik

Well, I guess you should analyze the slow running queries.

One particular thing to check out (since your performance counters seem
to be almost flat) is if there is excessive blocking. You could use
sp_who2 for this.

--
Gert-jan

Jimmy wrote:

Show quoteHide quote

>
> I have a dual core xeon 3.0 ghz system with a dell DAS disk array that
> contains the following disk layout:
>
> Status Name State Read Cache Write Cache Cache Policy Layout Size
>
> Used for Data Files
>
>   Virtual Disk 2 Ready Enabled-Read Ahead Adaptive Write Back Direct I/O
> RAID 10 838.12 GB
>
> Used for Transaction Log Files
>
>   Virtual Disk 1 Ready Enabled-Read Ahead Adaptive Write Back Direct I/O
> RAID 5 1,676.25 GB
>
> The hardware is:
>
> OS Name Microsoft(R) Windows(R) Server 2003, Standard Edition
> Version 5.2.3790 Service Pack 2 Build 3790
> Other OS Description    Not Available
> OS Manufacturer Microsoft Corporation
> System Name     --
> System Manufacturer     Dell Computer Corporation
> System Model    PowerEdge 1850
> System Type     X86-based PC
> Processor       x86 Family 15 Model 4 Stepping 3 GenuineIntel ~2992 Mhz
> Processor       x86 Family 15 Model 4 Stepping 3 GenuineIntel ~2992 Mhz
> Processor       x86 Family 15 Model 4 Stepping 3 GenuineIntel ~2992 Mhz
> Processor       x86 Family 15 Model 4 Stepping 3 GenuineIntel ~2992 Mhz
> BIOS Version/Date       Dell Computer Corporation A04, 9/22/2005
> SMBIOS Version 2.3
> Windows Directory       C:\WINDOWS
> System Directory        C:\WINDOWS\system32
> Boot Device     \Device\HarddiskVolume2
> Locale United States
> Hardware Abstraction Layer      Version = "5.2.3790.3959
> (srv03_sp2_rtm.070216-1710)"
> User Name       Not Available
> Time Zone       Central Standard Time
> Total Physical Memory   2,047.27 MB
> Available Physical Memory       158.00 MB
> Total Virtual Memory    3.85 GB
> Available Virtual Memory        1.98 GB
> Page File Space 2.00 GB
> Page File       C:\pagefile.sys
>
> My problem is queries take a long time to run... of course, there's a lot of
> data in there, and as far as I know, the indexes are somewhat optimal, I've
> spent alot of time tuning them to get them to be somewhat acceptable in terms
> of performance, but I can't help feel I'm missing something that's really
> slowing SQL server down as a whole..
>
> My disk queue length is mostly 0, and my processor usage is 1-2%, but
> queries still take 30 seconds to 2 minutes to return, and running batches of
> sproc commands yield about 6 batches per second.. sometimes this number goes
> up to 30, sometimes it goes down to 1, but all the while, the processor and
> disk queue remain low...
>
> Any suggestions?

14 Nov 2007 8:57 PM

Jimmy

Well.. compared to itself really.. sorry if I wasn't clear on that.. if it
sometimes goes at 30 executions per second, and then drops to 1 eps, then
goes back to 15 and jumps around like that, I would really like to know a way
to find out what's causing it to not be faster.. it doesn't seem like the
disk, and it doesn't seem like the processor since both counters are near
zero for utilization..

Show quoteHide quote

"Linchi Shea" wrote:

> Poor performance as compared to what? Did it run faster on a different
> platform? Note that a Dell PowerEdge 1850 can hardly be considered as high
> end.
>
> Linchi
>
> "Jimmy" wrote:
>
> > I have a dual core xeon 3.0 ghz system with a dell DAS disk array that
> > contains the following disk layout:
> >
> > Status Name State Read Cache Write Cache Cache Policy Layout Size
> >
> > Used for Data Files
> >
> >   Virtual Disk 2 Ready Enabled-Read Ahead Adaptive Write Back Direct I/O
> > RAID 10 838.12 GB
> >
> >
> > Used for Transaction Log Files
> >
> >   Virtual Disk 1 Ready Enabled-Read Ahead Adaptive Write Back Direct I/O
> > RAID 5 1,676.25 GB
> >
> > The hardware is:
> >
> > OS Name    Microsoft(R) Windows(R) Server 2003, Standard Edition
> > Version    5.2.3790 Service Pack 2 Build 3790
> > Other OS Description     Not Available
> > OS Manufacturer    Microsoft Corporation
> > System Name    --
> > System Manufacturer    Dell Computer Corporation
> > System Model    PowerEdge 1850
> > System Type    X86-based PC
> > Processor    x86 Family 15 Model 4 Stepping 3 GenuineIntel ~2992 Mhz
> > Processor    x86 Family 15 Model 4 Stepping 3 GenuineIntel ~2992 Mhz
> > Processor    x86 Family 15 Model 4 Stepping 3 GenuineIntel ~2992 Mhz
> > Processor    x86 Family 15 Model 4 Stepping 3 GenuineIntel ~2992 Mhz
> > BIOS Version/Date    Dell Computer Corporation A04, 9/22/2005
> > SMBIOS Version    2.3
> > Windows Directory    C:\WINDOWS
> > System Directory    C:\WINDOWS\system32
> > Boot Device    \Device\HarddiskVolume2
> > Locale    United States
> > Hardware Abstraction Layer    Version = "5.2.3790.3959
> > (srv03_sp2_rtm.070216-1710)"
> > User Name    Not Available
> > Time Zone    Central Standard Time
> > Total Physical Memory    2,047.27 MB
> > Available Physical Memory    158.00 MB
> > Total Virtual Memory    3.85 GB
> > Available Virtual Memory    1.98 GB
> > Page File Space    2.00 GB
> > Page File    C:\pagefile.sys
> >
> > My problem is queries take a long time to run... of course, there's a lot of
> > data in there, and as far as I know, the indexes are somewhat optimal, I've
> > spent alot of time tuning them to get them to be somewhat acceptable in terms
> > of performance, but I can't help feel I'm missing something that's really
> > slowing SQL server down as a whole..
> >
> > My disk queue length is mostly 0, and my processor usage is 1-2%, but
> > queries still take 30 seconds to 2 minutes to return, and running batches of
> > sproc commands yield about 6 batches per second.. sometimes this number goes
> > up to 30, sometimes it goes down to 1, but all the while, the processor and
> > disk queue remain low...
> >
> > Any suggestions?

14 Nov 2007 9:01 PM

Jimmy

I don't see any listed as blocked... I'm not sure if this is significant, but
the procedure is running right now 8 times a second... and in the sp_who2
list, it's status is "SLEEPING".. the command, cputime and diskio are:
INSERT, 1232172, 2899292

Show quoteHide quote

"Gert-Jan Strik" wrote:

> Well, I guess you should analyze the slow running queries.
>
> One particular thing to check out (since your performance counters seem
> to be almost flat) is if there is excessive blocking. You could use
> sp_who2 for this.
>
> --
> Gert-jan
>
>
> Jimmy wrote:
> >
> > I have a dual core xeon 3.0 ghz system with a dell DAS disk array that
> > contains the following disk layout:
> >
> > Status Name State Read Cache Write Cache Cache Policy Layout Size
> >
> > Used for Data Files
> >
> >   Virtual Disk 2 Ready Enabled-Read Ahead Adaptive Write Back Direct I/O
> > RAID 10 838.12 GB
> >
> > Used for Transaction Log Files
> >
> >   Virtual Disk 1 Ready Enabled-Read Ahead Adaptive Write Back Direct I/O
> > RAID 5 1,676.25 GB
> >
> > The hardware is:
> >
> > OS Name Microsoft(R) Windows(R) Server 2003, Standard Edition
> > Version 5.2.3790 Service Pack 2 Build 3790
> > Other OS Description    Not Available
> > OS Manufacturer Microsoft Corporation
> > System Name     --
> > System Manufacturer     Dell Computer Corporation
> > System Model    PowerEdge 1850
> > System Type     X86-based PC
> > Processor       x86 Family 15 Model 4 Stepping 3 GenuineIntel ~2992 Mhz
> > Processor       x86 Family 15 Model 4 Stepping 3 GenuineIntel ~2992 Mhz
> > Processor       x86 Family 15 Model 4 Stepping 3 GenuineIntel ~2992 Mhz
> > Processor       x86 Family 15 Model 4 Stepping 3 GenuineIntel ~2992 Mhz
> > BIOS Version/Date       Dell Computer Corporation A04, 9/22/2005
> > SMBIOS Version 2.3
> > Windows Directory       C:\WINDOWS
> > System Directory        C:\WINDOWS\system32
> > Boot Device     \Device\HarddiskVolume2
> > Locale United States
> > Hardware Abstraction Layer      Version = "5.2.3790.3959
> > (srv03_sp2_rtm.070216-1710)"
> > User Name       Not Available
> > Time Zone       Central Standard Time
> > Total Physical Memory   2,047.27 MB
> > Available Physical Memory       158.00 MB
> > Total Virtual Memory    3.85 GB
> > Available Virtual Memory        1.98 GB
> > Page File Space 2.00 GB
> > Page File       C:\pagefile.sys
> >
> > My problem is queries take a long time to run... of course, there's a lot of
> > data in there, and as far as I know, the indexes are somewhat optimal, I've
> > spent alot of time tuning them to get them to be somewhat acceptable in terms
> > of performance, but I can't help feel I'm missing something that's really
> > slowing SQL server down as a whole..
> >
> > My disk queue length is mostly 0, and my processor usage is 1-2%, but
> > queries still take 30 seconds to 2 minutes to return, and running batches of
> > sproc commands yield about 6 batches per second.. sometimes this number goes
> > up to 30, sometimes it goes down to 1, but all the while, the processor and
> > disk queue remain low...
> >
> > Any suggestions?
>

15 Nov 2007 6:47 AM

Linchi Shea

Were you running a script of some kind or an app that was submitting queries?
It could be your script or app that submitting queries fast enough to full
take advantage of the server. Just a guess, but worth checking out.

Linchi

Show quoteHide quote

"Jimmy" wrote:

> Well.. compared to itself really.. sorry if I wasn't clear on that.. if it
> sometimes goes at 30 executions per second, and then drops to 1 eps, then
> goes back to 15 and jumps around like that, I would really like to know a way
> to find out what's causing it to not be faster.. it doesn't seem like the
> disk, and it doesn't seem like the processor since both counters are near
> zero for utilization..
>
> "Linchi Shea" wrote:
>
> > Poor performance as compared to what? Did it run faster on a different
> > platform? Note that a Dell PowerEdge 1850 can hardly be considered as high
> > end.
> >
> > Linchi
> >
> > "Jimmy" wrote:
> >
> > > I have a dual core xeon 3.0 ghz system with a dell DAS disk array that
> > > contains the following disk layout:
> > >
> > > Status Name State Read Cache Write Cache Cache Policy Layout Size
> > >
> > > Used for Data Files
> > >
> > >   Virtual Disk 2 Ready Enabled-Read Ahead Adaptive Write Back Direct I/O
> > > RAID 10 838.12 GB
> > >
> > >
> > > Used for Transaction Log Files
> > >
> > >   Virtual Disk 1 Ready Enabled-Read Ahead Adaptive Write Back Direct I/O
> > > RAID 5 1,676.25 GB
> > >
> > > The hardware is:
> > >
> > > OS Name    Microsoft(R) Windows(R) Server 2003, Standard Edition
> > > Version    5.2.3790 Service Pack 2 Build 3790
> > > Other OS Description     Not Available
> > > OS Manufacturer    Microsoft Corporation
> > > System Name    --
> > > System Manufacturer    Dell Computer Corporation
> > > System Model    PowerEdge 1850
> > > System Type    X86-based PC
> > > Processor    x86 Family 15 Model 4 Stepping 3 GenuineIntel ~2992 Mhz
> > > Processor    x86 Family 15 Model 4 Stepping 3 GenuineIntel ~2992 Mhz
> > > Processor    x86 Family 15 Model 4 Stepping 3 GenuineIntel ~2992 Mhz
> > > Processor    x86 Family 15 Model 4 Stepping 3 GenuineIntel ~2992 Mhz
> > > BIOS Version/Date    Dell Computer Corporation A04, 9/22/2005
> > > SMBIOS Version    2.3
> > > Windows Directory    C:\WINDOWS
> > > System Directory    C:\WINDOWS\system32
> > > Boot Device    \Device\HarddiskVolume2
> > > Locale    United States
> > > Hardware Abstraction Layer    Version = "5.2.3790.3959
> > > (srv03_sp2_rtm.070216-1710)"
> > > User Name    Not Available
> > > Time Zone    Central Standard Time
> > > Total Physical Memory    2,047.27 MB
> > > Available Physical Memory    158.00 MB
> > > Total Virtual Memory    3.85 GB
> > > Available Virtual Memory    1.98 GB
> > > Page File Space    2.00 GB
> > > Page File    C:\pagefile.sys
> > >
> > > My problem is queries take a long time to run... of course, there's a lot of
> > > data in there, and as far as I know, the indexes are somewhat optimal, I've
> > > spent alot of time tuning them to get them to be somewhat acceptable in terms
> > > of performance, but I can't help feel I'm missing something that's really
> > > slowing SQL server down as a whole..
> > >
> > > My disk queue length is mostly 0, and my processor usage is 1-2%, but
> > > queries still take 30 seconds to 2 minutes to return, and running batches of
> > > sproc commands yield about 6 batches per second.. sometimes this number goes
> > > up to 30, sometimes it goes down to 1, but all the while, the processor and
> > > disk queue remain low...
> > >
> > > Any suggestions?

15 Nov 2007 5:38 PM

Jimmy

Yes I am, the app is fairly solid though.. and what it's doing is parsing
through xml and queuing up batches, then submitting batches of records in
preset chunks... (I've found that sending 500 batches at a time seems to work
the best) My CPU during this process isn't maxed either (my computer is
submitting the batches to the SQL Server)

One thing I'm checking to at the moment is the database indices.. I know
they're pretty optimal, but when I was looking at the disk in perfmon, only
the RAID10 drive was getting utilized during the times it slowed down, and
that's the drive that contains the MDF data... we didn't use RAID5 on that
drive because we thought it wouldn't have nearly as many writes, so even
though I have optimal indices on the tables I'm working with, I'm wondering
if I'm getting a bottleneck on disk performance since I'm using the same
filegroup as the table data... I'm going to try moving them to a new
filegroup on the RAID5 drive and see if that helps.

Show quoteHide quote

"Linchi Shea" wrote:

> Were you running a script of some kind or an app that was submitting queries?
> It could be your script or app that submitting queries fast enough to full
> take advantage of the server. Just a guess, but worth checking out.
>
> Linchi
>
> "Jimmy" wrote:
>
> > Well.. compared to itself really.. sorry if I wasn't clear on that.. if it
> > sometimes goes at 30 executions per second, and then drops to 1 eps, then
> > goes back to 15 and jumps around like that, I would really like to know a way
> > to find out what's causing it to not be faster.. it doesn't seem like the
> > disk, and it doesn't seem like the processor since both counters are near
> > zero for utilization..
> >
> > "Linchi Shea" wrote:
> >
> > > Poor performance as compared to what? Did it run faster on a different
> > > platform? Note that a Dell PowerEdge 1850 can hardly be considered as high
> > > end.
> > >
> > > Linchi
> > >
> > > "Jimmy" wrote:
> > >
> > > > I have a dual core xeon 3.0 ghz system with a dell DAS disk array that
> > > > contains the following disk layout:
> > > >
> > > > Status Name State Read Cache Write Cache Cache Policy Layout Size
> > > >
> > > > Used for Data Files
> > > >
> > > >   Virtual Disk 2 Ready Enabled-Read Ahead Adaptive Write Back Direct I/O
> > > > RAID 10 838.12 GB
> > > >
> > > >
> > > > Used for Transaction Log Files
> > > >
> > > >   Virtual Disk 1 Ready Enabled-Read Ahead Adaptive Write Back Direct I/O
> > > > RAID 5 1,676.25 GB
> > > >
> > > > The hardware is:
> > > >
> > > > OS Name    Microsoft(R) Windows(R) Server 2003, Standard Edition
> > > > Version    5.2.3790 Service Pack 2 Build 3790
> > > > Other OS Description     Not Available
> > > > OS Manufacturer    Microsoft Corporation
> > > > System Name    --
> > > > System Manufacturer    Dell Computer Corporation
> > > > System Model    PowerEdge 1850
> > > > System Type    X86-based PC
> > > > Processor    x86 Family 15 Model 4 Stepping 3 GenuineIntel ~2992 Mhz
> > > > Processor    x86 Family 15 Model 4 Stepping 3 GenuineIntel ~2992 Mhz
> > > > Processor    x86 Family 15 Model 4 Stepping 3 GenuineIntel ~2992 Mhz
> > > > Processor    x86 Family 15 Model 4 Stepping 3 GenuineIntel ~2992 Mhz
> > > > BIOS Version/Date    Dell Computer Corporation A04, 9/22/2005
> > > > SMBIOS Version    2.3
> > > > Windows Directory    C:\WINDOWS
> > > > System Directory    C:\WINDOWS\system32
> > > > Boot Device    \Device\HarddiskVolume2
> > > > Locale    United States
> > > > Hardware Abstraction Layer    Version = "5.2.3790.3959
> > > > (srv03_sp2_rtm.070216-1710)"
> > > > User Name    Not Available
> > > > Time Zone    Central Standard Time
> > > > Total Physical Memory    2,047.27 MB
> > > > Available Physical Memory    158.00 MB
> > > > Total Virtual Memory    3.85 GB
> > > > Available Virtual Memory    1.98 GB
> > > > Page File Space    2.00 GB
> > > > Page File    C:\pagefile.sys
> > > >
> > > > My problem is queries take a long time to run... of course, there's a lot of
> > > > data in there, and as far as I know, the indexes are somewhat optimal, I've
> > > > spent alot of time tuning them to get them to be somewhat acceptable in terms
> > > > of performance, but I can't help feel I'm missing something that's really
> > > > slowing SQL server down as a whole..
> > > >
> > > > My disk queue length is mostly 0, and my processor usage is 1-2%, but
> > > > queries still take 30 seconds to 2 minutes to return, and running batches of
> > > > sproc commands yield about 6 batches per second.. sometimes this number goes
> > > > up to 30, sometimes it goes down to 1, but all the while, the processor and
> > > > disk queue remain low...
> > > >
> > > > Any suggestions?

15 Nov 2007 2:42 PM

JXStern

On Wed, 14 Nov 2007 09:50:00 -0800, Jimmy
<Ji***@discussions.microsoft.com> wrote:

>Used for Transaction Log Files
>
> Virtual Disk 1 Ready Enabled-Read Ahead Adaptive Write Back Direct I/O
> RAID 5 1,676.25 GB
....
>Any suggestions?

In general, RAID5 writes slower than RAID0 or 1 or 10, and so is not
optimal for log files.

I'm not sure how to measure this, latch waits?

J.

15 Nov 2007 2:56 PM

TheSQLGuru

What about backups going on? Or Agent Jobs or maintenance plans?

Best is to get a professional in to give you a performance review. Probably
more things than you imagine could be improved based on my experience! :-)

--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.

Show quoteHide quote

"Jimmy" <Ji***@discussions.microsoft.com> wrote in message
news:DB578D91-122B-4000-8314-05B25150D149@microsoft.com...
>I have a dual core xeon 3.0 ghz system with a dell DAS disk array that
> contains the following disk layout:
>
> Status Name State Read Cache Write Cache Cache Policy Layout Size
>
> Used for Data Files
>
> Virtual Disk 2 Ready Enabled-Read Ahead Adaptive Write Back Direct
> I/O
> RAID 10 838.12 GB
>
>
> Used for Transaction Log Files
>
> Virtual Disk 1 Ready Enabled-Read Ahead Adaptive Write Back Direct
> I/O
> RAID 5 1,676.25 GB
>
> The hardware is:
>
> OS Name Microsoft(R) Windows(R) Server 2003, Standard Edition
> Version 5.2.3790 Service Pack 2 Build 3790
> Other OS Description Not Available
> OS Manufacturer Microsoft Corporation
> System Name --
> System Manufacturer Dell Computer Corporation
> System Model PowerEdge 1850
> System Type X86-based PC
> Processor x86 Family 15 Model 4 Stepping 3 GenuineIntel ~2992 Mhz
> Processor x86 Family 15 Model 4 Stepping 3 GenuineIntel ~2992 Mhz
> Processor x86 Family 15 Model 4 Stepping 3 GenuineIntel ~2992 Mhz
> Processor x86 Family 15 Model 4 Stepping 3 GenuineIntel ~2992 Mhz
> BIOS Version/Date Dell Computer Corporation A04, 9/22/2005
> SMBIOS Version 2.3
> Windows Directory C:\WINDOWS
> System Directory C:\WINDOWS\system32
> Boot Device \Device\HarddiskVolume2
> Locale United States
> Hardware Abstraction Layer Version = "5.2.3790.3959
> (srv03_sp2_rtm.070216-1710)"
> User Name Not Available
> Time Zone Central Standard Time
> Total Physical Memory 2,047.27 MB
> Available Physical Memory 158.00 MB
> Total Virtual Memory 3.85 GB
> Available Virtual Memory 1.98 GB
> Page File Space 2.00 GB
> Page File C:\pagefile.sys
>
> My problem is queries take a long time to run... of course, there's a lot
> of
> data in there, and as far as I know, the indexes are somewhat optimal,
> I've
> spent alot of time tuning them to get them to be somewhat acceptable in
> terms
> of performance, but I can't help feel I'm missing something that's really
> slowing SQL server down as a whole..
>
> My disk queue length is mostly 0, and my processor usage is 1-2%, but
> queries still take 30 seconds to 2 minutes to return, and running batches
> of
> sproc commands yield about 6 batches per second.. sometimes this number
> goes
> up to 30, sometimes it goes down to 1, but all the while, the processor
> and
> disk queue remain low...
>
> Any suggestions?

Thread options