In the first part of this article, which you can find here, I discussed some of the most common SQL Configuration options available through sp_configure. There are plenty of options that you can play with, but in this second article, I’ll talk about a few more important settings that didn’t fit in the last post.
Some of these settings do require that you enable advanced options. I discussed that in detail in Part 1, so I highly recommend you go and read that article first to get a sense of what needs to be done first:
Remote Admin Connections
This feature is very useful in an event of a frozen or non-responding SQL Server. By default, the feature is set to 0, which means an admin connection can only be setup locally on the server. However, if the server is inaccessible or frozen, you would want the ability to try and create this single admin connection remotely. Creating this connection remotely would allow you to run diagnostics, troubleshoot the issue, and perhaps even fix the problem without having to resort to the ultimate solution, a reboot of the SQL Server. Also, with the implementation of Windows Server Foundation in the future, we can expect that the local option for RAC/DAC will no longer exist and will always behave as remote.
To turn on this feature, it’s as simple as executing the following syntax:
EXEC master.sys.sp_configure N’remote admin connections’, ‘1’;
Now that the remote connection is available, we can utilize what’s called a DAC, or a Dedicated Admin Connection. We can do that from any machine on the network with SQLCMD or SSMS Installed. The thing to keep in mind is if you opt to use SSMS, the object explorer will not work. Since this feature operates on only 1 open connection, object explorer cannot be used so you’ll need to connect from the query window.
If you wish to open an admin connection using SQLCMD utility, you’ll need to provide the –A option, for example:
Sqlcmd –S sqlserverinstance –U sa –P sapassword –A
To connect to a database, for example master database, you can run:
Sqlcmd –A –d master
If you prefer GUI interface like most of us do, then you can do the same via SSMS. At the connection window, you will prefix your server name with ADMIN to initiate a DAC connection. For example, ADMIN:MSSQLSERVER, and then simply provide a sysadmin level credentials like you normally would.
DAC usually listens on port 1434, so I generally recommend when you create your firewall rule for port 1433 that you also include port 1434 as well.
User options feature allows you to change the default SET options so that they default to something else. Perhaps you found out that the SET options out of the box don’t work well with your application. Instead of having to set these through the app or on a user by user basis, you can simply set new defaults using this feature.
In order to make changes to the SET options, you’ll most likely need to consult a chart, unless you can remember it by heart. If so, then kudos to you, but if you’re like me, you’re going to need that chart. Well, here it is:
|1||DISABLE_DEF_CNST_CHK||Controls interim or deferred constraint checking.|
|2||IMPLICIT_TRANSACTIONS||For dblib network library connections, controls whether a transaction is started implicitly when a statement is executed. The IMPLICIT_TRANSACTIONS setting has no effect on ODBC or OLEDB connections.|
|4||CURSOR_CLOSE_ON_COMMIT||Controls behavior of cursors after a commit operation has been performed.|
|8||ANSI_WARNINGS||Controls truncation and NULL in aggregate warnings.|
|16||ANSI_PADDING||Controls padding of fixed-length variables.|
|32||ANSI_NULLS||Controls NULL handling when using equality operators.|
|64||ARITHABORT||Terminates a query when an overflow or divide-by-zero error occurs during query execution.|
|128||ARITHIGNORE||Returns NULL when an overflow or divide-by-zero error occurs during a query.|
|256||QUOTED_IDENTIFIER||Differentiates between single and double quotation marks when evaluating an expression.|
|512||NOCOUNT||Turns off the message returned at the end of each statement that states how many rows were affected.|
|1024||ANSI_NULL_DFLT_ON||Alters the session’s behavior to use ANSI compatibility for nullability. New columns defined without explicit nullability are defined to allow nulls.|
|2048||ANSI_NULL_DFLT_OFF||Alters the session’s behavior not to use ANSI compatibility for nullability. New columns defined without explicit nullability do not allow nulls.|
|4096||CONCAT_NULL_YIELDS_NULL||Returns NULL when concatenating a NULL value with a string.|
|8192||NUMERIC_ROUNDABORT||Generates an error when a loss of precision occurs in an expression.|
|16384||XACT_ABORT||Rolls back a transaction if a Transact-SQL statement raises a run-time error.|
It’s a long chart, but if you know the setting you wish to alter from its default behavior, all you need to find is the number associated with that setting.
Suppose you wish to turn on NOCOUNT set option globally for everyone. If you look at the chart, the ID for NOCOUNT is 512. Therefore if I wanted to run the script to do this for me, it would look like this:
EXEC sp_configure ‘user options’, 512;
If you wish to change it back, just run the same statement with a 0 instead.
Now, the ID isn’t just any random number. Each ID for each SET option represents it’s bit position in the list of SET options. In our NOCOUNT example above, the ID is 512. If you convert 512 to binary, it looks like this:
You’ll notice that 1 is exactly 10 places away from the right side, and if you look at our chart, NOCOUNT is exactly 10th SET option. So why does that matter? Well, suppose to want to turn on multiple options, how do we find out what the correct value is?
Suppose that in addition to NOCOUNT, I also want to turn on ANSI_PADDING and ANSI_NULLS. Those 2 setting are #5 and #6 in the list. NOCOUNT is #10. So let’s put 1s in their place and we get
If we convert that to decimal, we get 560. Now if you’re binary savvy, you can do the conversion yourself, otherwise a simple google search will do the conversion for you as well. Or, those of you with a keen eye may have noticed that if we simply add the decimal values (16, 32, 512) we’ll also get 560. You don’t even have to convert! But it’s fun to see where the numbers come from.
Now, in order to configure all 3 of those settings, all we need to do is run the same command with 560 as our value:
EXEC sp_configure ‘user options’, 560;
Do keep in mind that if the user connects with their own specific SET options, it will override whatever default settings are already there.
Optimize for Ad hoc Workloads
SQL Server relies on execution plans; it saves them in memory and re-uses them to optimize repeat queries as best as possible. By default, it will do this for all queries, even those that were only executed once. These single-execution plans can sometimes occupy more memory than normal, which could potentially lead to memory pressure. You want most of the available memory going to your SQL Server data cache, instead of being used for storage for plans you don’t really need anymore.
This setting changes the SQL behavior a little bit, in such a way that instead of storing these 1-time plans, only a hash is stored on the first run. If the query keeps running then the plan will be generated as normal. As you might’ve guessed, storing just the hash is way cheaper on memory than storing the whole plan.
To enable this feature, it’s as simple as running the following query:
EXEC master.sys.sp_configure N’Optimize for ad hoc workloads’, ‘1’;
Now if you already have a lot of 1-time plans, turning on this setting will not make them disappear. The setting will only be applied on new plans moving forward, however you’ll need to empty the cache manually of any existing single use plans. The fastest, easiest way to clear plan cache is to run the following:
DBCC FREESYSTEMCACHE(‘SQL Plans’)
This will clear out all plans, which can have some potential negative side effects until all the plans are rebuilt. For more advanced method of just clearing out the single use plans, I recommend visiting Kimberly Tripp’s post on SQLSkills.com for more in-depth information on plan caches, as well as queries that will help you eliminate just the single-use plans.
Scan for Startup Procs
This one is pretty simple and as the name suggests, it will scan for any procedures which are marked as “startup” when the SQL Service starts. There are numerous types of stored procedures one might want to run at SQL startup. A common one I use is to manually set a couple of trace flags at startup.
We all know we can turn on trace flags by editing startup parameters in SQL Configuration tool, but using startup procedure is another method in case you don’t have access to the configuration tool.
To turn on the feature, it’s as simple as running:
EXEC master.sys.sp_configure N’scan for startup procs’, ‘1’;
Here’s an example of my stored procedure that will turn on a couple of trace flags at startup:
CREATE PROC EnabletraceFlags
DBCC TRACEON (1118, -1);
DBCC TRACEON (3226, -1);
exec sp_procoption N’EnabletraceFlags’, ‘startup’, ‘on’
We create a proc called EnableTraceFlags, in which we enable two flags 1118 and 3226. Then we execute the proc which will turn on the flags for our current session. After that, we set startup option to on, which will mark it as a startup proc and our setting will make sure this proc is run next time SQL Service starts.
This option is very powerful, in fact sometimes even considered dangerous as a lot of DBAs avoid turning it on. It’s an extended procedure which allows SQL Server to run command shell commands. If you’re running the service as admin, then you’ll also have administrator level access to the command shell. The pros of having this tool enabled seem to always outweigh the cons, plus there are many other security measures that can be implemented without having to skip on this powerful tool.
So let’s go ahead and enable it:
EXEC master.sys.sp_configure N’xp_cmdshell’, ‘1’;
Now that it’s enabled, let’s try some commands. Suppose you want to get a directory listing of the default directory. For an admin level account, this will likely be C:\Windows\System32. You can check the directory with the following SQL command:
EXEC xp_cmdshell ‘dir’;
Based on your access you may get different results or may get an error that the access is denied. However, with properly elevated access you should be able to run any kind of windows command through SQL. It can be very useful for many things, such as manipulating files, backups, running tasks and programs, etc.
This sums up part 2 of the common sp_configure settings. Between these 2 articles, you’re likely going to find a few settings which you will most certainly use at some point during your SQL Configuration. While there are many more, some are highly specialized for a specific task, or a specific issue. One must exercise caution when modifying some of these settings as they can have negative impacts on performance. Before you make a change to a setting, spend time and familiarize yourself with it so that you understand the changes as well as any side effects that may show up.
I hope the article has been informative, please comment below if you’d like to see additional commands and configuration options explained. Thanks for reading!