Whats with 64 bit computing on SQL Server

What advantages we took when deployed the 64-bit computing within SQL Server

As you aware 64 bit computing with 64-bit processors have become the standard for systems ranging from the most scalable servers to desktop PCs. The way to take full advantage of these systems is with 64-bit editions of Microsoft Windows products.  Same way SQL Server has evolved in using 64 bit computing from SQL Server 2000 version onwards, so what improvements you can take advantage with this technology. For me it is with ETL to extract, transform, and load processes, they have coped with large data integration by conforming and loading the data into the target server in multiple phases.

Though you can say this was well manage with usual 32 bit editions, but there is a stage where the import of data is huge in terms of data warehousing database performs all the aggregation, sorting, and other operations, potentially contending with other equally important queries on the database server that use the common resources. As data volume grows with increasingly complex aggregations, memory pressure on the database server rises, causing data to be swapped out to disk when large data sets cannot fully fit in the memory.

So with 64 bit technology Integration Services (SSIS) has been evolved to confront all the issues and also be able to do all the aggregation and sorting of the entire data set in the memory, eliminating the penalty of having to swap out to disk. Being able to load larger data sets in direct addressable memory not only optimizes throughput, but frees up the database engine to serve other equally important queries and workloads. Similarly the performance speed during the aggregation and processing the large amounts of OLAP data is very much helpful in using with 64 bit editions, so with large scale query increases the requirement of calculations and processing data more than 3GB in size with concurrent users accessibiliy.

Similarly contention and deadlocks has improved better with SQL Server 2005 snapshot isolation eliminates these challenges and radically improves performance by allowing larger quantities of data to be processed by the query in memory. SQL Server 2005 (64-bit) servers run extremely large queries concurrently with optimal results, which is an important benefit for us to manage data warehousing. Same as with snapshot isolation, partitioned tables partitions can then be accessed independently, decreasing the performance impact arising from I/O bottlenecks when accessing large amounts of data (especially data sets larger than 3 gigabytes) from these tables. Because of memory-addressing limitations of 32-bit database instances, extremely large partitions had to spread across multiple instances of SQL Server. With 64-bit technologies, the memory addressability limitation within a SQL Server instance is eliminated, providing demanding workloads to keep the partitions within the same server or instance, and processing the data in memory without the limiting the impact of I/O.