July 16, 2009 at 6:56 am
Jeff Moden (7/16/2009)
Grant Fritchey (7/16/2009)
Jeff Moden (7/15/2009)
Grant Fritchey (7/15/2009)
J.D. Gonzalez (7/15/2009)
Execution plans attached.I'm sorry, but can you zip those up & reattach them. I'm having a hard time getting them to open as is.
They're a new feature as of today... you have to save the bloody things locally and then open them.
Help the stupid guy (meaning me), how? If I right click on them, I don't get save as. If I click on it directly it opens a new explorer window, with an aspx extension and an error message about the encoding.
I'm not sure what the problem there is, Grant. I'm using IE and when I right click on one, the expected popup menu with a "Save Target As" as one of the selections appears.
Must be denser than normal. It seems to be working now. Thanks for the patience.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 16, 2009 at 7:02 am
Heh... I have that very same problem when the caffeine levels are low.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 16, 2009 at 7:28 am
Checking out the plans, the first thing I'm noticing is that the estimated rows are pretty different. 1 for the slow plan and 584 for the fast plan. That tells me that whatever is different about the slow plan, it's causing it to get an incorrect number of rows from the statistics, which would help explain why it's slower.
Basically, when you use a function, SQL Server has to decide if it can rely on repeatable results, which will help determine what it does in the execution plan. For some reason, the faster query is actually showing as a much higher cost than the slower query (another example of cost estimates being a poor indicator for performance), but it's showing it's cost as being based on 1 row, not the 9000 actually returned. The faster query is showing a higher cost, and going into parallelism because of it, from the fact that it's estimating 584 rows and is generating a plan more in keeping with the 9000 actual. It has to be that, in this instance, the DATEADD function is leading to a bad row estimate, which is then causing a poor execution plan to be picked.
So you can see that it's using a Loop join when it's trying to move 9000 rows from two tables, but in the fast plan it's using a hash join to put the 9000 rows together, a much better choice based on the data being moved.
Why?
I don't know specifically. It really does have to do with the function. You could use any of the functions outlined to load a parameter and then you'll get a consistent plan, but using functions, you'll get different plans depending on how SQL Server resolves it. Just as Gus said earlier.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 16, 2009 at 8:18 am
Could it be that the problem is being caused by non-deterministic functions?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 16, 2009 at 8:23 am
Jeff Moden (7/16/2009)
Could it be that the problem is being caused by non-deterministic functions?
Yeah, that's basically it, but it's hard to tell someone exactly why one function turns out like this and the other function turns out like that because it's so internal to decisions made by the optimizer, that, to me anyway, it seems almost random when you start seeing it.
Of course, that's why it's easy to say, don't use functions in where clauses, although usually it's because someone is using a function on a column and preventing index use, blah, blah, blah.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 16, 2009 at 8:31 am
I would calculate the date you need with that math first and put it into a variable and use that. You will also need an OPTION RECOMPILE to ensure you get the optimal plan based on the calculated date. Dynamic sql is another option.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 16, 2009 at 8:38 am
I don't know that I'd recommend recompiling out of hand. The execution plan using a parameter is going to sample the appropriate indexes and arrive at a generic "best" plan. Most of the time, that will work extremely well, as good as a plan compiled for a specific value, sometimes better. It really depends on the distribution of the data within the index. If you have some outliers, values with very few rows compared to the average or values with lots of rows compared to the average, that you'll really need an automatic recompile.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 16, 2009 at 9:13 am
Grant Fritchey (7/16/2009)
I don't know that I'd recommend recompiling out of hand. The execution plan using a parameter is going to sample the appropriate indexes and arrive at a generic "best" plan. Most of the time, that will work extremely well, as good as a plan compiled for a specific value, sometimes better. It really depends on the distribution of the data within the index. If you have some outliers, values with very few rows compared to the average or values with lots of rows compared to the average, that you'll really need an automatic recompile.
I think we will have to disagree on this one. I estimate that 90+% of the time a user has a query with a date range comparison to a parameter cached plans lead to problems with certain values. Robbing a few CPU ticks from Peter avoids the disasterous occassional nested-loop-with-a-kajillion-rows Paul scenario.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 16, 2009 at 9:30 am
TheSQLGuru (7/16/2009)
I think we will have to disagree on this one. I estimate that 90+% of the time a user has a query with a date range comparison to a parameter cached plans lead to problems with certain values. Robbing a few CPU ticks from Peter avoids the disasterous occassional nested-loop-with-a-kajillion-rows Paul scenario.
I'm just curious, how far would you carry that approach? If, instead of generating the parameter, he was just passing it in through the procedure variables and consuming it, no changes, would you still go with recompiles on the procedure?
Definately not arguing.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 16, 2009 at 9:39 am
Grant Fritchey (7/16/2009)
TheSQLGuru (7/16/2009)
I think we will have to disagree on this one. I estimate that 90+% of the time a user has a query with a date range comparison to a parameter cached plans lead to problems with certain values. Robbing a few CPU ticks from Peter avoids the disasterous occassional nested-loop-with-a-kajillion-rows Paul scenario.
I'm just curious, how far would you carry that approach? If, instead of generating the parameter, he was just passing it in through the procedure variables and consuming it, no changes, would you still go with recompiles on the procedure?
Definately not arguing.
I think the case he has (fixed 1 month from getdate) is LESS likely to cause the bad situation than using user input param, which could vary widely. I would be even more likely to default to using OPTION RECOMPILE in the latter case.
Obviously there are cases where the data is regularly pruned, other where clause filters always restrict data to few rows (in a way the optimizer can know about at each execution), etc. where the RECOMPILE would not be required.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 16, 2009 at 10:14 am
Grant Fritchey (7/16/2009)
TheSQLGuru (7/16/2009)
I think we will have to disagree on this one. I estimate that 90+% of the time a user has a query with a date range comparison to a parameter cached plans lead to problems with certain values. Robbing a few CPU ticks from Peter avoids the disasterous occassional nested-loop-with-a-kajillion-rows Paul scenario.
I'm just curious, how far would you carry that approach? If, instead of generating the parameter, he was just passing it in through the procedure variables and consuming it, no changes, would you still go with recompiles on the procedure?
Definately not arguing.
Think "parameter sniffing" to determine how far you'd carry the approach and when you can least afford for it to occur.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 16, 2009 at 10:32 am
Jeff Moden (7/16/2009)
Think "parameter sniffing" to determine how far you'd carry the approach and when you can least afford for it to occur.
Yes, true. But there's good & bad to parameter sniffing.
I'm really just pursuing this out of curiousity and because my inclination is to let SQL Server handle most things on it's own. I've been bitten on multiple occasions by developers (and DBA's) that put various hints on most or all queries only to find that, in fact, the hint works on only a few of the queries and on others it causes as many problems as it fixes.
So I was just wondering how far down the recompile one, some, most, or all progression we were headed?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 16, 2009 at 11:26 am
Heh... nah... you and I both know... "It Depends". Test everything... leave little to chance.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 16, 2009 at 12:11 pm
Grant Fritchey (7/16/2009)
Jeff Moden (7/16/2009)
Think "parameter sniffing" to determine how far you'd carry the approach and when you can least afford for it to occur.Yes, true. But there's good & bad to parameter sniffing.
I'm really just pursuing this out of curiousity and because my inclination is to let SQL Server handle most things on it's own. I've been bitten on multiple occasions by developers (and DBA's) that put various hints on most or all queries only to find that, in fact, the hint works on only a few of the queries and on others it causes as many problems as it fixes.
So I was just wondering how far down the recompile one, some, most, or all progression we were headed?
"hints" is a very broad topic. Did the OPTION RECOMPILE get used widely and cause issues, or are you lumping that in with things like FORCE ORDER, INDEX hints, JOIN TYPE hints, etc, which are indeed fraught with risk?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 16, 2009 at 12:33 pm
TheSQLGuru (7/16/2009)
"hints" is a very broad topic. Did the OPTION RECOMPILE get used widely and cause issues, or are you lumping that in with things like FORCE ORDER, INDEX hints, JOIN TYPE hints, etc, which are indeed fraught with risk?
Unfortunately, I've seen all of the above and more. My favorite was the extensive use of FAST 1 because it provided such a "perfect" execution plan...
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 31 through 45 (of 46 total)
You must be logged in to reply to this topic. Login to reply