Wednesday, October 12, 2011

Major Configuration Options In SQL Server

Ad Hoc Distributed Queries
By default, SQL Server doesn't allow ad-hoc distributed queries using OPENROWSET or OPENDATASOURCE. When this option is set to 1, SQL Server allows ad-hoc access. When this option is not set or is set to 0, SQL Server does not allow ad-hoc access.

Affinity Mask
The affinity mask configuration allows you to specify which CPUs on a multiprocessor computer are to be used to run threads from an instance of SQL Server. You can use the affinity mask configuration option to exclude SQL Server threads from processors that reserved for operating system (OS) processes.

Agent XPs
The Agent XPs option to enable the SQL Server Agent extended stored procedures on this server. When this option is not enabled, the SQL Server Agent node is not available in SSMS Object Explorer.

Allow Updates
The allow updates optiom is to direct updates can be made to system tables. By default, allow updates is disabled, so users cannot update system tables through ad-hoc updates. Users can update system tables by using system stored procedures only. 

Caution  If you create stored procedures while the allow updates option is enabled, those stored procedures always have the ability to update system tables even after you disable allow updates.

Awe Enabled
To enable AWE, set awe enabled to 1. MS SQL Server will reserve almost all available memory, leaving 128 MB to other Application and OS, unless a value has been specified for max server. 


It is strongly recommended that you set a value for the max server memory option each time you enable AWE. 


Otherwise other applications or instances of MS SQL Server 2000 will have less than 128 MB of physical memory in which to run.

Fill Factor
Use the fill factor option to specify how full MS SQL Server should make each page when it creates a new index using existing data. The fill factor percentage affects performance because SQL Server must take time to split pages when they fill up. The recommended is use fill factor is 80% so 20% is reserved for free space.


The setting takes effect after stopping and restarting the server.

Lightweight Pooling

Use the lightweight pooling option is to reducing the system overhead associated with the excessive context switching. When excessive context switching is present, lightweight pooling can provide better throughput by performing the context switching inline, thus helping to reduce user & kernel transitions.

Setting lightweight pooling to 1 causes Microsoft SQL Server to switch to fiber mode scheduling. The default value for this option is 0. 


The setting takes effect after stopping and restarting the server.

Max Degree Of Parallelism
Use the max degree of parallelism option to limt the number of processors to use in parallel plan execution. Set the value to a number greater than 1 to restrict the max number of processors used by a single query execution. If a value is greater than the number of availble CPUs is specified, the actual number of available CPUs is used.

Max Worker Threads
Use the max worker threads option to configure the number of worker threds available to SQL Server processes. SQL Server uses the native thread services of the MS Windws 2000 and Windows Server 2003 operating systems so that one or more threads support each network that SQL Server supports simultaneously, another thread handles database checkpoints, and a pool of threads handles all users.

Recommend 1024 as the maximum for 32-bit SQL Server and 2048 for 64-bit SQL Server.

Min Memory Per Query
Use the min memory per query option to specify the minimum amount of memory(KB) that will be allocated for the execution of a query. For example, if min memory per query is set to 2048 kilobytes, the query is guaranteed to get at least that much total memory. 


You can set min memory per query to any value from 512 through 2147483647 KB (2 gigabytes). The default is 1024 KB.

Remote Access
Use the remote access option to control executing stored procedures from remote servers running instances of MS SQL Server. Set remote access to 1 (default) to allow permission to execute stored procedures from remote servers. Set the option to 0 to prevent execution of stored procedures from a remote server.

The setting takes effect after stopping and restarting the server.

Remote Login Timeout
Use the remote login timeout option to specify the number of seconds to wait before from failing remote login attempt. For example, if you are attempting to log in to a remote server and that server is down, remote login timeout ensures that you do not have to wait indefinitely before your computer ceases its attempts to log in.

The setting takes effect immediately (without a server stop and restart). 

Remote Query Timeout
Use the remote query timeout option to specify the number of seconds that must wait when processing a remote operation before SQL Server assumes the command failed or took too much time to perform (times out error). The default is 600, which allows a ten minute wait.

The setting takes effect immediately (without a server stop and restart).

User Connections
The user connections option to specify the maximum number of simultaneous user connections allowed on SQL Server. The actual number of user connections allowed is depends on the limits to your applications and hardware. 


SQL Server allows a maximum of 32,767 user connections.

XP_Cmdshell
the xp_cmdshell is Introduced in SQL Server 2005. This option that enables systm administrators to control whether the xp_cmdshell extended SP can be executed on a system. By default, the xp_cmdshell option is disabled on new installations.

SQL Mail XPs
Use the SQL Mail XPs option to enable SQL Mail on this server. The possible values are:
0 indicating SQL Mail is not available (default)
1 indicating SQL Mail is available

Remote Admin Connections Option
MS SQL Server 2005 provides a dedicated administrator connection (DAC). The DAC lets an administrator access a running server in order to execute diagnostic functions or T-SQL statements such as finding locks, blocks, cache, etc for troubleshoot problems on the server, even when the server is locked or running in an abnormal state and not responding to a Database Engine connection. 
0 - indicates only local connections are allowed using the DAC
1 - indicates remote connections are allowed using the DAC

Default Trace Enabled
The default trace enabled option to enable or disable the default trace log files. The default trace is for troubleshooting minor issues. It doesn't take much resources, you can always disable through SP_Configure.

Please check these interview questions as well. 
[SQL Server Database Administrator Interview Questions and Answers - Part #12] 
Tags: sql server interview questions. sql server 2008 interview questions. sql server 2008 interview questions and answers . sql server interview questions and answers for experienced. sql server interview questions and answers for freshers. sql server interview questions and answers. sql server dba interview questions and answers. sql server dba interview questions. sql server database administration interview questions . sql server 2008 database administration interview questions. sql server 2012 interview question and answers. sql server 2012 FAQ. sql server 2012 dba interview. sql dba 2012 interview question and answers. sql server 2008 r2 interview question and answers. sql 2012. sql 2008 r2. sql 200 r2 interview question and answers. sql server question and answers. sql server developer interview question and answers. sql developer interview question and answers.  sql server interview questions. sql server 2008 interview questions. sql server 2008 interview questions and answers. sql server interview questions and answers for experienced. sql server interview questions and answers for freshers. sql server interview questions and answers. sql server dba interview questions and answers. sql server dba interview questions. sql server database administration interview questions. sql server 2012 database administration interview questions. sql server interview questions for entry level. sql server interview questions for experienced. sp_configure options.

1 comments:

Thanks for bringing all important and most usable configuration operations for a DBA like me.

Post a Comment