SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL uses 2 different executions plan (slow and fast) for a simple query. Need a new index ?


SQL uses 2 different executions plan (slow and fast) for a simple query. Need a new index ?

Author
Message
Gamleur84
Gamleur84
SSC Eights!
SSC Eights! (980 reputation)SSC Eights! (980 reputation)SSC Eights! (980 reputation)SSC Eights! (980 reputation)SSC Eights! (980 reputation)SSC Eights! (980 reputation)SSC Eights! (980 reputation)SSC Eights! (980 reputation)

Group: General Forum Members
Points: 980 Visits: 249
Hi everyone,

I am experiencing a performance issue with one process in our system. The performance issue is intermittent, the users say that it is working just fine for ~10 minutes and then it is slow for ~10 minutes and then it becomes fast again, etc... Note that it is for the same input parameters. I was able to capture a a trace of the process being slow and a different trace when the process is fast. The culprit is the execution plan used for one specific query.

Here is the query:

SELECT * FROM TABLE1 T1
WHERE (((PARTITION = 5637144576) AND (DATAAREAID = N'dat')) AND (((((DATASOURCE = 1) AND NOT ((DATATYPE = @P1))) AND NOT ((AXMATCHINGCODE = @P2)))AND NOT ((FILEREFRECID = @P3)))AND (FILEREFRECID = @P4)))
ORDER BY T1.SESSIONID, T1.DATATYPE, T1.DOCUMENTNUMBER, T1.AXMATCHINGCODE, T1.MATCHED OPTION (FAST 19)


Pretty simple, isn't it ? My analysis says that there is no perfect index for that query and SQL says to be on the fence for two not very good index when it comes to seek the data. IndexA performs poorly,and IndexB provides good performance.

Fast Execution Plan : https://imgur.com/a/GWU3B
Slow Execution Plan: https://imgur.com/a/GWU3B

Now I am trying to understand few things:

1 - I can't explain exactly why the first execution plan is slow and the second is fast, any ideas ? ( I don't want to details my whole analysis since I do not come to a clear conclusion).
2 - I can't explain why SQL goes with one for 10 minutes and then the other one for 10 minutes, etc (please note the server has very low page life expectancy, do not expect anything to stay in cache for long)
3 - What would be the best index for this query, I tried few ones but SQL always pick IndexA with slow execution plan.
4 - Why there is no Order operator in the slow execution plan?

Thank you
Chris Harshman
Chris Harshman
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39440 Visits: 7335
Some questions and ideas about this, it's difficult to be precise since we're just looking at a picture of the execution plan:
- in the pictures you can see a variable @p2, but that's not in your query posted above, is that used as a parameter in the query? I could see different results because of more reads as hinted at in your pictures if this is a parameter.
- in the query posted above, you have a SELECT *, this means that SQL Server will always need to do a LOOKUP operation for any NONCLUSTERED index you put on the table. LOOKUPs are often expensive and depending on the number of rows the optimizer expects it may just decide to do a table scan. The query in the picture looks different, so I can't really tell what columns are involved in that query.
- of the columns that are equal conditions (PARTITION, DATAAREAID, DATASOURCE, FILEREFRECID), how selective are each column? (meaning how well does a single value filter the query)
ScottPletcher
ScottPletcher
SSC Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91397 Visits: 10117
Hard to tell without selectivity / row count info, as Chris noted.

But, if the partition and dataareaid are how you typically query this table, and guessing that PARTITION is more selective than DATAAREAID, I suggest trying this:
Cluster the table on:
(PARTITION, DATAAREAID, DATASOURCE)
Make DATAAREAID [var]char rather than n[var]char if you can. If it's not nchar, it's critical to get rid of the N'.

In fact, it's best just to get rid of it anyway. If it's a unicode string, SQL will convert it to unicode:

