Getting terrible performance on newly created views

Question

My team is moving several hundred database objects from our QA server to our UAT server. All of the new objects are in two schemas that don't yet exist on the UAT database.

The first thing we do upon creating all these objects is run some very complex views to do an initial data population of our base tables (yes, the views are badly written, and it would be much more efficient to write them as stored procs, but this isn't the thrust of my question).

Most of our data load testing has been done in a database where all the objects already exist, and the performance is fine. The longest load is ten minutes.

However, when we move these new schemas/objects onto a different server and try the data load, the same code doesn't finish in 10 hours. However, if we try to run the underlying view separately, while we wait for the first exercise of the view to finish, it's very fast.

It seems obvious this is a problem with the optimizer (we've verified that the code is exactly the same between servers: we're not missing any indexes or stats). If we cancel our long-running queries and restart them, then the performance matches QA.

Here's what we've tried:

• Rebuilding all the indexes.
• Updating all the statistics with fullscan.
• We've verified that SQL Server is not creating any additional statistics beyond what's automatically created by our indexes.
• DBCC FREESYSTEMCACHE('ALL')
• The new environment has better specs than the one we're porting from: more cores, more memory, plenty of space.

This stuff makes some difference, though it's still nowhere as quick as it is in our original environment.

What am I missing here? Beyond cleaning the optimizer, the indexes and the stats, what else could be driving the performance?

I also don't understand why the optimizer picks a terrible plan for the initial data load, but then, while that load is crawling along, it picks a great plan for the same code if I run that on another cnn.

SQL Version is SQL 2012, Svc Pack 3.

=========================

EDIT: I understand that nobody is going to be able to produce a piece of code that fixes my issue. I am curious if someone knows of an area I haven't explored yet. Examples:

• The command I'm using to flush the cache is incomplete.
• SQL Server keeps in memory stats that I'm not privy to (the dba I work with mentioned this, but did not provide a link, and I don't see anything online).
• A link that explains how the optimizer generates one plan for a view select, but then provides a completely different plan on another cnn for the same view select simultaneously.
• Some other type of metric that I need to look at that I haven't mentioned.

Answer 1

I've seen faster hardware run slower because it can hit concurrency issues that don't crop up on the slower hardware, especially when more cores are introduced.

Check your MAXDOP settings. CXPacket waits can indicate parallelism contention. https://www.brentozar.com/archive/2013/08/what-is-the-cxpacket-wait-type-and-how-do-you-reduce-it/