Stored Procedure running longer hours

Question

Stored Procedure running longer hours

pdsqsql 436

Hello,

I am facing currently issue as stored procedure running very longer hours although my DB is not that big means around 20 GB.

We are also running OLA's maintenance solution for Update Stats Daily and Index maintenance weekly basis.

Stored procedure is calling multiple Stored procedures and I am currently looking into the code but before I try to fixing code, I am reviewing Index Fragmentation, Update Stats and any missing indexes.

I am looking some clarification and guidance while reviewing the some of above things for tuning purpose.

I know even we are running Ola's maintenance solution, I can see some fragmented Indexes and stats not updated efficiently so I have started to Update the Stats with Full scan instead of Database level.

Do you think that Index rebuild based on >30% fragmentation report or I should consider Page Density, Page count or anything in combination?

I have also looked CPU and Memory while Process running and show maximum CPU and Memory utilization so I started to analyze those query and trying to add an Index.

I might later on look some redundant or unused Index and will decide to drop it.

Lot so tables we are doing kind of burn and build on a daily basis.

As Profile is old now so Extended Event set up will be the best idea and how I can set up to capture troublesome sql/objects?

Appreciate your help!

pdsqsql 436 Reputation points

2026-03-21T17:05:19.05+00:00

While also observing in Activity Monitor during process runs, "%Processor Time" showing very high means block is almost peak full green and somewhat "Batch Requests/sec" block.

2 answers

Your answer

pdsqsql 436 Reputation points

2026-03-21T17:05:19.05+00:00

While also observing in Activity Monitor during process runs, "%Processor Time" showing very high means block is almost peak full green and somewhat "Batch Requests/sec" block.

Answer 1

Erland Sommarskog 137.4K MVP Volunteer Moderator

For a 20 GB database, I would not bother very much about fragmentation. Overall, fragmentation is a bit of a thing of the past. Particularly, the type of fragmentation where pages that are logically adjacent in an index are not physically adjacent on disk. This mattered a lot with spinning disks. Far less so with SSDs. Fragmentation in the sense that pages may only be half-full still matters. But again, for this small database, no I don't think is where you should put your focus. It may actually not matter at all.

You say that there are stored procedures that call other stored procedures. I get an unpleasant feeling of that this is code that is running one-row-at-a-time processing in loops. The best way is to rewrite the code into set-based operation, but that can be a major undertaking. When you run loops, indexes become even more crucial, also on smaller tables. A thing I've seen more than once is people who runs a poor man's cursor, by looping over a temp table like this:

