asked
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.
Five tables isn't very bad at all. Are you sure that it's not testing views or joining views to views, something like that. Just a five table query is unlikely to hit the changes in the optimizer. Regardless, look to the execution plans and see if they're different. If so, try to determine why. Also, look at the statistics. See if they're different between the two systems.
The database on each server was a restore from the same 2008 R2 database backup. Then the restored database on 2012 was switched to Compatibility Level - "SQL Server 2012 (110)". Stats have been updated since the restore and there are NO data updates of any kind happening on these two servers (other than me creating this table via a view). The two servers are for report testing and comparing our reports running against 2008 and 2012. I do believe you're onto something when you mention the view. It is somewhat complex with a 5 table join and a whole bunch of CASE statements in the SELECT. Thanks for the hint. I will be looking into the optimizer / view. Thanks, Darrell
15 People are following this question.
