Ken,

Wishing you a speedy recovery, our prayers and wishes to get well soon!

Thanks Jeff.

We are still in testing mode so we are running through SSIS server ==> Integration Services Catalogs => SSISDB => SalesFolder => Projects => PAckages ==> Sales.dtsx (Execute from...

Thanks Jeff.

We are not using the linked server and both the servers are in the same domain like Dom1\Sql1 and Dom1\SSISSql1.

We are using sql service account is Mcrosoft cirtual account...

Thanks Brian for your detailed information with very good example.

I understood that i need to add more memory as increasing MAX memory could create problem as OS and application have...

Thanks Brian for your detail response.

We have total Memory 8 GB so i have increased MAX Memory to 6 GB and it's working fine right now. but we have also...

I have checked  with following query:

We don't have Resource pool enabled:

SELECT is_enabled FROM sys.resource_governor_configuration;

It returns '0'

Thanks once again Tyler.

Let me build the query to store the data from the sys.dm_exec_procedure_stats DMV view and store into table.

Thank You!

Thanks once again Tyler for your input.

I think it's a good idea to store the result in a one central table and run the daily job for each DB.

Although it's...

Thanks once again.

Right, i am doing for each DB, Actually  i am using to get for all the database in one procedure call and storing into table and running the...

Thanks for your response.

I am using in my stored procedure which creates temp tables and then loading into table for all the Databases in the server so in that some...

Frederico,

Thank you!

For your comments "Parameter Compiled Value Runtime Value  [@ONum] '31856258' '31856258'"

I  have used EXEC procedure WITH RECOMPILE to use the newer plan using only [@ONum] '31856258'

It's the same view...

Jeff,

Right now i am testing query with only one "ordNo" and based on timing trying to see how i can improve it but I think you are pointing to SET...

Thank you Frederico.

Completely agreed with that view will be the issue as joining multiple tables and using some function which slows down the process.

But didn't not much fully understood as...

Jeff,

Any idea how i can post better way the execution plan here as it's big and lengthy and in three part?

View code i have already posted, i am also posting...

Jeffery,

Thank so much for valuable suggestions.

Right now i am not worried much about 2017 as i'm trying to fix in 2014 as it's not like 2014 having an issue and...