SQL: My Optimization Recommendations
Author: jason
Date: 2014-06-26 13:13:33
Category: Musing

Here, for your reading pleasure, is my list of SQL optimizations and recommendations. Take them with a grain of salt. But don't, because salt can contribute to high blood pressure, so just use your normal skepticism...

Optimization Recommendations for SQL

Operating System
1. Use 64K NTFS Cluster Allocation sizes on the SQL Data/Log drives when formatting
a. http://technet.microsoft.com/en-us/library/cc966412.aspx
2. Install SQL Server to somewhere other than the C: Drive
3. Ensure the Anti-Virus engine is set to exclude the SQL binaries, data, and log files
4. Allow Instant File Initialization for the SQL Agent / Engine Service Account
a. http://technet.microsoft.com/en-us/library/ms175935(v=sql.105).aspx
5. Enable Lock Pages in Memory for the SQL Agent / Engine Service Account
a. http://technet.microsoft.com/en-us/library/ms190730(v=sql.105).aspx
6. Ensure Power Plan is set to High Performance
a. http://blogs.msdn.com/b/cindygross/archive/2011/03/09/power-saving-options-on-sql-server.aspx

SQL Server Settings
1. Get a good monitoring tool like FogLight, SolarWinds, something…
2. Configure nightly full backups and 15 minute transaction log backups
3. Set Trace Flag 3226 to limit backup messages
a. http://msdn.microsoft.com/en-us/library/ms188396.aspx
4. Enable Optimize for Ad hoc workloads
a. http://msdn.microsoft.com/en-us/library/cc645587.aspx
5. Set Cost Threshold for Parallelism to 25
a. http://msdn.microsoft.com/en-us/library/cc645587.aspx
6. Set the Maximum Server Memory to 70% (ish) of the Physical RAM
a. http://msdn.microsoft.com/en-us/library/ms178067.aspx
7. Add 1/2 to 1 TempDB data file for each CPU, size each data and log file appropriately, 8GB each initially for a big database server
a. http://technet.microsoft.com/en-us/library/ms175527(v=sql.105).aspx
8. Change the default number of SQL Error Logs from 6 to 30
a. http://msdn.microsoft.com/en-us/library/ms177285.aspx
9. Utilize Ola Hallogren scripts for Nightly or Weekly Integrity Checks and Re-Indexing
a. Install the scripts, create SQL Agent Jobs
b. Re-Indexing Fill Factor 80 or 90 and maybe Pad Index On
c. http://ola.hallengren.com/
10. Set the Policy Management – History Retention in Days to 30 or 60
a. http://msdn.microsoft.com/en-us/library/dd795280.aspx
11. Purge Backup History older than 30 or 60 Days
a. Consider adding an additional Step to the default SysPolicy Purge History Job
b. http://blog.sqlauthority.com/2008/11/11/sql-server-delete-backup-history-cleanup-backup-history/
12. Configure the Database to Send Email and Alerts
a. Setup Database Mail Profile and SMTP Server
b. Create a DBA Operator with Email Address
c. Assign the DBA Operator in Notification tab of SQL Agent Jobs to email alerts of Failures
13. Add SQL Alerts
a. http://msdn.microsoft.com/en-us/library/ms180982.aspx
b. http://www.sqlskills.com/blogs/glenn/creating-sql-server-agent-alerts-for-critical-errors/

SQL Database Settings
1. Locate the Data and Log files on separate drives
2. Ensure Database Options – Recovery – Page Verify is CHECKSUM
a. http://msdn.microsoft.com/en-us/library/bb402873.aspx
3. Set the Automatic - Auto Update Statistics Asynchronously is True
a. http://msdn.microsoft.com/en-us/library/ms190397.aspx
4. Size each Log File appropriately (prevent unexpected growth and NTFS fragmentation)
a. Small databases something like 1024MB
b. Large databases initially at 8192MB and add 8192MB pieces one at a time
c. Reference SQL Server Internals pages 178-180
5. Size each Data File appropriately (prevent unexpected growth and NTFS fragmentation)
a. Small databases something like 5-10GB
b. Large databases something like 50-100GB
6. Set the Data / Log file Auto Growth to a fixed 1024MB, re-evaluate periodically based on free space usage
a. http://blogs.msdn.com/b/batuhanyildiz/archive/2013/03/02/autogrowth-option-for-sql-server-database-files.aspx
b. For the log file reference SQL Server Internals pages 178-180

Stored Procedure / Query Optimizations
1. Avoid unnecessary network chatter
a. Use “SET NOCOUNT ON” at the top of each procedure/query
b. http://msdn.microsoft.com/en-us/library/ms189837.aspx
2. Avoid Parameter Sniffing
a. Declare Local Variables, set parameters to the local variables, use the variables in the queries
b. http://blogs.msdn.com/b/turgays/archive/2013/09/10/parameter-sniffing-problem-and-workarounds.aspx
3. Avoid excessive Recompiles
a. Schema qualify tables, procedures, functions etc. SELECT id FROM dbo.Table1 vs SELECT id FROM Table1
b. http://support.microsoft.com/kb/243586
4. Offload Transaction Time for Long Running UPDATES, DELETES, INSERTS
a. Use Temp Table variables or Temp Tables to store the transaction results, then use the Temp Table to perform the main UPDATE, DELETE, INSERT
5. Be aware of the SET OPTIONS
a. http://www.sommarskog.se/query-plan-mysteries.html

Keep Reading
1. SQL Customer Advisory Team: http://blogs.msdn.com/b/sqlcat/
2. SQL Server Blog: http://blogs.technet.com/b/dataplatforminsider/
3. Update Center SQL Server: http://technet.microsoft.com/en-us/sqlserver/ff803383.aspx
4. FogLight Community: http://en.community.dell.com/techcenter/f/
5. FogLight Support: https://support.software.dell.com/foglight
6. SQL Skills: http://www.sqlskills.com/
a. http://www.sqlskills.com/blogs/paul/
7. Brent Ozar: http://www.brentozar.com/
a. http://www.brentozar.com/blog/
8. Dave Pinal: http://blog.sqlauthority.com/
9. SQL Blogs: http://sqlblog.com/blogs/default.aspx
10. MS SQL Tips: http://www.mssqltips.com/

jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009