Return to Answer

added 2 characters in body

Source Link

edited Apr 22, 2023 at 14:27

44.2k
14
140
514

ongoing

If it happens again, I'd suggest using sp_WhoIsActive:

EXEC sp_WhoIsActive
    @get_plans = 1,
    @get_avg_time = 1;

The two additional parameters are to collect execution plans, and to look at the plan cache to see if you're dealing with queries that usually finish quickly, but are now running longer.

past tense

Since you don't have Query Store enabled, you may want to look at the plan cache. It's not always the best place to look, because there are many things that will clear it out, or cause the plans that were causing you the issue to be evicted.

Additionally, if you have optimize for ad hoc workloads enabled, you will only have stubs in place of some queries that may have been the root cause.

An easy way to look at the plan cache is to use sp_BlitzCache:

EXEC sp_BlitzCache
    @SortOrder = 'avg cpu';

EXEC sp_BlitzCache
    @SortOrder = 'executions';

The first will return available plans that used the most average cpu per execution, and the second will return available plans that execute the most frequently.

ongoing

If it happens again, I'd suggest using sp_WhoIsActive:

EXEC sp_WhoIsActive
    @get_plans = 1,
    @get_avg_time 1;

The two additional parameters are to collect execution plans, and to look at the plan cache to see if you're dealing with queries that usually finish quickly, but are now running longer.

past tense

Additionally, if you have optimize for ad hoc workloads enabled, you will only have stubs in place of some queries that may have been the root cause.

An easy way to look at the plan cache is to use sp_BlitzCache:

EXEC sp_BlitzCache
    @SortOrder = 'avg cpu';

EXEC sp_BlitzCache
    @SortOrder = 'executions';

The first will return available plans that used the most average cpu per execution, and the second will return available plans that execute the most frequently.

ongoing

If it happens again, I'd suggest using sp_WhoIsActive:

EXEC sp_WhoIsActive
    @get_plans = 1,
    @get_avg_time = 1;

The two additional parameters are to collect execution plans, and to look at the plan cache to see if you're dealing with queries that usually finish quickly, but are now running longer.

past tense

Additionally, if you have optimize for ad hoc workloads enabled, you will only have stubs in place of some queries that may have been the root cause.

An easy way to look at the plan cache is to use sp_BlitzCache:

EXEC sp_BlitzCache
    @SortOrder = 'avg cpu';

EXEC sp_BlitzCache
    @SortOrder = 'executions';

The first will return available plans that used the most average cpu per execution, and the second will return available plans that execute the most frequently.

Source Link

created Apr 22, 2023 at 14:02

Erik Reasonable Rates Darling

44.2k
14
140
514

ongoing

If it happens again, I'd suggest using sp_WhoIsActive:

EXEC sp_WhoIsActive
    @get_plans = 1,
    @get_avg_time 1;

The two additional parameters are to collect execution plans, and to look at the plan cache to see if you're dealing with queries that usually finish quickly, but are now running longer.

past tense

Additionally, if you have optimize for ad hoc workloads enabled, you will only have stubs in place of some queries that may have been the root cause.

An easy way to look at the plan cache is to use sp_BlitzCache:

EXEC sp_BlitzCache
    @SortOrder = 'avg cpu';

EXEC sp_BlitzCache
    @SortOrder = 'executions';

The first will return available plans that used the most average cpu per execution, and the second will return available plans that execute the most frequently.