Opinions vary as to whether or not you should enable Lock Pages in Memory by default or not, but everyone agrees that it should not be enabled until after an appropriate value for Max Server Memory is set first. If LPiM is set without setting Max Server Memory, it could cause severe external memory pressure that can only be corrected by manual intervention. It is required to be granted before enabling AWE on 32-bit systems, and is also a requirement to use large pages support in 64-bit SQL Server.
Frequently shrinking and growing log files causes high numbers of VLFs. Auto-growths of the log file requires pausing all queries using the transaction log to be blocked for the duration of the growth setting which causes query delays and could even be enough added time to cause queries to timeout. Auto-shrink runs as a background process and does not block user queries. Operations that modify file sizes, such as shrinkage or growth, cannot run at the same time as backups. If a backup is running when a growth or shrink starts, the backup will block. Likewise, a new backup process will be blocked by a growth or shrinkage. References: Shrinking a Database and Recommendations and Guidelines for Setting the Auto-shrink Database Option
If all 3 options are in play, the maximum degree of parallelism used at run-time will be the lesser of the query hint or Resource Group. In this case, the maximum degree of parallelism will be 4. The MaxDOP query hint can over-ride the server setting, but not the Resource Group setting.
The default power plan in Windows Server 2008 and newer is Balanced which will throttle the CPU power up and down based on load (but not very well). SQL Server should always use the High Performance power plan which allows the CPUs to run at full power. See more: Enabling High Performance Power Plan via PowerShell
A non-user domain account is one of the recommended account types to use as the service account for the SQL Server service. Managed Service Accounts (MSAs) are also recommended for the service account; however, an MSA can only be used on a single machine. Group Managed Service Accounts (GMSAs) can be used on a grouping of servers. Local System is a highly privileged account on the local machine and would give SQL Server too many rights at the OS level. Using it is a security risk. Security groups do not have passwords assigned to them and cannot be used to run a service.
Transaction log files are used sequentially and there is no performance benefit to having multiple log files. You should only create a single log file for any database. Additional log files should only be added as a temporary emergency action when you are out of disk space until the disk space can be expanded.
If you need to restrict the maximum number of connections for a given user or group, the recommended approach is to use a logon trigger. This cannot be constrained by Resource Governor.
Processor Affinity and I/O Affinity should both be left at the default setting of allowing SQL Server to set automatically except in very rare cases. When it does need to be adjusted, the way it is set depends on the individual needs for the system and there is no single guidance for how to set the settings.
Checksum has been the default and recommended page verification method since SQL Server 2005. You should update this setting when upgrading a database from a SQL Server 2000 or older instance. Torn page detection was the default and recommended setting in SQL Server 2000. Prior to SQL Server 2000, there was no page verification setting. It is a myth that you can achieve any performance boost from disabling page verification.
The Maximum Degree of Parallelism in this situation would be the lesser of 8 or the number of logical cores per NUMA node (16). Be wary of advice that tells you that an OLTP system should disable parallelism (1). There is a very slim edge case where this is the appropriate setting. Max DOP recommendations: Configure the Max Degree of Parallelism Server Configuration Option.
Share your Results :
Share your Results :
Share your Results :
Share your Results :
Please share this quiz to view your results.