Query tuning advice required please

Question

Query tuning advice required please

MikeRen

Ten Centuries

Points: 1295
More actions
April 25, 2016 at 6:59 am

#315913

Hello,
I have a query on a 2008R2 SP2 instance which is causing me grief. I'm convinced that in its basic form it used to run in under a second, which was acceptable. Now something has changed and for the first run it's taking 6-7 seconds or multiples thereof. The second run is as it was - under a second. By using FREEPROCCACHE I can force it to take the 6-7 seconds every time.
My initial course of action is to look at the Execution plan. This suggested adding an index to an underlying table but this action only seems to affect the timings by 1 second and not the advertised 50% (3 seconds).
My query has been pared down to it's simplest form and looks like this:-
SELECT cc.fchrCartonRef
FROM dbo.tblCartonContents AS cc WITH (NOLOCK)
JOIN cs3plc.scheme.rvShipOrderDetail AS sod
ON cc.fchrOrderNo = sod.order_no
WHERE cc.fchrCartonRef = '1604-660'
GO
I attached the file execution plan as a text file should that be of any use.
If I run a select on either the (first) table or the (second) view they each return in well under a second, it's the JOIN that's causing the delay but I can't work out why. Incidentally, the types of the fields used in the join are the same.
The problem seems to be something to do with the JOIN but I can't see what. I've welcome any advice on how I can restore the speed back into the view.
Thanks,
Mike

Viewing 9 posts - 1 through 8 (of 8 total)

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

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

I don't see any huge issues with the query posted. However, the execution plan is way more complex than I was expecting. Then I re-read your post and one of those is a view. We will need quite a more information here to help. Like the table definitions including indexes for the tables in the view, the view definition. Here is a great article about what you should post for performance problems. http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]

Also, why are you using NOLOCK here? That hint might be ok but all too often I see it as a "go faster" option without regards to the other issues that hint brings. Things like missing and/or duplicate rows. http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/[/url]

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 2

The problem in this case is the compile time.

If you look at the plan, you'll see that optimization timed out, and the compile time and CPU are both just over 5 seconds.

You'll either need to look at simplifying the query (maybe explicitly querying the underlying tables from the view that you need instead of forcing the optimizer to figure it out), simplifying/tuning the code underlying the view, or just accepting that if that query's plan needs to be recompiled, the first run will take about 5 extra seconds due to compile time.

Cheers!

MikeRen Ten Centuries Points: 1295 More actions · Answer 3

Also, why are you using NOLOCK here? That hint might be ok but all too often I see it as a "go faster" option without regards to the other issues that hint brings.

You're correct, I dislike it when my dev guys litter their T-SQL with (NOLOCK) hints. In this particular case I deem it necessary as the underlying table has historically suffered from locking issues which ultimately adversely affect the app. It was added with consideration.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

There are numerous RID lookups in the plan. It's rare for a table to play better without a clustered index than with one. There's even an ordinary index called PK_ something - on a heap.

Spend a little time analysing each of those tables which don't have a clustered index. It looks to me like someone's been playing silly bu88ers.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

MikeRen Ten Centuries Points: 1295 More actions · Answer 5

Jacob Wilkins (4/25/2016)
You'll either need to look at simplifying the query (maybe explicitly querying the underlying tables from the view that you need instead of forcing the optimizer to figure it out), simplifying/tuning the code underlying the view, or just accepting that if that query's plan needs to be recompiled, the first run will take about 5 extra seconds due to compile time.
Cheers!

Thanks for the tips.

Unfortunately for me, each time I call the query from my app it's with a different WHERE clause value and so recompiles every time.

I think I need to follow the route of simplification if at all possible.

MikeRen Ten Centuries Points: 1295 More actions · Answer 6

ChrisM@Work (4/25/2016)
Spend a little time analysing each of those tables which don't have a clustered index.

Most of those tables are vendor created which means I shouldn't be touching them. This is similar to the reason I cited above about the use of the (NOLOCK) hint, vendor DB isn't very good.

I'll complete the analysis though to check that there's nothing else in there I've missed.

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 7

mike.dinnis (4/25/2016)
Jacob Wilkins (4/25/2016)
You'll either need to look at simplifying the query (maybe explicitly querying the underlying tables from the view that you need instead of forcing the optimizer to figure it out), simplifying/tuning the code underlying the view, or just accepting that if that query's plan needs to be recompiled, the first run will take about 5 extra seconds due to compile time.
Cheers!
Thanks for the tips.
Unfortunately for me, each time I call the query from my app it's with a different WHERE clause value and so recompiles every time.
I think I need to follow the route of simplification if at all possible.

I'd do that and one other thing... try setting MAXDOP to 1 for this query. Just a hunch based on the row counts for each source node.

--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)

MadAdmin SSChampion Points: 11261 More actions · Answer 8

mike.dinnis (4/25/2016)
Jacob Wilkins (4/25/2016)
You'll either need to look at simplifying the query (maybe explicitly querying the underlying tables from the view that you need instead of forcing the optimizer to figure it out), simplifying/tuning the code underlying the view, or just accepting that if that query's plan needs to be recompiled, the first run will take about 5 extra seconds due to compile time.
Cheers!
Thanks for the tips.
Unfortunately for me, each time I call the query from my app it's with a different WHERE clause value and so recompiles every time.
I think I need to follow the route of simplification if at all possible.

Then do this so that new values will be able to reuse the previously compiled plan.

declare @var varchar(20) = '1604-660'

SELECT cc.fchrCartonRef AS 'carton_ref'

FROM dbo.tblCartonContents AS cc WITH (NOLOCK)

JOIN cs3plc.scheme.rvShipOrderDetail AS sod

ON cc.fchrOrderNo = sod.order_no

--AND RIGHT(' ' + CONVERT(VARCHAR, cc.fnOrderLine), 4) = sod.order_line_no

WHERE cc.fchrCartonRef = @var

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]