Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Ways to minimize sort operations


Ways to minimize sort operations

Author
Message
Nils Gustav Stråbø
Nils Gustav Stråbø
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2013 Visits: 3575
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 !
brian118
brian118
SSC Veteran
SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)

Group: General Forum Members
Points: 290 Visits: 585
Jason Lees-299789 (9/15/2010)
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.


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!

Regards
Brian Ellul
----------------------------------------------------------------------------------
Computers are incredibly fast, accurate, and stupid. Human beings are incredibly slow, inaccurate, and brilliant. Together they are powerful beyond imagination.
- Albert Einstein -
brian118
brian118
SSC Veteran
SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)

Group: General Forum Members
Points: 290 Visits: 585
Nils Gustav Stråbø (9/15/2010)
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 !


No problem and welcome :-)

Regards
Brian Ellul
----------------------------------------------------------------------------------
Computers are incredibly fast, accurate, and stupid. Human beings are incredibly slow, inaccurate, and brilliant. Together they are powerful beyond imagination.
- Albert Einstein -
salman.samad
salman.samad
SSC-Addicted
SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)

Group: General Forum Members
Points: 463 Visits: 230
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!
deanroush
deanroush
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 265
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!
alen teplitsky
alen teplitsky
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1595 Visits: 4621
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
sjsubscribe
sjsubscribe
SSC Veteran
SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)

Group: General Forum Members
Points: 281 Visits: 595
alen teplitsky (9/15/2010)
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


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.
alen teplitsky
alen teplitsky
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1595 Visits: 4621
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
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21075 Visits: 18259
Nice article - thanks. Good examples.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

bouarroudj Mohamed
bouarroudj Mohamed
SSC-Enthusiastic
SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)

Group: General Forum Members
Points: 124 Visits: 146
A more efficient and clean way to do it is on client side

Bouarroudj Mohamed
www.sqldbtools.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search