Dec 28, 2014

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.5 Who Am I? 

2. Application Code Best Practices  
2.6 Storing data vs calling web services 
2.8 Data Caching  
2.9 Power of transact SQL 


3. Efficient Transact SQL Query Writing 
3.5 Big Result Set 
3.6 Unnecessary ORDER BY and DISTINCT clause 
3.7 Queries in Transaction 
3.9 Isolation Level 
3.17 Schema qualified object name 
3.20 SELECT * and SELECT * INTO 
3.21 Avoid Use of Non-correlated Scalar Sub Query 
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.38 XML data 
3.40 Complex calculation before and after derived table 
3.41 Fastest way to populate records

4. Indexes and statistics 
4.1 Introduction 
4.3 Table without index 
4.7 Index for DML queries 
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
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.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  

2 comments:

  1. Anonymous7/22/2015

    Hello sir..
    your website is so easy to read and very much knowledgeable thank u very much for giving that type of knowledgeable ..but some links are not activated on database tuning part..how can i activated or those are under construction..plz tell

    ReplyDelete
    Replies
    1. It is under construction. We are working on it.

      Delete