We are upgrading our infrastructure from SQL 2000 to SQL 2008 R2. In this process, we are changing our hardware (32 bit to 64 bit), OS (from Windows 2003 to Windows 2008), SQL Server (2000 to 2008 R2). We are running on 2 node cluster. Using EqualLogic SAN for Data and Log drives. Lan connectivity is teamed, but SAN is not.
Coming to the problem: We did the following
Restore SQL2000 database on SQL2008
Run DBCC updateusage
Run Update Statistics for all tables (not with fullscan)
Again repeat the same process for about 4 times (It was a test case.). At the first time it took about 35 minutes to run the whole process for a 150 GB database. But on subsequent runs, the time to run these process were increased like about 10 minutes extra. This is happening only for one node. The other node, each time we tried took about an average of 36-40 minutes. We tried to copy the 150 GB file over the network to the local drive, it was transfering in about 105Mbps in a 2GB teamed lan network. The same file were transfering in about 175Mbps copied from local drive to SAN (which is on 10GB network) showing the usage as about 5-7%. But when we run the restore and update statistics operations, the SAN usage is about 0.5 to 1.5%. Every time we run the above process the memory usage increases while runnign the 2nd and 3rd step. The server has 60GB memory. The max memory option is set to 52GB. Only one instance running on this server. Also I have similar issue with other cluster which has two instances but configured as active-active. One node works fine, but the other node has issues.
The same performance degradation happens when we run heavy processing on this node after we do the restore and then update statistics operations.
If anyone have an answer to this problem, is very much appreciated.
Thanks in advance.