SQL Performance Tuning

SQL Performance Tuning

  • Basics:- Query plan, Logical operators and Logical reads
  • Point 1:- Unique keys improve table scan performance.
  • Point 2:- Choose Table scan for small & Seek scan for large records
  • Point 3:- Use Covering index to reduce RID (Row Identifier) lookup
  • Point 4:- Keep index size as small as possible.
  • Point 5:- use numeric as compared to text data type.
  • Point 6:- use indexed view for aggregated SQL Queries
  • Point 7:- Database Engine tuning advisor is your best friend.
  • Part 1 - Profiler, Tuning advisor, Clustered/Non-clustered indexes,DBCC command & Reading SQL Plan, OLTP & OLAP.
  • Part 2 - Estimated vs Actual plan/rows, SQL Plan revised, Table/Index scan, Indexes seek, Physical/Logical opertn, Execution mode, columnstore, Rebinds, Rewinds, Nested loop, Hash/Merge join
  • Part 3 - Interleaved Execution, Fragmentation, Performance counters, Page splits and Unused Indexes
  • Part 4 - Extended events, RID lookup and Covering indexes
  • Part 5 - Indexed views, Page splits & unused indexes, Dead locks, Query store Parameter sniffing Statistics
  • Part 6 - Inline vs Stored procedure SQL, Cache miss, Cache hit, Parameter sniffing and Query store.
  • Part 7 - Partitioning, Wait stats, Resource Governor, Inmemory Tables