I made the following post on the DNN Forums, and decided to post it here in case someone reads it here first...
This is all related to: Upgrading DNN from local SQL Server 2000 to Remote SQL Server 2005
We are experiencing an issue with DNN and a 64-Bit SQL Server 2005 database server that seems to be related to the SQLDataProvider. We are currently running a production web server (Server A) that has an instance of SQL Server 2000 running locally. We have a mixture of DNN3 and NON-DNN sites running on Server A. All DNN sites are fast and run smoothly, only experiencing a few issues during peak traffic times regarding the local SQL Server 2000. Previously we had a separate server for our production SQL Server 2000 databases. However, we recently acquired a very fast 64-Bit server that came from Overture.com (Server C) and had to make room in our rack by decommissioning the old database server after moving all databases to Server A. Now, we are in the process of moving all Server A databases to Server C , so we can go back to using Server A as web server only. We have been testing and planning our database move utilizing our development server (Server B). Our goal is to move ALL Server A databases to Server C.
Server A:
Dell 2850
Dual 3 Ghz Xeons
2 Gb RAM
32-Bit
Windows 2003 Standard Edition
SQL Server 2000
Server B:
Dell 1550
Dual 1 Ghz Pentiums
2 Gb RAM
32-Bit
Windows 2003 Enterprise Edition
SQL Server 2000 Standard Instance
SQL Server 2005 Enterprise Instance
Server C:
Dell 7150
Quad Itanium Processors
12 Gb RAM
64-Bit Windows 2003 Enterprise Edition
64-Bit SQL Server 2005 Enterprise Edition
We began testing the NON-DNN site and DNN site databases against Server B, all with success. We also setup DNN instances on Server B that used Server C as database server. Testing was successful, but all DNN databases tested were less than 100 Mb. We noticed that the DNN sites on Server B that talked to Server C DNN databases were slower, but dismissed it as being on the slower Server B when compared to Server A.
So, after much testing for NON-DNN sites, we moved all NON-DNN databases to Server C. All NON-DNN sites use the SQLOLEDB Provider. Our largest NON-DNN database is over 3.5 Gb. After the move, the site with the largest database experienced great performance/speed improvements. Some larger data transactions were executed in almost half of the time. We were very pleased with the results.
After successful NON-DNN databases move from Server A to Server C, we began moving the DNN databases to Server C. The first 3 were very small databases. However, the largest of the 3, we noticed a difference in speed. Concerned, but determined to move on, we moved a 2.5 Gb DNN database to Server C and gave it a try... The speed was horrible taking as much as 15-45 seconds to deliver a page. We quickly backed out of the upgrade and began our investigation…
Doing a little research, I dug into the DNN documentation for Data Access and looked at the code to see DNN uses System.Data.SQLClient as the provider. Why would SQLOLEDB be fast and the DNN SQLDataProvider be so dreadfully slow and only when on a 64-Bit server? I really have no desire to change any of the core DNN code to use the System.Data.OleDb as the provider to test and see if there is a difference, so any recommedations and/or suggestions are appreciated.
I know there are a few issues with 64-Bit SQL Server 2005 tools running slow: You may experience slow performance when you run 32-bit SQL Server tools on 64-bit operating systems. But, this should not be related to the issue since this concerns just the tools, or does it?
I plan on burning a support call to Microsoft, but would like to gain any insight others might have before I make the call.