Suggestions on a query plan

Question

Suggestions on a query plan

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

TomThomson SSC Guru Points: 104773 More actions · Answer 1

Jeff Moden - Friday, December 22, 2017 6:48 AM
vsamantha35 - Thursday, December 21, 2017 12:48 PM
Thanks Lynn. 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.
There's no real issue with sorting in the database if it's done correctly. The key is WHEN the sort appears in the execution plan. In most cases, ORDER BY will be among one of the last things done but not always... especially if the sorts are on calculated columns from a view, etc.
Issuing a draconian edict such as never sorting in the database will possibly lead to a great amount of stupid code because it's sometimes hugely advantageous to sort in the database. Don't issue such an edict.

I agree 100% with Jeff on this. By teaching developers not to do sorting in the DBMS you may be buying yourself massive problems in future.

It does seem strange that the index structure for this table (or view?) is such that it causesthe optimser to do the sort before filtering. So it ought to be fixable by getting a less costly index structure.

But there is another option, as 7 rows is a very small number: split the query into two separate queries: the first query creates a table variable or a temp table that contains the selected rows, and the second query sorts the rows in the temp table; the two queries can be combied in a single stored procedure, to be called by the client app, so not much hassle for the add devs. With 60 queries in parallel this could use 420 short-lived rows in tempdb, which may cause some performance issues, but surely nothing like what's currently happening.

Tom

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 2

Lynn Pettis - Thursday, December 21, 2017 2:40 PM
Joe Torre - Thursday, December 21, 2017 2:36 PM
@drew,
It depends on the server and clients.
Try creating this index:
CREATE INDEX IXLubeAnalyst__Sample__DateDrawn_EquipmentLife_LubricantLife_DateRegistered_SampleID ON LubeAnalyst.Sample ( DateDrawn DESC , EquipmentLife DESC , LubricantLife DESC , DateRegistered DESC , SampleID ASC ) ;
Same thing I mentioned except I made it a filtered index.

If you create such a covering index, then by all means, do include the sort in the database just to guarantee the order because the data will logically already be in the correct order. The sort probably won't even show up in the execution plan.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 3

ScottPletcher - Thursday, December 21, 2017 3:11 PM
The key lookups should cost more than the sort for only 7 rows (I know the lookup is in both queries). You should consider adding DateRegistered to the NC lindex.
Actually, you should really review the clustered index and consider using SampleStatusID as the first clustering key,, but you're probably much less likely to want to go that far, even though it could potentially help performance across many queries.

Making the lead column of the clustered index something with such low cardinality as SampleStatusID would guarantee wanton page splits of the CI on insert and would also cause data movement between pages when the status changed. Really not a good idea to do such a thing. I personally brought Expedia.com to its knees for a about 2 minutes with such an index and that wasn't even a clustered index.

One of the most important attributes for the keys of a clustered index is they must be immutable.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 4

Chris Harshman - Friday, December 22, 2017 11:01 AM
ScottPletcher - Thursday, December 21, 2017 3:11 PM
The key lookups should cost more than the sort for only 7 rows (I know the lookup is in both queries). You should consider adding DateRegistered to the NC lindex.
Actually, you should really review the clustered index and consider using SampleStatusID as the first clustering key,, but you're probably much less likely to want to go that far, even though it could potentially help performance across many queries.
Actually, I think some major index analysis needs to be done on this table, considering there are 15 indexes on it, and some look like near duplicates:
- [IDX_NC_Sample_ComponentID_SampleNumber]
- [IDX_NC_Sample_ComponentID]
can be consolidated, same index key column, just different include columns
- [IDX_NC_Sample_ComponentID_IsActive_SampleNumber_SampleStatusID]
is similar to above 2, research needed
- [IDX_NC_Sample_EquipmentLife_EquipmentLifeUnitID_LubricantLife_LubricantLifeUnitID_TopUpVolume]
- [nci_wi_Sample_2603D396CB0E4AF80959A093AB4C9037]
only difference is [IsActive] column being 8th or 3rd index key column, research needed
- [IDX_NC_Sample_EquipmentLife_EquipmentLifeUnitID_LubricantLifeUnitID_TopUpVolumeUnitID_TopUpVolumePerUnitID_IsActive]
is similar to above 2 but skips [LubricantLife] column as part of index key, research needed
- [IDX_NC_Sample_SampleCondition]
- [nci_wi_Sample_AF4FD50D969088FBC95F7D420791ED50]
can be consolidated, same index key column, second has 1 include column
5 indexes that start with [SampleStatusID] as the index key, research needed.
Also, before changing your clustering index, I'd look at overall index usage for a while and see if there is one of these NC indexes that is causing the majority of the lookups.

I'm not seeing those in the XML for the query plan so let me ask, where did you get that information?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 5

Jeff Moden - Friday, December 22, 2017 9:18 PM
I'm not seeing those in the XML for the query plan so let me ask, where did you get that information?

the original poster put a pesession file in the attached ZIP. That is a SQL Sentry Plan Explorer file, which has tons of more detail than a regular Management Studio plan. One of the features is something called Index Analysis, which shows all the indexes for a execution plan node of the table being referenced.

They've recently rebranded it as SentryOne and the full featured tool is available as a free download:
https://www.sentryone.com/plan-explorer
I've gotten addicted to it!