64-bit SQL Server 2005 Cluster: slow remote desktop

Hi,
We are moving from SQL Server 2000 to SQL Server 2005. Wondering if you
have the same problem or can share some of your experience to solve some
issue.
We have brand new clustered database servers which have Quad Duo CPUs
and 8GB memory each running SQL Server 2005 64bit Enterprise edition
with SP1 applied and on Win2K3 server x64 Enterprise edition. Only
database engine component are installed for now.
We have some strange behaviors from time to time that :
When we have some query running from some local PC Management Studio,
the remote desktop control is either takes a long time to connect or
very slow to react the requests. When it happened, sometime we can not
connect to the database server from Management Studio with the
connection timeout which was OK couple minutes ago. Interestingly at
those moments, the query is still running , no process blocking found,
very light CPU usage and low disk I/O detected from the monitoring tool
and it looks like the SQL server is not busy at all. No 3rd
party/identifiable process can be found running on the database server
also, the 'system idle process' is the main one with CPU count. And
overall the query just take very long than expected to finish. We could
have very normal days for a while and randomly we will encountered the
symptom above. Each time query we ran is different. Some are light and
some are heavy. The query we recently ran is to create index on many big
partitioned tables.
Thanks in advance.Hi,
Thanks for your post!
From your description, I understand that:
You installed SQL Server 2005 64bit as clustered servers. The Windows
Server 2003 was also 64bit.
When you executed the query in SQL Server Management Studio locally, the
remote desktop need a long time to connect the server and had very slow
responses for requests.
At that time, extra connections to the SQL Server were also slow and even
failed. The CPU use ratio and disk I/O level were low then.
This happened once a while. The latest query was to create index on many
large partitioned tables.
If I have misunderstood, please let me know.
To narrow down this issue, I appreciate you send me the performance report
files of SQL Server Profiler and Performance Monitor when the issue is
happening again.
Also, I appreciate you could tell me more about the query when this issue
happened:
What's kind of the query?
Does a simple query will cause that problem?
When this issue happened, what were recorded in the SQL error logs and
system event logs?
If you have any other concerns, please feel free to let me know. It's my
pleasure to be of assistance.
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others:
https://partner.microsoft.com/US/technicalsupport/supportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||American are in vacation "Independency Day".
Charles|||Hi ,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Have a great day!
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=============================================================This posting is provided "AS IS" with no warranties, and confers no rights.

64-bit SQL Server 2005 Cluster: slow remote desktop

Hi,
We are moving from SQL Server 2000 to SQL Server 2005. Wondering if you
have the same problem or can share some of your experience to solve some
issue.
We have brand new clustered database servers which have Quad Duo CPUs
and 8GB memory each running SQL Server 2005 64bit Enterprise edition
with SP1 applied and on Win2K3 server x64 Enterprise edition. Only
database engine component are installed for now.
We have some strange behaviors from time to time that :
When we have some query running from some local PC Management Studio,
the remote desktop control is either takes a long time to connect or
very slow to react the requests. When it happened, sometime we can not
connect to the database server from Management Studio with the
connection timeout which was OK couple minutes ago. Interestingly at
those moments, the query is still running , no process blocking found,
very light CPU usage and low disk I/O detected from the monitoring tool
and it looks like the SQL server is not busy at all. No 3rd
party/identifiable process can be found running on the database server
also, the 'system idle process' is the main one with CPU count. And
overall the query just take very long than expected to finish. We could
have very normal days for a while and randomly we will encountered the
symptom above. Each time query we ran is different. Some are light and
some are heavy. The query we recently ran is to create index on many big
partitioned tables.
Thanks in advance.Hi,
Thanks for your post!
From your description, I understand that:
You installed SQL Server 2005 64bit as clustered servers. The Windows
Server 2003 was also 64bit.
When you executed the query in SQL Server Management Studio locally, the
remote desktop need a long time to connect the server and had very slow
responses for requests.
At that time, extra connections to the SQL Server were also slow and even
failed. The CPU use ratio and disk I/O level were low then.
This happened once a while. The latest query was to create index on many
large partitioned tables.
If I have misunderstood, please let me know.
To narrow down this issue, I appreciate you send me the performance report
files of SQL Server Profiler and Performance Monitor when the issue is
happening again.
Also, I appreciate you could tell me more about the query when this issue
happened:
What's kind of the query?
Does a simple query will cause that problem?
When this issue happened, what were recorded in the SQL error logs and
system event logs?
If you have any other concerns, please feel free to let me know. It's my
pleasure to be of assistance.
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others:
https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
Thanks for your post!
From your description, I understand that:
You installed SQL Server 2005 64bit as clustered servers. The Windows
Server 2003 was also 64bit.
When you executed the query in SQL Server Management Studio locally, the
remote desktop need a long time to connect the server and had very slow
responses for requests.
At that time, extra connections to the SQL Server were also slow and even
failed. The CPU use ratio and disk I/O level were low then.
This happened once a while. The latest query was to create index on many
large partitioned tables.
If I have misunderstood, please let me know.
To narrow down this issue, I appreciate you send me the performance report
files of SQL Server Profiler and Performance Monitor when the issue is
happening again.
Also, I appreciate you could tell me more about the query when this issue
happened:
What's kind of the query?
Does a simple query will cause that problem?
When this issue happened, what were recorded in the SQL error logs and
system event logs?
If you have any other concerns, please feel free to let me know. It's my
pleasure to be of assistance.
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others:
https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||American are in vacation "Independency Day".
Charles|||Hi ,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Have a great day!
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=====================
This posting is provided "AS IS" with no warranties, and confers no rights.

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-2147483648214748364700
affinity64 mask-2147483648214748364700
allow updates0100
awe enabled0100
c2 audit mode0100
cost threshold for parallelism03276755
Cross DB Ownership Chaining0100
cursor threshold-12147483647-1-1
default full-text language0214748364710331033
default language0999900
fill factor (%)010000
index create memory (KB)704214748364700
lightweight pooling0100
locks5000214748364700
max degree of parallelism03211
max server memory (MB)4214748364721474836472147483647
max text repl size (B)021474836476553665536
max worker threads3232767255255
media retention036500
min memory per query (KB)512214748364710241024
min server memory (MB)0214748364700
nested triggers0111
network packet size (B)5123276740964096
open objects0214748364700
priority boost0100
query governor cost limit0214748364700
query wait (s)-12147483647-1-1
recovery interval (min)03276700
remote access0111
remote login timeout (s)021474836472020
remote proc trans0100
remote query timeout (s)02147483647600600
scan for startup procs0100
set working set size0100
show advanced options0111
two digit year cutoff1753999920492049
user connections03276700
user options0327676464
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.
"Nicholas Cain" <nicholas.cain@.nospam.t-mobile.com> wrote in message
news:Xns96A646766DD6Cnicholascainnospamtm@.207.46.2 48.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.
>

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:Xns96A646766DD6Cnicholascainnospamtm@.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.
>

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.
>