asked
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.
+99%. I've noticed that 64 bit seems to be slightly (meaning 5-10ms) slower in terms of processing, but it's memory management is so much better that it's usually a wash. These are not hard & fast numbers, just observation.
Grant, that suprises me. I have little experience with 32 bit -> 64 bit, I migrated a major production system about 5 years ago (64 bit systems ever since). It was in a huge mess (not to do with me luckily!) and we saw noticable improvements, but then again, it could just have had to do with it being a newer/cleaner system.
test 1 32 Work station vs 64 bitserver) When running the procedure on my desktop, it was not a warmed up and it took 30 minutes just like a 32 bit server. the desktop was: The workstation was a 1-year old of the shelf box. 1 Intel Core 2 Duo 3GHz, 3 GB RAM max 2 GB for SQL. 1 disk 200 GB. The server with 64 bit was warmed up and had: Utilization on only 1 of 8 CPU cores 64bit 3GHz RAM utilization looks too low but was also restricted to 12GB Server and SQL server did not perform other functions simultaneously. ran three times as long SAME WITH: Test case 2 server 32 bit vs server 64 bit) Window 2003 SP2, SQL 2005 SP3, 10GB of RAM assigned to SQL Server. My test 64bit server has only 4GB or RAM. 32bit Server has only 2GB of RAM.
You have tested a 32 bit and 64 bit system against each other, with differing configurations and hardware. This will not allow a good "apples to apples" comparison for the runtime. Why would you be limiting the Server hardware to just one of eight CPUs? Is it a limitation that has to be there? What editions of windows and SQL Server are you running? Standard Edition, Enterprise Edition?
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.
