none
SP running in the SSMS fast, but in VB.Net slow

    Question

  • We're having some performance issues with several sp's that are called by .Net programs.

    All these sp's run in SSMS fast to very fast.

    Some of these sp's return no records at all (just lookup tables with no records), but can take more than 30s to complete.

    Somebody a hint, where to start looking?

    Yours sincerely Henk
    Tuesday, July 28, 2009 8:14 AM

Answers

  • All these sp's run in SSMS fast to very fast.

    Some of these sp's return no records at all (just lookup tables with no records), but can take more than 30s to complete
    I suggest you start by checking server activity while the problem stored procedure is running (e.g. from SSMS: execute sp_who).  Check for a non-zero value under the blk column, which indicates the query is blocked by the that other session.
     
    You can also use Profiler (Performance Tools-->SQL Server Profiler) to trace the actual queries (SQL:BatchCompleted and RPC:Completed events) and include the execution plans (Showplan XML event).  Examine the execution plan to make sure the one from SSMS is the same as from the application.  I would expect the plans to be the same for the trivial query you mention, though, so I suspect some other issue.

    --
    Hope this helps.
     
    Dan Guzman
    SQL Server MVP
    http://weblogs.sqlteam.com/dang /
    Tuesday, July 28, 2009 12:06 PM
  • This sounds like it could be a case of parameter sniffing since the performance is so different between the two environments. When you say that the SPs run quickly in SSMS, do you mean that the SQL in the stored procedure is running quickly in SSMS, or that calling the stored procedure itself is very fast in SSMS?
    Assuming, however, that this is a case of paremeter sniffing, let's further assume that you have a stored procedure that has many optimal query plans, depending on the inputs you are using. In this case, you've run the query with one set of parameters and SQL Server has compiled and cached an execution plan that is optimal for that set of parameters. On a future run, you pass in a different set of parameters that has a very different optimal execution plan. SQL Server's default behavior is to reuse a cached execution plan which, in this case, in a non-performant execution plan.
    There are several ways around this problem.
    One way is to create a local copy of the parameters being passed in. For example:
    USE AdventureWorks ;
    GO
    
    CREATE PROCEDURE GetContact
      @FirstName NVARCHAR(50),
      @LastName NVARCHAR(50)
    AS 
      DECLARE @_FirstName AS NVARCHAR(50) ;
      DECLARE @_LastName AS NVARCHAR(50) ;
    
      SELECT  ContactID,
              NameStyle,
              Title,
              FirstName,
              MiddleName,
              LastName,
              Suffix,
              EmailAddress,
              EmailPromotion,
              Phone,
              PasswordHash,
              PasswordSalt,
              AdditionalContactInfo,
              rowguid,
              ModifiedDate
      FROM    Person.Contact
      WHERE   FirstName = @_FirstName
              AND LastName = @_LastName ;
    
    What will happen is that SQL Server will not be able to "sniff" the values of the variables you are using (@_Var1 and @_Var2) and instead will compile an execution plan that is based on average values in the table and index statistics. Unfortunately, you will not get great performance out of this situation. However, it can be very useful if your query plans take a long time to compile (e.g. they are very complex).
    The other option is to use the OPTION(RECOMPILE) hint on the queries inside the stored procedure. This forces SQL Server to recompile the query execution plan every time the stored procedure is run. This can add significant overhead to the amount of time that it takes to execute the stored procedure and can incur a large amount of CPU load.
    Here's what this would look like:
    USE AdventureWorks ;
    GO
    
    CREATE PROCEDURE GetContact
      @FirstName NVARCHAR(50),
      @LastName NVARCHAR(50)
    AS 
      SELECT  ContactID,
              NameStyle,
              Title,
              FirstName,
              MiddleName,
              LastName,
              Suffix,
              EmailAddress,
              EmailPromotion,
              Phone,
              PasswordHash,
              PasswordSalt,
              AdditionalContactInfo,
              rowguid,
              ModifiedDate
      FROM    Person.Contact
      WHERE   FirstName = @FirstName
              AND LastName = @LastName
      OPTION  (RECOMPILE) ; 
    
    There's a great article over at SQLMag that covers this in greater detail than you might need: http://www.sqlmag.com/Articles/ArticleID/94369/94369.html?Ad=1
    Hope this helps and, if not, let us know and we'll do what we can to help you out.
    Tuesday, July 28, 2009 12:16 PM

