SQL Mentoring Series — Part 3 of 3: Performance Engineering and Modern Data Architecture
SQL Mentoring Series — Part 3 of 3Part 1: Relational Foundations and Core Querying · Part 2: Advanced Querying, Analytics and Transactional SQL · Part 3: Performance Engineering and Modern Data Architecture
A practical mentoring series for junior developers who want to move from correct SQL syntax to reliable database engineering.Parts 1 and 2 taught us how to model business reality, query it correctly, analyse it, change it safely, and protect it when many users work concurrently.
Part 3 asks a different class of question:
Why does one correct query return in 20 milliseconds while another correct query takes 20 seconds?
At this stage, syntax is no longer the main challenge. We begin looking inside SQL Server.
We want to understand how rows are physically stored, how indexes help SQL Server navigate data, how the optimizer chooses an execution plan, how statistics influence its estimates, how plan caching can help or hurt, how to diagnose a slow query, and how analytical systems change the architecture when data volumes become much larger.
The seven modules in this part are:
- Module 14 — Index Architecture and Index Design
- Module 15 — How the Query Optimizer Thinks
- Module 16 — Reading Execution Plans and Diagnosing Slow SQL
- Module 17 — Statistics, Plan Caching, Query Store, and Intelligent Query Processing
- Module 18 — Data Quality, Analytics, and Storytelling
- Module 19 — Warehousing, Large-Scale SQL, and Production Applications
- Module 20 — Modern SQL and Final Capstone
Performance tuning is not about memorising tricks. It is about understanding what work SQL Server is doing, why it chose that work, and what information or design changes would allow it to do less unnecessary work.That means a good tuning conversation starts with evidence.
How many rows were estimated?
How many rows actually arrived?
How many pages were read?
Was the plan using a seek, scan, lookup, sort, hash, or spool?
Did memory spill to temporary storage?
Was the plan compiled for a very unusual parameter?
Are the indexes helping the important workload, or simply making writes more expensive?
A professional does not guess at these questions. A professional measures them.
Module 14 — Index Architecture and Index Design
Why indexes exist
Imagine a telephone directory containing one million names.
If the names are stored in random order, finding one person may require reading an enormous portion of the directory.
If the directory is arranged alphabetically, you can navigate towards the relevant section quickly.
That is the basic purpose of an index.
An index gives the database an organised access path.
It does not change the business meaning of the data. It changes how efficiently SQL Server may be able to locate it.
The important word is may.
An index does not force SQL Server to use it.
The optimizer compares available strategies and may decide that a scan is cheaper than using an index.
This is the first performance lesson:
An index is an option offered to the optimizer, not a command given to it.
Pages: the unit you should learn to think about
SQL Server stores ordinary row-based data in fixed-size pages.
When developers look at a table, they think in rows.
The storage engine frequently works in pages.
This distinction matters because performance is often related to how many pages SQL Server must touch.
Imagine a table containing 50 million orders.
If SQL Server reads nearly every page to find ten orders, that is a lot of unnecessary work.
If an index helps it navigate directly to the relevant pages, the workload can fall dramatically.
Later, when using SET STATISTICS IO, logical reads help us see this page activity.
This is why tuning is often less about making SQL syntax shorter and more about reducing unnecessary reads.
Heaps
A SQL Server table without a clustered index is called a heap.
Do not confuse this with the .NET managed heap. The term means something different here.
A heap still has pages and rows, and SQL Server still knows where those pages are. What it does not have is a clustered-index key organising the table through a B-tree.
A heap can be perfectly reasonable for some workloads, such as simple staging or temporary import tables.
But if the table is regularly searched by key or scanned in ordered ranges, a heap may not provide the navigation structure the workload needs.
Rows in a heap can be identified through a row identifier, or RID, which points towards the physical location of the row.
If a row grows and no longer fits where it currently lives, SQL Server may move it and leave a forwarding reference. Repeated forwarding can introduce extra work.
The right conclusion is not “heaps are bad.”
It is:
Use a heap when heap behaviour genuinely suits the workload.
Clustered indexes
A clustered index organises the table's data through a key.
Suppose the Orders table is clustered on OrderId.
At the leaf level of the clustered B-tree are the actual table rows.
That is why people often say:
The clustered index is the table.The statement is shorthand. The relational table still exists, but the leaf level of the clustered structure contains the table's data.
Because the data rows can participate in only one clustered organisation, a table can have only one clustered index.
This does not mean the clustered index must always be the primary key.
A primary key is a logical integrity rule.
A clustered index is a physical access structure.
SQL Server often creates a clustered index for a primary key by default when no clustered index already exists, which makes the two concepts appear identical. They are not.
The right clustered key depends on the workload.
B-tree navigation
Traditional rowstore indexes are arranged as balanced trees.
At the top is a root page.
Large indexes may contain intermediate pages.
At the bottom are leaf pages.
Imagine searching for Order 145,210.
SQL Server does not need to compare that value against every key in a million-row index.
It can navigate from the root to the relevant range, from there to a narrower range, and finally to the leaf containing the required record.
A balanced tree is valuable because the number of navigation levels remains relatively small even as the table grows.
This is the structural reason an index seek can locate a small number of rows efficiently.
Seek versus scan
A seek navigates to a specific key or key range.
For example:
SELECT OrderId, CustomerId, TotalAmount
FROM dbo.Orders
WHERE OrderId = @OrderId;With a suitable index on OrderId, SQL Server may seek directly to the row.
A seek can also navigate to the beginning of a range and read forward until the range ends.
A scan reads through all or a significant portion of an index or table.
Beginners often learn:
Seek good. Scan bad.
That is not a reliable rule.
Suppose your query genuinely requires 90 percent of a table.
Reading the structure sequentially may be cheaper than navigating through an index and performing millions of random lookups.
A scan of a tiny table can also be completely harmless.
The right question is:
Given how much data the query actually needs, was this access path sensible?Module 16 will teach us how to answer that with execution-plan evidence.
Choosing a clustered key
A useful clustered key is often described as:
- Narrow
- Stable
- Unique
- Increasing
Because the clustered key is also carried inside nonclustered indexes as the locator back to the base row. A wide clustered key makes every nonclustered index wider.
Why stable?
Changing the clustered key can require movement inside the clustered structure and maintenance in nonclustered indexes.
Why unique?
If the clustered key is not unique, SQL Server still needs a way to identify duplicate keys and may add internal uniqueness information.
Why increasing?
An increasing key often sends new rows towards the end of the index rather than inserting them randomly throughout the structure.
That can reduce some forms of page splitting.
However, these are engineering guidelines, not laws.
A heavily concurrent insert workload can create pressure on the final page of an ever-increasing index.
A good engineer tests the actual workload.
Nonclustered indexes
A nonclustered index is a separate B-tree containing selected key values and a locator to the full row.
Suppose the table is clustered on OrderId, but the application frequently searches by CustomerId.
A nonclustered index can be created:
CREATE INDEX IX_Orders_CustomerId
ON dbo.Orders(CustomerId);Now SQL Server has an access path ordered by customer.
On a clustered table, the nonclustered index normally carries the clustering key so SQL Server can locate the full base row.
On a heap, the locator is typically an RID.
This explains why one table can have several nonclustered indexes but only one clustered index.
Each nonclustered index is another separate access structure built for another navigation pattern.
Key lookups
Suppose the nonclustered index contains CustomerId, but the query also needs Status, OrderedAt, and TotalAmount.
SQL Server may use the nonclustered index to find matching rows and then visit the clustered index to retrieve the missing columns.
That second visit appears as a Key Lookup.
A lookup is not automatically a problem.
If the seek finds three rows, three lookups may be cheap.
If it finds 200,000 rows, 200,000 lookups may be very expensive.
This explains a common situation:
An index exists, but SQL Server chooses a scan.
The optimizer may estimate that scanning the table is cheaper than performing an enormous number of random lookups.
Covering indexes and INCLUDE
A covering index contains everything a query needs.
For example:
CREATE INDEX IX_Orders_CustomerId
ON dbo.Orders(CustomerId)
INCLUDE (OrderedAt, Status, TotalAmount);CustomerId is part of the key because it helps SQL Server navigate.
The included columns exist at the leaf level so SQL Server can return them without visiting the base table.
This distinction matters.
Key columns affect navigation and ordering.
Included columns help cover the query.
Do not place every selected column into the key simply because the query returns it.
A huge key makes the B-tree larger and more expensive to maintain.
Composite index order
Consider:
CREATE INDEX IX_Orders_Customer_Date
ON dbo.Orders(CustomerId, OrderedAt);The structure is ordered first by customer.
Inside each customer, rows are ordered by date.
That index can support:
A search by customer.
A search by customer plus date range.
It is usually less useful for a search only by date, because all dates are distributed inside customer sections.
This is why composite key order matters.
A common useful pattern is equality columns followed by range columns.
For example:
CustomerId = @CustomerId
OrderedAt >= @StartDateoften aligns naturally with:
(CustomerId, OrderedAt)Do not reduce this to “always put the most selective column first.”
Selectivity matters, but so do predicate type, ordering, joins, and the actual workload.
Filtered indexes
A filtered index contains only rows satisfying a condition.
Suppose the application frequently requests pending work and pending rows are a small fraction of the table.
You might use:
CREATE INDEX IX_Orders_Pending
ON dbo.Orders(OrderedAt)
INCLUDE (CustomerId, TotalAmount)
WHERE Status = 'Pending';The index is smaller because completed rows are not included.
That can reduce storage and maintenance while giving the optimizer statistics specifically describing the filtered population.
Filtered indexes are useful for carefully defined subsets such as pending, active, non-null, or unprocessed rows.
But they must match real query predicates, and parameterized queries should be tested because the optimizer must be able to prove that the filtered index is valid for the query.
Indexes make writes more expensive
Every index has a maintenance cost.
When a row is inserted, deleted, or an indexed value changes, SQL Server may need to update several structures.
If a table has fifteen indexes, a single business update may touch the base table plus many index structures.
More indexes can mean:
More storage.
More transaction log.
More memory.
Slower inserts.
Slower updates.
Slower deletes.
Longer maintenance operations.
This gives us the central index-design trade-off:
Indexes generally trade additional write and storage cost for faster access to important read patterns.Your job is not to create every possible index.
Your job is to support the important workload with the smallest useful set.
Module 15 — How the Query Optimizer Thinks
Declarative SQL creates a planning problem
When you write:
SELECT o.OrderId, c.CustomerName
FROM Orders AS o
JOIN Customers AS c
ON c.CustomerId = o.CustomerId
WHERE o.Status = 'Pending';you describe the required result.
You do not specify:
Which table must be read first.
Which index must be used.
Which join algorithm must be selected.
How much memory should be granted.
Whether the operation should run in parallel.
SQL Server must make those physical decisions.
The component responsible for creating the plan is the query optimizer.
The component responsible for executing the chosen plan is the execution engine.
This distinction matters.
The optimizer plans.
The execution engine performs.
Parsing and binding
The first stage is parsing.
Parsing asks:
Is this valid T-SQL grammar?
A missing comma or malformed clause may fail before SQL Server even considers the referenced objects.
Binding then resolves names.
Does dbo.Orders exist?
Does CustomerId exist?
Is the column reference ambiguous?
Are the referenced functions and objects valid?
A query can be syntactically correct but fail during binding because it references a column that does not exist.
A simple memory aid is:
Parsing understands the language.
Binding understands the names.
Logical and physical operations
After parsing and binding, SQL Server understands the logical request.
The logical operations might be:
Read orders.
Filter pending orders.
Join customers.
Return selected columns.
But a logical join does not tell SQL Server how to join.
The physical alternatives may include:
Nested Loops.
Merge Join.
Hash Join.
Likewise, logically finding rows may physically use:
An index seek.
An index scan.
A table scan.
A seek plus lookup.
Several physical plans can produce exactly the same correct result.
Optimization is about choosing an efficient physical implementation of the logical request.
Simplification
Before exploring a large number of plans, SQL Server may simplify the logical expression.
Redundant predicates can sometimes be removed.
Contradictions may be recognized.
Certain joins can sometimes be eliminated when constraints prove that the extra relation cannot change the result.
This is an important performance lesson:
Constraints do more than protect data quality.
Trusted primary keys, foreign keys, and uniqueness rules can give the optimizer information that helps it reason about the query.
Good data modelling therefore contributes to good optimization.
Candidate plans and search space
A query involving one table may have only a modest number of realistic strategies.
A query joining six tables can have an enormous search space.
Different join orders are possible.
Different access paths are possible.
Each logical join may have several physical algorithms.
Some operations may be parallelized.
The optimizer cannot spend unlimited time trying every theoretical plan.
Optimization itself costs CPU and elapsed time.
SQL Server therefore searches intelligently and tries to find a low-cost plan within a reasonable optimization budget.
This leads to an important correction:
SQL Server does not promise the perfect plan. It chooses the lowest-cost plan among the useful alternatives it considered.A theoretically better plan may exist outside the explored search space.
Cost-based optimization
SQL Server uses a cost-based optimizer.
It estimates the expected cost of candidate plans and compares them.
Cost considers work such as:
CPU.
I/O.
Memory-related operations.
Estimated row counts.
The algorithms involved.
The cost value is an internal comparison measure. Do not read it as exact seconds.
Suppose Plan A performs an index seek and ten lookups.
Plan B scans five million rows.
Plan A may be cheaper.
Now suppose the query is expected to return four million rows.
Four million lookups may be far more expensive than scanning once.
The scan may win.
This is why an index does not guarantee a seek.
The optimizer chooses the complete estimated plan, not the operator that looks nicest in isolation.
Cardinality estimation
Cardinality means the number of rows.
The optimizer constantly asks questions such as:
How many pending orders are likely?
How many rows will this predicate return?
How many matching customer rows will this join produce?
How many groups will this aggregate create?
These estimates influence nearly everything:
Access path.
Join type.
Join order.
Memory grant.
Parallelism.
Sort strategy.
Aggregation strategy.
If the optimizer expects 10 rows but actually receives 10 million, the chosen plan can be badly mismatched to reality.
Cardinality estimation is therefore one of the central themes of SQL Server performance tuning.
Statistics and histograms
SQL Server needs information about the distribution of values to estimate cardinality.
Statistics provide that information.
An index usually has statistics on its key, and SQL Server can also create statistics independently where appropriate.
Statistics summarize the data rather than storing every individual value.
A histogram gives the optimizer information about the distribution of values across ranges.
This matters because data is rarely uniform.
Suppose Status contains:
95 percent Completed.
4 percent Pending.
1 percent Failed.
A query for Failed may return very few rows.
A query for Completed may return almost the entire table.
The same SQL shape can require very different physical strategies depending on the parameter value.
This is where statistics connect directly to plan selection.
Selectivity
Selectivity describes how strongly a predicate reduces rows.
A predicate returning one row from ten million is highly selective.
A predicate returning nine million is not.
Highly selective predicates often make targeted index access attractive.
Low-selectivity predicates may make scans more sensible.
Again, do not treat this as a fixed rule.
Coverage, ordering, row width, joins, and other factors still matter.
But selectivity is one of the optimizer's most important considerations.
Join ordering
SQL is declarative, so the written table order does not necessarily force physical join order.
The optimizer may decide that filtering a small selective table first gives a much smaller intermediate result.
That smaller result may then be joined efficiently to a much larger table.
This is why good predicates and good estimates matter so much.
If the optimizer incorrectly believes a filter will return five rows but it actually returns five million, a join order that looked excellent during optimization may perform badly at runtime.
The optimizer is not being irrational.
It is acting on an incorrect estimate.
That distinction changes how you troubleshoot.
Do not immediately ask:
Why did SQL Server choose this stupid plan?
Ask:
What estimate made this plan look cheap?
That question is far more productive.
Module 16 — Reading Execution Plans and Diagnosing Slow SQL
Execution plans are evidence
When a query is slow, do not begin by adding an index.
Do not begin by rewriting everything.
Do not begin by adding hints.
First, gather evidence.
An execution plan shows the physical operators SQL Server chose.
The graphical plan is a tree of operations.
Common operators include:
- Index Seek
- Index Scan
- Table Scan
- Key Lookup
- Nested Loops
- Merge Join
- Hash Match
- Sort
- Stream Aggregate
- Hash Aggregate
- Filter
- Spool
- Parallelism operators
The goal is to understand the flow of rows and identify where work becomes unexpectedly expensive.
Estimated versus actual plans
An estimated plan shows what SQL Server intends to do without executing the query.
An actual plan includes execution-time information after the query runs.
The actual plan is especially useful because you can compare:
Estimated rows.
Actual rows.
That comparison often reveals the real tuning problem.
Suppose a Nested Loops join was chosen because SQL Server estimated 20 outer rows.
At runtime, 2 million rows arrived.
Nested Loops may now perform an enormous number of inner operations.
The join algorithm is not necessarily the root problem.
The cardinality estimate may be the real cause.
Seek, scan, and lookup
A seek is useful when SQL Server can navigate efficiently to a value or range.
A scan reads a larger portion of a structure.
A Key Lookup retrieves missing columns after a nonclustered index identifies the target rows.
Do not diagnose operators by name alone.
A scan returning 100 rows from a tiny table may be trivial.
A seek followed by 500,000 lookups may be devastating.
Always ask:
How many times did this operator execute?
How many rows did it process?
How many pages did the query read?
What percentage of the complete query work did it represent?
Sorts
Sorts appear for reasons such as:
ORDER BY.
Merge Join requirements.
Window functions.
Stream Aggregate input.
Duplicate removal.
Sorts need memory.
If the operation receives insufficient memory, it can spill to tempdb.
A spill does not make the query incorrect.
It makes the physical execution more expensive.
When you see a large sort, ask:
Could an index provide the required ordering?
Is the sort actually necessary for the business requirement?
Are estimates wrong, causing the memory grant to be too small?
Do not simply remove every sort. Many are logically required.
Hash operations
Hash Join and Hash Aggregate build in-memory hash structures.
They are excellent operators for many workloads.
A Hash Join is not a failure.
A Hash Aggregate is not a failure.
They become concerns when:
The input is unexpectedly huge.
The memory grant is inadequate.
The operation spills heavily.
A better access path could have reduced the input dramatically before the hash operation.
Again, the operator is evidence, not guilt.
Spools
A spool stores an intermediate result so SQL Server can reuse it.
It may be introduced to avoid repeating expensive work or to protect correctness under certain update patterns.
Some spools are very helpful.
Some reveal that SQL Server is compensating for a difficult query or missing access path.
Do not remove a spool simply because you learned that spools can be expensive.
Ask why SQL Server needed it.
STATISTICS IO
SET STATISTICS IO ON is one of the most useful tuning tools for developers.
For example:
SET STATISTICS IO ON;
SELECT ...
FROM ...;
SET STATISTICS IO OFF;The output includes page-read information.
Logical reads tell you how many pages SQL Server accessed from the buffer cache.
This lets you compare query designs objectively.
Query A may finish in 100 milliseconds today because all pages happen to be cached.
Query B may finish in 120 milliseconds.
But if Query A reads 500,000 pages and Query B reads 5,000, Query B may be much healthier under concurrency and colder cache conditions.
Elapsed time alone is not enough.
STATISTICS TIME
SET STATISTICS TIME ON reports CPU and elapsed timing information.
CPU time helps show how much processor work the query consumed.
Elapsed time includes waiting as well as CPU.
A query may have low CPU but high elapsed time because it waited on locks, storage, memory, or another resource.
A good investigation combines:
Execution plan.
Logical reads.
CPU time.
Elapsed time.
Wait information.
Do not optimize one metric blindly.
A practical diagnostic sequence
When a production query is slow, use a disciplined sequence.
First, reproduce the exact query and parameter values if possible.
Second, capture the actual execution plan.
Third, compare estimated rows with actual rows.
Fourth, inspect access paths.
Fifth, look for repeated key lookups, large scans, expensive sorts, hash spills, or spools.
Sixth, inspect STATISTICS IO and STATISTICS TIME.
Seventh, ask whether indexes, statistics, SARGability, query shape, or parameter sensitivity explain the plan.
Only then decide what to change.
This approach is much more reliable than adding an index because the plan displayed a missing-index suggestion.
Module 17 — Statistics, Plan Caching, Query Store, and Intelligent Query Processing
Statistics are the optimizer's description of your data
The optimizer cannot inspect every table row during compilation.
That would defeat the purpose of optimization.
Instead, it relies heavily on statistics.
Statistics summarize the distribution of values.
This helps the optimizer estimate cardinality.
Good statistics do not guarantee a perfect plan.
Bad or stale statistics can make good planning much harder.
Imagine a table that contained 100,000 rows when statistics were collected.
The table now contains 50 million rows and has a very different value distribution.
The optimizer may make decisions based on an outdated description.
Statistics maintenance therefore matters, especially on rapidly changing or skewed data.
Do not update every statistic constantly without reason. Statistics maintenance itself has a cost.
The goal is sufficiently accurate information for useful estimates.
Histograms
A histogram divides the value distribution into steps.
It helps answer questions such as:
How common is this value?
How many rows may fall inside this range?
Are some values far more frequent than others?
Histograms are especially important when data is skewed.
Suppose most customers have fewer than ten orders, but one enterprise customer has five million.
A plan suitable for a typical customer may be terrible for the huge one.
That takes us to parameter sensitivity.
Plan caching
Optimization is not free.
If SQL Server had to fully compile and optimize every repeated query, CPU would be wasted.
SQL Server therefore caches execution plans and can reuse them.
Plan reuse is often a major performance benefit.
The first execution may compile the plan.
Later executions can reuse it.
But one cached plan must sometimes serve very different parameter values.
That can become a problem.
Parameter sniffing
Suppose a stored procedure accepts @CustomerId.
When the procedure is compiled, SQL Server may inspect the parameter value and use statistics to estimate how many rows that value is likely to return.
This is called parameter sniffing.
Parameter sniffing is fundamentally an optimization.
The optimizer wants real parameter information so it can choose a good plan.
The problem appears when the data is highly skewed.
Customer 10 may have three orders.
Customer 999 may have five million.
A plan compiled for Customer 10 may use Nested Loops and lookups.
Reusing that same plan for Customer 999 may be disastrous.
A plan compiled for Customer 999 may use a scan or hash strategy that is wasteful for the tiny customer.
The issue is therefore not “parameter sniffing is bad.”
The issue is:
One reusable plan may not suit every parameter population.
Recompilation and optimization choices
Several techniques can address parameter-sensitive workloads.
A query or procedure can sometimes be recompiled so the optimizer gets fresh parameter information for each execution.
Hints can tell SQL Server to optimize for a particular value or for an unknown/general value.
Code can sometimes be redesigned so separate workload shapes receive separate statements.
But every option has trade-offs.
Recompilation consumes CPU.
A general plan may be mediocre for everyone.
Forcing a value can become wrong as the data changes.
Hints can outlive the problem they were introduced to solve.
Treat these as targeted engineering tools, not default fixes.
Parameter-sensitive plan optimization
Modern SQL Server versions include parameter-sensitive plan optimization capabilities designed to keep multiple plan variants for parameter-sensitive queries.
The optimizer can use histogram boundaries to divide parameter populations into ranges and select an appropriate variant.
The important idea for a junior developer is not the implementation detail.
It is the architectural problem being solved:
Sometimes one query text genuinely needs more than one good plan because different parameter values represent radically different workloads.
Query Store
Query Store records query, plan, and runtime history.
This is extremely valuable because production performance problems are often historical.
A query was fast yesterday.
It is slow today.
Without history, you may only see the current plan.
Query Store helps answer:
Did the execution plan change?
When did runtime increase?
Was the old plan consistently faster?
How frequently does the query execute?
Did a regression begin after a deployment or statistics change?
Query Store turns plan troubleshooting from a snapshot into a timeline.
It can also support plan forcing and some intelligent optimization features.
But forcing a plan should be treated as operational control, not a substitute for understanding the underlying cause.
Intelligent Query Processing
SQL Server has introduced several features that allow runtime information to improve query behaviour.
Examples from the tuning source include:
Memory grant feedback.
Cardinality estimation feedback.
Degree-of-parallelism feedback.
Interleaved execution.
Table-variable deferred compilation.
Adaptive joins.
The names can sound intimidating, but the central idea is simple:
Traditional optimization makes decisions before full runtime reality is known.
Intelligent Query Processing allows SQL Server, in selected situations, to use runtime or historical information to improve future decisions.
Memory grant feedback
Suppose the optimizer grants enough memory for 100 rows, but 10 million arrive.
The query spills badly.
Memory grant feedback can use execution information to adjust future grants.
The opposite problem also matters.
If a query receives vastly more memory than it needs, other concurrent queries may be starved.
Feedback aims to move the grant towards a more appropriate level.
Adaptive joins
Sometimes SQL Server does not know during compilation whether the input will be tiny or large.
An adaptive join can postpone the final choice between certain join strategies until runtime information becomes available.
Again, the principle is more important than memorizing feature internals:
SQL Server is increasingly able to adapt when compile-time estimates are uncertain.
Module 18 — Data Quality, Analytics, and Storytelling
Fast wrong answers are still wrong
Part 3 is performance-heavy, but performance is not the final goal.
A query returning in five milliseconds is useless if the business metric is misleading.
Data quality is part of engineering quality.
Before presenting analysis, ask:
Are values missing?
Are duplicates genuine or accidental?
Are identifiers consistent?
Are dates plausible?
Are categories standardized?
Did joins multiply the grain?
Are extreme values errors or legitimate outliers?
Does the sample represent the population we are discussing?
This is where technical SQL skill meets analytical responsibility.
Profiling data
Data profiling means learning the statistical and structural shape of the dataset.
Useful questions include:
How many rows exist?
How many values are null?
How many distinct values occur?
What are the minimum and maximum values?
What categories dominate?
Are there suspiciously rare values?
Are supposed unique identifiers actually unique?
Do date ranges make sense?
SQL aggregate functions, grouping, percentiles, and window functions are powerful profiling tools.
The goal is to understand the data before telling a story about it.
Missing values
Null is not automatically bad data.
A null DeliveredAt may correctly mean an order has not been delivered.
A null CustomerId on an order may indicate invalid data if every order must belong to a customer.
Data quality therefore depends on business meaning.
Do not measure quality using rules disconnected from the domain.
Duplicates
Two equal-looking rows are not automatically duplicates.
Two customers named John Smith may be different people.
One customer may appear twice due to an integration error.
Duplicate detection often requires several fields and business context.
Fuzzy matching can help identify candidates, but similarity is not identity.
Treat deduplication as evidence-based classification, not a string-comparison trick.
Outliers
Outliers may represent:
Data-entry errors.
Fraud.
Rare but legitimate business events.
System defects.
High-value customers.
Unusual market conditions.
Do not delete an outlier because it makes a chart look untidy.
Investigate what it means.
The median and percentiles are often useful because they describe skewed distributions more robustly than the mean.
For example, median property price may represent the typical transaction better than average price when a few extremely expensive properties exist.
Reconciliation
A trustworthy report should often reconcile to an authoritative total.
If a dashboard reports monthly revenue, can that revenue be traced back to the source transactions?
If a warehouse contains 1,000,000 orders but the operational system contains 1,002,000 for the same period, where did the difference arise?
Reconciliation gives reporting credibility.
A technically impressive dashboard that cannot be tied back to source facts should not be trusted.
Storytelling
Analysis is not complete when the query returns.
You need to communicate:
What question was asked?
What data was used?
What assumptions were made?
What method was used?
What answer was found?
How certain is that answer?
What action, if any, should follow?
Visualizations should clarify the evidence rather than exaggerate it.
Avoid misleading axes, cherry-picked periods, unexplained exclusions, and decorative charts that hide the actual comparison.
Good analytical storytelling makes the methodology visible enough that another professional can challenge or reproduce the conclusion.
Module 19 — Warehousing, Large-Scale SQL, and Production Applications
OLTP and analytics want different things
An operational transaction system is usually designed to support many small, fast, concurrent changes.
Examples include:
Create an order.
Update a payment.
Reserve stock.
Change customer details.
This is commonly called OLTP: Online Transaction Processing.
Analytical workloads ask different questions:
What was revenue by region over five years?
Which product categories are growing?
What is customer retention by cohort?
How does performance compare across hundreds of dimensions?
These queries may scan huge amounts of historical data.
Trying to make one schema perfectly serve both workloads can create conflict.
Normalized transactional models protect write integrity.
Analytical models often reshape data to make large reads and aggregations simpler.
Fact tables and dimensions
A common warehouse design is the star schema.
At the centre is a fact table.
A fact table represents measurable business events at a clearly defined grain.
For example:
One row per sales order line.
Measures might include:
Quantity.
Net amount.
Tax amount.
Cost.
Surrounding the fact table are dimension tables.
Dimensions describe the context of the facts.
Examples include:
Customer.
Product.
Date.
Region.
Salesperson.
The schema looks conceptually like a star because dimensions surround the central fact.
The grain must still be explicit.
If FactSales means one row per order line, do not casually mix monthly summaries into the same table.
Why denormalization appears in warehouses
A transactional system may normalize customer, address, region, and category information into several related tables.
An analytical system may deliberately flatten descriptive information into dimensions.
This reduces join complexity and supports predictable analytical access.
That is deliberate denormalization for a read-heavy workload.
It does not mean normalization was wrong.
Different workloads justify different physical models.
Slowly changing dimensions
Business descriptions change over time.
A customer may move region.
A product may change category.
A salesperson may change team.
Historical reporting must decide whether old facts should reflect:
The dimension as it was when the event occurred.
Or the dimension's current value.
Slowly changing dimension techniques preserve the required history.
The implementation pattern depends on the business requirement, but the underlying question is always:
When descriptive data changes, what historical truth should reports preserve?
Columnstore indexes
Traditional rowstore structures store complete rows together.
That is excellent for point lookups and small transactional operations.
Large analytical queries often need only a few columns across millions of rows.
Columnstore indexes store data by column and use compression-oriented structures suited to analytical scanning and aggregation.
If a query needs SalesAmount and RegionKey across hundreds of millions of fact rows, column-oriented storage can avoid dragging many irrelevant columns through the processing pipeline.
Columnstore also works with batch-mode execution, where SQL Server can process groups of rows more efficiently than traditional row-at-a-time patterns for many analytical operations.
The tuning source treats columnstore and batch mode as central warehouse performance technologies.
Do not interpret this as “columnstore is faster than rowstore.”
Rowstore and columnstore are designed for different workload shapes.
ETL and ELT
Warehouses require data movement.
ETL means Extract, Transform, Load.
Data is extracted from source systems, transformed, and then loaded into the analytical structure.
ELT means Extract, Load, Transform.
Data is loaded first and transformed within the target analytical environment.
Modern cloud systems may use combinations of both.
The important engineering requirements are:
Repeatability.
Incremental processing.
Failure recovery.
Idempotency.
Auditability.
Data-quality checks.
Reconciliation.
A pipeline should know which source period or batch it processed and should be safe to restart after failure.
Production application separation
An ASP.NET Core transactional API should not necessarily run massive historical reports against the same structures used for customer checkout.
Heavy analytical queries can consume CPU, memory, I/O, and concurrency resources required by operational transactions.
Depending on scale, architecture may separate:
Transactional database.
Read replicas.
Reporting database.
Warehouse.
Lakehouse or analytical platform.
Cache.
The right architecture depends on data volume, freshness requirements, cost, and workload.
Do not distribute data simply because distributed systems sound modern.
Separate workloads when their operational requirements genuinely conflict.
Module 20 — Modern SQL and Final Capstone
Modern SQL is broader than rows and B-trees
Relational databases continue to support their traditional strengths:
Structured data.
Transactions.
Constraints.
Set-based querying.
Indexes.
Joins.
Aggregation.
At the same time, modern SQL platforms increasingly support specialist data and processing patterns.
Examples include:
JSON and semi-structured documents.
Spatial data.
Temporal history.
Columnar analytics.
Graph-like relationships.
Vector representations for semantic search.
The important principle is not to replace relational modelling with whatever feature is newest.
Use each feature where its data model and access pattern genuinely fit.
A customer ID, payment amount, order status, and foreign-key relationship are still excellent relational facts.
Flexible metadata may fit JSON.
Geographical boundaries may fit spatial types.
Embeddings may fit vector structures when the requirement is semantic similarity rather than exact relational equality.
Modern SQL architecture is about combining specialised capabilities without abandoning data discipline.
Observability belongs in database engineering
A production database should not be a black box.
You should know:
Which queries consume the most resources?
Which queries recently became slower?
Which plans changed?
Where blocking occurs?
Which statements spill to tempdb?
Which indexes are heavily used?
Which indexes impose write cost but provide little read value?
What is the database's CPU, I/O, memory, and concurrency behaviour?
Execution plans, Query Store, Extended Events, DMVs, application telemetry, and cloud monitoring all contribute to this picture.
Performance tuning is not a one-time cleanup.
It is an operational capability.
Security and least privilege
Database performance is important, but security is part of correctness.
Applications should connect with the minimum permissions required.
Do not give an application administrative rights because it makes development easier.
Separate deployment permissions from runtime permissions where practical.
Use parameterized SQL.
Protect secrets.
Audit sensitive changes.
Restrict direct access to confidential tables.
Do not expose more data than the application actually needs.
A high-performance data breach is still a failed system.
Final Capstone — Diagnosing a Slow Order Search
Let us bring the complete course together.
Imagine an ASP.NET Core application with an Angular front end.
Users search orders by customer, status, and date.
The query has become slow after the system grew from 100,000 orders to 80 million.
A junior response might be:
“Add an index.”
A performance engineer follows a disciplined sequence.
Step 1 — Reconfirm the business question
What exactly does the screen need?
Does it need 50 rows?
Does it need every column?
Does it need total counts?
What filtering is mandatory?
What ordering is required?
Performance starts with correct requirements.
Returning 30 unnecessary columns and millions of rows cannot be fixed solely through indexing.
Step 2 — Check the query shape
Suppose the query contains:
WHERE YEAR(OrderedAt) = @YearThat function may reduce the optimizer's ability to use an ordered index effectively.
Rewrite the date condition as a range.
Confirm that joins preserve the intended grain.
Remove unnecessary joins.
Select only required columns.
Step 3 — Measure the existing behaviour
Capture the actual execution plan.
Enable STATISTICS IO.
Enable STATISTICS TIME.
Record:
Elapsed time.
CPU.
Logical reads.
Estimated rows.
Actual rows.
Wait behaviour.
Do not change anything yet.
Create a baseline.
Step 4 — Inspect estimates
The plan estimates 100 matching orders.
The actual result contains 4 million.
That is a major clue.
Ask:
Are statistics stale?
Is the data highly skewed?
Was the plan compiled for an unusual parameter?
Is the predicate difficult to estimate?
Do not blame the join operator before understanding why the optimizer expected only 100 rows.
Step 5 — Inspect indexes
Perhaps the table has:
A clustered key on OrderId.
A nonclustered index on CustomerId.
The search filters by CustomerId, Status, and OrderedAt, then returns a small set of columns.
Maybe a composite index aligned with the important access pattern could reduce reads dramatically.
For example, a design might involve customer and date as key columns, with selected output columns included.
But do not create it in isolation.
Check existing indexes.
Check write volume.
Check whether the new index overlaps with others.
Test the actual workload.
Step 6 — Re-test
After the change, measure again.
Did logical reads fall?
Did CPU fall?
Did elapsed time improve?
Did the actual plan change?
Did writes become materially more expensive?
A tuning change is not successful because the new plan looks prettier.
It is successful because the workload improved measurably without unacceptable side effects.
Step 7 — Watch plan stability
If the query is highly parameter-sensitive, test several representative customers.
Small customer.
Medium customer.
Huge customer.
A plan that is excellent for one may be poor for another.
Use Query Store to monitor plan history and runtime behaviour.
Consider whether parameter-sensitive optimization or carefully targeted recompilation is more appropriate than forcing one universal plan.
Step 8 — Reconsider architecture at scale
If this screen is actually running a five-year analytical report across tens of millions of orders, perhaps the operational order database is the wrong place for the workload.
A warehouse or reporting structure may be more appropriate.
This is the final maturation of SQL thinking:
Sometimes the solution is not a faster query.
Sometimes the solution is the correct data architecture.
The Performance Engineer's Mental Checklist
When a SQL query is slow, ask these questions in order.
First:
What result does the business actually need?
Second:
What is the grain of the rows being processed?
Third:
Are predicates SARGable?
Fourth:
What indexes exist, and what access paths do they provide?
Fifth:
What execution plan did SQL Server choose?
Sixth:
How many rows were estimated and how many actually arrived?
Seventh:
How many logical reads occurred?
Eighth:
Did sorts or hashes spill?
Ninth:
Are statistics accurate enough?
Tenth:
Could the cached plan have been compiled for an unrepresentative parameter?
Eleventh:
Is the workload transactional or analytical?
Twelfth:
Did the proposed fix improve measured workload behaviour rather than simply changing the plan shape?
This checklist protects you from random tuning.
Questions a Junior Developer Should Be Able to Answer After Part 3
You should be able to explain what a SQL Server page is and why page reads matter.
You should know the difference between a heap and a clustered table.
You should be able to describe the root, intermediate, and leaf levels of a B-tree.
You should understand why a clustered index and primary key are different concepts.
You should know the difference between a seek and a scan without saying that every scan is bad.
You should understand how a nonclustered index locates the base row.
You should be able to explain a Key Lookup.
You should know what makes an index covering.
You should understand the different roles of key columns and included columns.
You should know why composite index order matters.
You should understand selectivity.
You should know why every index has a write cost.
You should be able to explain parsing, binding, optimization, and execution.
You should understand the difference between logical and physical operators.
You should know why SQL Server cannot evaluate every theoretical plan.
You should understand that optimization is cost based.
You should know what cardinality estimation means.
You should understand why statistics and histograms influence plan choices.
You should be able to compare estimated and actual rows in an execution plan.
You should know how STATISTICS IO and STATISTICS TIME help a tuning investigation.
You should understand that Hash Join, Hash Aggregate, and scans are not automatically bad.
You should know what a spill means.
You should understand why plans are cached.
You should be able to explain parameter sniffing without simply calling it a bug.
You should understand why one cached plan may not fit highly skewed parameter values.
You should know the purpose of Query Store.
You should understand the broad idea behind Intelligent Query Processing.
You should know the difference between OLTP and analytical workloads.
You should understand fact tables, dimensions, and star schemas.
You should know why columnstore indexes suit many large analytical workloads.
Finally, you should be able to describe a measured tuning process rather than reaching immediately for an index or query hint.
Suggested Practice Project for Junior Developers
Continue the order-management system from Parts 1 and 2.
Generate enough sample order data that query plans become interesting.
Create a clustered index on the main order identifier and explain why you chose that key.
Create a nonclustered index on CustomerId.
Run a customer-order query that needs additional columns and observe whether SQL Server uses Key Lookups.
Add carefully chosen included columns and compare the new plan and logical reads.
Create a composite index on customer and date.
Compare queries filtering by:
Customer only.
Customer plus date.
Date only.
Explain why the same composite index does not support each search equally well.
Create a filtered index for pending orders and test the exact query pattern it is intended to support.
Measure the write implications rather than assuming the filtered index is free.
Capture an actual execution plan for a join between orders and customers.
Identify the join operator.
Compare estimated rows with actual rows.
Use SET STATISTICS IO ON.
Record logical reads before and after an index change.
Use SET STATISTICS TIME ON.
Compare CPU and elapsed time.
Create a skewed customer distribution where one customer has many more orders than others.
Execute the same stored procedure for both a small customer and a huge customer.
Observe how plan reuse can become parameter-sensitive.
Use Query Store in a suitable SQL Server environment to inspect query and plan history.
Build a simple analytical reporting schema containing a sales fact table and dimensions for date, customer, product, and region.
Explain why the grain of the fact table matters.
Test a large aggregate query against the analytical model.
Finally, write a short tuning report containing:
The business requirement.
The original query.
Baseline elapsed time.
Baseline CPU.
Baseline logical reads.
Important execution-plan observations.
The suspected root cause.
The change made.
The measurements after the change.
Any trade-offs introduced.
This is how a performance engineer communicates.
Not:
“I added an index and it seems faster.”
But:
“The query previously read 420,000 pages because the available access path caused a large scan. The revised index reduced reads to 3,200 pages for the target workload, lowered CPU, and removed repeated lookups. We also measured the additional write cost and confirmed it was acceptable.”
That difference in communication is significant.
Conclusion — From SQL Developer to SQL Engineer
Part 1 taught us to model and retrieve business truth.
Part 2 taught us to transform, analyse, modify, and protect that truth.
Part 3 teaches us to understand the machinery responsible for producing those results efficiently.
The biggest lesson is that SQL Server performance is not mysterious.
A query becomes a plan.
The plan contains physical operators.
Those operators process estimated numbers of rows.
The estimates come from information such as statistics.
Indexes provide alternative access paths.
Memory grants support operators that need working memory.
Plan caching avoids repeated compilation but can create parameter-sensitive behaviour.
Query Store records history.
Warehouse designs reshape data when analytical workloads no longer fit the transactional model comfortably.
Once you understand these relationships, tuning becomes less about tricks and more about diagnosis.
The professional question changes from:
“How can I make this query faster?”to:
“What work is SQL Server doing, why did it choose that work, and what is the smallest justified change that will reduce unnecessary work while preserving correctness?”That is the mindset of SQL performance engineering.
Sources and Scope
This guide is a synthesis of the advanced performance and analytical material used for Part 3 of the mentoring course.
The principal sources are:
- Benjamin Nevarez, *SQL Server Query Tuning and Optimization* — query processor architecture, execution plans, indexes, query optimizer internals, statistics, cost estimation, plan caching, parameter sniffing, Query Store, Intelligent Query Processing, data warehousing, columnstore indexes, and query hints.
- *SQL for Data Analytics*, 4th Edition — B-tree indexing concepts, query planning, analytics, statistical thinking, data quality, and star-schema analytical design.
- Josephine Bush, *Learn SQL Database Programming* — SQL best practices, indexing guidance, query optimization principles, and data storytelling.