LeonesIT
asked on
Poor Disk Performance
Goodday, I have a SQL 2005 Disk IO performance issue. I've got the following server config with a customer:
HP Proliant ML350 G4
1 Processor
3 GB mem
Smart Array 641 controller
RAID 5 with 3 disks of 67 GB 15K Ultra320
After upgrading from SQL 2000 to SQL 2005 we have problems in combination with Exact Globe 2003. The proble is slow response from within the Exact Globe Client software. Normal operations, wich took 3 seconds, now take 20 - 60 seconds. I take a look at the Performance Monitor of Windows. I see that at the moment a user does something in Exact, the performance counters: Disk Reads/sec, Avg. Disk Queue Length, Current Disk Queue Length and Avg. Disk Read Queue Lengt go sky high, up to 100. The CPU or memory values are ok.
Does anyone have any suggestions? Thank you.
HP Proliant ML350 G4
1 Processor
3 GB mem
Smart Array 641 controller
RAID 5 with 3 disks of 67 GB 15K Ultra320
After upgrading from SQL 2000 to SQL 2005 we have problems in combination with Exact Globe 2003. The proble is slow response from within the Exact Globe Client software. Normal operations, wich took 3 seconds, now take 20 - 60 seconds. I take a look at the Performance Monitor of Windows. I see that at the moment a user does something in Exact, the performance counters: Disk Reads/sec, Avg. Disk Queue Length, Current Disk Queue Length and Avg. Disk Read Queue Lengt go sky high, up to 100. The CPU or memory values are ok.
Does anyone have any suggestions? Thank you.
ASKER
Don't know. Another person did this.
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
ok, that will not be evident.
suggestion 1:
run UPDATE STATISTICS on the database(s), and see if that helps?
suggestion 2:
what is the max memory setting of the sql 2005 instance?
what is the actual RAM of the machine?
suggestion 3:
run a sql profiler to trace the sql statements, take them to sql management studio, and run them (starting with the ones using high read/high cpu) with the "include actual execution plan" selected (Query Menu). if possible, compare with the same query on the sql 2000/query analyser.
check if the query uses proper indexes or full table scans.
suggestion 1:
run UPDATE STATISTICS on the database(s), and see if that helps?
suggestion 2:
what is the max memory setting of the sql 2005 instance?
what is the actual RAM of the machine?
suggestion 3:
run a sql profiler to trace the sql statements, take them to sql management studio, and run them (starting with the ones using high read/high cpu) with the "include actual execution plan" selected (Query Menu). if possible, compare with the same query on the sql 2000/query analyser.
check if the query uses proper indexes or full table scans.
ASKER
ok, sorry, but I'm not really familiar with SQL, so that why the following questions
suggestion 1:
where to run this in? which utility to use?
suggestion 2:
Max mem for the instance taken from the Server Properties: 2000000
Total Physical Memory RAM taken from the Task Manager: 3145188
suggestion 3:
I wil; try to find it out on how to do this.
suggestion 1:
where to run this in? which utility to use?
suggestion 2:
Max mem for the instance taken from the Server Properties: 2000000
Total Physical Memory RAM taken from the Task Manager: 3145188
suggestion 3:
I wil; try to find it out on how to do this.
ASKER
Some more info:
there are 2 partitions, C and D
all the databases reside on te C partition
there are Windows Paging Files configured on both partitions, C (2048MB swap) and D (4096MB swap)
don't ask me why they did this, its something I have to accept and deal with
there are 2 partitions, C and D
all the databases reside on te C partition
there are Windows Paging Files configured on both partitions, C (2048MB swap) and D (4096MB swap)
don't ask me why they did this, its something I have to accept and deal with
Sounds like you might consider putting sql server on a dedicated machine so that other apps aren't competing with it. Also, you can move databases after they've been installed...just need to detach, move, then attach them.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
the UPDATE STATISTICS is a sql statement to be run using the SQL Management Studio.
>suggestion 2:
>Total Physical Memory RAM taken from the Task Manager: 3145188
means you have 3GB RAM
>Max mem for the instance taken from the Server Properties: 2000000
as that setting is in MB, that would mean 2TB... please try to change to 2000
>suggestion 2:
>Total Physical Memory RAM taken from the Task Manager: 3145188
means you have 3GB RAM
>Max mem for the instance taken from the Server Properties: 2000000
as that setting is in MB, that would mean 2TB... please try to change to 2000
how did you do that, exactly?