SQL Server X64 and X86 - differences and what you see in terms of performance?
Is 64 bit means more memory? Yes, to be precise.
Going forward you will see majority of Microsoft product will go with 64-bit, such as Operating System, Web & Database Application software. So what is the difference between X64 and X86 based applications.
As 64-bit is huge-pool of memory having large number of concurrent users to the application is no more a problem, with this more physical memory that can be addressed, the greater the number of concurrent user connections can be sustained without breaking a sweat. So if you have been asked to migrate to 64-bit do not hesitate to say 'YES' as such a migration of environment to the 64-bit SQL Server will still require a good deal of planning and work. Not to mention about ETL process completion, using the legendary DTS methodolog system from SQL Server 2000 and before has not been ported to 64-bit environments. SSIS is a replacement for DTS and will work with SQL engine within a 64 bit application that will be significantly more powerful and flexible. So it is better to get more practice with SSIS so these packages can be converted or rewritten before the 64-bit leap.
32-bit application is one of the reason that memory has been a constraint on scaling-up server capacity. Once you consider that on a Windows system or any application, half of this is reserved for the system address space (aka PAE with /3GB switch is used in boot.ini) the remaining private address space for each process will get hit even it is a small one to compare, having load on virtual memory. So to answer to this problem of memory addressing is 64-bit computing, which allows much more memory to be addressed. Because of memory-addressing limitations of 32-bit database instances, extremely large partitions had to spread across multiple instances of SQL Server
Then coming to the SQL Server technologies such 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. The simple list of terms are as follows:
- They're most likely buying a server that will support 64 bit
- The cost of 32 or 64 bit software (Windows & SQL) is the same.
- A 32 bit standard Windows/SQL install will support 2-3 GB of memory
- A 64 bit standard Windows/SQL install will support 32 GB of memory.
- Adding memory to the 32 bit system will require an upgrade to enterprise editions of both Windows & SQL. Even with discounts this can be in the $10,000 + range plus memory
- Adding memory to the 64 bit system means just buying the memory.
In relation to the advantages list I would like to refer the documentation about the advantages of 64-bit environments for SQL Server which relates to the AP's Advance Planning and Optimization with SQL Server. As referred above Analysis Services (OLAP based applications) can be one of the most memory-hungry applications around, especially if you're working with databases several gigabytes in size or processing data cubes with many dimensions. Now the consolidation of servers is an important bit within the Organisations, so having such restricted compatability using 64-bit server will reduce the memory barriers that existed before are gone, these apps can run side by side on the same machine and still show off a boost in performance that they couldn't have before, don't take it granted and always test your applications in real-time.
Having higher resources is always helpful and due to the nature of Windows Server that is a complex system and performance is constrained by the component with the lowest performance. So if the problem persists best option is to identify the bottleneck at first going through an issue of memory bandwidth. As memory plays major role in how fast data can be moved to and from memory and written to disks. Then comes to the latency (speed) to cater those data needs, that leads to I/O performance about how much data must be moved by inserting or updating.
