SQL Server Performance Tuning

About QuestPond: It started 15 years back with the one single goal of creating quality step-by-step IT programming-related lessons.

We saw many lessons online, either they are done too fast or too slow or are too complicated.

The main goal of QuestPond is to create Step by Step lessons on C#, ASP.NET, Design patterns, SQL, and so on. As years moved on I added other step-by-step lessons like Angular, SharePoint, MSBI, Azure and so on.

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

Do Visit to enroll all course :- https://www.questpond.com/learn-sql-server-step-by-step/cid9