The first checklist before start using any application using SQL Server should be the default configurations.
What configurations I used to change?
- Max Server Memory: SQL Server will grab all memory available and that will cause concurrency with the OS
- MAXDOP: for OLTP also yes, the good thing is to use it
- Cost Threshold for Parallelism: the default value is too low, and the number will depend on the environment, I used to monitor and change as needed. I use to start with 40
- Model File Sizes: the model database will provide information when need to create more databases. (initial size and autogrowth are way too small and need to avoid file system fragmentation)
- Tempdb Files: look how many CPUs in the server and if the number is less than 8, so would be the number of CPUs, if it is more, so, start with 8 and the files should be the same size and the same autogrowth.
- Database Mail: used to send alerts, very important to get notified when something bad happens.
- Backup Compression: the benefit will be much more than the CPU cost.
- Remote Dedicated Administrator Connection: provides dedicated CPU, memory, and scheduler and by default only works via RPD or physically on server
- Maintenance: start as soon as possible with checkdb, index maintenance, statistics, backups. In this case, I like Ola Hallengren SQL Server solution.
- The last but not least, Security: this is the most important in the list, user accounts or even DBAs as sysadmin, look my tip in MSSQLTips. In addition, disable sa, disable unnecessary logins.
