This module lays the theoretical foundations for optimizing SQL Server queries. It answers the fundamental question: when should you start optimizing your T-SQL queries?

2.1 What is sufficient performance?

The notion of “good performance” is relative and depends entirely on the context of use. A 30-second wait is unacceptable when purchasing concert tickets, but perfectly acceptable when waiting for a bus. Likewise :

A query that runs in 15ms may seem great, but if it’s called thousands of times per minute, reducing it to 10ms is a significant gain for the system.
A query that runs in 50ms and is only called a few times per hour is probably not worth investment in optimization.

Key principle: You must identify the queries that have the most impact on the system and concentrate optimization efforts on them.

Validation process: Without validating that the changes made have actually improved performance, it is impossible to measure the impact on the system. Important validation points are:

Measure the performance of applications or reports that depend on database queries
Check cache plan query
Run queries on a non-production system

Important note: Query execution time is not the only metric to consider. CPU and disk I/O bottlenecks should also be monitored.

2.2 The most common performance bottlenecks

The main performance issues in SQL Server are:

Inadequacy of indexing strategy – Missing or poorly designed indexes
Missing or inaccurate statistics – The Query Optimizer does not have the correct information
Sub-optimal T-SQL code – Poorly written queries
Sub-optimal execution plans – Unsuitable execution plans generated by the optimizer
Suboptimal reuse of execution plans – Plans are not reused efficiently
Frequent query recompilation – Engine regenerates execution plans too often

2.3 The SQL Server Query Optimizer

The SQL Server Query Optimizer is the brains behind SQL Server query performance. Understanding its nuances helps to understand why a particular query might be a bottleneck.

Analogy with Google Maps: Just as Google Maps evaluates several possible routes to go from Salzburg to Paris taking into account the distance, traffic jams, roadworks and type of road, the Query Optimizer evaluates different ways of obtaining a result and calculates their respective cost.

Query Optimizer main tasks:

Generate candidate execution plans – Propose a series of physical operations to produce the result requested by the query
Evaluate the cost of each plan – Calculate the cost (CPU, memory, I/O combination) for each candidate plan
Select optimal plan – Choose the plan with the lowest estimated cost and pass it to the Execution Engine

Query Optimizer constraints:

It operates within a limited time frame: just as you cannot wait hours for Google Maps to generate a route, SQL Server cannot spend hours evaluating all possible plans
It needs reliable data information (accurate metadata, including statistics) to make the best decisions
The “optimal” plan is not always the most optimal in absolute value, because the evaluation is done within time constraints

2.4 The steps of the query optimization process

SQL is a declarative language: we specify what data we want, but not the steps necessary to obtain it. This is the role of the Query Processor Engine.

Order of evaluation of a T-SQL statement (different from the order of writing):

Order of execution	Clause	Role
1	`FROM`	Identify source tables
2	`WHERE`	Filter master data
3	`GROUP BY`	Group filtered data
4	`HAVING`	Filter grouped data
5	`SELECT`	Return final result
6	`ORDER BY`	Sort data in ascending/descending order
7	`TOP`	Limit results to top records

The two components of the Query Processor Engine:

Query Optimizer – Responsible for creating the query plan
Execution Engine – Responsible for executing the plan and returning the result

Steps for processing a request:

Parsing – T-SQL syntax validation. If you write SLEECT instead of SELECT, processing stops immediately.
Binding – Identification of all necessary objects (tables, columns, indexes). SQL Server verifies that these objects actually exist in the database. Data types and aggregation types are identified.
Optimization – Generation of candidate execution plans and evaluation of their cost. The plan with the lowest cost is passed to the Execution Engine.
Execution – Execution of the plan. After execution, the query plan can be cached in memory for subsequent executions.

Mapping logical operations to physical operations:

The logical operation SORT has the same name in its physical implementation
The logical operation JOIN can be physically implemented in three different ways: Nested Loop Join, Merge Join, or Hash Join

2.5 Demo: Capture query performance metrics

This demo shows several ways to capture performance metrics in SQL Server Management Studio (SSMS), using the Contoso database.

Demo query used:

SET STATISTICS IO, TIME ON;

SELECT cu.FirstName
    , cu.LastName
    , geo.RegionCountryName AS country
    , geo.CityName AS city
    , SUM(fso.SalesAmount) AS salesAmount
FROM dbo.FactOnlineSales AS fso
    INNER JOIN dbo.DimCustomer AS cu ON cu.CustomerKey = fso.CustomerKey
    INNER JOIN dbo.DimGeography AS geo ON geo.GeographyKey = cu.GeographyKey
WHERE cu.Education = 'Bachelors'
GROUP BY cu.CustomerKey
    , cu.FirstName
    , cu.LastName
    , geo.RegionCountryName 
    , geo.CityName
HAVING SUM(fso.SalesAmount) > 500
ORDER BY salesAmount DESC

Methods for capturing performance metrics:

Method 1: SET STATISTICS TIME AND IO ON

By uncommenting SET STATISTICS IO, TIME ON and executing the query, the Messages tab displays:

Total CPU time and elapsed time
The number of reads for each table involved in the execution

Advantages: Simple and straightforward. Disadvantages: Only provides a measurement for a specific execution. In reality, average metrics would be more representative.

Method 2: QueryTimeStats in the Execution Plan

After enabling the actual execution plan and re-executing the query:

Right click on the leftmost operator in the plan
Open Properties panel
Expand QueryTimeStats property

Advantages: Simple to perform. Disadvantages: Same limitation as SET STATISTICS – single run information.

Method 3: Dynamic Management Views (DMV)

To measure on a large scale, DMV (Dynamic Management Views) allow metrics to be captured in a scalable manner. There are two categories:

Queries currently running:

-- Requêtes en cours d'exécution (fichier : DMV Query.sql)
SELECT txt.[text]
    , qp.query_plan
    , req.cpu_time
    , req.logical_reads
    , req.writes