All replies

  • is ur application web based or windows.

    If web u need to look into the IIS buffer pool area.
    Mohd Sufian www.sqlship.wordpress.com Please mark the post as Answered if it helped.
    Tuesday, July 28, 2009 11:51 AM
  • once i had faced the same problem and i remember sql server was generating different query plans.

    what i did i profiled the sp and got the execution plan from the profiler and after review the real execution plan what i got from the profiler, i was able to optimize my SP


    Regards
    Arif


    --------------------------------------------------------------------------------
    If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

    If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution

    Tuesday, July 28, 2009 12:04 PM
  • All these sp's run in SSMS fast to very fast.

    Some of these sp's return no records at all (just lookup tables with no records), but can take more than 30s to complete
    I suggest you start by checking server activity while the problem stored procedure is running (e.g. from SSMS: execute sp_who).  Check for a non-zero value under the blk column, which indicates the query is blocked by the that other session.
     
    You can also use Profiler (Performance Tools-->SQL Server Profiler) to trace the actual queries (SQL:BatchCompleted and RPC:Completed events) and include the execution plans (Showplan XML event).  Examine the execution plan to make sure the one from SSMS is the same as from the application.  I would expect the plans to be the same for the trivial query you mention, though, so I suspect some other issue.

    --
    Hope this helps.
     
    Dan Guzman
    SQL Server MVP
    http://weblogs.sqlteam.com/dang /
    Tuesday, July 28, 2009 12:06 PM
  • This sounds like it could be a case of parameter sniffing since the performance is so different between the two environments. When you say that the SPs run quickly in SSMS, do you mean that the SQL in the stored procedure is running quickly in SSMS, or that calling the stored procedure itself is very fast in SSMS?
    Assuming, however, that this is a case of paremeter sniffing, let's further assume that you have a stored procedure that has many optimal query plans, depending on the inputs you are using. In this case, you've run the query with one set of parameters and SQL Server has compiled and cached an execution plan that is optimal for that set of parameters. On a future run, you pass in a different set of parameters that has a very different optimal execution plan. SQL Server's default behavior is to reuse a cached execution plan which, in this case, in a non-performant execution plan.
    There are several ways around this problem.
    One way is to create a local copy of the parameters being passed in. For example:
    USE AdventureWorks ;
    GO
    
    CREATE PROCEDURE GetContact
      @FirstName NVARCHAR(50),
      @LastName NVARCHAR(50)
    AS 
      DECLARE @_FirstName AS NVARCHAR(50) ;
      DECLARE @_LastName AS NVARCHAR(50) ;
    
      SELECT  ContactID,
              NameStyle,
              Title,
              FirstName,
              MiddleName,
              LastName,
              Suffix,
              EmailAddress,
              EmailPromotion,
              Phone,
              PasswordHash,
              PasswordSalt,
              AdditionalContactInfo,
              rowguid,
              ModifiedDate
      FROM    Person.Contact
      WHERE   FirstName = @_FirstName
              AND LastName = @_LastName ;
    
    What will happen is that SQL Server will not be able to "sniff" the values of the variables you are using (@_Var1 and @_Var2) and instead will compile an execution plan that is based on average values in the table and index statistics. Unfortunately, you will not get great performance out of this situation. However, it can be very useful if your query plans take a long time to compile (e.g. they are very complex).
    The other option is to use the OPTION(RECOMPILE) hint on the queries inside the stored procedure. This forces SQL Server to recompile the query execution plan every time the stored procedure is run. This can add significant overhead to the amount of time that it takes to execute the stored procedure and can incur a large amount of CPU load.
    Here's what this would look like:
    USE AdventureWorks ;
    GO
    
    CREATE PROCEDURE GetContact
      @FirstName NVARCHAR(50),
      @LastName NVARCHAR(50)
    AS 
      SELECT  ContactID,
              NameStyle,
              Title,
              FirstName,
              MiddleName,
              LastName,
              Suffix,
              EmailAddress,
              EmailPromotion,
              Phone,
              PasswordHash,
              PasswordSalt,
              AdditionalContactInfo,
              rowguid,
              ModifiedDate
      FROM    Person.Contact
      WHERE   FirstName = @FirstName
              AND LastName = @LastName
      OPTION  (RECOMPILE) ; 
    
    There's a great article over at SQLMag that covers this in greater detail than you might need: http://www.sqlmag.com/Articles/ArticleID/94369/94369.html?Ad=1
    Hope this helps and, if not, let us know and we'll do what we can to help you out.
    Tuesday, July 28, 2009 12:16 PM
  • very good explanation peschka
    I just want to add some thing for HeSitated

    You need to identify for which query, the execution plan is costly. this can be found by comparing the execution plans
    the execution plan created through your  .net application can be obtained by profiler as Guzman explained
    the execution plan of  SSMS can be obtained by pressing Ctrl+L  in the  SSMS query window



    --------------------------------------------------------------------------------
    If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

    If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution

    Tuesday, July 28, 2009 1:36 PM
  • Thank you guys!

    Although my collegue hasn't found the solution...

    All your tips will bring us there.

    Thanks again!
    Tuesday, July 28, 2009 8:30 PM
  • Jeremiah, thank you so much!  I had the same problem and it was the parameter sniffing!

     

    Thanks again

    Thursday, April 29, 2010 4:04 PM