locked
Strange behavior of procedures. RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Dear All,

    I am very new for this forum. So, please guide me for the following question. In our organization we are using sql server 2008 R2. But we found some strange behavior for our procedures. Some time, procedures are not providing any output and we have to drop and recreate it (here, recreate means copy TSQL, Drop that procedure and create that procedure from copy TSQL) and after this the procedures are working fine.

    So, please any one can help me to identify this issue? Sorry, if I am on wrong forum.

    Gunjan.

    Friday, February 3, 2012 12:30 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    I realise that you are new to this forum, so here is a piece of advice: we know T-SQL here. We are extremely poor at mind-reading.

    If you have problem with a piece of code, you need to post that piece of code. You may change table names etc, but in such case, please make sure that the problem still exhibits.

    I would also like to take the occasion to make sure that I understand your description correctly. You do something like:

       EXEC your_sp @par1, @par2, ....

    And you get no rows back. Then you drop the procedure and create it again. Next when you run it, you do get rows back?

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Kalman Toth Friday, March 2, 2012 11:56 AM
    Saturday, February 4, 2012 5:52 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    We are extremely poor at mind-reading.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Agree with Erland! Unless we know the exact code and we can repro, its hard to troubleshoot and provide solution. 
    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    • Marked as answer by Kalman Toth Friday, March 2, 2012 11:56 AM
    Saturday, February 4, 2012 6:05 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    But we found some strange behavior for our procedures. Some time, procedures are not providing any output and we have to drop and recreate it (here, recreate means copy TSQL, Drop that procedure and create that procedure from copy TSQL) and after this the procedures are working fine.


    This is the tell-tale sign of parameter sniffing with a caviat: I  interpret of "not providing an output" as taking such a long time that the client program times out.

    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    • Marked as answer by Kalman Toth Friday, March 2, 2012 11:58 AM
    • Edited by Kalman Toth Friday, October 12, 2012 7:58 PM
    Saturday, February 25, 2012 2:59 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    When you say that it does not produce any output, from where are you running it? From the application or from SQL Server Management Studio?

    Could you post the code?

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, February 3, 2012 12:56 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    i have tried from both the end at the same time it is not produce any out put or Error.

     

    code look like very same like other procedure.

     

    Ex.

    Create procedure Test

    (

     @para1 <dataType>,

     @Para2 <dataType>,

     @Para... 

     

    }

    as

    begin

     

     

     Select tbl1.*, tbl2.*

     from tbl1

    Inner join Tbl2 on (<condition>)

    Inner join Tbl3 on (<condition>)

    Inner join Tbl4 on (<condition>)

    Inner join Tbl5 on (<condition>)

    Left join Tbl6 on (<condition>)

    Left join Tbl7 on (<condition>)

    Left join Tbl9 on (<condition>)

    where @Para1 and @Para2 and @Para.....

     

    end 

    Hope it will help you to identify my issue.


    Saturday, February 4, 2012 8:17 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    It maybe a parameter sniffing issue. Here is how to fix it:

    http://www.sqlusa.com/bestpractices/parameter-sniffing/

    Optimization article:

    http://www.sqlusa.com/articles/query-optimization/

    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER BEST PRACTICES
    • Proposed as answer by Peja Tao Monday, February 6, 2012 6:25 AM
    • Marked as answer by Peja Tao Thursday, February 9, 2012 8:00 AM
    • Unmarked as answer by Naomi N Sunday, February 19, 2012 3:31 PM
    • Unproposed as answer by Naomi N Sunday, February 26, 2012 3:08 AM
    Saturday, February 4, 2012 8:47 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    as i know, it is better

    1. always give procedure parameters a default values

    2. to prevent execution plan to fail use a parameter remap, as was said at previous post.

     

    if procedure starts to produce wrong output or stops producing output it is something strange. I`m fighting such behavour with oracle (but i`m very new to oracle).

    if it is not a black magic, check procedure timeouts and application`s exception handling, and if required go deeper: set sql trace flags, check event log etc. May be something wrong inside DB engine.

    Saturday, February 4, 2012 5:06 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    if procedure starts to produce wrong output or stops producing output it is something strange. I`m fighting such behavour with oracle (but i`m very new to oracle).


    >>Are you seeing this behaviour in Oracle or SQL?
    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Saturday, February 4, 2012 5:10 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I realise that you are new to this forum, so here is a piece of advice: we know T-SQL here. We are extremely poor at mind-reading.

    If you have problem with a piece of code, you need to post that piece of code. You may change table names etc, but in such case, please make sure that the problem still exhibits.

    I would also like to take the occasion to make sure that I understand your description correctly. You do something like:

       EXEC your_sp @par1, @par2, ....

    And you get no rows back. Then you drop the procedure and create it again. Next when you run it, you do get rows back?

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Kalman Toth Friday, March 2, 2012 11:56 AM
    Saturday, February 4, 2012 5:52 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    We are extremely poor at mind-reading.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Agree with Erland! Unless we know the exact code and we can repro, its hard to troubleshoot and provide solution. 
    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    • Marked as answer by Kalman Toth Friday, March 2, 2012 11:56 AM
    Saturday, February 4, 2012 6:05 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi All,

    Let me thank you for your valuable time which you have spent for my issue. Here, let me clarify that, this issue is not persistent with particular procedure. It will occur with any procedure at any time. Once again, I would like to say that, when we run procedure, no output will return (from application asp.net or Microsoft Sql Server Management studio).But, when we re-create it, than after it start giving output.

    Gunjan.

    Sunday, February 19, 2012 11:09 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Maybe there is some common pattern to them?

    Again, please, if you want any help, share as much information as you can.

    You say that the procedure does not produce any output. So what do you see in Management Studio? "Command(s) completed successfully."? Or does the query not complete at all?

    When the procedure produces output, what exactly does that mean? Result set? Output parameters? Messages printed?

    Please post a sample procedure for which you get this problem.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, February 19, 2012 12:45 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    But we found some strange behavior for our procedures. Some time, procedures are not providing any output and we have to drop and recreate it (here, recreate means copy TSQL, Drop that procedure and create that procedure from copy TSQL) and after this the procedures are working fine.


    This is the tell-tale sign of parameter sniffing with a caviat: I  interpret of "not providing an output" as taking such a long time that the client program times out.

    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    • Marked as answer by Kalman Toth Friday, March 2, 2012 11:58 AM
    • Edited by Kalman Toth Friday, October 12, 2012 7:58 PM
    Saturday, February 25, 2012 2:59 PM