2

Suppose that I wish to run the following

/* Check for any pending configurations, just in case. */
SELECT *
FROM sys.configurations
WHERE value <> value_in_use;
GO

EXEC sp_configure 'remote admin connections', 1;
GO
RECONFIGURE
GO

Is there any risk, such as clearing the plan cache or blocking queries, to running this during a busy day? I know that the remote admin connection is safe. I'm concerned about sp_configure.

J. Mini
  • 1,161
  • 8
  • 32

1 Answers1

2

For this specific sp_configure setting, no it will not clear the plan cache and there shouldn’t be any risk running that. In my experience this is one of our preferred sp_configure settings to set and I’ve never seen it resulting in blocking or any other issues when enabling.

Your question seems to more so be concerned about running sp_configure specifically and if that will cause issues running it on a busy day. While I’ve personally not seen running it intraday result in any blocking or issues, I have seen it hang for a bit after changing a setting and it took a while to kick over. But while it was hanging and when I ran sp_WhoIsActive, there was no blocking or impact on other queries running. Mind you this only had happened once or twice.

When in doubt, run it after hours.

There are some that will clear the plan cache and here’s a Brent Ozar blog post where he talks about sp_configure settings that clear the plan cache:

https://www.brentozar.com/archive/2017/09/sp_configure-options-clear-plan-cache/

Hope this helps!

Jordan Boich
  • 179
  • 5