Hidden gem – SP_CONFIGURE “A journey discussing each options”

I have been working on various configure options which sql will allow its users to do and they are really interesting.

Note : Changing these options should be done only after proper research and study and its not recommended to deploy this in Production environment without conduting tests in DEV/QA.

This post will explain first 18 sp_configure options :

Ad Hoc Distributed Queries:

What it means? : Ad hoc distributed queries use the OPENROWSET and OPENDATASOURCE functions to connect to remote data sources that use OLE DB.This method should be used for data sources which are not used frequently. Default value is 0,which means SQL server does not allow adhoc access by default.

affinity I/O mask & affinity mask:

What it means? : Both affinity I/O and affinity mask goes together. Affinity mask – Controls processors, and this should to be set after a lot of research and study. This can degrade performance. Affinity I/O will help you to nominate processors to be used for sql server I/O activity.

By default affinity mask is 0, which means that windows will decide (Algorithm) on which process needs which CPU.

Below is the table, which defines a sample affinity mask for a 8 CPU server:

By setting affinity I/O you are specifying a CPU to use only for I/O.

Affinity mask and Affinity I/O should not be set as 1.

Agent XPs :

What it means? This option is enabled to allow SQL Server Agent extended procedure and the SQL Server Agent node will be unavailable in SSMS if the value 0.


The default value is Zero and it Changes to 1 when SQL Server Agent is started, ie the value will be 1 if SQL Server Agent is set to automatic start during setup.)

allow updates:

What it means? By setting this option as 1,sql server will allow users will proper permission to update system tables and can create SPROC’s that update system table. It is always a best practice to keep this setting default.

awe enabled:

What it means? In SQLServer we can use the Address Windowing Extensions (AWE) API to access the physical memory in excess to the limit set on configured virtual memory.

AWE is not required and cannot be configured for 64 bit windows operating systems.

blocked process threshold :

What it means? This is a threshold in seconds, which is defined to generate reports on blocking.


c2 audit mode:

What it means? This feature should be turned on only if there is a specific requirement. This feature is used to track all successful and failed access to objects. This will help to track system activity and possible security violations.

The audit data is saved as a file and is saved in the default data directory and once the size reaches 200 MB a new file will be created. There is a danger of running out of space for this directory and SQL Server will shutdown on its own if this scenario occurs.

clr enabled :

What it means?  Enabling this option will ensure that user assemblies can be run in SQL Server.

CLR is a best replacement for xp_cmdshell.


cost threshold for parallelism:

What it means? This is a threshold at which SQL Server will create and run parallel plans for sql queries. The default value is 5.SQL Server will create and run a parallel plan only when the cost of running the single plan is more than the value mentioned for cost threshold.

Setting the value means all the queries below the value will use single plan.

This value will be considered only if you are running multiple processors.


cross db ownership chaining:

What it means? This option is disabled by default and should be enabled with proper knowledge on how it works and the security threat comes in with this option. If you set cross db ownership at Instance level, this means all databases will have this feature enabled and this is indeed a potential risk.

Ownership chain works like this:

User X grants permission to User Y on view Month Jan.

When User Y does a Select *, his permission is checked and as he has the permission data is retrieved.

Month Jan also require information from view Sales.SQL Server finds that User X is the owner and doesn’t check the permission of view Sales.

Sales also require information from view Invoice. SQL Server finds that User X is the owner and doesn’t check the permission of view Invoice.

When view Month Jan tries to pull information from Table Projects, the server will check if cross –database chaining is enabled, if enabled it will check the permission of the table. As User X is the owner, information is provided.

Database Mail XPs :

What it means? This will enable database email on the server.

default full-text language:

What it means? Specify a default language value for full-text indexed columns. The default setting is chosen from default language of the server.

default language:

What it means? This is the default language setting for SQL Server and default value is English.

default trace enabled:

What it means? Option to enable or disable the default trace log files.

You can read my article https://smartsqlserver.wordpress.com/2011/03/29/who-created-a-server-login-in-sql-server/ for more details on default trace.

disallow results from triggers :

What it means? This option will be removed from future version, hence its not recommended by Microsoft to use it for future deployments. This is an option to control whether triggers return result sets.

fill factor (%):

What it means? When an index is created fill factor value determines the percentage of space needs to be filled with data on the leaf node. This will reserve free space for future growth.




Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s