64 Bit Windows Server with 64 Bit SQL Server 2005 | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

64 Bit Windows Server with 64 Bit SQL Server 2005

Does anyone have experience with 64 bit SQL Server 2005 on 64 Bit Windows Server? I am doing research to determine what 64 bit would do for us today. I am looking for justification to upgrade to 64 bit. Our database is a very heavy transactional database. Can anyone point me to any particular web sites or white papers that I can read? I am trying to justify would the benefit in performance justify the cost. Would there be a great increase in performance for a heavy transactional database? Would it help in locking? etc… Has anyone had any issues with 64 bit that they are now sorry that they upgraded from 32 bit to 64 bit?
for only 32->64 bit, nothing else,
ie, identical hw, identical sw, OS, SQL 2005 for small memory test, 64-bit should 2-3% slower than 32-bit
that is not a mistake
the reason being 64-bit code is larger than 32-bit, generates more memory bandwidth etc however, if you have large memory requirements, then unfettered access to > 3GB memory (no AWE overhead and limitations) can be a big help
if your app has VAS issues, 64-bit helps, but you should still fix your VAS issues. HW SQL & 64-bit
for many people, thee upgrade will involve a HW change,
possible OS change (beyond 32->64),
SQL Server 2000 to 2005,
all in addition to the 64-bit upgrade most especially for the SQL Server 2000 to 2005 change,
get a performance baseline first, including perfmon and profiler traces
learn to parse profiler to determine average CPU & duration of top SQL
learn to interpret execution plans,
some plans will change, some times with minor or even severe negative consequences
if you did the baseline, and know how to read execution plans
any negative execution plan changes can be handled
otherwise, you may experience significant heart ache 1. HW, consider there will probably be a 2-4X boost on this alone
2. OS upgrading Win2K3 32-bit to 64-bit has little risk
3. SQL 2000- 2005: as described above
4. memory – another big benefit is that SQL 2005 Std can use > 2GB memory,
a serious annoyance on SQL 2000 Std a potential negative in going to 64-bit
in 32-bit SQL, the procedure cache was limited to approx 800-1000MB
of course, such a grossly large procedure cache should be avoided at nearly all costs apparently some one went 64-bit with an app that did not use stored procs, ie, straight SQL
which blew up the procedure cache to 32GB on a 64GB system
so every call required a search of the 32G procedure cache, and still not find a match, a terrible waste SQL 2005 has better ability to auto parameterize SQL
but stored proc is still the way to go
]]>