SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Brian Ellul / Ways to minimize sort operations / Latest Posts

RE: Ways to minimize sort operations

reggiesnelly — Fri, 17 Sep 2010 13:53:01 GMT

Good intro article. The next time if you can add larger data sets to query where there might be issues such as memory pressure, IO, etc. A few hundred records seems too small to base decision in a production environment.

RE: Ways to minimize sort operations

bouarroudj Mohamed — Thu, 16 Sep 2010 09:34:37 GMT

A more efficient and clean way to do it is on client sideBouarroudj Mohamedwww.sqldbtools.com

RE: Ways to minimize sort operations

SQLRNNR — Wed, 15 Sep 2010 12:52:04 GMT

Nice article - thanks. Good examples.

RE: Ways to minimize sort operations

alen teplitsky — Wed, 15 Sep 2010 11:56:31 GMT

the enterprise SSD's are something like $7000 for 200GB or so. forgot the exact numbers from the HP branded ones. a 146GB hard drive is less than $300. for the data i can buy up a lot of hard drives to spit the IO up. the new Proliant G7's support up to 16 hard drives in a 2U server

RE: Ways to minimize sort operations

sjsubscribe — Wed, 15 Sep 2010 11:44:33 GMT

[quote][b]alen teplitsky (9/15/2010)[/b][hr]there were a few times i changed the sort order of indexes to make things a bit faster. when you can't spend $6000 on extra storage because all the internal hard drive bays are full but you need to optimize I/O performance you look at every little thing.i'm waiting for the SSD server drives to get cheaper to try them out by putting tempdb on them[/quote]After playing with SSDs for tempdb, I've concluded it's best to make all drives SSDs to get the max bang for the migration. Just tempDB seems to be an awfully small improvement for the level of disruption on the main box. On the other hand, for external raids and such, SSDs can be slowly implemented as prices come down.

RE: Ways to minimize sort operations

alen teplitsky — Wed, 15 Sep 2010 09:32:56 GMT

there were a few times i changed the sort order of indexes to make things a bit faster. when you can't spend $6000 on extra storage because all the internal hard drive bays are full but you need to optimize I/O performance you look at every little thing.i'm waiting for the SSD server drives to get cheaper to try them out by putting tempdb on them

RE: Ways to minimize sort operations

deanroush — Wed, 15 Sep 2010 09:24:44 GMT

Thank you for taking the time and effort to submit:-) this article. I always appreciate reading about how to optimize queries and actually being able to understand what the writer is trying to convey. I also appreciate the other feedback as a reality check!

RE: Ways to minimize sort operations

salman.samad — Wed, 15 Sep 2010 09:08:55 GMT

It's always refreshing to read clear, consise articles that get your grey matter spinning, even though they might not apply to you directly.Nice job Brian!

RE: Ways to minimize sort operations

brian118 — Wed, 15 Sep 2010 07:36:46 GMT

[quote][b]Nils Gustav Stråbø (9/15/2010)[/b][hr]Brian, I'm have to apologize to you because for some reason my eyes and brain didn't see that you kept the ORDER BY clause. For some reason I thought that the article explained a way to get rid of the ORDER BY, but when I read it again I don't know where that idea came from :rolleyes:Again, I'm sorry. Its a good article ![/quote]No problem and welcome :-)

RE: Ways to minimize sort operations

brian118 — Wed, 15 Sep 2010 07:30:06 GMT

[quote][b]Jason Lees-299789 (9/15/2010)[/b][hr]Like Nils, I dont quite understand the reason for the article, in 95% of cases the sort operation is the least of your worries in a plan, especially when you've joined 2 or 3 tables together to bring back an amalgamaged dataset, such as Stock levels by product, and unfilled orders. I'm not a fan of the Non-clustered Index with all other columns in an Include, one or two columns that are commonly associated with the business key, eg Indexed on ProductNumber, with the ProductName and ProductCode as include columns is reasonable.[/quote]Thanks for your feedback!I would put my worries when studying a plan where I have the biggest cost, which in this case I wanted it to be the sort operator, and that's why I didn't use a complicated SELECT statement. Yes, I agree, if the SORT operator is only a fraction of the total query cost I would not bother with improving it!

RE: Ways to minimize sort operations

Nils Gustav Stråbø — Wed, 15 Sep 2010 07:29:22 GMT

Brian, I'm have to apologize to you because for some reason my eyes and brain didn't see that you kept the ORDER BY clause. For some reason I thought that the article explained a way to get rid of the ORDER BY, but when I read it again I don't know where that idea came from :rolleyes:Again, I'm sorry. Its a good article !

RE: Ways to minimize sort operations

brian118 — Wed, 15 Sep 2010 07:22:35 GMT

