July 17, 2019 at 10:59 pm
OK, at the moment, no sample data, and I don't have the query in front of me.
Some tech notes: Both the Production and QA servers are the same version / service pack / cumulative updates, QA does have fewer processors and RAM, both are virtual machines, both databases have the same indexes throughout.
One of the devs came to me the other day with an odd performance issue. His query (a rather simple query, a mere 4 joins, 2 of which were left joins,) took under a second in QA, but almost a minute in production. Did some checking, noticed the query plans between the two were slightly different. In QA one table was joined (hash match) earlier in the plan (further to the right,) while in production it was just slightly later and a nested loop inner join. Further, the estimate of the number of records was WAY off in production.
Ah-ha says I, statistics are whacked!
Ha-ha says the database, aside from the fact you update them automatically twice a week with fullscan, that didn't change anything!
OK, fine, lets see if it's something because the hardware is different, lets restore a copy of prod down to QA and see what happens. Nope, same thing. I did find if I forced the troublesome inner join to use a hash match, I got both the same plan shape and speed as QA.
At this point, it was getting close to the end of the day, this query isn't causing anyone any problems, so we left it.
Overnight, I thought maybe QA was on a higher compatibility level than production, this turned out not to be the case, both were on 100. Interestingly though, we were now getting the same plan shape in QA as we had been getting in production yesterday. Turns out QA was auto-refreshed by a customer process overnight.
So, what the heck, it's a copy, lets flip the prod copy to compatibility level 120 (SQL2014) and see what happens. Well, we got the plan shape QA had been giving us and a fast return of the results. So, to test and make sure nothing else changes, we flipped QA to 120 as well. And then got a WHOLE NEW plan shape in QA...
So, the question. Any ideas on why prod data and QA data will get different plan shapes, and even more different with the compatibility level upped? I know about the new query estimator engine, so I did expect maybe some change, but this was almost a radical change (and still fast, so the dev is happy.)
July 18, 2019 at 11:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
July 19, 2019 at 5:21 pm
Not sure if you ever figured this out or how the refresh process works but I've seen similar things that ended up being related to data skew. So that would depend on the refresh, if all the data is exactly the same between the environments.
Sue
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply