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.5 Who Am I?
2. Application Code Best Practices
2.1 Introduction
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.4 Dynamic SQL
3.5 Big Result Set
3.6 Unnecessary ORDER BY and DISTINCT clause
3.7 Queries in Transaction
3.9 Isolation Level
3.10 Big Join statements
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
4. Indexes and statistics
4.1 Introduction
4.2 What is index
4.3 Table without index
4.4 Clustered Index
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
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:
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
It is under construction. We are working on it.
Post a Comment