SQL Server 2014 Checklist for Performance

Hello once again :)

Over time and discussing with other DBAs, I have compiled a checklist for performance. Here are some areas and comments for each. 

SQL Server 2014 Checklist for Performance

- Test your changes on your test servers
- Make changes incrementally - small change at a time
- Use 64 bit, even on a laptop

Memory
Set MIN and MAX values for memory. Max setting use script to calculate
Enable Optimize for Ad Hoc Workloads

CPU
Set "Cost Threshold for Parallelism" = OLTP = 45 and Reporting = 25. Default is 5 which is to low

Set "Max Degree of Parallelism"  leave it on after you have changed the cost threshold. 

Set NUMA = number of physical processors, not cores, is a good place to start

Disk
TEMPDB on separate disks,

Set TEMPDB data and logs onto separate disks

Use multiple files wtih equal sizes, not equal to the number of processors

Use index compression

More disks is better but limited to the number of controllers

Statistics
Enable AUTO_CREATE and AUTO_UPDATE

Make plans to manual updates on statistics with full scans

Defragment Indexes
Number of pages matter,defrag below 300-500 pages

Cannot defrag below 8 pages

Defragment indexes with less then 50% fragmentation and rebuilt index if higher than 50%

DO NOT USE 
Disable AUTO_CLOSE

Disable AUTO_SHRINK

DO NOT use Profiler GUI, use extended events, even in SQL 2008

Create server side trace using T-SQL scripts. Can you use the GUI to create but execute via T-SQL

Database Design
Separate log and data files onto separate disks

Use multiple file groups even on a single disk

Turn off AUTO_GROW (depends). If not, use fixed growth, not percentage. Do not leave defaults in place

Normalize the data as it benefits performance

Enforce constraints, have foreign keys, primary keys, unique indexes

Use narrow indexes, when possible

Indexes work better on integers - performance better

Don't create too many indexes (depends)

Rebuilt cluster indexes 

Coding
Return only use data you need

Use stored procedures or parameterized queries

Avoid cursors, WHILE, LOOP

Quality all object names

Avoid using sp_* stored procedure names

Avoid functions on columns and LIKE command

SET NOCOUNT ON

Don't nest the views and join views to views

Don't use NOLOCK 

Avoid recompiling execution plans

Use table variables instead of temp tables

Multi statement table valued functions are very bad!

Let me know if you have any additional checks to add

Thanks,

Rudy