WHILE EXISTS (SELECT * FROM #tmp WHERE done = 0)
BEGIN
   SELECT TOP(1) @id = id, ...
   FROM  #tmp
   WHERE  done = 0
   ORDER BY id 

   --  Do stuff

   UPDATE #tmp
   SET done = 1
   WHERE id = @id
END

And the temp is entirely un-indexed, so all these operations require scans. It just takes a couple of thousands of rows to make this very costly.

Assuming that your code is running loops, a very good tool to troubleshoot this is sp_sqltrace, originally written by Lee Tudor, and which I am happy to host on my web site. You can simply say:

EXEC sp_sqltrace 'EXEC slow_sp', @order = 'Duration'

And it will start a trace, filtered for your spid, capture sp_statement_completed. When slow_sp has completed, it will analyse the trace, and if the same statement is executed multiple times, the data for those executions will be aggregated into a single row. The statement(s) that gets to the top is one you should look into. Beware that if the procedure runs for more than five minutes, you need to adjust the @trace_timeout parameter, because by default the trace stops after five minutes.

Then again, maybe you don't need the data from the full execution. You can also say:

EXEC sp_sqltrace 77, @order = 'Duration'

sp_sqltrace will now set up a trace filtered for spid 77 (or the spid where the slow procedure is running), and run that trace for 10 seconds (adjustable with the parameter @snoop_time) and then perform the aggregation.

You can also use sp_sqltrace to capture execution plans, would that be needed.

Even if I have given you an introduction and some examples, I still recommend that you read the manual page before you start playing.

pdsqsql 436 Reputation points

2026-03-21T22:11:17.1833333+00:00

Erland,

Thanks for your detailed explanation and providing Trace tool.

I am glad you captured exact scenario about my multiple stored procedures calling, Cursor usage, #temp table which reflects your brilliancy and how your experience shows very well expertism you have.

I agreed calling multiple stored procedures and using Cursor so it will be a very slow operation.

I will try to use tool and see how it works but I had question before I use it.

My process runs for many hours before it get completed whole transaction.

What's the best way I should use it to use parameter?

[1] If I use following: @order ='Duration' then what's the number (parameter @snoop_time) I should set as procedure will be running for an hours.

EXEC sp_sqltrace 'EXEC slow_sp', @order = 'Duration'

[2] If I use following: spid 77 you have mentioned means once I run the process then capture Spid and run sp_sqltrace? Any duration needs to set up?

EXEC sp_sqltrace 77, @order = 'Duration'

You have mentioned Index Rebuild or Update Stats with FULL scans will not much effective?

I am just curios if any above with MAXDOP or if I look for Parameter Sniffing or Recompile will be helpful?
Erland Sommarskog 137.4K Reputation points MVP Volunteer Moderator

2026-03-21T22:31:21.82+00:00
When it comes to cursors, make sure that all cursors are STATIC or at least FAST_FORWARD. If you only say:

DECLARE cur FOR SELECT ...

You get a dynamic cursor which means that the cursor condition is re-evaluated on every FETCH. That can be slow.

If the process runs for hours as a scheduled job, I would be tempted to snoop it for 30 seconds to get a first idea. Maybe that reveals something really bad directly. You could also make multiple samples while the procedure is running.

I would be a hesitant to run sp_sqltrace on the full execution if we are talking hours. The trace file could get quite big. Or, more likely, it would reach the max size before the procedure completes. (Trace file always have a max size. I don't remember what size we set in sp_sqltrace, but we don't seem to have a parameter to control it.) Don't forget to set the @trace_timeout parameter if you are going to run that long. By default the trace stops after five minutes.

Is the procedure just one single loop, or does it run a number of sections? In the latter case, I would be inclined to add some logging to get an idea how long each section takes to get better understanding where to put the focus. Of course, this assumes that you can change the procedure to add the loggin.

You have mentioned Index Rebuild or Update Stats with FULL scans will not much effective? I am just curios if any above with MAXDOP or if I look for Parameter Sniffing or Recompile will be helpful?>

First make the analysis of where the bottlenecks are before you start thinking about solutions.
pdsqsql 436 Reputation points

2026-03-22T14:35:30.3366667+00:00

Erland,

Thanks once again for explaining brilliant way.

I tried to create in my utility Database and ran as below and found following result.

EXEC [dbo].[sp_sqltrace] 'EXEC IQ_Test..sp_iq_process_rt_calls 12'

I just randomly run the Trace procedure but I will be running when actually calls are making.

I am just trying to understand that Xplan showing Null and what exactly I will be analyzing?

Duration I normally know what it's taking while actual process call running.
Erland Sommarskog 137.4K Reputation points MVP Volunteer Moderator

2026-03-22T15:37:44.32+00:00

Xplan is NULL because by default, sp_sqltrace does not capture execution plans. (Execution plans are expensive to capture, and they distort the aggregation, since each execution typically has unique numbers.) When you are trying to find the bottleneck, you should not include execution plans.

The most interesting column when you explore a certain workload is the Factor column, the leftmost one. If you are lucky, you may see one statement with a factor over 50%. Then that is the statement you need to look into. Beware that when you have nested procedures, you have a number of EXEC statements for the procedure calls. Those as such are not very interesting.

Of the numbers to the right, the Duration is the most interesting, since that is the execution time.

And if you did not read the manual page, do it now. And if you did read it, you may want to re-read it, as that will answers some of your questions.
pdsqsql 436 Reputation points

2026-03-22T21:48:42.6933333+00:00

Thanks Erland for your kind response.

I understand your concern although I have read manual pages and might read it again.

Duration is more likely understood as we are running job so almost it's taking that much time.

Factor column also get it but only don't understood that why it's showing 100% what I ran to get Trace below

"EXEC [dbo].[sp_sqltrace] 'EXEC IQ_Test..sp_iq_process_rt_calls 12'"

Is it Sp_SqlTrace can call anytime even stored procedure not running ? Reason I am asking as What I see 85% 1st sql which it captured so it's getting previous expensive Sql from the history?
Erland Sommarskog 137.4K Reputation points MVP Volunteer Moderator

2026-03-23T04:16:42.3133333+00:00

If you give sp_sqltrace a batch, that batch will always display as 100%, since that is how long time the trace run. The statement in the procedure only took 85% of the time; the rest might have been spent on compilation or some such. Don't worry too much about those details.

If you snoop spid, the percentage numbers can be all over the place. But again, it is the high percentages for individual statements that are of interest.
pdsqsql 436 Reputation points

2026-03-24T03:56:51.91+00:00

Erland,

Thanks for your help, appreciate it!

I ran while running actual process and found following:

I am trying to understand what exactly getting direction to apply the action.

Is it First Factor% or Duration with Reads?

Should I just run the Text column ones and see the explain plan?
pdsqsql 436 Reputation points

2026-03-24T04:01:29.38+00:00

I have ran following:

[1]

EXEC [dbo].[sp_sqltrace] 'EXEC Q_Test..sp_q_prs_rt_cal 12' ==> This one got 7 rows

[2]

EXEC sp_sqltrace 86, @order = 'Duration' ==> This one got almost 380 rows (Partial records have pasted above for understanding purpose which had high READS and Factor ranges from 41% to 104%
Erland Sommarskog 137.4K Reputation points MVP Volunteer Moderator

2026-03-24T05:03:45.2733333+00:00

So the top individual statement in this sample is OPEN CURSOR. This is exactly what I discussed earlier. It is a dynamic cursor, the default type. This should be changed to a STATIC cursor, so that the cursor is served from a worktable in tempdb.

For the next two statements, the INSERT and DELETE I don't see the full statement and I don't know the tables, but I would be surprised if supported indexes are missing. You may need to look at the plan to figure that out.
pdsqsql 436 Reputation points

2026-03-24T13:03:14.7966667+00:00

Erland,

Thanks once again for pointing it out.

Yes, I will be planning to update CURSOT as you suggested.

INSERT and DELETE was long so I just wanted to mention that there were INSERT and DELETE.

DELETE has where conditions.

Yes, I have created some Missing Indexes and also update stats.
Anjan Kumar Chippa (Quadrant Resource LLC) 0 Reputation points Microsoft External Staff Moderator

2026-03-26T11:41:46.79+00:00

Hi pdsqsql,

Thank you for the response. We wanted to kindly follow up to check if your issue resolved? or let us know if you need any further assistance.

Thanks and regards,
Anjan Kumar Chippa
pdsqsql 436 Reputation points

2026-03-26T21:41:40.6066667+00:00

Thanks for follow up, I am still working and find to resole the issue as still didn't get desired resolution.
pdsqsql 436 Reputation points

2026-03-28T01:30:17.91+00:00

Erland,

I don't understand that I have modified Stored procedure to use Static Cursor and also I tried to add RECOMPILE (OPTION (RECOMPILE)) but doesn't seems any effect.

I have also added some of the index based on query and also updated again Stats with FULL SCAN but not seeing any impact.

I'm kind of in puzzle.

I am thinking to run "DBCC FREEPROCCACHE" if it can make difference.

Any suggestion for Parameter sniffing?
Erland Sommarskog 137.4K Reputation points MVP Volunteer Moderator

2026-03-29T00:37:49.7266667+00:00

I guess you need to run more and longer traces. Maybe a ten-second sample was not enough.
Anjan Kumar Chippa (Quadrant Resource LLC) 0 Reputation points Microsoft External Staff Moderator

2026-04-07T09:40:28.1+00:00

Hi pdsqsql,

As we haven’t heard back from you, we wanted to kindly follow up to check if your issue is resolved? or let us know if you need any further assistance.

Thanks and regards,
Anjan Kumar Chippa
pdsqsql 436 Reputation points

2026-05-07T20:27:24.5733333+00:00

Anjan,

Still working on it as not able to optimize the code, it's still running longer.

I tried to update stats, Rebuild Indexes, New/Modified Index creation with some covering Index.

I have also adjusted MAXDOP, Cost Threshold parameter.
Anjan Kumar Chippa (Quadrant Resource LLC) 0 Reputation points Microsoft External Staff Moderator

2026-05-12T07:00:34.0333333+00:00

Hi pdsqsql,

Thank you for the response. It looks like you have already tried most of the common performance tuning steps including statistics updates, index optimization and MAXDOP/Cost threshold adjustments.

I request you to please follow this below documentation related to the stored procedure, for reference:

Stored Procedures (Database Engine) - SQL Server | Microsoft Learn

Thanks and regards,
Anjan Kumar Chippa
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Anjan Kumar Chippa (Quadrant Resource LLC) 0 Reputation points Microsoft External Staff Moderator

2026-05-18T05:14:52.69+00:00

Hi pdsqsql,

As we haven’t heard back from you, we wanted to kindly follow up to check if your issue is resolved?

Thanks and regards,
Anjan Kumar Chippa

Answer 2

For a stored procedure that suddenly runs for hours, focus first on identifying the exact bottleneck, then use index/statistics work as a supporting action rather than the primary strategy.

Identify the actual expensive queries

Use DMVs to find the high‑CPU and long‑running statements while the procedure is running. The guidance for high CPU and slow queries is:
- Identify queries currently consuming CPU, then, if needed, look at historical CPU‑bound queries.
- For each top query, capture the actual execution plan and logical reads.
For slow queries in general, use the same approach: identify the specific statements inside the procedure that are waiting or consuming CPU, then analyze their plans.

Statistics before index rebuilds

Index rebuilds are expensive in CPU, I/O, and log usage. They should not be run just because fragmentation is >30%.
A rebuild has a powerful side effect: it fully updates statistics on the index key columns (equivalent to UPDATE STATISTICS ... WITH FULLSCAN) and forces recompilation of dependent plans. Many “improvements after rebuild” are actually due to better statistics, not reduced fragmentation.
Because of this, for performance tuning of a long‑running procedure, updating statistics is often the first and cheaper step:
- Update statistics on the tables used by the expensive queries (key columns and important non‑indexed columns) with a higher sampling rate or FULLSCAN where justified.
- sp_updatestats can be used to refresh all stats in a database, but for a specific procedure, target only the relevant tables.
The resource cost of updating statistics is minor compared to index rebuild and often completes in minutes, whereas rebuilds can take hours.

How to decide on index rebuild vs. reorganize

Do not rely on a single fragmentation threshold (for example, “>30% = rebuild”) in isolation.
Consider at least these together:
- Fragmentation level
- Page density (how full pages are)
- Page count (very small indexes often do not benefit from rebuilds)
- Workload pattern (OLTP vs. reporting, heavy range scans vs. point lookups)
- How often the table is “burn and build” (bulk delete/insert patterns can make fragmentation transient)
For heavily updated or “burn and build” tables, review fragmentation and page density after large delete/update operations. Rebuild or reorganize only where fragmentation and low page density are clearly affecting the specific queries identified in step 1.
Since Ola’s solution already handles fragmentation based on thresholds, use it as baseline maintenance and reserve ad‑hoc rebuilds for cases where a particular index is clearly implicated in a bad plan.

High CPU and memory during the run

When CPU is high and the procedure is active, use the high‑CPU troubleshooting pattern:
- Identify the top CPU‑consuming queries.
- Examine their actual execution plans.
- For each, apply:
  - Statistics updates (as above).
  - Missing index analysis using the missing index DMVs, then create only those indexes that clearly reduce logical reads for the target queries.
  - Query rewrites to improve SARGability (avoid functions on columns in predicates, mismatched data types, non‑searchable patterns) and to remove unnecessary row goals (TOP, FAST N, etc.) when they cause bad nested loops.
If, after statistics updates and targeted indexing, CPU is still high, focus on query and schema design rather than more index maintenance.

Handling redundant/unused indexes

Once the main performance issue is understood, review indexes used by the expensive queries and those never used.
Dropping unused or redundant indexes can help overall performance by reducing write and maintenance overhead, but do this after stabilizing the critical workload.

Extended Events instead of Profiler

For ongoing troubleshooting of long‑running procedures, Extended Events is preferred over Profiler.
Typical approach:
- Create an Extended Events session to capture:
  - Long‑running statements (by duration or CPU).
  - High logical reads.
  - Wait information for sessions running the stored procedure.
- Filter by database and/or object name where possible to keep the session lightweight.
Use the captured data to:
- Identify which nested procedure or statement is slow.
- Correlate waits (for example, THREADPOOL, I/O waits) with specific queries.

Maintenance cadence in this scenario

With Ola’s solution:
- Keep weekly index maintenance, but avoid adding more frequent rebuilds unless a specific index is proven to be the cause.
- Ensure statistics are updated often enough. For busy OLTP workloads, daily or even more frequent targeted stats updates on critical tables may be needed.
For Configuration Manager and similar workloads, guidance is to:
- Perform index maintenance as often as once a week.
- Update statistics as often as once a day. This pattern is a good reference: statistics more frequent than index rebuilds.

In summary: for a stored procedure running for hours, prioritize identifying the specific high‑CPU/long‑running queries and improving their plans via statistics, targeted indexes, and query rewrites. Use fragmentation and index rebuilds as a secondary, evidence‑based optimization, not based solely on a fixed “>30% fragmentation” rule.

References: