I understand that the 64 environment allows more addressable memory.
Are there any additional performance benefits other than possibly those
gained by increased memory addressability?
Assuming the same database running on a 32 bit environment and a 64 bit
environment and applications hitting both environments with the same
work load. Can the 64 bit environment have a higher throughput. For
this hypothetical case let us assume that advantages of having a bigger
proc cache does not help.
cheers
KenHere is an MS enumeration of the advantages:
http://www.microsoft.com/sql/techinfo/whitepapers/advantages-64bit-environment.mspx
> Are there any additional performance benefits other than possibly those
> gained by increased memory addressability?
We've seen a roughly 40% reduction in query times on a 64bit server vs.
its 32bit equivalent (sql2k5 on both). We're using Intel with 64bit
extensions rather than Itanium.
> Assuming the same database running on a 32 bit environment and a 64 bit
> environment and applications hitting both environments with the same
> work load. Can the 64 bit environment have a higher throughput.
This does seem to be the case, though you still have to consider disk
IO limitations (assuming you're not storing your entire database in the
increased RAM on your 64bit server).
Although the performance increases are notable, and MS claims the 64bit
version is fully functional and supported, we have had a few trouble
areas with the 64bit version. SSIS seems quirky, along with
OPENDATASOURCE between 64bit and 32bit servers (not that you'd
necessarily want to use that a lot :))
All-in-all, we're not disappointed.
Have fun!
KenJ|||Proc Cache isn't the main consumer of memory which benefits being on 64 bit.
The main beneficiary is the increased data caching capability & this is by
far the most significant single reason for upgrading to 64 bit, at least
from a performance perspective.
Regards,
Greg Linwood
SQL Server MVP
<raidken@.yahoo.com> wrote in message
news:1156990437.923057.219500@.i42g2000cwa.googlegroups.com...
>I understand that the 64 environment allows more addressable memory.
> Are there any additional performance benefits other than possibly those
> gained by increased memory addressability?
> Assuming the same database running on a 32 bit environment and a 64 bit
> environment and applications hitting both environments with the same
> work load. Can the 64 bit environment have a higher throughput. For
> this hypothetical case let us assume that advantages of having a bigger
> proc cache does not help.
> cheers
> Ken
>|||I agree that the main beneficiary of increased memory is the buffer
cache. Even with 32 bit a large amounts of memory could be addresses
through AWE. Which brings up another question, does 64 bit give a
substantial performance boost by eliminating the need for AWE?
KenJ, you mentioned that query performance increased by 40%, was your
32 bit database memory bound? Do u attribute the improvement to higher
buffer hit rate, reduced IO queue length? I would be very interested in
discussing this further if you are willing.
cheers,
ken|||It appears that you are expecting to gain performance going to 64 bit. That
may very well be the case, but it may just turn out not to be case. The
outcome really depends on your workloads. I have seen the same app coming out
with lower throughput on 64 bit than on 32 bit with the same hardware. You
need to test your app to be sure.
Linchi
"raidken@.yahoo.com" wrote:
> I understand that the 64 environment allows more addressable memory.
> Are there any additional performance benefits other than possibly those
> gained by increased memory addressability?
> Assuming the same database running on a 32 bit environment and a 64 bit
> environment and applications hitting both environments with the same
> work load. Can the 64 bit environment have a higher throughput. For
> this hypothetical case let us assume that advantages of having a bigger
> proc cache does not help.
> cheers
> Ken
>|||using 64b instead of AWE provide a small boost.
if you take a look at the TPC.org web site, you'll see some benchmarks.
a 4CPU server provides 206000tpmC using SQL2005 x64 (128Gb of RAM) and
188000 tpmc with SQL 2000 (64Gb of RAM)
so with having 2times the memory on the server provides only a few advantage
and the x64 version is not so helpfull in this case.
in fact, the difference is also at the disk level, to compensate the lack of
memory the SQL 2000 benchmark use 2 time more disks (total of 30TB versus
15TB)
The advantage for small and mid sizebusiness is the ability to share the
server with multiple applications and let SQL Server to manage more memory
without locking this memory.
the AWE option lock the memory and this memory is not available for other
applications, its good for high performance and dedicated servers. But in a
real world 1 server support more then 1 application (the company install RS,
SQL and AS on the same server to reduce the license cost) locking the memory
is not good. In these conditions the x64bits version is really good, all the
server memory is used but not locked and regarding which application is more
on demand then the server balance the memory usage between them.
But for dedicated servers like intensive transactionnal systems the
advantage is small.
but the big changes are in AS2005 and SSIS where the x64bit platform provide
a huge advantage.
and to finish, because there is no difference in the price and for future
compatibility, use x64 version. There is no disadvantage of the x64 platform
versus the x32, so if you have the choice, use it!!!
also 32bits CPUs will quickly disappeared from the market with the price war
between Intel and AMD.
<raidken@.yahoo.com> wrote in message
news:1156995016.052764.299640@.i42g2000cwa.googlegroups.com...
>I agree that the main beneficiary of increased memory is the buffer
> cache. Even with 32 bit a large amounts of memory could be addresses
> through AWE. Which brings up another question, does 64 bit give a
> substantial performance boost by eliminating the need for AWE?
> KenJ, you mentioned that query performance increased by 40%, was your
> 32 bit database memory bound? Do u attribute the improvement to higher
> buffer hit rate, reduced IO queue length? I would be very interested in
> discussing this further if you are willing.
> cheers,
> ken
>|||There is some degree of overhead associated with AWE infrastructure, but
it's a relatively small part of the picture.
There's also more to compare than simply whether you can address 32Gb via 32
bit AWE or via 64 bit without AWE. For example, the 64 bit version of SQL
Server 2005 Standard Edition 2005 is limited to 32Gb RAM (on Win 2003 EE 64
bit), whilst the 32 bit version of SQL Server 2005 Standard Edition can't
get anywhere near that amount (I'm not sure what the actual amount is, but I
think it might be 4Gb)
Regards,
Greg Linwood
SQL Server MVP
<raidken@.yahoo.com> wrote in message
news:1156995016.052764.299640@.i42g2000cwa.googlegroups.com...
>I agree that the main beneficiary of increased memory is the buffer
> cache. Even with 32 bit a large amounts of memory could be addresses
> through AWE. Which brings up another question, does 64 bit give a
> substantial performance boost by eliminating the need for AWE?
> KenJ, you mentioned that query performance increased by 40%, was your
> 32 bit database memory bound? Do u attribute the improvement to higher
> buffer hit rate, reduced IO queue length? I would be very interested in
> discussing this further if you are willing.
> cheers,
> ken
>|||Hi Linchi
Any chance you can share what type of workload you've identified that
exhibits these characteristics & how this has been measured?
Regards,
Greg Linwood
SQL Server MVP
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:173E5682-2471-4A6F-9DD7-33125DCEB413@.microsoft.com...
> It appears that you are expecting to gain performance going to 64 bit.
> That
> may very well be the case, but it may just turn out not to be case. The
> outcome really depends on your workloads. I have seen the same app coming
> out
> with lower throughput on 64 bit than on 32 bit with the same hardware. You
> need to test your app to be sure.
> Linchi
> "raidken@.yahoo.com" wrote:
>> I understand that the 64 environment allows more addressable memory.
>> Are there any additional performance benefits other than possibly those
>> gained by increased memory addressability?
>> Assuming the same database running on a 32 bit environment and a 64 bit
>> environment and applications hitting both environments with the same
>> work load. Can the 64 bit environment have a higher throughput. For
>> this hypothetical case let us assume that advantages of having a bigger
>> proc cache does not help.
>> cheers
>> Ken
>>|||raidken@.yahoo.com wrote:
> I understand that the 64 environment allows more addressable memory.
> Are there any additional performance benefits other than possibly those
> gained by increased memory addressability?
Putting aside the benefits for a moment, I'd strongly advise checking
for 64-bit availability of components where necessary. I recently got
dragged in to the later stages of a project where this hadn't been done
and had real trouble getting a 64-bit SQL 2005 installation to link
through to an old Informix box. In the end they had to roll it back to
32-bit.|||Greg;
I can't share it publicly. But if you drop me an email.
Linchi
"Greg Linwood" wrote:
> Hi Linchi
> Any chance you can share what type of workload you've identified that
> exhibits these characteristics & how this has been measured?
> Regards,
> Greg Linwood
> SQL Server MVP
> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
> news:173E5682-2471-4A6F-9DD7-33125DCEB413@.microsoft.com...
> > It appears that you are expecting to gain performance going to 64 bit.
> > That
> > may very well be the case, but it may just turn out not to be case. The
> > outcome really depends on your workloads. I have seen the same app coming
> > out
> > with lower throughput on 64 bit than on 32 bit with the same hardware. You
> > need to test your app to be sure.
> >
> > Linchi
> >
> > "raidken@.yahoo.com" wrote:
> >
> >> I understand that the 64 environment allows more addressable memory.
> >> Are there any additional performance benefits other than possibly those
> >> gained by increased memory addressability?
> >>
> >> Assuming the same database running on a 32 bit environment and a 64 bit
> >> environment and applications hitting both environments with the same
> >> work load. Can the 64 bit environment have a higher throughput. For
> >> this hypothetical case let us assume that advantages of having a bigger
> >> proc cache does not help.
> >>
> >> cheers
> >>
> >> Ken
> >>
> >>
>
>
)