November 27, 2019 at 12:05 am
Hi,
I have a strange situation where SORT operator is taking 10 sec in prod database and when run the same query in Test SQL instanceI'm not seeing that SORT and query completes in 2 sec. I have backed the same PROD database and restored in Test SQL instance.
any thoughts on why SORT is not happening in Test SQL instance, having same prod database?
November 27, 2019 at 12:43 am
In prod, Sort Operator, I'm seeing below warning:
Operator used tempdb to spill data during execution with spill level 1 and 1 spilled thread(s), Sort wrote 51440 pages to and read 51440 pages from tempdb with granted memory 972840KB and used memory 972832KB
November 27, 2019 at 2:23 am
Hi,
I have a strange situation where SORT operator is taking 10 sec in prod database and when run the same query in Test SQL instanceI'm not seeing that SORT and query completes in 2 sec. I have backed the same PROD database and restored in Test SQL instance.
any thoughts on why SORT is not happening in Test SQL instance, having same prod database?
You need to post the actual .sqlplan files here for us to help on that one. Otherwise, all we can say is that there's something different on the two boxes and you'll need to find it.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 27, 2019 at 1:50 pm
Yep, something is different between the two machines. What? Not a clue. Post the plans. If you need to, use SentryOne's Plan Explorer to anonymize the plans before you post them.
"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
November 27, 2019 at 6:11 pm
Thanks so much,
I have attached anonymized sql plans using SentryOne's Plan Explorer
This website is blocking sqlplans, so I've uploaded XML version of it. Please advise
November 27, 2019 at 7:14 pm
Thanks for posting those.
The test system is moving 9 rows on an estate of 1.3 million. Production is moving 6.7 million rows on an estimate of 9.3 million. I think, but I could be wrong, moving 9 rows is going to be radically faster than moving 6.7 million. I'd say these are not the same databases or the same data sets. To compare two systems, it's not enough for the query to be the same, you have to have the same data sets and the same statistics. Variation on these will lead to variations in behavior. The query hash and the query plan hash are the same between the systems, so the plans being arrived at are the same.
You have a bunch of issues going on here, not simply a painful sort operation. You have multiple conversions, implicit and explicit, that could be negatively impacting your queries. Look at the properties of the first operator to see a list of these. You have a lot of functions running on columns like this:
(Function2(Variable21, ?) = ?
That's going to cause you all sorts of headaches because that will prevent statistics and index use by the optimizer leading to table scans and inefficient joins. That pain is probably most evident in the fact that you're returning 37 rows (16 estimated) in Prod and 9 rows (16 estimated again) in Test, but these are filtered down from 6.7 million. Fixing these functions on columns and the conversions ought to make it possible to use your indexes and statistics to get better plans which, probably, will filter at the index instead of later.
At the very least, an index on Columns 3 & 36 (can't see the names, look at the Sort, NodeID 26) can provide a means of getting ordered data that would eliminate the sort operation and keep the Merge Join in place (the reason for the Sort, one table of 6.7 million rows joining to another of 9.6 million, a merge is a good choice, better would be filtering at access). You would only need to INCLUDE Column 35 to make that a covering index. This is all on Object 7. However, the likelihood, this doesn't help much because the core issue is all those functions and conversions.
"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
November 27, 2019 at 7:58 pm
thanks Grant.
I just took fresh backup of PROD database and restored in Test server and executed the same query and SORT is happening prod but not in Test. It executes in test 3 sec where as in prod 13 sec.
Thanks for your inputs but I'm still confused why sort is happening in prod but not in test.
I will give a try with index
November 27, 2019 at 11:25 pm
Well, in the two plans you showed, the sort was happening in both, just that one was only moving 9 rows. Is there a different plan?
"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
December 2, 2019 at 12:27 pm
OK, it's not doing the sort operation, but has the query sped up? I'm not comfortable with this solution. The problem you are having is not the sort operation. The problem you are having is a badly constructed query that needs a lot of tuning work. If you can change the code (as you had to do in order to put the hint in), then you should be spending your time addressing the issues I've raised.
"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
December 2, 2019 at 3:59 pm
please, show result of the following from both PROD and TEST:
select name, value_in_use from sys.configurations where name like '%server memory%'
December 2, 2019 at 8:00 pm
yes Grant, it is executing in 1 sec after forcing index seek
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply