Lag time for Executing the sql Procs

v-aninam 1

Hi Experts,

I had one concern and need you guys to help here

Env: VS,sql,SSIS,SSRS,SSAS 2014

The caller is SSIS and calls is a stored procedure that loops in Database and calls the different dependent Child Stored procedures like 200 of them(everything is sequential to follow the dependencies).like mentioned.. there 4 to 5 processes (threaded jobs) will run in parallel. Now the question, I am seeing so much lag time to call the store procedures
Example: 1 stored proc will execute in 30 sec.. to start itself it takes 3 min and then completes in 30 sec. So for each child stored procedure, it waits 30 sec to 3 min to kick start the execution. So total 200 Stored procedures avg execution time is 6 min to 10 min... but because of lag time.. its takes 20 to 25 min of time.

I have identified the cached plan are wiping out and recreating because of AUTO_CREATE_STATISTICS/ AUTO_UPDATE_STATISTICS .->is this the cause for the lagtime?
Memory pressure is almost 65 to 70% and we have 300 GB of RAM assigned to sqlserver
No Significant wait starts observed for memory /CPU Bottle necks
we have the best Disks similar to SSD.

why i am seeing significant lag times.. and how to reduce the same. Please redirect me to right path.

Thanks
v-aninam

CathyJi-MSFT 22,201 Reputation points Microsoft Vendor

2022-02-23T06:25:36.36+00:00

Hi @v-aninam ,

Did you mean that SSIS takes more time to call same stored procedures than SSMS? Did you try to compare execution plan? At a guess, for some reason the request from the SSIS is producing a different plan than when you run it from SSMS. This could be the parameters you use, this could be the optimizer doing something odd.
v-aninam 1 Reputation point

2022-02-23T15:05:54.273+00:00

Hi Cathyi-msft/OlafHelper-2800,

Thanks for answering. Initially I also assumed the same because we are using loop in SSIS and that might be the culprit and later changed to while loop with in databases itself...In both the cases i am seeing the lag to kick start the Stored Procedures.

Each and every table was designed with right partitioning and Clustered and i don't see any performance problems/locks/blocks/waitstats(memory/CPU/Spinlocks) with actual execution of stored procedures...Problem is with only with "lag time" to kick start the execution.

we have validated the executed plans of the caller sp with sample while loop calling child sp's.. nothing odd..

will AUTO_CREATE_STATISTICS/ AUTO_UPDATE_STATISTICS wipe-out the cached plan and creates a new plan every when 20% of data changes + it will create a new process for enriching the Stats(internally by locking the table) this is causing the lag.--> is my assumption right?

Justification for that assumption: when we monitor, we see sometimes the cache plans are getting utilized(when change in data is less then 10% ) and less lag time observed... vice-versa but for very large tables(transactional) the lag is high...

wanted to test with AUTO_UPDATE_STATISTICS Async property and verify the lag times.

Thanks
Tom Phillips 17,731 Reputation points

2022-02-23T15:16:10.163+00:00

You will likely get a better answer if you tag your question with sql-server-integration-services.
v-aninam 1 Reputation point

2022-03-08T16:55:46.737+00:00
Hi ErlandSommarskog, I have tried the same with multiple batch loads by creating the below scenarios

Renamed the existing procedure.

Created a new procedure reusing the old name. this procedure just lag to audit table(start and end times) with no business logic--> then no lagtime observed(Because the no business logic/ no execution plans are getting wiped and recreated).

When I Created a new procedure reusing the old name with business logic then observed the lagtime as usual(Because of business logic involved and autostats(Async/Sync) both used by interchanging then also execution plans are getting wiped and recreated causing the lag timings).

@CathyJi-MSFT / @Olaf Helper / @Erland Sommarskog / @Tom Phillips
Guys,
Do you think any other thoughts.. do you guys need any logs/any setting modifications? Before i actually open the MS-Connect ticket(ticket with MS).

Regards
v-aninam
v-aninam 1 Reputation point

2022-03-08T17:01:59.117+00:00
Hi ErlandSommarskog,

thanks for your suggestions,

autostats enriches the histogram for data distribution and better estimates and comes up with a new query plan right? and the cache is limited causing the old one to wiped out.

I have tried the same with multiple batch loads by creating the below scenarios

Renamed the existing procedure.

Created a new procedure reusing the old name. this procedure just lag to audit table(start and end times) with no business logic--> then no lagtime observed(Because the no business logic/ no execution plans are getting wiped and recreated).

When I Created a new procedure reusing the old name with business logic then observed the lagtime as usual(Because of business logic involved and autostats(Async/Sync) both used by interchanging then also execution plans are getting wiped and recreated causing the lag timings).