[quote][b]Nils Gustav Stråbø (9/15/2010)[/b][hr]I'm a little confused here. What is the intention of the article? To avoid unnecessary Sort operators in the execution plan or to eliminate ORDER BY and still get the results sorted?If the idea is to eliminate the need for ORDER BY then what this article describes should not be trusted. There is no guarantee that an index (clustered or non-clustered) will return the result in an ordered manner. IT might work today, but what happens when the amount of data or statistics changes? Will the QO find a better plan and havoc the ordered results that used to be returned to the client? It might very well do so.There is only one way to guarantee ordered results. Say it after me: ORDER BY.On the other hand, if you don't want SQL Server to waste memory, disk I/O and CPU on sorting, then you could send the result to the client and let it do the sorting. There is usually lots of unused CPU capacity on the client computers.[/quote]Thanks for your feedback!The scope behind this article is to show how an already sorted result set will improve query performance since the data is already sorted. Let's take a DWH were having multiple indexes and/or Indexed views are not an issue since the data is preloaded during the ETL process. This method will improve query performance when running SQL or Reports on the source table.And yes, I'll say it after you.... the ORDER BY will always guarantee that the data set is ordered, in fact that's why I did also specify that in the SELECT statements. :-)With regards to your last suggestion, yes that can be an option, however I'm trying to keep all processing centralized on the server, which is accessible by all. I would argue that if a data set is always required ordered by a specific column/s and multiple users need to access this data, why not sort it for everyone in the first place.

RE: Ways to minimize sort operations

WayneS — Wed, 15 Sep 2010 06:56:20 GMT

Very nice article - Thanks!I'm especially glad to see the effect of an update statement on the different methods discussed. And as you so properly point out, the proper answer is "it depends".

RE: Ways to minimize sort operations

Rob Ashton — Wed, 15 Sep 2010 05:58:23 GMT

This might be a little nit-picky, so I apologise in advance, but I understood the DBCC DROPCLEANBUFFERS command to only clear [b]clean[/b] buffers from the buffer pool. If you don't use the CHECKPOINT command before calling DBCC DROPCLEANBUFFERS, you haven't forced dirty pages out of the page cache onto disk to maximise the effectiveness of DBCC DROPCLEANBUFFERS. (See [url=http://msdn.microsoft.com/en-us/library/ms187762.aspx]http://msdn.microsoft.com/en-us/library/ms187762.aspx[/url])If I'm right, this may also cause misleading performance results (or at least difficult-to-repeat performance results) between executions.

RE: Ways to minimize sort operations

Nils Gustav Stråbø — Wed, 15 Sep 2010 03:37:54 GMT

[quote][b]steveb. (9/15/2010)[/b][hr]I dont see how people are saying this is dangerous, from looking at his code he does put Order by on each of the select statments so the data will always be ordered.[/quote]Oops, my bad!! I apologize :crying:

RE: Ways to minimize sort operations

hallidayd — Wed, 15 Sep 2010 03:17:00 GMT

I agree with Steve - I'm not sure you have paid full attention to the SQL in the article. He is not suggesting removing order by, but creating indexes to optimise for the clause.Having said that, I can't remember ever creating an index to support an order by. As said, there are countless other factors far more important.I do have one issue with the article: there is no reason the IO should have been greater for the first query than the subsequent ones. I suspect that this was actually due to fragmentation of the original clustered index that of course was not present in the indexes created specifically for the tests. As such, this measure of improvement is false.

RE: Ways to minimize sort operations

steveb. — Wed, 15 Sep 2010 03:02:29 GMT

I dont see how people are saying this is dangerous, from looking at his code he does put Order by on each of the select statments so the data will always be ordered.

RE: Ways to minimize sort operations

Jonathan AC Roberts — Wed, 15 Sep 2010 02:36:01 GMT

So the whole article is basically saying adding an index can assist in the performance of queries containing ORDER BY.

RE: Ways to minimize sort operations

Jason-299789 — Wed, 15 Sep 2010 01:54:37 GMT

Like Nils, I dont quite understand the reason for the article, in 95% of cases the sort operation is the least of your worries in a plan, especially when you've joined 2 or 3 tables together to bring back an amalgamaged dataset, such as Stock levels by product, and unfilled orders. I'm not a fan of the Non-clustered Index with all other columns in an Include, one or two columns that are commonly associated with the business key, eg Indexed on ProductNumber, with the ProductName and ProductCode as include columns is reasonable.

RE: Ways to minimize sort operations

Nils Gustav Stråbø — Wed, 15 Sep 2010 00:59:00 GMT

I'm a little confused here. What is the intention of the article? To avoid unnecessary Sort operators in the execution plan or to eliminate ORDER BY and still get the results sorted?If the idea is to eliminate the need for ORDER BY then what this article describes should not be trusted. There is no guarantee that an index (clustered or non-clustered) will return the result in an ordered manner. IT might work today, but what happens when the amount of data or statistics changes? Will the QO find a better plan and havoc the ordered results that used to be returned to the client? It might very well do so.There is only one way to guarantee ordered results. Say it after me: ORDER BY.On the other hand, if you don't want SQL Server to waste memory, disk I/O and CPU on sorting, then you could send the result to the client and let it do the sorting. There is usually lots of unused CPU capacity on the client computers.

Ways to minimize sort operations

brian118 — Tue, 14 Sep 2010 20:11:37 GMT

Comments posted to this topic are about the item [B]Ways to minimize sort operations[/B]