(((PARTITION = 5637144576) AND (DATAAREAID = N'dat')) ...

SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
Gamleur84
Gamleur84
SSC Eights!
SSC Eights! (980 reputation)SSC Eights! (980 reputation)SSC Eights! (980 reputation)SSC Eights! (980 reputation)SSC Eights! (980 reputation)SSC Eights! (980 reputation)SSC Eights! (980 reputation)SSC Eights! (980 reputation)

Group: General Forum Members
Points: 980 Visits: 249
Thank you for the replies.

- As for now, I would like to find a solution without changing the query if possible.
- I changed the query in my post to reflect the query in the trace (@P1, @P2, @P3, @P4, @P5)
- Partition is not selective at all, in fact all the rows have the same partition.
- DATAAREAID can be two values, let's say 50/50 distribution.
- Unfortunately, I can not get rid of PARTITION and DATAAREAID since the system is designed that way. PARTITION will always be the first column and DATAAREAID will always be the second column in the index.

DATATYPE, DATASOURCE, FILEREFRECID and AXMATCHINGCODE are the real filters.

In the slow execution plan:
Seek Predicate: PARTITION, DATAAREAID
Predicate: DATATYPE, AXMATCHINGCODE

In the fast execution plan:
Seek Predicate : PARTITION, DATAAREAID, FILEREFRECID
Predicate: FILEREFRECID

Maybe I don't understand the Non-Cluster Seek Operation, but it is clear that PARTITION + DATAREAID + FILEREFRECID will return less rows than PARTITION + DATAAREAID. Is it a clue ?
Does SQL perfom a first read on the Seek Predicate and then another read for the Predicate on a smaller set of data ?
Where exactly does SQL filter DATASOURCE?

I am not expecting a full explanation from community, but your questions and remarks help me to understand better!

Thank you
ScottPletcher
ScottPletcher
SSC Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91397 Visits: 10117
Again, you need to cluster the table on those columns then. You can adjust nonclus indexes all day and it most often won't do you much good.

SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
Gamleur84
Gamleur84
SSC Eights!
SSC Eights! (980 reputation)SSC Eights! (980 reputation)SSC Eights! (980 reputation)SSC Eights! (980 reputation)SSC Eights! (980 reputation)SSC Eights! (980 reputation)SSC Eights! (980 reputation)SSC Eights! (980 reputation)

Group: General Forum Members
Points: 980 Visits: 249
Thank you,

Any recommendations on the order of the columns ?
ScottPletcher
ScottPletcher
SSC Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91397 Visits: 10117
Based on your last comments, this looks like the only way that might really help. Your keys columns are exceptionally non-selective, which makes it more difficult to get best performance.

(DATASOURCE, DATAAREAID, PARTITION)

SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
Gamleur84
Gamleur84
SSC Eights!
SSC Eights! (980 reputation)SSC Eights! (980 reputation)SSC Eights! (980 reputation)SSC Eights! (980 reputation)SSC Eights! (980 reputation)SSC Eights! (980 reputation)SSC Eights! (980 reputation)SSC Eights! (980 reputation)

Group: General Forum Members
Points: 980 Visits: 249
Thanks, I got the values:

DATASOURCE= 2 distinct values
DATATYPE = 5 distinct values
FILEREFRECID = 3820131 distinct values
AXMATCHINGCODE = 12 distinct values
DATAAREAID = 2 distinct values
PARTITION = 1 distinct values.

A non-clustered index with PARTITION, DATAAREAID, FILEREFRECID columns seems to be the best fit for my query and indeed I get great performance when the optimizer picks that Index.

My real problem I guess is why does SQL pick sometimes the other terrible index? Even the execution plan does not make sense(why there is no sort operation) ?
Statistics are updated. Does not seem to be a parameter sniffing issue.
sgmunson
sgmunson
SSC Guru
SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)

Group: General Forum Members
Points: 103475 Visits: 7414
Gamleur84 - Monday, February 26, 2018 1:11 PM
Thanks, I got the values:

DATASOURCE= 2 distinct values
DATATYPE = 5 distinct values
FILEREFRECID = 3820131 distinct values
AXMATCHINGCODE = 12 distinct values
DATAAREAID = 2 distinct values
PARTITION = 1 distinct values.

A non-clustered index with PARTITION, DATAAREAID, FILEREFRECID columns seems to be the best fit for my query and indeed I get great performance when the optimizer picks that Index.

My real problem I guess is why does SQL pick sometimes the other terrible index? Even the execution plan does not make sense(why there is no sort operation) ?
Statistics are updated. Does not seem to be a parameter sniffing issue.

Mind you, it's usually a measure of last resort, but you could add a table hint; specifying that index; to the query, and at least see if that forces SQL Server to "do the right thing".


Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
Gamleur84
Gamleur84
SSC Eights!
SSC Eights! (980 reputation)SSC Eights! (980 reputation)SSC Eights! (980 reputation)SSC Eights! (980 reputation)SSC Eights! (980 reputation)SSC Eights! (980 reputation)SSC Eights! (980 reputation)SSC Eights! (980 reputation)

Group: General Forum Members
Points: 980 Visits: 249
Finally I found my issue : Parameter Sniffing issue.

Under certain circumstances, the values for FILEREFRECID were set to 0. It mean no more predicates for FILEREFRECID and leads to the use of a different execution plan. The execution plan was stored in cached and used for the same query with values other than 0 for FILEREFRECID.
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