About Me - Who is DR DBA?

Hello. My name is Rudy Panigas and I am a Production Senior Microsoft SQL Server Database Administrator (DBA) with over 14 years of experience. Have published articles with SQL Central, am a leader for the my local PASS chapter in Toronto (TORPASS) and love to automate anything SQL including disaster recovery. I created this blog to share my views, knowledge and to discuss Microsoft SQL server in order to help out others in this field. Please test these scripts before using. I do not warranty and do not take any responsibility for it, its misuse or output it creates. You can reach me at this email address: sqlsurgeon@outlook.com

Tuesday, November 25, 2014

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

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

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

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


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 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 

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


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



No comments:

Post a Comment

Note: Only a member of this blog may post a comment.