32 bit vs. 64 bit performance, Why is SQL code running slower in 64 bit
For some reason when running a certain procedure that takes about 90 minute in a 64 bit SQL server environment takes 30 minutes in a 32 bit environment. I verified this, the vendor verified this as did another client. All are 3 to 1 ratio. The advent of this database was attaching a db (.mdf & .ldf files) from a sql server created on a different server and I am not sure about the environment. If it went from different versions of SQL, could be sysobjects be effected? would it suffer from not going through an active upgrade? Is their certain types of code or functions that I should look for? How to fix? The current production environment has been running for 2+ years Thanks
First of all, you need to check a few things out. Are you really comparing two identical systems except for 64 and 32 Bit? What are the differences between the 32 Bit and 64 Bit systems? - Windows Edition - Hardware Setup - SQL Server settings (especially min and max memory and parallelism) What behaviour are you seeing when running the procedure on each system? Is one system behaving differently than the other (I/O, CPU usage, Memory comsumption)? Are you testing the systems the same way? Are you calling the stored procedure from a restart of the server, or are you using a "warmed up" system (data is in the sql server cache)? If you can confirm a few of these points, it will help you narrow down what could be causing the problems. All things being equal, I would expect the 64 Bit system to be slightly faster.
Did some more testing and found some ways to improve perfomance significantly! While most things ran faster, there where some areas that ran slower. Specifically when there where subqueries and their fix was to replace them with in-line tables. While the 32 bit engine was seemingly tuned for this kind of data processing, the 64 bit was not. If a subquery (correlated) returns a single record (or a few) the difference might not be accented but if the subquery returns many rows, the sql engine will be forced to loop through and evaluated/join in each iteration. For an in-line table approach, it will utilized a join approach and rely on more memory which suited for the 64 bit's strength. In line tables might be a better approach in general but when working with legacy code, it is something to look for.