Tuesday, January 24, 2012

SQL Server Performance - Part 1

I recently delivered training on SQL Server Performance factors. Look for upcoming blog posts for these topics to be covered up in near future:

1.       Data Organization in SQL Server
a.        B-Tree (Balanced Tree)
                                                               i.      Index Root
                                                             ii.      Data Page
                                                           iii.      Intermediate Node
                                                           iv.      Leaf Node
                                                             v.      Index Row
                                                           vi.      Example of Jumps
b.       Heap
2.       Index
a.        Index Types
                                                               i.      Clustered
                                                             ii.      Non-Clustered
1.       Included Columns
2.       Covering Index
3.       Filter Index
3.       Index Fragmentation
a.        Fragmentation
b.       Fill Factor
4.       Execution Plan
a.        Compute Scalar
                                                               i.      Stream Aggregate
                                                             ii.      Hash Aggregate
b.       Index Seek
                                                               i.      Clustered Index Seek
                                                             ii.      Index Seek
c.        Index Scan
                                                               i.      Clustered Index Scan
                                                             ii.      Index Scan
d.       Key Lookup (Bookmark Lookup)
e.        Nested Loops
f.         Hash Match
g.        Merge Join
h.       Sort
5.       Indexed Views
6.       Partitioning
a.        Partition Function (How to partition)
                                                               i.      RANGE RIGHT
                                                             ii.      RANGE LEFT
b.       Partition Scheme (Where to partition)