Showing posts with label raid. Show all posts
Showing posts with label raid. Show all posts

Monday, March 26, 2012

Performance Degraded and RESOURCE_SEMAPHORE started appearing

Hello,
We are running a 4 CPU SQL Server 2000 with RAID arrays of 5 disks each on
two separate channels of a controller.
It all worked great... However, a few days ago the performance went way
down.
I am noticing a lot of RESOURCE_SEMAPHORE waittypes in the sysprocesses
table.
Also the Avg Disk Queue Length counter started showing high numbers.
Is there something specific I need to look for? When running the SQL
Profiler I do not see anything unusual - just that the queries are taking
much longer to complete.
Should I reboot the server? It was not rebooted since March, 2003.
Please let me know!
Thank you,
ArsenHi Andrew,
All disks look okay. I do not see scans of tables. Indexes are used as
before.
Although, I just noticed that the Cache Hit is around 52%, where as on my
other SQL Server boxes it is around 98%. Could this be something?
How to fix this? Reboot?
Arsen|||while this sounds like a hardware problem occured at the
point a few days ago, can you just quickly check to see if
there is a particular query causing this
look in profiler for anything >> 5sec cpu or duration
>--Original Message--
>Hello,
>We are running a 4 CPU SQL Server 2000 with RAID arrays
of 5 disks each on
>two separate channels of a controller.
>It all worked great... However, a few days ago the
performance went way
>down.
>I am noticing a lot of RESOURCE_SEMAPHORE waittypes in
the sysprocesses
>table.
>Also the Avg Disk Queue Length counter started showing
high numbers.
>Is there something specific I need to look for? When
running the SQL
>Profiler I do not see anything unusual - just that the
queries are taking
>much longer to complete.
>Should I reboot the server? It was not rebooted since
March, 2003.
>Please let me know!
>Thank you,
>Arsen
>
>.
>

Tuesday, March 20, 2012

Performance between Server and PC

Hi All,
Recently we have an application running for 9 hours+ on an HP DL760 G2 with
8 Processors, 12GB RAM, 4 HDD 15K RPM 142GB running on Raid 1+0. We have
tested with the same database and application on a normal pc which only have
2GB RAM, Pentium Dual Core 930 3GHz, SATA HDD without running RAID, it only
took about 5 hours to complete. Both server and PC is using Windows 2003
Enterprise (32 bit) and SQL 2000 Enterprise (32 bit).
According to the client, their normal operation is working fine. The issue
only happen on that application. But it is weird that the same application
running on normal PC is faster than the server. We just reformat that server,
but the result still took 9hours + to complete.
does anyone know what happen? I have checked all the counter, everything
seem works fine. But I found that the process is pretty fast for 1st 4 hours
then the performance will start degrade.
I also enabled AWE on that server with setting the max memory to 9.6GB RAM
(80% of 12GB). did I leave out something?
I have also tested on HP ML530 G2, Dual Processors and 2GB RAM with RAID 5
(3 HDD 10K RPM). The application still take about 9+ hours to complete.
Any advice is much appreciated. Thanks.
--
Regards,
VenedictVenedict wrote:
> Hi All,
> Recently we have an application running for 9 hours+ on an HP DL760 G2 with
> 8 Processors, 12GB RAM, 4 HDD 15K RPM 142GB running on Raid 1+0. We have
> tested with the same database and application on a normal pc which only have
> 2GB RAM, Pentium Dual Core 930 3GHz, SATA HDD without running RAID, it only
> took about 5 hours to complete. Both server and PC is using Windows 2003
> Enterprise (32 bit) and SQL 2000 Enterprise (32 bit).
> Any advice is much appreciated. Thanks.
When you say "application", what are you referring to? You make
references to the application "completing", which leads me to believe
this is a report or stored procedure.
Have you looked at sysprocesses while this "application" is running? My
hunch is that query parallelism is causing this. If you look at
sysprocesses, you'll likely see several threads with the same spid, some
with a waittype of CXPACKET. You might want to experiment with
different degrees of parallelism.