September 19, 2018 at 7:02 pm
I had to move some tables and procs to new DB. The proc that was working relatively faster (27 secs) in old DB now taking longer than 2 mins. I've attached execution plan from both DBs. Any help suggestions could be greatly appreciated.
Statistics and Indexes seem accurate.
How was the data moved?
1) First created schema on new DB.
2) Used import export wizard and kept the identity key as is.
3) Then applied FK scripts
After that, made sure all the indexes were copied and rebuild.
September 20, 2018 at 3:39 am
SQL_Surfer - Wednesday, September 19, 2018 7:02 PMI had to move some tables and procs to new DB. The proc that was working relatively faster (27 secs) in old DB now taking longer than 2 mins. I've attached execution plan from both DBs. Any help suggestions could be greatly appreciated.
Statistics and Indexes seem accurate.
How was the data moved?
1) First created schema on new DB.
2) Used import export wizard and kept the identity key as is.
3) Then applied FK scriptsAfter that, made sure all the indexes were copied and rebuild.
Look at Query 1 in the old plan and the new plan. Query 1 in the old plan has an implicit conversion warning, in the new plan it doesn't. This means you almost certainly have data type differences between your old database and your new database.
Many of your queries are affected by implicit conversions. At least one of them is conversion of a table column to NVARCHAR to match an NVARCHAR variable. Change the datatype of the variable to that of the column and you will probably change a table scan to a seek.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 20, 2018 at 5:44 am
I'd love to help out, but I really don't want to dig through all 20-30 plans here. Which one am I to focus on?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 20, 2018 at 5:51 am
Indexes, or queries, are not the same between the two databases, so we're comparing apples to hammers. Look at Query 5. In the old plan, it has three index seeks. In the new plan, it has to do a key lookup to retrieve, presumably, the same data. That means there are structural differences between objects or code. To truly compare performance, we need to be able to compare like to like, otherwise we're not so much spotting performance differences as we are spotting structural differences. Maybe that's the issue, but it makes full comparisons hard.
You also have differences in the statistics. Query 1 shows 83k in the old plan and 85k in the new plan. I'd suggest updating all the stats on both databases using WITH FULL SCAN, again in order to compare like to like. Differences in statistics frequently explain any difference in execution plans.
Let's resolve all this stuff, then we can concentrate on if there really is a fundamental performance difference between the two databases.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 21, 2018 at 9:55 am
And just in case, do you happen to be changing SQL versions? Perhaps from a 2012 or earlier to 2014 or later? That would entail a change in the optimizer, and that could have all manner of grief, but again, to pile on to what's already been said by Grant and ChrisM - we don't have enough evidence to know if we're even comparing like to like, and what they did see suggested possible differences in the objects
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply