Optimize SQL for SCOM

Maybe I’m old school, but do you ever feel like optimizing SQL is like playing a video game?

Keep on shooting, hopefully you don’t hit your thrusters and drift and then die!

 

Does your SQL DBA Team (or did you):

NTFS Allocation Unit set to 64KB for the SQL Server drives

Dedicate memory for the OS (set SQL to use max memory)

Set autogrow to be greater than 1 MB?

MaxDOP (degrees of parallelism)

TempDB’s are on separate mounts (and match the number of cores)

Database and log files are on separate mounts

Backup SQL Server encryption keys

Disable XpCommandShell

 

Most of the documentation can be had from a single link

https://docs.microsoft.com/en-us/system-center/scom/plan-sqlserver-design?view=sc-om-1711

 

Disable XpCommandShell https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/xp-cmdshell-transact-sql