Lag time for Executing the sql Procs

Question

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,431 Reputation points Microsoft External Staff

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,786 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

Your answer

CathyJi-MSFT 22,431 Reputation points Microsoft External Staff

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,786 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

Answer 1

Erland Sommarskog 135.4K MVP Volunteer Moderator

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.

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

Answer 2

v-aninam 1

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

please redirect me to right direction.

thanks

0 comments

Answer 3

Erland Sommarskog 135.4K MVP Volunteer Moderator

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.

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.

Answer 4

Tom Phillips 17,786

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/

0 comments

Answer 5

Olaf Helper 47,621

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"?

0 comments