The 64 bit question

[Frank Rizzo]

Hello,

I have a pretty nice box in production (2 dual core Opterons) and it is
running the 32-bit version of Windows Server 2003 and 32-bit version of
SQL Server 2000. The application that's running on it is very
performance sensitive and runs 24x7x365. However, after a bit of
profiling, I determined that the application spends something like 90%
of it time waiting for SQL Server to process queries.

So, my question is whether simply installing Windows 2003 64-bit edition
and SQL Server 2005 64-bit edition and recompiling my app for 64 bits
(in vs 2005) will improve the performance.

I realize this is a blanket question and I am providing very few
specifics. However, in general, based on the fact that the 90% of the
time spent in SQL Server, will moving to 64 bit speed things up?

Regards

 

[Daniel O'Connell [C# MVP]]

Hello,

I have a pretty nice box in production (2 dual core Opterons) and it is
running the 32-bit version of Windows Server 2003 and 32-bit version of
SQL Server 2000. The application that's running on it is very performance
sensitive and runs 24x7x365. However, after a bit of profiling, I
determined that the application spends something like 90% of it time
waiting for SQL Server to process queries.

So, my question is whether simply installing Windows 2003 64-bit edition
and SQL Server 2005 64-bit edition and recompiling my app for 64 bits (in
vs 2005) will improve the performance.

I realize this is a blanket question and I am providing very few
specifics. However, in general, based on the fact that the 90% of the
time spent in SQL Server, will moving to 64 bit speed things up?

I wouldn't think so. Even assuming that the 64 bit processor would speed up
the DB slightly, you are still spending a large percentage of tiem in the
DB.

Is your application doing anything but the DB querying?

 

[Willy Denoyette [MVP]]

Hello,

I have a pretty nice box in production (2 dual core Opterons) and it is
running the 32-bit version of Windows Server 2003 and 32-bit version of
SQL Server 2000. The application that's running on it is very performance
sensitive and runs 24x7x365. However, after a bit of profiling, I
determined that the application spends something like 90% of it time
waiting for SQL Server to process queries.

So, my question is whether simply installing Windows 2003 64-bit edition
and SQL Server 2005 64-bit edition and recompiling my app for 64 bits (in
vs 2005) will improve the performance.

I realize this is a blanket question and I am providing very few
specifics. However, in general, based on the fact that the 90% of the
time spent in SQL Server, will moving to 64 bit speed things up?

Regards

The question you need to answer is : what is my application is waiting for?
Which resource is the 'bottleneck' (if there is one). The resources you need
to consider are Disk IO, Memory and CPU. The latter can be excluded here,
the most important is Disk IO. So you will have to measure your IO
throughput, if this is too high, you could consider to optimize your disk IO
subsystem (RAID arrays, location of your indexes, distribution of your
tables, what raid arrays are there used for tables and indexes etc...).
When the IO system is optimal or can't be optimized at all, you can try to
optimize you DB, check your indexes, are they optimized did you select the
right keys etc.., you can verify this by running the query optimizer and
inspect the query plan. If the DB is very large and the high number of IO
requests are due to a lack of SQL memory for index/data buffers, you'll have
to add memory. And ONLY when you really need more than 4GB of memory you
should consider moving to 64 bit.

Willy.