none
Stored proc slow after migration RRS feed

  • Question

  • My team just completed a migration of business logic jobs and databases from 2008 R2 to a 2014 Always On cluster.  The 2008 R2 server hosted databases, SSIS packages, and SQL Agent scheduled jobs.  We separated out the scheduled jobs and SSIS packages on a new stand-alone server and the databases migrated to the cluster.  The migrated databases have stored procs in them.  The procs are called from the stand-alone job server using CmdExec job steps as in: SQLCMD -S <fully qualified server name> -d <db name> -Q "EXEC <sproc name>".

    One of the stored procs is running very slow, but completes.  What tools can i use to diagnose where the problem query is within the stored proc while the job is running?  I don't have the benefit of running the stored proc code interactively and looking at the resulting execution plan as there are staging tables that need to be processed by other steps in the scheduled job.  Are there DMVs or extended events that can pinpoint the cause of the slow running proc?  Thanks.

    Thursday, February 13, 2020 10:50 PM

All replies

  • Hi District9

    >What tools can i use to diagnose where the problem query is within the stored proc while the job is running?
    1.error log;
    2.“Activity Monitor” in SSMS;
    3.“Query Store”;
    4.ApexSQL Plan;
    Please reference this:how-to-identify-slow-running-queries-in-sql-server

    >Are there DMVs or extended events that can pinpoint the cause of the slow running proc?
    These tools can supply useful information for you to analyze the cause.
    Please reference this:extended-events

    Note:

    In any case, you need to collect the SQL Server ERRORLOG. For SQL Server issues other than performance issues,
    ERRORLOG can provide direct or indirect information to help you locate the problem. For performance issues, ERRORLOG can often
    Shows where the performance bottlenecks of the current system are. 
    The performance bottleneck is a very important breakthrough for troubleshooting problems with poor overall performance.

    Best Regards.
    yuxi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, February 14, 2020 3:17 AM
  • So did you, post migration, updated stats for full database ?. Have you made sure you have configured proper max server memory and max degree of parallelism. These are basic things post migration which normally solves the post migration slowness issue.

    Your first aim should be to know why proc is running slow, in job history, if there are multiple steps check which step is taking more time than expected.

    You can also use Brent Ozar's sp_blitzcache to check why proc is running slow.


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Friday, February 14, 2020 7:21 AM
    Moderator
  • You migrated to SQL 2014? I hope that you have SP3 CU4 applied, that is the last version released before SQL 2014 went out of regular support.

    A quick tip is that you should make sure that you have trace flag 4199 enabled in the startup options, so that you buy in on optimizer fixed since SQL 2008 was released, and particularly fixes to issues in the new cardinality estimator that came with SQL 2014.
    There is no guarantee that TF 4199 fixes the problems, since the new CE has meant a shakeup for more than less optimal query that runs better with old CE, often due to pure luck.


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

    Friday, February 14, 2020 7:54 AM
  • +1 to what Erland said, and just in case you fail with all the options to make query run faster and you do not have developer you can change compatibility level of database to 100 so that it can use old CE and may prepare plans which are better. But with updated stats and latest SP/CU for 2014 it should be work just fine

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP


    Friday, February 14, 2020 8:39 AM
    Moderator
  • +1 to what Erland said, and just in case you fail with all the options to make query run faster and you do not have developer you can change reocvery model of database to 100 so that it can use old CE and may prepare plans which are better. But with updated stats and latest SP/CU for 2014 it should be work just fine

    I think you meant to say that the compatibility level should be to 100, not the recovery model...


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

    Friday, February 14, 2020 10:34 PM

  • I think you meant to say that the compatibility level should be to 100, not the recovery model...

    Yes ..sorry about that...corrected

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Saturday, February 15, 2020 6:42 AM
    Moderator