FROM sys.dm_exec_requests AS req
CROSS APPLY sys.dm_exec_query_plan (req.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(req.plan_handle) AS txt

This view returns not only the query definition and the number of logical reads/writes, but also the actual execution plan in XML format.

Previously executed queries:

-- Requêtes précédemment exécutées
SELECT txt.[text]
    , qp.query_plan
    , st.execution_count
    , st.min_logical_writes
    , st.max_logical_reads
    , st.total_logical_reads
    , st.total_elapsed_time
    , st.last_elapsed_time
FROM sys.dm_exec_query_stats AS st
CROSS APPLY sys.dm_exec_query_plan (st.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(st.sql_handle) AS txt

Method 4: Query Store

The Query Store is a SQL Server feature introduced in SQL Server 2016 that stores various data about queries executed against a specific database.

Enable: Default disabled. Can be activated via:

Database properties in SSMS
T-SQL

Activation mode via SSMS: Database properties → Query Store → Operation Mode → Read-Write

Reports available:

Top Resource Consuming Queries – Quickly displays the queries that consume the most resources
Overall Resource Consumption – Summarizes metrics into four categories: duration, execution count, CPU time, logical reads

3. Understanding Query Plans

Module duration: 11m 53s

An execution plan is the “blood test” of your request, and you are the doctor who must know how to read and interpret it.

3.1 Estimated vs Actual Execution Plan

It is important to understand that there are not really two different execution plans. This is the same plan, but the actual execution plan provides additional information about the actual execution of the query.

Characteristic	Estimated Execution Plan	Current Execution Plan
Availability	Before execution	After execution
Content	Execution plan alone	Execution plan + runtime metrics
Query Duration	No	Yes
Wait Statistics	No	Yes
Actual number of rows	No	Yes
Actual number of executions	No	Yes

Only case where they can differ: When the query recompiles.

Additional information provided by the current execution plan:

Query duration
Wait statistics
Actual number of rows (vs estimated number of rows)
Actual number of executions

3.2 Read and interpret a Query Plan

The components of an execution plan:

Operators (graphic icons)

Each operator represents a process that does something on the data to satisfy the query. Each operator has:

A name and the name of its physical action (ex: inner join)
An estimated cost: combination of resources (CPU, memory, I/O) necessary for this specific operator
Additional information in the current plan (execution time, number of lines)

Arrows

Arrows represent data flow between operators. The thicker the arrow, the greater the volume of data circulating between operators. A thick arrow is often a red flag.

Tooltips

When hovering over an operator, a tooltip displays detailed information. Right-click + Properties takes you to the full properties window.

Reading direction

Logical data flow starts with the top right operator and ends with the leftmost operator (usually SELECT)
To read the plan, start from left to right

Join Operators

Join operators always have two inputs (since a JOIN operation requires two inputs) and generate a single output.

Key indicators to monitor in an execution plan:

Indicator	Meaning
First operator (left)	The leftmost operator’s Properties window provides a good starting point for understanding the overall plan
Most expensive operators	Identify operators with the highest estimated cost
Warnings	Help identify potential problems immediately
Unknown operators	Any unusual operator deserves careful investigation
Thick arrows	Report movement of large amounts of data
Scan operations	Scans generally create pressure on the I/O; not always bad, but worth watching
Estimated vs Actual number of rows	A large gap between the two can affect query performance

3.3 Demo: Analyze and compare Query Plans with SSMS

This demo shows three advanced techniques in SSMS.

Technique 1: Read and interpret a basic Query Plan

Enable actual execution plan in SSMS
Run query
The logical data flow starts with the Clustered Index Scan (top right)
Data progresses to the left until it reaches the SELECT operator

Technique 2: Compare two Query Plans

Right click on the current execution plan → Save execution plan as → Exec plan 1
Modify the query (eg: add a country filter = Australia, modify the HAVING value from 1000 to 100)
Execute the new query → Save as Exec plan 2
Right click on the current plan → Compare Showplan → Load Exec plan 1

Three comparison windows:

Central window – Visual representation of similarities and differences
Properties window (right) – Vertical preview of the two plans with the differences
Showplan Analysis window (bottom) – Configuration options for comparing plans

Technique 3: Live Query Statistics

Allows you to see in real time what is happening during the execution of a complex or long query.

Arrow type identification:

Solid arrows – Operations already completed
Dotted Arrows – Allows monitoring of data flow in real time

Particularly useful for long queries (e.g. queries on 100 million rows).

4. Understanding the Reuse Plan and the Cache Plan

Module duration: 11m 33s

4.1 Operation of the Caching Plane

Query optimization is a resource-intensive process. SQL Server therefore stores execution plans in memory in an area called plan cache, so that they can be found and reused quickly.

Caching process:

Soumission de la requête
        ↓
SQL Server recherche un plan correspondant dans le plan cache
        ↓
    ┌── Plan trouvé ──→ Réutilisation du plan (gain CPU et mémoire)
    └── Plan non trouvé → Processus d'optimisation → Création d'un nouveau plan → Stockage dans le cache

Key factor affecting plan reusability: Data filtering. You should always retrieve only the data you need, only when you need it.

4.2 Ad Hoc Workloads vs Prepared Workloads

Ad Hoc Workloads

Ad hoc workloads run queries without parameters – whether hardcoded values or local variables.

Cache plan behavior: If the values in the WHERE or HAVING clauses change, SQL Server generates a new execution plan because the query is no longer exactly the same. Hardcoded values become part of the plan and are stored with it.

Reuse condition: To reuse the same plan, the query must match exactly, including spaces and all special characters.

Ad hoc workloads problem: If your system relies on a lot of ad hoc queries, the query cache can become cluttered with many execution plans that will likely never be reused.

Solution: Optimize for Ad Hoc Workload – Parameter activatable at the server level which can be useful in these situations (detailed in module 6).

Prepared Workloads

Prepared workloads rely on parameters as a fundamental component. The idea is to use parameters to modify the results without changing the SQL code itself.

Two main methods:

Stored Procedures – Used to encapsulate one or more instructions accepting user-defined parameters
sp_executesql – Executes a T-SQL statement that can include parameters, without being defined as a stored procedure

4.3 Clearing the Cache Plane

Two commands to clear the cache:

-- Supprime TOUS les execution plans de TOUTES les bases de données du serveur
-- ATTENTION : impact sévère sur la performance en production !
DBCC FREEPROCCACHE;

-- Portée plus étroite : supprime tous les plans de la base de données courante seulement
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

Warning: These commands should be used with extreme caution in a production environment. DBCC FREEPROCCACHE can have a severe negative impact on performance.

4.4 Recommended Practices for Cache Plane

Practical	Rationale
Use explicit parameters in queries	Increases the level of reusability of plans
Use stored procedures whenever possible	Reused from cache, usually better than ad hoc queries
Use `sp_executesql` as an alternative to stored procedures	Prepared workload, benefits from plan reuse
Avoid or reduce ad hoc queries	Double overhead: additional compilation time + cache plan footprint
Enable “Optimize for Ad Hoc Workloads”	Keeps ad hoc plans out of cache as much as possible (only stored after 2nd run)

4.5 Demo: Query Plane Cache for Ad Hoc and Prepared Workloads

Query Cache Plane

-- Vue de base du plan cache (fichier : DMV Query Cache.sql)
SELECT cp.refcounts
    , cp.usecounts
    , cp.size_in_bytes
    , cp.cacheobjtype
    , cp.objtype
    , cp.plan_handle
FROM sys.dm_exec_cached_plans AS cp

Important columns:

Column	Description
`refcounts`	Number of objects in the cache that reference a certain plan
`usecounts`	Number of times a specific plan was searched for (not necessarily used)
`size_in_bytes`	Plan size in memory
`cacheobjtype`	Plan type available
`objtype`	Exact type of query plan object: `Adhoc`, `Prepare`, `Proc`, `View`
`plan_handle`	Useful for joining other DMVs and retrieving the actual SQL statement

Behavior for Ad Hoc Workloads

-- Rechercher des plans ad hoc spécifiques dans le cache (fichier : DMV Query Cache Ad Hoc.sql)
SELECT cp.usecounts
    , cp.cacheobjtype
    , cp.objtype
    , txt.text
FROM sys.dm_exec_cached_plans AS cp
    CROSS APPLY sys.dm_exec_sql_text (cp.plan_handle) AS txt
WHERE txt.text like '%' + 'geo.RegionCountryName = ''Australia''' + '%'

Observation: For the same query returning the same results, SQL Server generates different plans because of differences in comments or indentation. The usecounts value increases when the plan is reused. Changing a filter value (eg: Bachelors → High School) generates a new plan.

Behavior for Prepared Workloads with sp_executesql

-- Utilisation de sp_executesql (fichier : sp_executesql command.sql)
DECLARE @q NVARCHAR(MAX)
    , @params NVARCHAR(MAX)

SET @q = N' SELECT cu.FirstName
    , cu.LastName
    , geo.RegionCountryName AS country
    , geo.CityName AS city
    , SUM(fso.SalesAmount) AS salesAmount
FROM dbo.FactOnlineSales AS fso
    INNER JOIN dbo.DimCustomer AS cu ON cu.CustomerKey = fso.CustomerKey
    INNER JOIN dbo.DimGeography AS geo ON geo.GeographyKey = cu.GeographyKey
WHERE cu.Education = @Education
    AND geo.RegionCountryName = @country
GROUP BY cu.CustomerKey
    , cu.FirstName
    , cu.LastName
    , geo.RegionCountryName 
    , geo.CityName
HAVING SUM(fso.SalesAmount) > 100
ORDER BY salesAmount DESC'

SET @params = N' @education NVARCHAR(64), @country NVARCHAR(64)'

EXEC sp_executesql @q
                , @params
                , @Education = 'Bachelors'
                , @country = 'France'

Cache check for prepared workloads:

-- Rechercher des plans préparés dans le cache (fichier : DMV Query Cache Prepared.sql)
SELECT cp.usecounts
    , cp.cacheobjtype
    , cp.objtype
    , txt.text
FROM sys.dm_exec_cached_plans AS cp
    CROSS APPLY sys.dm_exec_sql_text (cp.plan_handle) AS txt
WHERE txt.text like '%' + '@country' + '%'

Key observation: Unlike ad hoc queries, changing the value of the @country parameter from ‘France’ to another value does not generate a new plan. SQL Server reuses the existing plan.

Warning: Any change in the SQL statement definition (even trivial ones, such as capitalizing the first letter of a parameter name) will force SQL Server to create a new plan.

5. Understanding Indexes and Statistics

Module duration: 22m 13s

5.1 Introduction to Indexes in SQL Server

Indexes help navigate to relevant data as efficiently as possible, just as a book index guides you to a specific topic without having to go through the entire content.

The best indexing strategy (general principle):

Create as few indexes as possible to be referenced by as many queries as possible.

In theory simple, in practice often difficult to balance because there is no universal approach. A good indexing strategy can make a significant difference to SQL Server performance.

Index classification in SQL Server:

Depending on where the index is applied: Rowstore or Columnstore
Depending on the organization of the data: Clustered or Non-Clustered

5.2 Clustered vs Non-Clustered Index

Table without index: Heap

A table without a clustered index is called a heap. It is simply an unordered data set with a row identifier as a pointer to the storage location. To recover data from a heap, the only option is to scan the entire table row by row – often very inefficient.

Clustered Index

Characteristic	Description
Number per table	One and only one clustered index per table
Data organization	Data is reordered and sorted by key column (usually primary key)
Storage	Physical data is stored in the index itself
Additional space	Does not require additional space (sorts existing data)
Performance	Generally faster than non-clustered index

Non-Clustered Index

Characteristic	Description
Number per table	Multiple non-clustered indexes can exist on the same table
Data organization	Stored in a different location than the physical table data
Storage	Consumes additional space
Performance	Requires an additional lookup step to find the row address and retrieve the other column values

5.3 Rowstore vs Columnstore Index

Rowstore Index

Stores data in rows – each row is a physical storage unit. Mainly suitable for OLTP (Online Transaction Processing) workloads.

Columnstore Index

Stores data in columns – each column is an individual physical structure. Particularly suitable for:

Analytical workloads (aggregations, groupings)
Large tables (fact tables in dimensional modeling, or any table with more than 100,000 rows)

Two main advantages of columnstore indexes:

Only columns required by the query are scanned (unlike rowstore indexes)
More optimal data compression by default

Index combination:

You can combine columnstore and rowstore indexes on the same table
It is not possible to have both a clustered columnstore and a clustered rowstore index (data can only be sorted one way)
Since SQL Server 2016: creation of non-clustered columnstore indexes on rowstore tables, and non-clustered rowstore indexes on columnstore tables

5.4 The price of Indexes

Having indexes on your data has a cost:

Cost	Description
Disk space	Indexes take up storage space
Processing time for DML	INSERT, UPDATE, DELETE statements require more time because the changes must propagate through the index structure
Reorganization of data	The data in the index being ordered, the insertion is not done anywhere; data needs to be reorganized

5.5 Recommended Practices for Index Design

1. Workload type

Workload	Recommended index
OLTP (transactional)	Rowstore index, in particular the clustered rowstore index for data storage
OLAP / Analytics	Clustered columnstore index

2. Understanding filter conditions

The Query Optimizer first identifies columns used in WHERE and/or HAVING clauses, as well as join columns, to check if there are indexes on these columns. However, having an index on these columns does not guarantee that SQL Server will use it – it also depends on the selectivity of the data.

3. Column data type

Indexes are consuming space. A narrow data type like INT is much different from a variable VARCHAR type. The data type also affects arithmetic manipulations (generally more efficient on numeric types).

4. Data Selectivity

Selectivity represents the number of lines defined by the predicate. The rule is to create indexes on columns with high selectivity (columns with many unique values).

Example:

Gender column: very low selectivity (only 2-3 distinct values) → bad candidate for a single index
BirthDate column: high selectivity (very few customers born on the same day) → good candidate for an index

5. The order of columns in the index

In a non-clustered index containing multiple columns, data is sorted on the first column first, then additionally on each subsequent column. The order of the columns therefore has a significant impact on the effectiveness of the execution plan.

5.6 Demo: Query performance with Indexes

This demonstration uses the DimCustomer table from the AdventureWorksDW2020 database.

Example 1: Impact of filter conditions on index usage

-- SET STATISTICS IO, TIME ON activé pour toutes les requêtes

-- Sans filtre : 18 500 lignes, 984 logical reads, 465 ms → Table Scan
SELECT [CustomerKey], [FirstName], [LastName], ...
FROM [AdventureWorksDW2020].[dbo].[DimCustomer]

-- Avec filtre sur la clé primaire : 3 logical reads, exécution quasi instantanée → Clustered Index
SELECT [CustomerKey], [FirstName], [LastName], ...
FROM [AdventureWorksDW2020].[dbo].[DimCustomer]
WHERE CustomerKey = 11001

Example 2: Data selectivity and use of indexes

-- Évaluer la sélectivité et la densité d'une colonne (fichier : M05_Demo 1_Indexes.sql)
SELECT COUNT(DISTINCT c.Gender) AS DistinctValues
    , COUNT(c.Gender) AS TotalRows
    , CAST(COUNT(DISTINCT c.Gender) AS DECIMAL) / CAST(COUNT(c.Gender) AS DECIMAL) AS ColSelectivity
    , (1.0 / COUNT(DISTINCT c.Gender)) AS ColDensity
FROM dbo.DimCustomer AS c

Result: ~9,000 women on ~18,500 lines → very low selectivity for Gender.

-- Requête avec filtre sur Gender et BirthDate (sans index optimal)
SELECT [CustomerKey], [BirthDate], [Gender]
FROM [AdventureWorksDW2020].[dbo].[DimCustomer]
WHERE Gender = 'F'
    AND BirthDate = '1973-11-06'

-- Création d'un index sur Gender seulement → SQL Server l'IGNORE (table scan quand même)
-- Raison : Query Optimizer juge moins coûteux de scanner la table entière

-- Création d'un index incluant BirthDate → UTILISÉ ! Index Seek, seulement 2 logical reads
CREATE INDEX ix_Cust_gender ON dbo.DimCustomer (BirthDate, Gender) WITH DROP_EXISTING;

Example 3: The order of columns in the index

USE AdventureWorks2019;

-- Créer un index sur City et PostalCode (dans cet ordre)
CREATE INDEX ix_Address_city ON Person.Address (City, PostalCode);

-- Filtre sur City → fonctionne parfaitement : 2 logical reads, Index Seek
SELECT AddressID, City, PostalCode
FROM Person.Address
WHERE City = 'Seattle'

-- Filtre sur PostalCode seulement → 108 logical reads, Index Scan au lieu de Seek !
-- Raison : PostalCode est la 2e colonne de l'index
SELECT AddressID, City, PostalCode
FROM Person.Address
WHERE PostalCode = '98104'

Conclusion: The order of the columns in the index definition has an absolute impact on the efficiency of the execution plan.

5.7 Understanding Statistics

Statistics stores and maintains information about the distribution of data across table columns, allowing SQL Server to decide which path to take when creating an execution plan.

Main role: Estimate row counts (number of rows), which helps the Query Optimizer make better decisions.

Fundamental rule about columns requiring statistics:

Columns in SELECT clause do not need statistics
Columns in WHERE clause or joins must have statistics

Auto-create: SQL Server automatically creates statistics for:

Columns used for filtering and joins
Columns used to create an index

Statistics on indexed columns

Each rowstore index has automatically created statistics (non-modifiable behavior)
SQL Server automatically updates statistics for indexed columns unless this option is disabled (deprecated)

Statistics on unindexed columns

These columns frequently appear as predicates in WHERE or in joins. SQL Server also automatically creates statistics for these columns.

When to disable this option: Only if you run many ad hoc queries that you are certain will never run more than once.

5.8 Demo: Query performance with Statistics

Example 1: The impact of up-to-date statistics on indexed columns

-- Création de la table de démo (fichier : M05_Demo 2_Statistics.sql)
CREATE TABLE dbo.psdemo_stats (
    col1 INT,
    col2 INT IDENTITY
);

-- Insertion de 2 000 lignes
SELECT TOP 2000 IDENTITY(INT, 1, 1) AS num
INTO #numbers
FROM master.dbo.syscolumns AS c1, master.dbo.syscolumns AS c2;

INSERT INTO dbo.psdemo_stats (col1)
SELECT 2 FROM #numbers;

DROP TABLE #numbers;

-- Création d'un non-clustered index
CREATE NONCLUSTERED INDEX ix_psdemo_stats ON dbo.psdemo_stats (col1);

-- Requête initiale : retourne 1 ligne → Index Seek utilisé
SELECT col1, col2 FROM dbo.psdemo_stats WHERE col1 = 2

Scenario with statistics disabled:

-- Désactiver AUTO_UPDATE_STATISTICS
ALTER DATABASE DataMozart SET AUTO_UPDATE_STATISTICS OFF;

-- Après l'ajout de 2 000 lignes supplémentaires (maintenant 2 001 lignes avec col1=2),
-- SQL Server estime encore 2 lignes au lieu de 2 001 !
-- Écart de 100 000% entre l'estimated et l'actual number of rows

Behavior: With outdated statistics, the Query Optimizer generates incorrect row count estimates, leading to suboptimal execution plans.

Example 2: Statistics on unindexed columns

-- Création de deux tables avec distributions de données différentes

-- Table 1 : 1 ligne avec col2=1, 10 000 lignes avec col2=2
CREATE TABLE dbo.ps_demo_stats2 (t1_col1 INT IDENTITY, t1_col2 INT);
-- (1 insertion avec valeur 1, puis 10 000 insertions avec valeur 2)
CREATE CLUSTERED INDEX ix_col1 ON dbo.ps_demo_stats2 (t1_col1);

-- Table 2 : 1 ligne avec col2=2, 10 000 lignes avec col2=1
CREATE TABLE dbo.ps_demo_stats3 (t2_col1 INT IDENTITY, t2_col2 INT);
-- (1 insertion avec valeur 2, puis 10 000 insertions avec valeur 1)
CREATE CLUSTERED INDEX ix_col1_t2 ON dbo.ps_demo_stats3 (t2_col1);

-- Requête de jointure avec AUTO_CREATE_STATISTICS activé → Plan d'exécution correct
SELECT t1.t1_col2, t2.t2_col2
FROM dbo.ps_demo_stats2 AS t1
    INNER JOIN dbo.ps_demo_stats3 AS t2
    ON t1.t1_col2 = t2.t2_col2
WHERE t1.t1_col2 = 2;

Without statistics (AUTO_CREATE_STATISTICS disabled):

The execution plan is full of warnings
The most serious warning on the Nested Loops operator: “No join predicate”
SQL Server assumes that all values in one table will match all values in the other table
Appearance of a Table Spool operator as temporary storage for intermediate results

6. Optimize Ad Hoc Expressions and Parameterization

Module duration: 10m 54s

6.1 Demo: Optimize Ad Hoc workloads

How Optimize for Ad Hoc Workload works

When the system relies on many ad hoc queries, the query cache fills up with execution plans that will probably never be used again. The Optimize for Ad Hoc Workloads setting helps handle this issue.

Operating principle:

When running an ad hoc query for the first time, SQL Server stores a stub of the plan (not the full plan)
This stub only takes a few hundred bytes of memory (ex: 448 bytes)
On second run, SQL Server stores the full plan (which can take tens of kilobytes, ex: 65 KB)

Benefits: Reduces cache plan footprint and allows only frequently executed queries to have their full plan stored in the cache.

Activation via SSMS:

Right click on the server name → Properties → Advanced
Property “Optimize for Ad Hoc Workloads” → True

Querying the cache plan:

-- Vérifier le contenu du plan cache (fichier : Optimize for Ad-Hoc Workloads.sql)
SELECT usecounts
    , cacheobjtype
    , objtype
    , size_in_bytes
    , [text]
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)

-- Requête ad hoc exemple
SELECT TOP 1000 *
FROM dbo.DimCustomer
WHERE LastName = 'Yang'

Important Note: Enabling this feature only affects new execution plans. Existing plans in the cache remain unchanged.

6.2 Understanding Parameter Sniffing

Packaging Box Analogy

Imagine a large retailer that sells both chewing gum and washing machines. A customer first orders a piece of gum, so you prepare an envelope. But then the same customer orders a washing machine. Trying to wrap the machine in the envelope would be absurd. This is exactly how parameter sniffing works in SQL Server.

Parameter Sniffing Mechanism

When SQL Server compiles a stored procedure for the first time, the parameter value used during this first execution is used to generate the execution plan. In subsequent runs, the same plan with the same parameter value is reused.

Parameter sniffing is not always bad. It can work more or less efficiently depending on the data distribution and the queries executed.

How to identify a parameter sniffing problem?

Symptom	Description
Inconsistent performance of the stored procedure	The same procedure is performed efficiently sometimes, slowly other times
Sudden decrease in performance	Performance deteriorates suddenly
Improvement after updating statistics	The procedure improves after `UPDATE STATISTICS`
Best performance outside of the procedure	The query within the procedure is best executed as a standalone query

Solutions to bad parameter sniffing

Solution	Description	Advantages	Disadvantages
Disable parameter sniffing	Configurable at server, database, or individual procedure level	Scope Flexibility	May impact other queries
Use a local variable	Replace the parameter with a local variable	Uses the average value of the histogram	Old bypass, less recommended today
WITH RECOMPILE	Recompile the query on each execution	Permanently fixes the problem	CPU cost per execution, plans not stored in cache
OPTIMIZE FOR query hint	Force SQL Server to use a specific value or UNKNOWN	Precise control	Risk if data distribution changes
Force plan via Query Store	Use the Query Store plan forcing feature	Based on historical performance	Requires Query Store enabled

6.3 Demo: Parameter Sniffing and Recompilation

Initial setup

-- Création d'un index pour supporter les démonstrations
-- (fichier : Parameter Sniffing WITH RECOMPILE.sql)
CREATE INDEX ix_Product_Sales ON dbo.FactOnlineSales (ProductKey) 
    INCLUDE(SalesOrderNumber)

Data distribution by ProductKey

-- Requête 1 : 304 lignes (le "chewing-gum")
SELECT SalesOrderNumber, SalesAmount, productKey
FROM dbo.FactOnlineSales
WHERE ProductKey = 1827

-- Requête 2 : ~215 000 lignes
SELECT SalesOrderNumber, SalesAmount, productKey
FROM dbo.FactOnlineSales
WHERE ProductKey = 1662

-- Requête 3 : ~273 000 lignes (la "machine à laver")
SELECT SalesOrderNumber, SalesAmount, productKey
FROM dbo.FactOnlineSales
WHERE ProductKey = 176

Observation: The first two queries use the same plan (Index Seek), but SQL Server considers it more efficient to do an Index Scan for the third (many more rows).

Parameter sniffing demonstration

-- Création de la stored procedure
CREATE PROCEDURE Get_Product_SalesAmt
@ProductKey int
AS
SELECT SalesOrderNumber, SalesAmount, productKey
FROM dbo.FactOnlineSales
WHERE ProductKey = @ProductKey;

-- 1ère exécution avec la "machine à laver" → SQL Server compile pour 273 000 lignes → Table Scan
EXECUTE Get_Product_SalesAmt @ProductKey = 176

-- 2ème exécution avec le "chewing-gum" → MÊME TABLE SCAN !
-- Bien que 304 lignes seulement, SQL Server estime encore 273 000 lignes !
-- Compiled value : 176 / Runtime value : 1827 → Parameter Sniffing visible dans Properties
EXECUTE Get_Product_SalesAmt @ProductKey = 1827

Solution 1: WITH RECOMPILE

-- Modifier la procédure avec WITH RECOMPILE
ALTER PROCEDURE Get_Product_SalesAmt
@ProductKey int
WITH RECOMPILE
AS
SELECT SalesOrderNumber, SalesAmount, productKey
FROM dbo.FactOnlineSales
WHERE ProductKey = @ProductKey;

Result: SQL Server generates a different plan for each parameter value, identical to the plans in the first direct runs.

Disadvantages:

High CPU cost on each run
Plans are not stored in cache (unable to investigate problematic queries later)

Solution 2: OPTIMIZE FOR query hint

-- Forcer SQL Server à utiliser une valeur spécifique
ALTER PROCEDURE Get_Product_SalesAmt
@ProductKey int
AS
SELECT SalesOrderNumber, SalesAmount, productKey
FROM dbo.FactOnlineSales
WHERE ProductKey = @ProductKey
OPTION (OPTIMIZE FOR (@ProductKey=1827));

7. Understanding Plan Guides and Query Hints

Module duration: 7m 14s

7.1 Introduction to Guide Plans

When to use Plan Guides?

The SQL Server Query Optimizer is very intelligent. In the vast majority of cases, it is better to let it do its job. However, some scenarios require taking control and providing explicit instructions to change the default behavior.

Plan guides help optimize query performance in scenarios where you cannot or do not want to modify the actual query text in SQL Server. They impact optimization by attaching query hints or a fixed query plan to queries.

Particularly useful when a small subset of queries perform poorly.

Operating mechanism

Creation of the guide plan by specifying the T-SQL instruction with the query hint to apply
When executing the query, SQL Server compares the T-SQL statement to the guide plan
If match, the query hint is attached to the runtime

The three types of Guide Plans

Type	Application	Description
OBJECT guide plan	Queries executed as stored procedures and functions (scalar and table-valued UDF)	Optimizes existing procedures without modifying their code
SQL plan guide	Queries executed as standalone T-SQL statements	Applies hints to ad hoc queries
TEMPLATE plan guide	Standalone queries configured according to a specified form	Overrides the database parameterization option for a query class

Syntax: sp_create_plan_guide

-- Exemple de création d'un plan guide de type OBJECT
-- (fichier : demos.sql, Module 07)
sp_create_plan_guide   
    @name = N'Guide_PS',  
    @stmt = N'SELECT SalesOrderNumber
                , SalesAmount
                , productKey
        FROM dbo.FactOnlineSales
        WHERE ProductKey = @ProductKey;',  
    @type = N'OBJECT',  
    @module_or_batch = N'dbo.Get_Product_SalesAmt',  
    @params = NULL,  
    @hints = N'OPTION (OPTIMIZE FOR (@ProductKey = ''176''))';

Warning: Plan guides should really be the exception rather than the rule. In most cases, the SQL Server Query Optimizer chooses the most optimal execution plan.

7.2 Demo: Optimize performance with Query Hints

This demonstration uses the same three queries and the same stored procedure as module 6 to illustrate how query hints can resolve bad parameter sniffing problems.

Query Hint: OPTIMIZE FOR specific value

-- Forcer l'utilisation du plan optimal pour le "chewing-gum" (ProductKey 1827)
ALTER PROCEDURE Get_Product_SalesAmt
@ProductKey int
AS
SELECT SalesOrderNumber, SalesAmount, productKey
FROM dbo.FactOnlineSales
WHERE ProductKey = @ProductKey
OPTION (OPTIMIZE FOR (@ProductKey=1827));

Behavior: SQL Server generates the optimal plan for ProductKey 1827 (Index Seek). Even when running with ProductKey 176 (the “washing machine”), the “chewing gum” plan is used, because that’s what was explicitly requested.

When to use: When you know which parameter value is most frequently used.

Disclaimer: By taking this responsibility, it is assumed that this plan will be “good enough” for all scenarios. This is particularly risky when the data distribution changes frequently.

Query Hint: OPTIMIZE FOR UNKNOWN

-- Utiliser la valeur moyenne des statistiques comme estimée
ALTER PROCEDURE Get_Product_SalesAmt
@ProductKey int
AS
SELECT SalesOrderNumber, SalesAmount, productKey
FROM dbo.FactOnlineSales
WHERE ProductKey = @ProductKey
OPTION (OPTIMIZE FOR UNKNOWN);

Behavior: The Query Optimizer uses the average value of the statistics histogram as the row estimate. No compiled value is displayed in the Properties window.

Comparison OPTIMIZE FOR UNKNOWN vs WITH RECOMPILE

Behavior	`OPTIMIZE FOR UNKNOWN`	`WITH RECOMPILE`
Plan stored in cache	✅ Yes	❌ No
Plan generated at each execution	❌ No	✅ Yes
CPU cost per execution	Low	High
Further investigation possible	✅ Yes	❌ No

8. Overall course summary

This course covered the fundamental and advanced concepts of query optimization in SQL Server:

Fundamental concepts

Concept	Key points
Good performance	Set the right expectations and identify “good enough” before you start optimizing
Bottlenecks	Inadequate indexing, suboptimal execution plans, missing or inaccurate statistics
Query Optimizer	Cost-based engine that selects the optimal plan from many candidate plans
Execution Plan	The “blood test” of your request – you must know how to read and interpret it
Estimated vs Actual	Same plan, but the actual plan includes additional runtime information

Cache Plan

Concept	Key points
Caching Plan	SQL Server stores plans in memory for reuse, saving CPU and memory
Data filtering	Retrieving only the necessary data significantly increases the chance of plan reuse
Ad Hoc vs Prepared	Ad hoc queries without parameters generate a plan by combination of values; prepared workloads reuse plans

Indexing

Concept	Key points
Indexing strategy	As few indexes as possible, referenced by as many queries as possible
Clustered vs Non-Clustered	Only one clustered index per table; multiple non-clustered possible
Rowstore vs Columnstore	Columnstore for analytical workloads; rowstore for transactional workloads with lots of DML
Selectivity	Create indexes on columns with high selectivity; column order has a direct impact on efficiency

Statistics

Concept	Key points
Role	Data distribution information that allows the Query Optimizer to generate the optimal plan
Up to date	Up-to-date statistics are essential for correct line estimates
Automatic	SQL Server automatically creates and updates statistics for indexed and filter predicate columns

Parameter Sniffing and Query Hints

Concept	Key points
Parameter Sniffing	SQL Server generates the plan based on the first compiled parameter value and reuses that plan
Bad sniffing	Problem when data distribution is very uneven between parameter values
Solutions	WITH RECOMPILE, OPTIMIZE FOR, OPTIMIZE FOR UNKNOWN, plan guides
Guide Map	Exception, not the rule – for cases where you cannot modify the query directly

9. Demo Code Files

Module 02 – Introduction to Optimization

`02/demos/Statistics Contoso Query.sql`

Demo main query used throughout the course. Retrieves customer information (first name, last name, country, city) and the total amount of sales per customer, filtered by education level and minimum amount.

SET STATISTICS IO, TIME ON;

SELECT cu.FirstName
    , cu.LastName
    , geo.RegionCountryName AS country
    , geo.CityName AS city
    , SUM(fso.SalesAmount) AS salesAmount
FROM dbo.FactOnlineSales AS fso
    INNER JOIN dbo.DimCustomer AS cu ON cu.CustomerKey = fso.CustomerKey
    INNER JOIN dbo.DimGeography AS geo ON geo.GeographyKey = cu.GeographyKey
WHERE cu.Education = 'Bachelors'
GROUP BY cu.CustomerKey
    , cu.FirstName
    , cu.LastName
    , geo.RegionCountryName 
    , geo.CityName
HAVING SUM(fso.SalesAmount) > 500
ORDER BY salesAmount DESC

`02/demos/DMV Query.sql`

Queries using Dynamic Management Views to capture metrics for active and previously executed queries.

-- Requêtes actuellement en cours d'exécution
SELECT txt.[text]
    , qp.query_plan
    , req.cpu_time
    , req.logical_reads
    , req.writes
FROM sys.dm_exec_requests AS req
CROSS APPLY sys.dm_exec_query_plan (req.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(req.plan_handle) AS txt

-- Requêtes précédemment exécutées
SELECT txt.[text]
    , qp.query_plan
    , st.execution_count
    , st.min_logical_writes
    , st.max_logical_reads
    , st.total_logical_reads
    , st.total_elapsed_time
    , st.last_elapsed_time
FROM sys.dm_exec_query_stats AS st
CROSS APPLY sys.dm_exec_query_plan (st.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(st.sql_handle) AS txt

Module 04 – Cache Plan

`04/demos/SQL Queries/DMV Query Cache.sql`

Basic cache plan query via sys.dm_exec_cached_plans.

SELECT cp.refcounts
    , cp.usecounts
    , cp.size_in_bytes
    , cp.cacheobjtype
    , cp.objtype
    , cp.plan_handle
FROM sys.dm_exec_cached_plans AS cp

`04/demos/SQL Queries/DMV Query Cache Ad Hoc.sql`

Searching for specific plans in the cache for ad hoc workloads.

SELECT cp.usecounts
    , cp.cacheobjtype
    , cp.objtype
    , txt.text
FROM sys.dm_exec_cached_plans AS cp
    CROSS APPLY sys.dm_exec_sql_text (cp.plan_handle) AS txt
WHERE txt.text like '%' + 'geo.RegionCountryName = ''Australia''' + '%'

`04/demos/SQL Queries/DMV Query Cache Prepared.sql`

Searching for prepared plans in the cache (parameterized plans).

SELECT cp.usecounts
    , cp.cacheobjtype
    , cp.objtype
    , txt.text
FROM sys.dm_exec_cached_plans AS cp
    CROSS APPLY sys.dm_exec_sql_text (cp.plan_handle) AS txt
WHERE txt.text like '%' + '@country' + '%'

`04/demos/SQL Queries/sp_executesql command.sql`

Demonstration of the use of sp_executesql for prepared workloads.

DECLARE @q NVARCHAR(MAX)
    , @params NVARCHAR(MAX)

SET @q = N' SELECT cu.FirstName
    , cu.LastName
    , geo.RegionCountryName AS country
    , geo.CityName AS city
    , SUM(fso.SalesAmount) AS salesAmount
FROM dbo.FactOnlineSales AS fso
    INNER JOIN dbo.DimCustomer AS cu ON cu.CustomerKey = fso.CustomerKey
    INNER JOIN dbo.DimGeography AS geo ON geo.GeographyKey = cu.GeographyKey
WHERE cu.Education = @Education
    AND geo.RegionCountryName = @country
GROUP BY cu.CustomerKey
    , cu.FirstName
    , cu.LastName
    , geo.RegionCountryName 
    , geo.CityName
HAVING SUM(fso.SalesAmount) > 100
ORDER BY salesAmount DESC'

SET @params = N' @education NVARCHAR(64), @country NVARCHAR(64)'

EXEC sp_executesql @q
                , @params
                , @Education = 'Bachelors'
                , @country = 'France'

Module 05 – Indexes and Statistics

`05/demos/SQL Queries/M05_Demo 1_Indexes.sql`

Complete demonstrations on indexes: filtering, data selectivity, and column ordering.

-- #1 Impact des conditions de filtrage
SELECT [CustomerKey], [GeographyKey], [CustomerAlternateKey], [Title],
       [FirstName], [MiddleName], [LastName], [NameStyle], [BirthDate],
       [MaritalStatus], [Suffix], [Gender], [EmailAddress], [YearlyIncome],
       [TotalChildren], [NumberChildrenAtHome], [EnglishEducation],
       [SpanishEducation], [FrenchEducation], [EnglishOccupation],
       [SpanishOccupation], [FrenchOccupation], [HouseOwnerFlag],
       [NumberCarsOwned], [AddressLine1], [AddressLine2], [Phone],
       [DateFirstPurchase], [CommuteDistance]
FROM [AdventureWorksDW2020].[dbo].[DimCustomer]
WHERE CustomerKey = 11001

-- #2 Sélectivité des données
-- Identification de la sélectivité et densité d'une colonne
SELECT COUNT(DISTINCT c.Gender) AS DistinctValues
    , COUNT(c.Gender) AS TotalRows
    , CAST(COUNT(DISTINCT c.Gender) AS DECIMAL) / CAST(COUNT(c.Gender) AS DECIMAL) AS ColSelectivity
    , (1.0 / COUNT(DISTINCT c.Gender)) AS ColDensity
FROM dbo.DimCustomer AS c

-- Requête avec hint d'index explicite
SELECT [CustomerKey], [BirthDate], [Gender]
FROM [AdventureWorksDW2020].[dbo].[DimCustomer] WITH (INDEX(ix_Cust_gender))
WHERE Gender = 'F'
    AND BirthDate = '1973-11-06'

-- Création d'un index sur Gender et BirthDate
CREATE INDEX ix_Cust_gender ON dbo.DimCustomer (BirthDate, Gender) 
    WITH DROP_EXISTING;

-- #3 Ordre des colonnes dans l'index
USE AdventureWorks2019;

CREATE INDEX ix_Address_city ON Person.Address (City, PostalCode);

SELECT AddressID, City, PostalCode
FROM Person.Address
WHERE City = 'Seattle'        -- Utilise l'index efficacement
-- WHERE PostalCode = '98104'  -- Index moins efficace (2e colonne)

`05/demos/SQL Queries/M05_Demo 2_Statistics.sql`

Demonstrations on the impact of statistics on query performance.

-- #1 Créer la table de démo et un non-clustered index
CREATE TABLE dbo.psdemo_stats (col1 INT, col2 INT IDENTITY);

SELECT TOP 2000 IDENTITY(INT, 1, 1) AS num
INTO #numbers
FROM master.dbo.syscolumns AS c1, master.dbo.syscolumns AS c2;

INSERT INTO dbo.psdemo_stats (col1)
SELECT 2 FROM #numbers;

DROP TABLE #numbers; 

CREATE NONCLUSTERED INDEX ix_psdemo_stats ON dbo.psdemo_stats (col1);

ALTER DATABASE DataMozart SET AUTO_UPDATE_STATISTICS OFF;

SELECT col1, col2 FROM dbo.psdemo_stats WHERE col1 = 2

-- #2 Statistiques sur les colonnes non indexées

-- Table 1 : 1 ligne avec t1_col2=1, 10 000 lignes avec t1_col2=2
CREATE TABLE dbo.ps_demo_stats2 (t1_col1 INT IDENTITY, t1_col2 INT);
INSERT INTO dbo.ps_demo_stats2 (t1_col2) VALUES (1);

SELECT TOP 10000 IDENTITY(INT, 1, 1) AS num
INTO #numbers
FROM master.dbo.syscolumns AS c1, master.dbo.syscolumns AS c2;

INSERT INTO dbo.ps_demo_stats2 (t1_col2) SELECT 2 FROM #numbers;
CREATE CLUSTERED INDEX ix_col1 ON dbo.ps_demo_stats2 (t1_col1);

-- Table 2 : 1 ligne avec t2_col2=2, 10 000 lignes avec t2_col2=1
CREATE TABLE dbo.ps_demo_stats3 (t2_col1 INT IDENTITY, t2_col2 INT);
INSERT INTO dbo.ps_demo_stats3 (t2_col2) VALUES (2);
INSERT INTO dbo.ps_demo_stats3 (t2_col2) SELECT 1 FROM #numbers;
DROP TABLE #numbers;
CREATE CLUSTERED INDEX ix_col1_t2 ON dbo.ps_demo_stats3 (t2_col1);

-- Activer/désactiver AUTO-STATISTICS
ALTER DATABASE DataMozart SET AUTO_CREATE_STATISTICS ON;
-- ALTER DATABASE DataMozart SET AUTO_CREATE_STATISTICS OFF;

-- Requête de jointure
SELECT t1.t1_col2, t2.t2_col2
FROM dbo.ps_demo_stats2 AS t1
    INNER JOIN dbo.ps_demo_stats3 AS t2
    ON t1.t1_col2 = t2.t2_col2
WHERE t1.t1_col2 = 2;

Module 06 – Ad Hoc and Parameter Sniffing

`06/demos/SQL Queries/Optimize for Ad-Hoc Workloads.sql`

Demonstration of cache plane inspection and activation of ad hoc optimization.

-- Vérifier le contenu du plan cache
SELECT usecounts
    , cacheobjtype
    , objtype
    , size_in_bytes
    , [text]
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)

-- Requête ad hoc exemple (exécuter avant et après activation de Optimize for Ad Hoc Workloads)
SELECT TOP 1000 *
FROM dbo.DimCustomer
WHERE LastName = 'Yang'

`06/demos/SQL Queries/Parameter Sniffing WITH RECOMPILE.sql`

Complete demonstration of parameter sniffing with the WITH RECOMPILE and OPTIMIZE FOR solution.

-- Index de support
CREATE INDEX ix_Product_Sales ON dbo.FactOnlineSales (ProductKey) 
    INCLUDE(SalesOrderNumber)

-- Trois requêtes avec distributions de données très différentes
SELECT SalesOrderNumber, SalesAmount, productKey
FROM dbo.FactOnlineSales WHERE ProductKey = 1827  -- ~304 lignes

SELECT SalesOrderNumber, SalesAmount, productKey
FROM dbo.FactOnlineSales WHERE ProductKey = 1662  -- ~215 000 lignes

SELECT SalesOrderNumber, SalesAmount, productKey
FROM dbo.FactOnlineSales WHERE ProductKey = 176   -- ~273 000 lignes

-- Création de la stored procedure
CREATE PROCEDURE Get_Product_SalesAmt
@ProductKey int
AS
SELECT SalesOrderNumber, SalesAmount, productKey
FROM dbo.FactOnlineSales
WHERE ProductKey = @ProductKey;

-- Exécution
EXECUTE Get_Product_SalesAmt @ProductKey = 1827

-- Modification avec WITH RECOMPILE
ALTER PROCEDURE Get_Product_SalesAmt
@ProductKey int
WITH RECOMPILE
AS
SELECT SalesOrderNumber, SalesAmount, productKey
FROM dbo.FactOnlineSales
WHERE ProductKey = @ProductKey;

-- Modification avec Query Hint OPTIMIZE FOR
ALTER PROCEDURE Get_Product_SalesAmt
@ProductKey int
AS
SELECT SalesOrderNumber, SalesAmount, productKey
FROM dbo.FactOnlineSales
WHERE ProductKey = @ProductKey
OPTION (OPTIMIZE FOR (@ProductKey=1827));

Module 07 – Plan Guides and Query Hints

`07/demos.sql`

Complete demonstration including all query hints solutions and the creation of a guide plan.

-- Index de support
CREATE INDEX ix_Product_Sales ON dbo.FactOnlineSales (ProductKey) 
    INCLUDE(SalesOrderNumber)

-- Trois requêtes avec distributions différentes
SELECT SalesOrderNumber, SalesAmount, productKey
FROM dbo.FactOnlineSales WHERE ProductKey = 1827  -- ~304 lignes

SELECT SalesOrderNumber, SalesAmount, productKey
FROM dbo.FactOnlineSales WHERE ProductKey = 1662  -- ~20 000 lignes

SELECT SalesOrderNumber, SalesAmount, productKey
FROM dbo.FactOnlineSales WHERE ProductKey = 176   -- ~273 000 lignes

-- Création de la stored procedure
CREATE PROCEDURE Get_Product_SalesAmt
@ProductKey int
AS
SELECT SalesOrderNumber, SalesAmount, productKey
FROM dbo.FactOnlineSales
WHERE ProductKey = @ProductKey;

-- Exécution initiale
EXECUTE Get_Product_SalesAmt @ProductKey = 1827

-- Version avec RECOMPILE
ALTER PROCEDURE Get_Product_SalesAmt
@ProductKey int
AS
SELECT SalesOrderNumber, SalesAmount, productKey
FROM dbo.FactOnlineSales
WHERE ProductKey = @ProductKey;

-- Version avec OPTIMIZE FOR valeur spécifique
ALTER PROCEDURE Get_Product_SalesAmt
@ProductKey int
AS
SELECT SalesOrderNumber, SalesAmount, productKey
FROM dbo.FactOnlineSales
WHERE ProductKey = @ProductKey
OPTION (OPTIMIZE FOR (@ProductKey=1827));

-- Version avec OPTIMIZE FOR UNKNOWN
ALTER PROCEDURE Get_Product_SalesAmt
@ProductKey int
AS
SELECT SalesOrderNumber, SalesAmount, productKey
FROM dbo.FactOnlineSales
WHERE ProductKey = @ProductKey
OPTION (OPTIMIZE FOR UNKNOWN);

-- Création d'un Plan Guide
sp_create_plan_guide   
    @name = N'Guide_PS',  
    @stmt = N'SELECT SalesOrderNumber
                , SalesAmount
                , productKey
        FROM dbo.FactOnlineSales
        WHERE ProductKey = @ProductKey;',  
    @type = N'OBJECT',  
    @module_or_batch = N'dbo.Get_Product_SalesAmt',  
    @params = NULL,  
    @hints = N'OPTION (OPTIMIZE FOR (@ProductKey = ''176''))';

Search Terms

query · optimization · sql · server · microsoft · databases · statistics · index · plan · cache · optimize · parameter · sniffing · hoc · queries · workloads · columns · indexes · performance · dmv · plane · plans · data · hints

Table of Contents

1. Course presentation

2. Introduction to Query Optimization in SQL Server