1. Lock Pages in Memory.
2. Set default data and log directories. (Different LUN's)
3. Set SQL to High Priority on all Processors
4. Boost SQL Server Priority on Windows
5. Allocate a maximum amount of memory
6. Spread your data across LUNS
7. PCI and DISA (STIG) Compliance
PCI-DSS Compliance - MS-SQL High Object Permission Granted to PUBLIC or Guest Revoke any object privileges assigned to PUBLIC or GUEST
From the query prompt (repeat for each database):
use <database name>
revoke <privilege> on <object name> from '<public or guest>'
repeat for each object privilege assigned to public or guest.
7. Create a custom group for SYSADMIN functions.
-add authorized users to the custom group.
-add the group the SYSADMIN fixed server role
-remove BUILTIN\Administrators from the role.
8. Move TempDB onto its own Drives. 1 file for each processor.
9. Separate System Databases from User Databases.
10. Change default Sql Ports
11. Use windows logins and disable sql logins. Users and apps should use active directory controlled logins.
12. Setup Backups
13. Setup Maintenance Plans
14. Setup Performance Counter Log and Perfmon.msc counter
15. Separate logs and data to different drives.
16. Install SQL Performance Dashboard RDL’s
17. Set up a system to collect Database size change statistics for storage planning
18. Set up a system to collect table row count change statistics for scalability planning
19. Set up a system to collect disk IO statistics for scalability planning
20. Set up Performance Monitor Log collection system and analyze the log to get SQL performance baseline for scalability planning
21. Set up a system to collect blocking and deadlock statistics for trouble shooting and proactively response to possible production issues
22. Setup poor performing query trace.
http://www.edgeventures.com/KB/content.aspx?g=8B03A690-F509-4037-8740-B6570EB3ADA4
23. Perform Volume Maintenance Security Policy : File Growth Performance.
Add the SQL Server Service User to this Group Policy
GPEDIT.MSC -> Computer Configuration\Windows Settings\Security Settings\Local Policies\User Rights Assignment