Today I remember one of my older cartoon years ago created for Indexing and Performance.
Every single time when Performance is discussed, Indexes are mentioned along with it. In recent times, data and application complexity is continuously growing. The demand for faster query response, performance, and scalability by organizations is increasing and developers and DBAs need to now write efficient code to achieve this.
DBA and Developers
A DBA’s role is critical, because a production environment has to run 24×7, hence maintenance, trouble shooting, and quick resolutions are the need of the hour. The first baby step into any performance tuning exercise in SQL Server involves creating, analysing, and maintaining indexes.
Though we have learnt indexing concepts from our college days, indexing implementation inside SQL Server can vary. Understanding this behaviour and designing our applications appropriately will make sure the application is performed to its highest potential.
Video Learning
Vinod Kumar and myself we often thought about this and realized that practical understanding of the indexes is very important. One can not master every single aspects of the index. However there are some minimum expertise one should gain if performance is one of the concern.
We decided to build a course which just addresses the practical aspects of the performance. In this course, we explored some of these indexing fundamentals and we elaborated on how SQL Server goes about using indexes. At the end of this course of you will know the basic structure of indexes, practical insights into implementation, and maintenance tips and tricks revolving around indexes. Finally, we will introduce SQL Server 2012 column store indexes. We have refrained from discussing internal storage structure of the indexes but have taken a more practical, demo-oriented approach to explain these core concepts.
Course Outline
Here are salient topics of the course. We have explained every single concept along with a practical demonstration. Additionally shared our personal scripts along with the same.
Introduction
Fundamentals of Indexing
Index Fundamentals
Index Fundamentals – Visual Representation
Practical Indexing Implementation Techniques
Primary Key
Over Indexing
Duplicate Index
Clustered Index
Unique Index
Included Columns
Filtered Index
Disabled Index
Index Maintenance and Defragmentation
Introduction to Columnstore Index
Indexing Practical Performance Tips and Tricks
Index and Page Types
Index and Non Deterministic Columns
Index and SET Values
Importance of Clustered Index
Effect of Compression and Fillfactor
Index and Functions
Dynamic Management Views (DMV) – Fillfactor
Table Scan, Index Scan and Index Seek
Index and Order of Columns
Final Checklist: Index and Performance
Well, we believe we have done our part, now waiting for your comments and feedback.
Reference: Pinal Dave (http://blog.sqlauthority.com)
Filed under: PostADay, SQL, SQL Authority, SQL Index, SQL Performance, SQL Query, SQL Server, SQL Tips and Tricks, SQLServer, T SQL, Technology, Video