none
how to resolve slow running stored producer ?

    Question

  • Hi All,

    I have faced issue regarding stored procedure.

    actually  my application team ran the stored procedure regular basics (contain some inserts)  ,,every day it take 20 mins for executing but today it's tacking the more than 2 hours (still it's running) . 

    i have checked the which they are inserting and ran the updated statics also,i have checked  and fixed the table fragmentation and indexes related issues but still it's tacking the logtime for executing .

    what is the recompile ?

    how to resolve the slow running stored procedure?

    Please post the your valuable ANS ASAP.

    Thanks in Advance. 


    RAM

    Monday, July 22, 2013 6:24 AM

All replies

  • First of all, you need to find the query which taking time and share the execution plan for the query. We would be able to help you better.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, July 22, 2013 6:32 AM
  • Hi,

       Kindly Check the following some tips,

      

    Recompilation process is same as compilation and degrades server performance.When SQL Server 2005 recompiles stored procedures, only the statement that causes recompilation is compiled, rather than the entire procedure. Recompilation occurs because of following reason:

    • On schema change of objects.
      • Adding or dropping column to/from a table or view
      • Adding or dropping constraints, defaults, or rules to or from a table.
      • Adding or dropping an index to a table or indexed view if index is used by the plan.
      • Adding or dropping trigger from a table
      • Dropping statistics from a table that is used in plan.
      • On change of the SET options: When a compiled plan is created, the SQL server also stores the environmental setting of a connection (SET option) with it. If the same stored procedure is executed by another connection that has a different SET option, then the existing cached plan is not reused. To reduce recompilation caused by Environment change, we should not change the SET options in the connection setting and stored procedure.
    • On statistics change of tables: Every time the SQL Server uses an already cached compiled plan, it checks the optimality before using it. SQL Server decides whether the plan is optimal for current amount of data in underlying tables. It could be that the data amount in underlying tables changed so much that the previously created plan is not optimized. For each table & index, the SQL server maintains a modification counter and if the counter values exceed the defined threshold, the previously created compiled plan is considered stale plan and a new plan is created.

    • Use Table variable instead of temporary tables: Because a change in cardinality of a table variable does not cause recompilations, consider using a table variable instead of a temporary table when faced with a problem of excessive recompilation. However, the use of table variables can lead to poorer query plans. Distribution statistics are not stored for table variables, and cardinality is only available during recompilation and not during initial compilation. One has to check whether this is the case and make an appropriate trade-off.

    • Use NOLOCK in your stored procedure everywhere you are using Tablename

    NOLOCK indicates that no shared locks are issued against the table that would prohibit other transactions from modifying the data in the table.

    The benefit of the statement is that it allows you to keep the database engine from issuing locks against the tables in your queries; this increases concurrency and performance because the database engine does not have to maintain the shared locks involved

    SELECT COUNT(*) FROM SalesHistory WITH(NOLOCK)

    • Include SET NOCOUNT { ON} in your stored procedure

    Stops the message indicating the number of rows affected by a Transact-SQL statement from being returned as part of the results.

    Syntax

    SET NOCOUNT { ON | OFF }

    Monday, July 22, 2013 9:15 AM
  • I concur with Latheesh - the execution plan will reveal why you are getting the sudden slowness. I would check for parameter sniffing.

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Monday, July 22, 2013 1:17 PM
    Moderator
  • Ram,

    While execution of your SP, if its taking more than 2 hrs I guess there must be some blocking on any objects which are participating in Insert queries. Please check using SP_who2.


    Thanks <br/> Manish <br/> <br/> Please use <font color="#009933">Marked as Answer</font> if my post solved your problem and use <font color="#009933">Vote As Helpful </font>if a post was useful.

    Monday, July 22, 2013 1:25 PM
  • Hi All thanks for your replies ...

    Manish,

    I have checked for blocking but there is no any blocking on that section.

    Thanks 


    RAM

    Tuesday, July 23, 2013 4:45 AM

  • Hi TSRM,

    When we perform the stored produce, it is necessary to use the resources of CPU, Memory, or Disk(I/O) . We can use a specific DMV to query if you are encounter the problems of waiting for resources. For example, we can query wait_type, wait_time_ms, waiting _tasks_count via the sys.dm_os_wait_stats DMV.

    In addition, for troubleshooting the slow running query/SP, we need to note the usage of local variables and parameters sniffing. Parameters sniffing allows SQL to compile a plan that is tailored to the type of parameter that is actually passed into the SP. but if we use local variables, SQL Server does not know the actual values and builds the execution plan by supposing that 30% of the table`s rows will be returned by the query.

    Refer to the following link, it contains some examples with very good explanations:
    http://www.sqlpointers.com/2006/11/parameter-sniffing-stored-procedures.html

    Thanks,
    Sofiya Li

    Sofiya Li
    TechNet Community Support

    Monday, July 29, 2013 9:31 AM
    Moderator
  • Hi there,

    Looks like your stored procedure required to redesign. run profiler trace so that you can find out which statement is taking too much time to execute.

    try to split your stored procedure in to transactional part and maintenance part(drop index, recreate, update stats etc..) if you are dealing huge amount of data. 

    what is the recompile ?

    By definition Stored procedure is a compiled sql statement/staments. when it complied Query optimiser decides how to execute it with least amount of effort.

    In your case you have large amount of inserts/ deletes/updates. If you do not recompile query optimiser still uses the old statistics. This will result some performance degrade. in order to use the updated statistics by the query optimiser stored procedure recompile option will be used.

    I hope this helps you.

    Cheers

    kumar

    Tuesday, July 30, 2013 1:07 PM