February 23, 2018 at 10:08 am
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
February 23, 2018 at 12:00 pm
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)
February 23, 2018 at 12:45 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 23, 2018 at 1:12 pm
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
February 23, 2018 at 2:26 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 26, 2018 at 9:18 am
Thank you,
Any recommendations on the order of the columns ?
February 26, 2018 at 12:27 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 26, 2018 at 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.
February 27, 2018 at 7:39 am
Gamleur84 - Monday, February 26, 2018 1:11 PMThanks, 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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 1, 2018 at 11:01 am
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.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply