SQL Server Performance

64 Bit Windows Server with 64 Bit SQL Server 2005

Discussion in 'SQL Server 2005 Performance Tuning for Hardware' started by lcerni, Feb 20, 2007.

  1. lcerni New Member

    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?
  2. joechang New Member

    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

Share This Page