1

Starting at midnight for 30-40 minutes our application responds slowly (or times-out completely) What would cause this kind of cpu usage on a SQL 2012 server?

CPU Load chart

I cannot find any scheduled tasks or maintenance plans which run at this time.

closed as too broad by Erik Darling, Shanky, hot2use, Mark Sinkinson, Colin 't Hart Mar 28 '18 at 12:11

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Schedule sp_whoisactive to run during that time and log to table. This way you can get info on what was running. Also check if any data loading is being done at that time. – Kin Shah Mar 27 '18 at 22:53
  • Are you sure the CPU load is coming from SQL Server and not an external application? – Tom V Mar 28 '18 at 9:52
  • I scheduled sp_whoisactive to output to a table every minute (overkill) during the problem time. It had all of the details to find a long running query that appears to be causing the problem. – MattO Mar 29 '18 at 17:12
1

You can look at some joined system views like dm_exec_query_stats, dm_exec_sql_text and dm_exec_query_plan. The goal is to look what's on cache.

Look for last_execution_time field and search the time you found the problems. You may get an idea of what was going on during that time.

Not the answer you're looking for? Browse other questions tagged or ask your own question.