Sp_configure on Managed instance

-> We use Sp_configure almost everyday on our Database Server in On-Premise.

-> We can access Sp_configure in Managed Instance as well. But not all setting can be tweaked.

-> We will check most of the used configuration settings and see if it can be changed or not,

NameCan be changed?
Show Advanced OptionsYes
Ad Hoc Distributed QueriesYes
allow updatesNo
automatic soft-NUMA disabled No
backup checksum default Yes
backup compression defaultYes
blocked process threshold (s) Yes
clr enabled Yes
clr strict security Yes
contained database authentication Yes
cost threshold for parallelism Yes
cross db ownership chaining Yes
Database Mail XPs Yes
filestream access level No
fill factor (%)No
index create memory (KB) Yes
lightweight pooling No
locks No
max degree of parallelism Yes
max server memory (MB) No
max text repl size (B) No
max worker threads Yes
min memory per query (KB) Yes
min server memory (MB) No
nested triggers Yes
network packet size (B) Yes
Ole Automation Procedures Yes
optimize for ad hoc workloadsYes
priority boost No
recovery interval (min) Yes
remote admin connections Yes
Replication XPs Yes
scan for startup procs No
xp_cmdshell Yes, But when you execute xp_cmdshell in query window. You will get an error “‘xp_cmdshell’ is not supported in this version of SQL Server.”

Thank You,
Vivek Janakiraman

The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Leave a Reply