INFRASTRUCTURE LEARNINGINFRASTRUCTURE LEARNING
インフラ大全インフラ大全IT INFRASTRUCTURE / LEARNING
Infrastructure learning guide
Find a lesson or lab/

Esc to close · Search terms stay on your device

LEARNING日本語

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.

What you will learn

Explain index benefits and maintenance costs, then distinguish query results, plans, estimates and runtime evidence.

Prerequisite

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

From query meaning to planning and runtime evidence
From query meaning to planning and runtime evidence1: SQL and context — Supplies meaning and planning context — Processing plan. 2: Processing plan — Execution supplies runtime evidence — Runtime evidence12SQL and contextProcessing planRuntime evidenceFrom query meaning to planning and runtime evidence1: SQL and context — Supplies meaning and planning context — Processing plan. 2: Processing plan — Execution supplies runtime evidence — Runtime evidence12SQL and contextProcessing planRuntimeevidence
  1. SQL and context → Processing planSupplies meaning and planning context
  2. 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.

Stipulated documents and owners
Document IDTitleOwner ID
101Handbook1
102Diagram1
103Budget2
104Archive3
105UnassignedNULL
SELECT for reasoning only; not executed here
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.

Possible access-method distinctions
PerspectiveWhat to consider here
Table or index scanRead an input range and evaluate owner_id
Index seekNavigate to a qualifying owner_id range; not evidence this plan was chosen
Also request titleThe stipulated index keys lack title; consider another retrieval step or a different design

Potential read benefits carry storage and write costs

Rowstore roles within this lesson
TypeStructureCost to assess
ClusteredLeaf level contains row data and determines the table data structureKey selection, updates and effects on other indexes
NonclusteredSeparate structure containing keys and row locatorsStorage and INSERT/UPDATE/DELETE maintenance
Covering a querySupplies the columns required by a particular queryNot a universal index type covering every query

Read estimated and actual plans within their evidence limits

Plan kinds and their evidence
KindEvidence suppliedStill to check
Estimated planCompile-time plan and estimates without executing that queryActual rows and waits for that execution
Actual planThe used plan with runtime informationThat target, parameters and time match the investigation
Displayed plan costOptimizer-estimated comparative costsDo 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

  1. Fix database, SQL, parameters, time and the required business result.
  2. Identify plan kind; inspect operators, row counts and warnings alongside actual wait, CPU and I/O observations.
  3. Assess proposed indexes for storage and write effects as well as reads.
  4. Where available, inspect Query Store plan/runtime history; check capture, retention and aggregation intervals rather than treating aggregates as one execution.
  5. 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.

The stipulated SELECT returns 101 and 102. What follows?
Read the answer and reason

Answer: Those rows satisfy owner_id=1; the access method needs separate evidence. · The five-row fixture supports the result, not an observed plan or runtime.

A small table uses a scan. What next?
Read the answer and reason

Answer: Evaluate data volume, runtime evidence and workload needs. · An operator name alone is not a performance verdict.

Which statement about obtaining an UPDATE actual plan is correct?
Read the answer and reason

Answer: It executes the statement, so assess its data effects. · Runtime information comes from execution; this is not merely viewing a picture.

You also need title from the stipulated (owner_id,document_id) index. What next?
Read the answer and reason

Answer: Check required columns, extra retrieval/design and maintenance costs. · Coverage is query-specific; assess maintenance as well as retrieval.

Check, then explain in your own words

  1. Derive the 101,102 result from the fixture.
  2. Name performance findings that require actual evidence.
  3. Explain both read benefits and maintenance costs.
  4. 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.