Blog
SQL Server Processors & Security Options
By Don Kiely | November 30, 2012
SQL Server 2012 supports multiple processors if they are available on the server. The Processors page, shown in the figure below on a dual-processor machine with the processor information expanded, lets you control how SQL Server uses multiple processors.
The list of processors lets you specify which processors to use for this instance of SQL Server. You can change the Processor Affinity to dedicate one or more processors to SQL Server and reduce some of the overhead of moving threads between processors. This change can help the database server's performance but may hinder Windows' performance. I/O Affinity binds disk input/output operations to one or more processors. Using this option essentially binds I/O threads to the specified processors.
On multiprocessor machines, the check boxes below the list of processors save you time by letting you change the options for all processors. When you check either or both options, the corresponding options in the grid above are disabled.
The lower part of the page lets you control threading. You can set the maximum worker threads, which helps limit the resources consumed on servers with hundreds of client connections, since normally a new Windows thread is created for each connection. Setting a maximum can hurt performance because once the limit is reached, any new connection attempts must wait for another connection to close before accessing the server.
You can also boost the priority of SQL Server threads. This can result in more processor time relative to other processes on the server, but can actually hurt performance if essential Windows functions are unable to run efficiently.
Keep in mind that changing processor options reduces SQL Server's ability to manage its own resources. You should consider doing so only when you have a situation that SQL Server does not seem to be handling appropriately.
Security Options
Security is a critical feature in SQL Server 2012, and the Security options page in Server Properties lets you configure a few settings, shown below. Probably the most important setting on this page is the server authentication mode, either Windows authentication alone or mixed mode in which both SQL Server and Windows authentication are available.
The login auditing options control which authentication operations are logged in the Windows event logs. The default is Failed logins only, which is a great tool to alert you to some types of attacks, such as a dictionary attack on user names and passwords. The more information you log the better chance you have of troubleshooting, but the greater the effect on performance.
You can set up a proxy account for use when calling the xp_cmdshell extended stored procedure, which runs code in a Windows command shell. Code that runs in the command shell has the permissions of the specified user. Make sure that whatever account you specify here has the least privileges necessary for the tasks you'll perform in the command shell. Otherwise, an attacker who gains control of SQL Server can run high-level operations from the command shell.
The C2 audit tracing option configures the server to record attempts to access Transact-SQL statements and database objects. This saves a huge amount of information, so you'll need to monitor the disk files to make sure that you don't run out of disk space, which can cause SQL Server to shut itself down to prevent attacks. The cross-database chaining option can enable ownership chaining across SQL Server instances. This is a potential security hole, so you should have a good reason to enable this option.