@CathyJi-MSFT / @Olaf Helper / @Erland Sommarskog / @Tom Phillips
Guys,
Do you think any other thoughts.. do you guys need any logs/any setting modifications? Before i actually open the MS-Connect ticket(ticket with MS).

Regards
v-aninam

6 answers

Olaf Helper 44,501 Reputation points

2022-02-23T06:45:57.547+00:00

why i am seeing significant lag times

We don't know your database design nor what the stored procedures do, so we can not guess the cause.

What about blocking; if all SP accesses the same resources/tables, they can block each other and that would cause the "lag time"?
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Tom Phillips 17,731 Reputation points

2022-02-23T15:14:50.367+00:00

Most likely you are seeing the limits of parallelism in SSIS, not a problem with the proc compile time.

SSIS as a default only runs CPUs/2 parallel processes. If you have more parallel processes, it does not start a new task.

See:
https://www.mssqltips.com/sqlservertip/6078/parallel-execution-of-tasks-in-ssis/
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Erland Sommarskog 111.1K Reputation points MVP

2022-02-23T22:53:53.833+00:00

It is very difficult to diagnose problems only from a verbal description. It's even clear to me what the lag time is. Are you saying that it takes three minutes before the procedure actually starts running? How did you conclude that?

There can indeed be a startup time if the plan for the procedure is not in the cache and has to be compiled. But three minutes, that's quite excessive, unless this is a monster procedure. Then again, you need some skill to actually record this delay, so I am not sure that this is what you are seeing.
Please sign in to rate this answer.
v-aninam 1 Reputation point

2022-02-24T23:16:39.953+00:00

Hi ErlandSommarkog,

Thanks for your reply..Below are my replies.

Question: "Are you saying that it takes three minutes before the procedure actually starts running? How did you conclude that?"
Ans: the first step in each stored procedure is to audit (basically logging the start time) and last step to log endtime..so that stored procedure execution time is date diff of start and enddates .. but for lag time.. we run all stored procedures sequentially ..so previous step end time and next start time will be my lag timing's.(Looping is at the database level).

I suspect the cache plans are wiping out at some regular intervals because of the these database setting AUTO_CREATE_STATISTICS/ AUTO_UPDATE_STATISTICS ( these are synchronous an internal process will start enriching the stats if the current load inserts/modifies 20% of actual data). before the next stored procedures triggers will inserts data other table.

I have already mentioned that i don't any performance problems rather ending having lag problems :).. so trying to change the above database properties to Async manner and testing it.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
v-aninam 1 Reputation point

2022-02-24T23:22:52.353+00:00

Experts,
Any update on this Question.. we are still bleeding ..

please redirect me to right direction.

thanks
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Erland Sommarskog 111.1K Reputation points MVP

2022-02-25T22:34:29.447+00:00
I would suggest that you do this with a few of the procedures:

Rename the existing procedure.

Write a new procedure reusing the old name. All this procedure does is to: a) Log to audit table. b) Call the old procedure. c) Write to audit table.

If you see lag between the wrapper and the inner procedure, this is a strong indication that compilation is the culprit. But as I said, I find it unlikely that compilation takes three minutes.

Also, autostats will not wipe out the cache. It can trigger recompilation, but this occurs on statement level, so it is not the entire procedure that is recompiled. So if plans are being wiped out of the cache entirely, it is for some other reason.
Please sign in to rate this answer.
v-aninam 1 Reputation point

2022-03-08T16:52:33.61+00:00

Hi ErlandSommarskog,

thanks for your suggestions,

autostats enriches the histogram for data distribution and better estimates and comes up with a new query plan right? and the cache is limited causing the old one to wiped out.

I have tried the same with multiple batch loads by creating the below scenarios

Renamed the existing procedure.

Created a new procedure reusing the old name. this procedure just log to audit table(start and end times) with no business logic--> then no lagtime observed(Because the no business logic/ no execution plans are getting wiped and recreated).

When I Created a new procedure reusing the old name with business logic then observed the lagtime as usual(Because of business logic involved and autostats(Async/Sync) both used by interchanging then also execution plans are getting wiped and recreated causing the lag timings).

@CathyJi-MSFT / @Olaf Helper / @Erland Sommarskog / @Tom Phillips
Guys,
Do you think any other thoughts.. do you guys need any logs/any setting modifications? Before i actually open the MS-Connect ticket(ticket with MS).

Regards
v-aninam
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Lag time for Executing the sql Procs

6 answers

Your answer