Import/Export Wizard Performance Diff Between 2008 and 2012
Copying 89 million rows of data from one table to another via a view: • Testing the same senarion on two different SQL servers - one 2008 R2 other 2012 • Source, destination tables and view all in the same database on each server • No indexes on destination table • No data in destination table • Identical tables, data and view on each server • Logged onto each server directly using Remote Desktop to run Import/Export Wizard • Identical databases on each server and even the allocated and free space is identical • Amount of free space eliminated the need to allocate pages while copying • Drives on the same SAN for both servers • I am the ONLY user on either server - no other processes or logins active • Both SQL servers are identical o Windows Server 2008 R2, 64 bit, 8 CPU, 128 GB RAM On SQL 2012 Copied 89 million rows in 50 minutes On SQL 2008 R2 Copied 7 million rows in 50 minutes Is there this big a difference in the Import/Export Wizard's performance between SQL Server 2008 and 2012? Or do I have some other configuration or server difference that isn't real obvious? Thanks, Darrell Deck, West Bend
Well, I really fought with this, Yes, 5 tables isn't much. Also checked MAXDOP config and made them the same on both servers, still no luck. Checked max and min memory, the same. Checked for disabled indexes and found none. Finally I took the SELECT from the view and used it as a query for my source and it works!!! The use of the "view" in the Import/Export Wizard was going to take 17 hours to copy the 89 million rows. I stopped it after 7 hours and it still wasn't half way done. The use of the SELECT statement from the view as a "query" for the source in the Import/Export Wizard took 50 minutes!!! Something isn't quite right with the use of a "view" as the source in the Import/Export Wizard. Thanks, Darrell
Assuming that the statistics on the 2012 system were rebuilt after the upgrade, preferably with a full scan, and that you're running it under 11.0 compatibility, then, on a guess, I'd suggest the possibility of differences with the optimizer. You say you're referencing a view. How complex is that view? I have seen edge cases with very complex queries involving large numbers of objects that performed better on older versions of SQL Server. But, they were always the types of objects were problematic anyway, they just became more so as the optimizer became more stringent with newer versions.