December 17, 2014 at 1:32 pm
Nice question Steve, thanks.
Wasn't sure between the large number of inserts & the index changes, so decided to do some research first, and then got it right.
Logic told me that the large number of inserts had to cause a recompile, as it would eventually force an update of statistics. So should have got it without research...
December 17, 2014 at 2:22 pm
Good question, thanks.
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
December 17, 2014 at 4:08 pm
I think it would be useful to tell us for which version of SQL Server this question is corresponding. I am not sure that with the new query optimizer ( corresponding to SQL Server 2014 ) the answer is the same one than for SQL Server 2012 ( the differences seem important according a session I listened in Paris SQL Server Days 2 weeks ago ).
Have a nice day
December 17, 2014 at 5:06 pm
patricklambin (12/17/2014)
I think it would be useful to tell us for which version of SQL Server this question is corresponding. I am not sure that with the new query optimizer ( corresponding to SQL Server 2014 ) the answer is the same one than for SQL Server 2012 ( the differences seem important according a session I listened in Paris SQL Server Days 2 weeks ago ).Have a nice day
The QO in 2014 did not change. It is the Cardinality Estimator that changed. The things that can cause a plan to be removed from cache in 2014 are the same as for 2012 and older.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 17, 2014 at 10:03 pm
This was removed by the editor as SPAM
December 18, 2014 at 3:48 am
Gr8 question, very informative, thanx 🙂
December 18, 2014 at 5:15 am
I'm not really sure why "A large number of changes to keys caused by insert or delete operations" is a correct answer. The answer states nothing about whether the inserts and deletes are on tables used by the query. Since we are so used to trick questions, I think a lot of people would have been skeptical about this selection.
December 18, 2014 at 6:56 am
I'm confused by the second option, deleting an index whether it is used by the plan or not. If an index was used by the plan, wouldn't dropping it then require a recompile?
December 18, 2014 at 7:02 am
Iwas Bornready (12/18/2014)
I'm confused by the second option, deleting an index whether it is used by the plan or not. If an index was used by the plan, wouldn't dropping it then require a recompile?
The point of that option is that it is regardless of the index use. Since it is not true for both cases (used or not used) then it should not be selected.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 18, 2014 at 10:59 am
SQLRNNR (12/18/2014)
Iwas Bornready (12/18/2014)
I'm confused by the second option, deleting an index whether it is used by the plan or not. If an index was used by the plan, wouldn't dropping it then require a recompile?The point of that option is that it is regardless of the index use. Since it is not true for both cases (used or not used) then it should not be selected.
This is the one that got me too. I didn't read the whole answer well enough before selecting it. Nice question. Thanks Steve.
December 18, 2014 at 11:05 am
I knew two and guessed the third.
Good question, learned something as well.
Not all gray hairs are Dinosaurs!
December 18, 2014 at 10:26 pm
Actually, Changing the max Memory Setting drops the contents of the Procedure cache causing ALL queries to be re-complied. Also a large number of inserts, updates or deletes will not necessarily cause a re-compile. If the threashold of data doesn't cause a the resampling for the statisics then the query plan is still valid and therefore no recompilation.
Try It !!
December 19, 2014 at 7:47 am
david.gugg (12/17/2014)
Dropping any index on the table, whether it is used by the query plan or not
This should probably say "Dropping an index on the table that is not used by the query plan." If the index is used by the query, won't it necessarily cause a recompile? 70% choose this option.
Had the same question as even the answer say
does it mean than any index change will not cause recompile
------------------------------------------------------------------------------------
Ashish
January 2, 2015 at 4:32 pm
Looks like updating statistics alone does not cause a recompile, at least in SQL Server 2014:
http://www.brentozar.com/archive/2015/01/updating-statistics-cause-recompile-no-data-changed/
If data hasn't changed, then no recompile.
January 10, 2016 at 2:00 am
Here is something wrong with question.
I picked
"Dropping any index on the table, whether it is used by the query plan or not"
According to MSDN https://msdn.microsoft.com/en-us/library/ms181055.aspx
one of reason to recompile plan
"Dropping an index used by the execution plan."
From my sense:
In case index is used by query plan and it dropped - query plan will be invalidated, and recompiled.
Just checked it. EXevent shows recompile_cause = "Schema changed" as it should be.
Were is my point ? 😉
MS SQL 2008 MCITP x 3
MS SQL 2012 MCSE x 2
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply