SQL Server Performance Tuning

This post keeps complete best practices of database and query performance tuning dashboard and optimization techniques tips and tricks with examples. Detail discussion of how to improve SQL server 2008, 2012 and 2014 performance

1. Common causes database performance bottleneck

1.1 Introduction

1.2 An interesting survey 

1.3 Query Execution Life Cycle 

1.4 Predict about Future data 

1.5 Who Am I? 

2. Application Code Best Practices  

2.1 Introduction 

2.2 Prepared statements 

2.3 Pagination Strategy 

2.4 Looping database objects 

2.5 Mixed Database Layers 

2.6 Storing data vs calling web services 

2.7 Batch Insert 

2.8 Data Caching  

2.9 Power of transact SQL 

3. Efficient Transact SQL Query Writing 

3.1 Introduction 

3.2 Set Based Approach 

3.3 Joining vs Sub-Query 

3.4 Dynamic SQL 

3.5 Big Result Set 

3.6 Unnecessary ORDER BY and DISTINCT clause 

3.7 Queries in Transaction 

3.8 Stored procedure Best Practices 

3.9 Isolation Level 

3.10 Big Join statements  

3.12 Stored procedure vs Function 

3.13 Dynamic predicate in WHERE clause 

3.14 Getting aggregate values from same set of tables 

3.15 Use table value function when it is needed 

3.16 Multi-statement vs. In line Table Valued Function 

3.17 Schema qualified object name 

3.18 Check the existing of records 

3.20 SELECT * and SELECT * INTO 

3.21 Avoid Use of Non-correlated Scalar Sub Query 

3.23 When to use derived tables, CTE and Temp tables 

3.24 Deleting archive data from live big table 

3.26 Collation to compare 

3.27 Try not to use COUNT(*) to obtain the record count in a table 

3.28 Full text search 

3.32 OUTPUT clause 

3.33 Hierarchical data 

3.34 OUTER APPLY

3.35 Ranking, windowing functions 

3.36 PIVOT \UNPIVOT 

3.37 MERGE statement data synchronization

3.38 XML data 

3.39 Local temporary table vs. Table Variables 

3.40 Complex calculation before and after derived table 
3.41 Fastest way to populate records

4. Indexes and statistics 

4.1 Introduction 

4.2 What is index

4.3 Table without index 

4.4 Clustered Index 

4.5 Non-Clustered Index

4.6 How to start creating indexes 

4.7 Index for DML queries 

4.8 Indexed view 

4.9 Column store index 

4.10 Filtered Index 

4.11 XML indexes 

4.12 Spatial indexed 

4.13 What is statistics 

4.14 What information kept by statistics 

4.15 Statistics in query performances 

5. Understanding execution Plan

5.1 Introduction
5.2 Estimated execution vs. actual execution plan

6. Database Design best practices 

7. How to optimize a unknown Database server 

7.1 Where to start query tuning? 

7.2 Development Server Vs Production server 

7.3 Getting all the queries 

7.4 Get all the missing indexes 

7.5 Get all the duplicate indexes 

7.6 Get all useless indexes 

8. Database Server Settings best practices 

8.1 Fill Factor 

8.2 Index fragmentation 

8.3 Resource governor 

9. Database performance Tools 

9.1 Introduction 

9.2 SQL server Tuning Advisor 

9.3 Data Collection 

9.4 Client Statistics 

9.5 Query Profiler 

9.6 Extended events 

9.7 DMVS 

9.10    Activity Monitor

10. Advanced database performance topics 

10.1 Clustering 

10.2 Replication 

10.3 Resource Governor 

10.4 Data cleaning 

10.5 Schedule server restart