SQL Server Instance configurations can be changed in the properties. Right click on the SQL Server Instance and select properties.
General page: I look under the general page for easy access to the machine (or VM) amount of RAM, processors, and if the server is clustered. Also, If you are using Always On Availability Groups, then HADR should be set to True.
To see more about the general page: Microsoft’s explanation of the General Page
Memory page: Use this page to set the Min and Max SQL Server memory usage. I never touch the index creation or min memory per query; I just leave those at default.
Max memory should/must be changed. In order to calculate the correct settings for Min and Max SQL memory, there is a delicate balance between OS and SQL Server. If you have multiple instances of SQL Server on the same server, then both SQL Servers are trying to regulate memory at the same time. Calculating memory is not an exact science.
Just for fun, click on those radio buttons on the bottom. Switch between Config and running. Configured Values are the values you typed into the input fields. Running values is what is actually being used at this time. If Config and Running do not match, then the SQL Server Instance needs a reboot in order for the changes to take affect.
See these blogs for more help:
- Microsoft’s Memory How to Configure
- Microsoft’s Memory options page information
- Brent Ozar on setting memory
- Klaus Aschenbrenner Three Configuration Options that must change
- Glenn Berry 2008 Memory settings – still holds true
- Interesting Max Memory Limits for 2016 SP1
Processors page: Usually there is nothing to configure here. However, I have used this page when RedGate was hammering processor 0, so I set SQL Server to not use that one processor. I also used changed the CPU affinity when I had two instances of SQL Server running on the same server. I was able to direct SQL Server one to processors 0+1 and SQL Server two to processors 2+3. I did that so the two SQL instances would not step on each other while processing. As with the memory page, config and running show what is selected and what is actually happening, respectively.
DO NOT SELECT boost SQL Server Priority.
Processor affinity changes
Max Worker threads
- Max worker threads and when to change
- Brent Ozar Do lots of connections slow down the SQL Server
- Erik Darling Don’t touch max worker threads
Security Page: The server authentication you choose is based on the authentication you prefer. I usually use SQL Server and Windows Authentication, just in case Active Directory goes offline.
For Login Auditing, I normally use Failed logins and monitor weekly. However, at one company that stored encrypted credit card information the auditor asked us to store both failed and successful logins. Please be aware that logging successful logins on a busy system will make for VERY LARGE files in your log directory. So plan disk space accordingly. Logging is also recorded in the Windows application event log.
Set up the server proxy account to run xp_cmdshell from a SQL Server login that has less privileges than sysadmin. The last three checkboxes I have never used, but included links to documentation.
- Microsoft Server Properties Security Page
- Choose an Authentication Mode
- Andy Hayes Auditing SQL Server Logins
- ED Vassie Create a Proxy for xp_cmdshell
- Microsoft Common criteria compliance
- Dinesh C2 Auditing
- Microsoft Cross Database Ownership Chaining
Connections Page: Connections should be set to 0 (unlimited), unless you want to limit the number of connections to the instance. I always make sure that allow remote connections is checked.
Database Settings Page: These are the default settings for every database in your instance. I like to check Compress Backup.
Advanced page: My favorite Server Properties page. I have been on this page too many times to count. Most of the time I am here to configure Cost Threshold or Max DOP.
Enable Contained Databases – Allows logins to be contained within the database. All metadata is contained in the database and the contained database does not rely on the instance properties. Microsoft Contained Databases
Filestream – Used to store BLOB data (examples: Word docs, images, pdf, etc…) on disk with a pointer stored in the Filestream attribute column. Jacob Sebastian Intro to Filestream
Miscellaneous – I have never changed any of these options, but have a link to describe each one. Microsoft Advanced page properties
Network – I have never messed with these properties. SQL Soldier To fiddle with or not to fiddle with
Parallelism – Oh here it is, my favorite part. Performance 101. There are many blogs on how to configure Cost Threshold and Max DOP, but he proper configuration for those fields is all trial and error. Testing, tons of testing to figure out what the proper balance is for those fields. Never set Max DOP to the number of processors on your server or one query can take up all the threads and not leave any for other queries. I also do not let SQL Server determine what Max DOP should be, so I do not set that value at 0. I set my Max DOP in multiples of 2 or 4, just my rule of thumb, and leave at least 2 cores for other processes.
- Kendra Little – Max degree of confusion
- Jared Karney – Determine Cost Threshold
- Rob StGeorge – Cost Best Practice Recommendations
- Randy Dyess – Query Wait option
- Microsoft Locks option config
Permissions Page: This topic I will leave to Microsoft. This topic requires a 3 day class. I will have to blog on this topic in the future.
- Microsoft Permissions Page information
- Microsoft Authorization and Permissions
- Microsoft Server Level Roles