0

I have a job that runs daily and executes dozens of stored procedures.
Most of them run just fine, but several of them recently started taking a while to run (4-5 minutes).
When I come in in the morning and try to troubleshoot them, they only take 10-20 seconds, just as they supposed to.
This has been happening for the last 10 days or so. No changes had been made to the server (we are running SQL 2012).
How do I even troubleshoot it and what can I do to fix this??
Thanks!!

Tamila
  • 177
  • 1
  • 3
  • 17
  • 4
    How are you troubleshooting them? Are you running them in SSMS? Do they have parameters? It could be parameter sniffing. It could be that the execution plans have reached the tipping point due to skewed data and the plan is less than optimal. It could be a number of things. Can you post one of the procs and answer my questions about debugging techniques? – Sean Lange Mar 10 '16 at 16:47
  • Did anything change around the time the SP's started to slow down? Like is a new job kicking off around the same time. – Bob Kaufman Mar 10 '16 at 16:50
  • If it's only on the first run that things are slow then I would definitely look to your plan cache. Whenever you run anything in SQL it has to do some checks to see if it already has a plan or needs to create one. If your plan cache is bloated, this check can take way longer than it should. Also, you say "first run" but this is a recurring issue so is this the first run after a server restart or...? –  Mar 10 '16 at 18:23
  • @BobKaufman, I checked with my DBA, and nothing seemed to change on the server, there are no other jobs that are running at the same time. – Tamila Mar 10 '16 at 22:48
  • @Kalmino, when I say "first run", I really mean first run of the day. Like I said, it runs daily, from the SSIS package. The server doesn't restart during the night, so I have no clue why it would run slow in the morning, but then run fine when I try to run it on its own later. Also, since it runs automatically from the job, how can I check the plan cache? I am very much a newbie when it comes to tuning and optimizing... – Tamila Mar 10 '16 at 22:51
  • @SeanLange, I am just trying to run it by itself in SSMS. It has a date parameter. It produces the same results with different dates - slow when run in the morning from SSIS, fast when I run from SSMS. It's a relatively straight-forward insert statement, but again, how can I check the plan when it runs from the SSIS automatically? – Tamila Mar 10 '16 at 22:54
  • 2
    It is almost certainly parameter sniffing. It has all the tell tale signs. It runs fast in SSMS but slow from a procedure. The parameter is a datetime which is one of the most common problems for parameter sniffing because the data amount can be so skewed. Take a look at this article by Gail Shaw. It demonstrates the problem and several methods to help improve performance. http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/ – Sean Lange Mar 11 '16 at 13:54

1 Answers1

1

You can use some DMVs (Dynamic Management Views) that SQL provides to investigate the plan cache. However, the results can be a little intimidating and without some background in it, it may be hard to dig through the results. I would recommend looking into some DMVs like sys.dm_exec_query_stats and sys.dm_exec_cached_plans. Kimberly Tripp from SQLSkills.com does some GREAT courses on Pluralsight on how to use these and get some awesome results by building more advanced queries off of those DMVs.

As well, these DMVs will return a plan_handle column which you can pass to another DMV, sys.dm_exec_query_plan(plan_handle), to return the Execution Plan for a specific statement. The hard part is going to be digging through the results of dm_exec_cached_plans to find the specific job/stored procs that are causing issues. sys.dm_exec_sql_text(qs.[sql_handle]) can help by providing a snapshot of the SQL that was run for that job but you'll get the most benefit out of it (in my opinion) by CROSS APPLYing it with some of the other DMVs I mentioned. If you can identify the Job/Proc/Statement and look at the plan, it will likely show you some indication of the parameter sniffing problem that Sean Lange mentioned.

Just in case: parameter sniffing is when you run the first instance of a query/stored proc, SQL looks at the parameter that you passed in and builds a plan based off of it. The plan that gets generated from that initial compilation of the query/proc will be ideal for the specific parameter that you passed in but might not be ideal for other parameters. Imagine a highly skewed table where all of the dates are '01-01-2000', except one which is '10-10-2015'.
Passing those two parameters in would generate vastly different plans due to data selectivity (read: how unique is the data?). If one of those plans gets saved to cache and called for each subsequent execution, it's possible (and in some cases, likely) that it's not going to be ideal for other parameters.

The likely reason why you're seeing a difference in speed between the Job and when you run the command yourself, is that when you run it, you're running it Ad Hoc. The Job isn't, it's running them as Stored Procs, which means they're going to use different execution plans.

TL;DR: The Execution Plan that you have saved for the Job is not optimized. However, when you run it manually, you're likely creating an Ad Hoc plan that is optimized for that SPECIFIC run. It's a bit of a hard road to dig into the plan cache and see what's going on but it's 100% worth it. I highly recommend looking up Kimberly Tripp's blog as she has some great posts about this and also some fantastic courses on Pluralsight regarding this.