Topic lessonTopic: SQL Server performance and concurrency
SQL Server indexes and execution plans: seeks, scans and runtime evidence
An index provides a data-access structure; an execution plan describes how SQL is processed. Use one document query to separate its result, access method, estimates and runtime observations. A seek is not automatically fast, and a scan is not automatically a failure.
Explain index benefits and maintenance costs, then distinguish query results, plans, estimates and runtime evidence.
SELECT chooses columns, WHERE selects rows and ORDER BY specifies output order. The five rows below independently copy the teaching fixture; no plan or elapsed time has been measured.
Separate the required rows from the access method
- SQL and context → Processing planSupplies meaning and planning context
- Processing plan → Runtime evidenceExecution supplies runtime evidence
- SQL and context
- Fix database, table, predicate and needed columns.
- Processing plan
- Choose methods using schema, indexes and statistics.
- Runtime evidence
- Inspect the executed target and runtime row counts.
Equivalent results can be produced through different access methods. The optimizer considers the query, schema, available indexes and statistics. A scan can be reasonable for a small table. An operator name alone is not a performance verdict.
Read owner 1 documents from five rows
Use independent CorpDocs-I.app.Documents, with unique document_id, nullable owner_id and no other changes or row-level filtering. For comparison, stipulate a clustered rowstore keyed by document_id and a nonclustered rowstore keyed by owner_id,document_id. No index is actually created.
| Document ID | Title | Owner ID |
|---|---|---|
| 101 | Handbook | 1 |
| 102 | Diagram | 1 |
| 103 | Budget | 2 |
| 104 | Archive | 3 |
| 105 | Unassigned | NULL |
SELECT document_id
FROM app.Documents
WHERE owner_id = 1
ORDER BY document_id;Under these conditions, 101 and 102 qualify and ORDER BY returns them in that order. Those two rows alone do not establish a seek, page-read count or fast execution. The fixture supports result reasoning; the actual access method needs separate evidence.
| Perspective | What to consider here |
|---|---|
| Table or index scan | Read an input range and evaluate owner_id |
| Index seek | Navigate to a qualifying owner_id range; not evidence this plan was chosen |
| Also request title | The stipulated index keys lack title; consider another retrieval step or a different design |
Potential read benefits carry storage and write costs
| Type | Structure | Cost to assess |
|---|---|---|
| Clustered | Leaf level contains row data and determines the table data structure | Key selection, updates and effects on other indexes |
| Nonclustered | Separate structure containing keys and row locators | Storage and INSERT/UPDATE/DELETE maintenance |
| Covering a query | Supplies the columns required by a particular query | Not a universal index type covering every query |
Read estimated and actual plans within their evidence limits
| Kind | Evidence supplied | Still to check |
|---|---|---|
| Estimated plan | Compile-time plan and estimates without executing that query | Actual rows and waits for that execution |
| Actual plan | The used plan with runtime information | That target, parameters and time match the investigation |
| Displayed plan cost | Optimizer-estimated comparative costs | Do not read cost percentages as measured elapsed-time percentages |
A large estimate-versus-actual row discrepancy is a lead for investigating data distribution, statistics and parameters. It is not a unique diagnosis; inspect execution counts and operator properties. Actual plans still contain estimates.
Compare equivalent queries and conditions
- Fix database, SQL, parameters, time and the required business result.
- Identify plan kind; inspect operators, row counts and warnings alongside actual wait, CPU and I/O observations.
- Assess proposed indexes for storage and write effects as well as reads.
- Where available, inspect Query Store plan/runtime history; check capture, retention and aggregation intervals rather than treating aggregates as one execution.
- Compare result correctness and load under equivalent conditions and assess other important queries.
Plans and SQL can contain object names, literals and business information. Share investigation material under the applicable data-handling policy; this site provides no input for real data or plan files.
Separate result, plan and observation
Choose an option to see the result and explanation.
Check, then explain in your own words
- Derive the 101,102 result from the fixture.
- Name performance findings that require actual evidence.
- Explain both read benefits and maintenance costs.
- Distinguish estimated plans, actual plans and Query Store history.
Check your understanding: An Index Seek appears. Does that establish good performance?
No. Inspect volume, extra retrieval, rows, load and correctness.
A seek names one access method. Assess the whole query and its required result under equivalent observed conditions.
Sources and conditions checked
Primary references are collected here with their scope, conditions and corresponding sections. Independent expert review and live-environment validation have not been performed.
- Microsoft: Execution plansChecked:
Scope and conditionsOptimizer/plans, estimated versus actual evidence and small-table scans.
Used in this lessonSeparate the required rows from the access method Read estimated and actual plans within their evidence limits Compare equivalent queries and conditions Separate result, plan and observation
Scope and conditionsRowstore structures, coverage, column choices and maintenance costs; no measured fixture plan.
Used in this lessonRead owner 1 documents from five rows Potential read benefits carry storage and write costs Separate result, plan and observation
Scope and conditionsActual plans execute SQL and include runtime information; no SQL executed here.
Used in this lessonRead estimated and actual plans within their evidence limits Separate result, plan and observation
Scope and conditionsNon-executing plans and absence of runtime information.
Used in this lessonRead estimated and actual plans within their evidence limits
Scope and conditionsQuery/plan/statistics history with capture, retention and aggregation conditions.
Used in this lessonCompare equivalent queries and conditions