Thanks for the replies
Is seeing PAGEIOLATCH waits also associated with query store by any chance ?

We wanted SQL server don't process transactions, but at the same time didn't want to shut SQL services down, because that would flush the plan cache.

and the initial...

Thanks Sue for replying 

My issue got resolved.

It turned out to me one of the DT agents was in a stopped state at the same time during...

Also when I manually run the clean up stored procedure, it just says it removed 0 transactions with 0 commands

Any kind of help is highly appreciated

Agreed.

Avoid any unexpected issues with package deployment by sticking with the latest version of SSDT

Can you try to tweak the database level setting "Use legacy cardinality estimator" and then check the results ?

In our environment it has helped at couple of places...

Agreed

Here is the disk layout we have in our environment

Separate mount point for system databases, which contain all the data and log files for the system...

Can you try to truncate / delete the backup history information ?

Is there a way to selectively update statistics? Instead of performing full scan or 5 % to all the statistics, the statistics where modifications have been made, only update those...

Thanks for the reply.

The worst case would be reinitialize the complete table. We wanted to avoid it because the table was one of the core tables in our...

Can you set up profiler while you run the stored procedure manually and also catch the errors / exceptions in the profiler ?

Please try to check then at...

[font="Verdana"]Yeah, I meant core based license

Sorry for the typo

Upgrading from CAL license to core license without uninstalling the SQL instance.

[/font]