Recently, I've been working on a project that is completely different than SQL Full-Text Search (SQL FTS), challenging and fun too :-) and is related to SQL Server 2000 (64-bit) Enterprise Edition and the newly anounced Intel's Itanium 2 64-bit CPU codenamed "Madison". Earlier this year Microsoft released both Windows Server 2003 (64-bit) as well as SQL Server 2000 (64-bit) EE and with newly emerging hardware vendor's 64-bit server platforms, SQL Server 2000 64-bit processing is set to take-off, IMHO.
Obviously, one benefit of 64-bit processing is the increased “flat” or linear memory addressability of up to the TB level of RAM (practical limit of 512GB), and therfore no fooling around with PAE, AWE, etc. When comparing SQL Server 2000 (64 bit) running on the new Itanium 2 platforms, the 64-bit platform will show performance improvement directly related to this larger memory addressability including reduced I/O due to larger memory buffer pools. Database applications that require work loads larger than 4GB, can benefit from the higher memory addressability of the 64-bit platform as memory sensitive workloads consume more memory.
Obviously, one benefit of 64-bit processing is the increased flat or linear memory addressability of up to the TB level of RAM (practical limit of 512GB), and therfore no fooling around with PAE, AWE, etc. When comparing SQL Server 2000 (64 bit) running on the new Itanium 2 platforms, the 64-bit platform will show performance improvement directly related to this larger memory addressability including reduced I/O due to larger memory buffer pools. Database applications that require work loads larger than 4GB, can benefit from the higher memory addressability of the 64-bit platform as memory sensitive workloads consume more memory.
However, while the benefits of 64-bit processing is well know in general terms, and if I may, I'd like to get your opinion and feedback on exact T-SQL queries that you believe might benefit from both the increased memory as well computational features of the Madison chip. For example, SQL queries that can use in-memory Hash Joins, Sorts, hash aggregates, and other queries that can take advantage of Parallelism could benefit from 64-bit processing as well as large workloads to reduce i/o (this is not necessarily a complete list), but what exact examples of T-SQL ad hoc or user stored procedures would you test?
Also, what actual and practical SQL queries could benefit from the 128 registers of both floating point and integer? As these too can be valid examples of why Microsoft customers would want to upgrade their 32-bit SQL Server's to SQL Server 2000 (64-bit) EE, IMHO.
I invite your feedback and comments and examples of ad hoc and stored procedures that might benefit true 64-bit processing and I'll pick the best of them to test next week!