Instance Level Settings [SQL SERVER 2012]

Instance Level Settings

Instance-level settings is also known as SQL Server options include memory use, fill factor, processor, I/O Affinity etc.SQL Server  settings apply only for one instance without affecting other instances on the same host computer.

                   Instance-level settings can be configured through the Server Properties dialog box. The Server Properties dialog box contains the following pages:

General

View general information about the instance, including the host operating system, root directory, and server collation. You can’t modify settings on this page.

Memory 

Configure instance memory options, including minimum, maximum, index creation memory, and minimum memory per query.

Processors

Configure processor affinity and I/O affinity, limit worker threads, boost SQL Server priority, and use Windows fibers (lightweight pooling).

Security 

Configure server authentication, log on auditing, server proxy account, Common Criteria compliance, C2 audit tracing, and cross-database ownership Chain

Connections

Configure maximum connections; query governor and default connection options, whether to allow remote connections, and whether to require distributed transactions for server-to-server communication.

Database Settings

Configure default index fill factor, default backup media retention, recovery filter and database default locations.

Advanced 

Configure FILESTREAM options; enable contained databases; and trigger firing, two-digit year cutoff, network packet size, remote logon timeout, and parallelism options.

Permissions  
Configure instance-level permissions.

Configuring Memory Allocation

         By default, a SQL Server 2012 instance uses memory dynamically, querying the host operating system on a periodic basis to determine how much free memory is available and releasing memory back to the host as needed to avoid paging.

       
Instance-Level Settings of Memory:

Min Server Memory  
Max Server Memory 
 

Lowest Min Memory for SQL Server 2012
32-bit = 64 MB
64-bit = 128 MB

Default Settings

Min memory  =  0 MB

Max memory =  2,147,483,647 MB

Use sp_configure when  advanced options are enabled to configure maximum

and minimum server memory. 


Configuring Instance to use Min = 1024 MB , Max = 8,096 MB

EXEC sys.sp_configure 'show advanced options', 1;

GO

RECONFIGURE; GO

EXEC sys.sp_configure 'min server memory', 1024;

GO

EXEC sys.sp_configure 'max server memory', 4096;

GO

RECONFIGURE;

GO

REFERENCES:

Training Kit(Exam 70-462): Administering Microsoft SQL Server 2012 Databases

Memory Allocation on MSDN