64 bit computing configuration with SQL Server 2005 - what you can take-on?
It has been a while in posting X64 related information here, so that comes now about configuration gotchas you may need, for a starter though.
X64 computing platforms have become common installations now a days due to the hardware reduced costing and availability, at the same time the DBA will have big job to tune the platform in terms of manageability & performance. In this regard running ERP application such SAP product is supportable on SQL Server Enterprise edition within 32-bit and 64-bit platforms. Being SAP is another demanding application to implement in large-scale deployment by using Windows Server 2003 or even latest Windows Server 2008 can cope up the largest workloads to be processed by offering a sizeable amount of memory including SAP BW with Unicode implementations.
In this scenario running such large workloads require high-end hardware resources such as Memory, that may not be possible in handing within 32-bit platforms having the issues like frequent out-of-memory errors can occur when running large batch jobs SAP application instances. There is no alternative that you cannot optimize the code programatically for the SAP processes that are complicated in terms of ETL procesess. This is where a bit clarification on memory limit configuration is essential to understand within 32 bit platform such as 32-bit virtual address space that can be addressed to 4 GB. So taking the defaults of 2 GB or 3 GB are directly addressable by an application process whereas 1GB or 2 GB are only addressable by the operating system. This will limit the growth of taking 4GB virtual address memory cieling in 32-bit that will have a degrade process on overall performance, and increase costs since each server has a limited processing capability. Also the future of SAP deployment will be on 64-bit only, that means the upgrade of SAP platform is compulsory!
Taking into the consideration of 64-bit within SQL Server will have support of IA64 and x64 computing platforms and makes no distinction between these platforms in regard to their limitations. That will provide an increased linear address space, without requiring the use of an additional layer such as AWE. This means such query process can be assigned up to 16 GB of memory to perform joins, sorts, and grouping, leaving only a very small number of queries needing tempdb. Most queries can be performed fully in memory using the data buffers available to platform specific defaults, greatly improving performance. No doubt that pushing up the memory in a dramatic manner can reduce the I/O rate, which improves the response time by a factor of 2 to 4 and lowers investment costs for I/O hardware.
The configuration factor within 64bit computing is 'lock pages in memory', such I refer related blog posts [http://sqlserver-qa.net/blogs/perftune/archive/2007/07/24/1132.aspx & http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/05/03/using-64-bit-sql-server-2005-lock-pages-in-memory.aspx]. This configuration parameter step should not be used within 32-bit editions (SQL Server) in order to avoid SQL Server buffer pool pages being paged out into the Windows page file(s) one needs to allow the privilege to ‘lock pages in memory’ for the user context which starts SQL Server Services. Also one of the Technet document refers about CPU & memory limitations between 32 & 64 bit platforms, as below:
|
General Memory Limits |
32-bit |
64-bit |
|
Total virtual address space |
4 GB |
16 TB |
|
Virtual address space per 32-bit process |
2 GB (3 GB if the system is booted with the /3gb switch)
4 GB if compiled with /LARGEADDRESSAWARE
2 GB otherwise |
Not applicable |
|
Virtual address space per 64-bit process |
Not applicable |
8 TB |
|
Paged pool |
470 MB |
128 GB |
|
Non-paged pool |
256 MB |
128 GB |
|
System cache |
1 GB |
1 TB |
|
Physical Memory and CPU Limits |
32-bit |
64-bit |
|
Windows Server 2003 Standard Edition |
4 GB/1 to 4 CPUs |
32 GB/1 to 4 CPUs |
|
Windows Server 2003 Enterprise Edition |
64 GB/1 to 8 CPUs |
1 TB/1 to 8 CPUs |
|
Windows Server 2003 Datacenter Edition |
64 GB/1 to 32 CPUs |
1 TB/1 to 64 CPUs |
(Source Technet)
One can raise a doubt about physical disk related configuration, well the inbuilt SQL Server 2005 on-disk structures between all three supported platforms (IA64, x64 and x86) are the same. The upgrade path (migration) from a 32-bit computing platform to one of the 64-bit platforms can be handled easily either by copying the database files to the new 64-bit hardware or by restoring a backup from the 32-bit platform to the 64-bit platform, usual practice. So the main transition practice you must observe is on application side, as the database side can be done server by server or system by system. The application must support platform heterogeneous systems between the three different platforms, if you compare. This still can be done using x64 servers for the ERP base application tier and there is no change for Cluster based infrastructure with the IA64 database server and having the rest of application server instances on x86 or x64 does work since usual application data stores the executables on the Central Instance in platform dependent directories. So the architecture solution must address the 3 important factors such as High Availability [to provide best performance & ensure fault tolerance], Scalability [such as adding new server hardware without disruption] and Large data volumes support [application & data configuration portability]. The usual SQL Server 2005 architecture are designed to use commodity servers and high-end spec server architecture for the performance & storage (mainly) that is available from all hardware vendors, by leveraging usual commodity type architecture to reduce costing to ensure support & maintenance process can be well-managed.
For 64-bit computing setup, another configuration set is to ensure that playing with 'lightweight pooling parameter' is not recommended in general because it results in only minor performance improvements. This parameter is typically used for benchmark workloads that are extremely uniform. Also the 'max worker threads' parameter should be left to default (0) as this is dynamic, that will decide based on the platform (32Bit or 64Bit) and on the number of CPU Cores available on the maximum number of worker threads. The actual number of worker threads can be evaluated by taking help of DMV such as: select max_workers_count from sys.dm_os_sys_info.
On the database side configuration, the important one is using checksum compared to ‘torn page detection’ which was used so far, can increase resource consumption slightly. Dependent on the I/O volume, an increase of up to 5% CPU resource consumption can be observed. Be aware that databases which are getting upgrades from SQL Server 2000, restored from a SQL Server 2000 or SQL Server 2000 databases getting attached remain on the level those have been before (usually torn page detection) that leaves ‘checksum’ checks are not activated and in case of creating database within 2005 version will have this 'checksum' enabled by default.
With this it wraps up this post and next post will have a cover the backup methods & advantages within 64-bit platform.
**__________________________________**
SQL Server MVP, Sr. DBA & industry expert.
-
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.