Saturday, February 25, 2012

64bit i/o Performance

Hoping someone has some experience with this. I am in the middle of
attempting to migrate over to a 64bit SQL cluster, however have found a
major problem with i/o requests being painfully slow. Slower in fact than
on a 32bit system with less than half the power of the Itanium.
Below is the output from the sp_configure, I'm not seeing anything.
Server info: HP rx7620, 4x1.6GHZ Itanium2 CPU, 32GB RAM, 120GB local
disk, 500GB (multiple luns) XP1024 SAN, running secure path (autopath set
to SQR).
Any thoughts?
Thanks,
Nic
affinity mask -2147483648 2147483647 0 0
affinity64 mask -2147483648 2147483647 0 0
allow updates 0 1 0 0
awe enabled 0 1 0 0
c2 audit mode 0 1 0 0
cost threshold for parallelism 0 32767 5 5
Cross DB Ownership Chaining 0 1 0 0
cursor threshold -1 2147483647 -1 -1
default full-text language 0 2147483647 1033 1033
default language 0 9999 0 0
fill factor (%) 0 100 0 0
index create memory (KB) 704 2147483647 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max degree of parallelism 0 32 1 1
max server memory (MB) 4 2147483647 2147483647 2147483647
max text repl size (B) 0 2147483647 65536 65536
max worker threads 32 32767 255 255
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 0 0
nested triggers 0 1 1 1
network packet size (B) 512 32767 4096 4096
open objects 0 2147483647 0 0
priority boost 0 1 0 0
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote login timeout (s) 0 2147483647 20 20
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 600 600
scan for startup procs 0 1 0 0
set working set size 0 1 0 0
show advanced options 0 1 1 1
two digit year cutoff 1753 9999 2049 2049
user connections 0 32767 0 0
user options 0 32767 64 64Nicholas,
We run systems of the this size and larger. Mainly HP as well. When we've
moved from 32 bit to 64bit we've seen large increases in IO throughput.
Average Queue Lengths drop and throughput on the SAN increases.
Do you have a base line of IO on the 32bit system that you can compare to
the 64bit?
The first thing to look at would be are there any hot spots on your disk?
Check for a high average queue length.
What SQL build are you running? The same build numbers in 32bit and 64bit
can have different issues.
It's difficult without more information about your processes but here are a
few suggestions for you to try on your config.
Set the min a min and max memory to about 28GB. More or less depending on
what else you have running.
max DOP is 1? If you don't get blocked schedulers up this or set it to 0.
max worker threads may need to be higher but that depends on the number and
size of the processes.
"Nicholas Cain" <nicholas.cain@.nospam.t-mobile.com> wrote in message
news:Xns96A646766DD6Cnicholascainnospamt
m@.207.46.248.16...
> Hoping someone has some experience with this. I am in the middle of
> attempting to migrate over to a 64bit SQL cluster, however have found a
> major problem with i/o requests being painfully slow. Slower in fact than
> on a 32bit system with less than half the power of the Itanium.
> Below is the output from the sp_configure, I'm not seeing anything.
> Server info: HP rx7620, 4x1.6GHZ Itanium2 CPU, 32GB RAM, 120GB local
> disk, 500GB (multiple luns) XP1024 SAN, running secure path (autopath set
> to SQR).
>
> Any thoughts?
> Thanks,
> Nic
>
> affinity mask -2147483648 2147483647 0 0
> affinity64 mask -2147483648 2147483647 0 0
> allow updates 0 1 0 0
> awe enabled 0 1 0 0
> c2 audit mode 0 1 0 0
> cost threshold for parallelism 0 32767 5 5
> Cross DB Ownership Chaining 0 1 0 0
> cursor threshold -1 2147483647 -1 -1
> default full-text language 0 2147483647 1033 1033
> default language 0 9999 0 0
> fill factor (%) 0 100 0 0
> index create memory (KB) 704 2147483647 0 0
> lightweight pooling 0 1 0 0
> locks 5000 2147483647 0 0
> max degree of parallelism 0 32 1 1
> max server memory (MB) 4 2147483647 2147483647 2147483647
> max text repl size (B) 0 2147483647 65536 65536
> max worker threads 32 32767 255 255
> media retention 0 365 0 0
> min memory per query (KB) 512 2147483647 1024 1024
> min server memory (MB) 0 2147483647 0 0
> nested triggers 0 1 1 1
> network packet size (B) 512 32767 4096 4096
> open objects 0 2147483647 0 0
> priority boost 0 1 0 0
> query governor cost limit 0 2147483647 0 0
> query wait (s) -1 2147483647 -1 -1
> recovery interval (min) 0 32767 0 0
> remote access 0 1 1 1
> remote login timeout (s) 0 2147483647 20 20
> remote proc trans 0 1 0 0
> remote query timeout (s) 0 2147483647 600 600
> scan for startup procs 0 1 0 0
> set working set size 0 1 0 0
> show advanced options 0 1 1 1
> two digit year cutoff 1753 9999 2049 2049
> user connections 0 32767 0 0
> user options 0 32767 64 64|||I actually ran some i/o stats outside of SQL and got great performance
(over 6300 io/sec), however when running through SQL this drops off
massively to something around 600 io/sec.
I changed the DOP to 1 to see if that would improve things any (testing a
bulk insert), however it didn't do anything and I was going to change it
back.
As a comparison from the SQL standpoint, I have a 32bit system with 4GB
RAM connected to the same SAN. I'm running a bulk insert with exactly the
same parameters into a database, logically and physically layed out the
same way and it's taking 3.5 minutes to complete the insert on the 32bit
system and well over 5 minutes on the 64bit.
Just doesn't seem right to me, especially when the raw i/o figures (using
iometer) show that we have at least twice the i/o throughput on the 64bit
system.
Nic
"Danny" <someone@.nowhere.com> wrote in
news:c0KHe.9971$Bx5.7552@.trnddc09:

> Nicholas,
> We run systems of the this size and larger. Mainly HP as well. When
> we've moved from 32 bit to 64bit we've seen large increases in IO
> throughput. Average Queue Lengths drop and throughput on the SAN
> increases.
> Do you have a base line of IO on the 32bit system that you can compare
> to the 64bit?
> The first thing to look at would be are there any hot spots on your
> disk? Check for a high average queue length.
> What SQL build are you running? The same build numbers in 32bit and
> 64bit can have different issues.
> It's difficult without more information about your processes but here
> are a few suggestions for you to try on your config.
> Set the min a min and max memory to about 28GB. More or less
> depending on what else you have running.
> max DOP is 1? If you don't get blocked schedulers up this or set it
> to 0. max worker threads may need to be higher but that depends on the
> number and size of the processes.
>

No comments:

Post a Comment