sort performance in query plan

Maahi 2017-12-21 19:15:10

Hi All,

This is related query tuning. I have a query which is doing expensive sorting.
Without ORDER BY clause the query is returning the result set very fast when compared with having an ORDER BY DESC clause. Is there a way to avoid sorting from db side and suppose if the developer is so adamant and not listening for remove the ORDER BY clause. Is there an alternative way so that we can eliminate the sort operator in the execution plan.

Attaching the plan. Please share your thoughts.link text

Thanks

M

Aaron Bertrand 2017-12-21 19:27:10
You can have the developer sort in the app (can be quite effective when using a caching layer, for example), or you can add/change an index so that sorting is supported without an additional sort operator. There is no way to get SQL Server to sort without, well, sorting.
Maahi 2017-12-21 19:51:47
Thanks Aaron. One more thing, I want to ask is that, how can we make the DEV team understand why not to do the sorting at DB layer and can push it to the application layer. Most people are very reluctant to do so and they want everything to me done(all kinds of massaging within stored proc iteself) and they just add the data set to the grid control or whatever … they use it in the front end.. How to make them understand in a nice polite way.
Hugo Kornelis 2017-12-21 19:27:40
So I've looked at the execution plans. They are estimated plans, not actual plans, so they don't show the work actually done.

However, the plans show the exact same plan with the addition of a sort operator. (Might sound obvious to you but it's not – adding an ORDER BY can result in the optimizer picking a completely different execution plan). And they show an estimated row count of 7. There is no way that sorting 7 rows will ever take more than just a few milliseconds.

If you see much bigger performance issues than that, then please post the ACTUAL execution plans and I'll see what they show.

Maahi 2017-12-21 20:26:01
Thanks Hugo. The actual problem is, running the query individually for 1 user it is performing well. however, when the same sp/sql stmt is execute with 60 concurrent connections then the average response time is more than 3 secs and there SLA for the query to return the results back should be 1 or less than 1 secs. That's the concern.
Hugo Kornelis 2017-12-21 20:41:43
Even for 60 concurrent connections, fetching and sorting just 7 rows should not take that much time.

Please post an actual query plan instead of the estimated plan, there must be something else going on.

Maahi 2017-12-21 20:51:00
Its the actual plan only. I have executed the sql stmts in ssms and sending the plan again. If you can provide email and provide the test data as well.
Maahi 2017-12-21 20:51:57
attached "plan-using-ssms.zip" file.
Hugo Kornelis 2017-12-21 20:56:36
No, you are right. I didn't look good enough, they are actual plans. My bad.

If you want to get to the bottom of this, you can run a test with those 60 concurrent users with some extensive monitoring set up to help you identify why everything is running so slow. There has to be a bottleneck somewhere, but it's not visible from the execution plan.

Alternatively, just follow the suggestions you got both here and over at SQLServerCentral.com: either tell your devs to use the processing power of the client machines to do the sort, or create the nonclustered index that was suggested to you (which would enable SQL Server to produce the data in order without having to sort).

Aaron Bertrand 2017-12-21 20:58:23
Why would you fetch the plan with SSMS instead of Plan Explorer? The latter gives you much richer metrics, and eliminates interference from async_network_io.
Maahi 2017-12-21 21:08:39
I am currently connected from a remote machine where plan explorer was not installed and that's the reason shared plan generated from ssms.