Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Sachin.Nand
2937 Posts |
 Posted - 2010-01-12 : 01:23:00
|
| What can be the reason for a SP to recompile except that of adding new indexes,removing & adding statistics,dynamic sql,adding with reciompile options in the sp,not using the cached query plan.PBUH |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-12 : 01:42:38
|
| http://www.sql-server-performance.com/articles/per/optimizing_sp_recompiles_p1.aspx |
 |
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-01-12 : 02:00:33
|
| Thnx for the link.One more thing I need to know if there is a SP using a temporary table will the query plan be cached for that temp table to.Also if the the physical structure & no of rows of the table to which the SP is referencing has not changed but the no of rows in the temp table has changed will the Sp recompile?PBUH |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-01-12 : 09:18:44
|
No one knows the answer or no one is interested in replying ?  PBUH |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-12 : 11:09:49
|
| Well ... my answer would be:If I was interviewing you I wouldn't expect you to know information like this off-the-top-of-your-head - no one here answered yet, so its obviously not a simple thing that everyone knows. However, I would expect you to know how to find out.Saying you would ask on SQLTeam / Internet first would be fine (you might get a quick answer, and that would save company money).But then telling me how you would find out, empirically, would be the real test.So ... how would you find out? |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-01-13 : 01:25:32
|
| Kirsten first of all I guess I dint deserve that kind of reply from you or anyone else.Now that you have replied even though you dont know the answer "empirically" or any other way I would expect reply from some one who do know it properly because I have seen hundreds of post where the posters have asked questions like this & they did get proper reply to their doubts.One of them is this http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=126570PBUH |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-13 : 02:29:41
|
| "Kirsten first of all I guess I dint deserve that kind of reply from you or anyone else."Why, What's wrong with my answer?I've told you how I would have expected you to answer that interview question, and how I would expect you to find out if you were working for me.Also that I don't think that knowledge of the correct answer is commonplace (otherwise you would have had an answer here by now); I would not expect people working for me to know that fact, but I would certainly expect people working for me to be able to describe to me how they would put together a test to work out the answer.For example, Performance Monitor will report Cache Hits / Misses. Would that be the right tool to check if the SProc caches the query plan for temporary table, or is there a better way? What test plan would you use to deduce the other parts of the question - if the number of rows in the table changes etc?Actually maybe its a better interview question that I thought at first. I doubt any/many know the answer, but it opens a whole discussion on how to test for optimisation. |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-01-13 : 02:50:11
|
| Kirsten my apologies if I said something bad.Then I guess the simple answer would be trial & error.PBUH |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-13 : 02:54:19
|
| No worries."Then I guess the simple answer would be trial & error."Well, that would be my suggestion.If you are interested in knowing the answer making some tests will teach you all sorts of other useful information at the same time. |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-01-13 : 02:59:29
|
| Yeah sure will definately do that.PBUH |
|
|
|
|
|
|
|
|
Interview question on SP recompilation