64-bit - Query performance

There are clearly a lots of advantages on going to 64-bit. Read my previous blog on advantages of 64-bit SQL Server 2005. There were few cases where they found out that the query performance was bit slower in 64-bit vs. 32-bit, but that depends on type of workload. I was told not to generalize that 64-bit results in slower query response time. There could be many reasons:

Bad query plan – you need to consider re-writing query to optimize it to better utilize the cpu cycle. Certain CPU bound OLTP type workload could gain some performance advantage in 32-bit over 64-bit since the working set is smaller in 32-bit than 64-bit. This is a bigger issue for chips with smaller amounts of L2/L3 cache. The latest intel chips do not suffer from this as much. Below are typical size of L2/L3 cache in some standard platforms:

For EM64T, typical numbers for current chips are from 2MB to 16MB for L3 and 1MB to 2MB range for L2.

For IA64 – single core is generally 6MB /9MB and dual core is 24MB shared across both cores.

For AMD – there is no L3 cache and 1 MB L2. However this is a very different architecture with respect to memory access.

http://www.intel.com/products/server/processors/index.htm?iid=serv_body+proc

http://www.amd.com/us-en/Processors/ProductInformation/0,,30_118_8796,00.html