Ad hoc update to system catalogs is not supported

While preparing the demo for my previous post “Optimize for ad hoc workloads – SQL 2008“, I encountered an issue and that’s the topic for this blog.

I executed the sp_configure followed by RECONFIGURE and it returned me an error.

sp_configure 'optimize for ad hoc workloads', 1;
reconfigure

The error message stated the ad hoc updates to the system catalogs is not supported.

The workaround to this issue is either to change the setting to allow adhoc updates or to run the reconfigure with ‘override’  option.

Here is how you can change enable the adhoc updates to the system catalogs

sp_configure 'allow updates', 0;
reconfigure

Now you will be able to run the reconfigure statements with out any issues.

The second option is to run the reconfigure with ‘override’ option. This was you don’t require to change the ‘allow updates’ option.

sp_configure 'optimize for ad hoc workloads', 0;
reconfigure with override

The default value for  ‘allow updates’ option is 0 when SQL Server is installed and with this configuration the catalogs cannot be updated manually. If we set it to 1, the catalogs can be updated by any user having appropriate permission and this is very dangerous. So the safer option is to alter the configuration value for ‘allow updates’ to 0.

MSDN also states that SQL 2005 onwards this setting has no effect, though it seems to force the ‘override’ hint when this value is set to 1.

Reference
http://technet.microsoft.com/en-us/library/aa196704(v=sql.80).aspx
http://technet.microsoft.com/en-us/library/ms179262.aspx
http://www.mssqltips.com/sqlservertip/2875/how-to-allow-ad-hoc-updates-in-sql-server-system-catalogs/