DEV Community: mohamed Tayel

SQL Server Query Store Hints: Fix Query Performance Without Changing Application Code

mohamed Tayel — Tue, 14 Jul 2026 13:03:57 +0000

In the previous articles in this series, we introduced SQL Server Query Store, used it to investigate performance regressions, and explored how unstable execution plans can contribute to parameter sniffing problems.

Query Store helped us answer important diagnostic questions:

Has the query used multiple execution plans?
When did its performance begin to change?
Which plan consumed more CPU or execution time?
Was the problem related to parameter sensitivity?
Did SQL Server select a less efficient plan after recompilation?

Identifying the problem, however, is only the first step.

Once a problematic query has been found, the next question is:

How can we influence the query’s execution behavior when changing the application code is difficult, risky, or temporarily impossible?

This is where Query Store Hints become useful.

Query Store Hints allow database administrators to apply query-level optimizer hints without modifying the original SQL statement, changing a stored procedure, or redeploying the application.

In this article, we will explore:

What Query Store Hints are.
How they differ from traditional query hints.
How SQL Server associates a hint with a query.
How to find the correct query_id.
How to apply a simple MAXDOP hint.
How to verify that the hint was applied.
How to measure performance before and after the change.
How to safely remove the hint.

The Real-World Problem: Queries You Cannot Easily Change

In an ideal development process, a slow query can be modified, tested, reviewed, and deployed as part of a new application release.

Production environments, however, are rarely that simple.

The problematic SQL statement might be:

Hard-coded inside a legacy application.
Generated automatically by an ORM.
Produced by a reporting platform.
Executed by third-party software.
Located inside a stored procedure owned by another team.
Part of an application with a long release cycle.
Running during a critical business period when deployment is too risky.

In these situations, the database administrator might understand which optimizer behavior should be tested but still be unable to modify the query text directly.

Historically, the available alternatives included:

Creating a plan guide.
Changing a database-wide configuration.
Changing a server-wide configuration.
Forcing a previously captured execution plan.
Requesting an emergency application release.

Some of these approaches are complex to manage.

Others affect a much larger workload than the individual query causing the problem.

Query Store Hints provide a more targeted option.

They allow SQL Server to apply an optimizer hint to a specific query already captured by Query Store while the application continues sending the original SQL statement.

What Is a Query Store Hint?

A traditional query hint is written directly inside the SQL statement using an OPTION clause.

For example:

SELECT
    ProductID,
    SUM(LineTotal) AS TotalSales
FROM Sales.SalesOrderDetail
GROUP BY
    ProductID
OPTION (MAXDOP 1);

The following part is the traditional query hint:

OPTION (MAXDOP 1)

It tells SQL Server that this statement should use a maximum degree of parallelism of one.

The problem is that adding this hint requires changing the SQL statement itself.

With Query Store Hints, the application can continue executing the original query:

SELECT
    ProductID,
    SUM(LineTotal) AS TotalSales
FROM Sales.SalesOrderDetail
GROUP BY
    ProductID;

The DBA applies the hint separately:

EXEC sys.sp_query_store_set_hints
    @query_id = 123,
    @query_hints = N'OPTION (MAXDOP 1)';

SQL Server associates the hint with the query identified by:

query_id = 123

The application does not need to know that the hint exists.

There is no need to:

Modify the application source code.
Change the stored procedure.
Rebuild the application.
Create a new deployment package.
Schedule an immediate application release.

How Query Store Hints Work

The Query Store Hint workflow contains five main steps:

The application executes a query.
Query Store captures the query, its execution plans, and runtime statistics.
The DBA identifies the query’s query_id.
The DBA applies a hint using sys.sp_query_store_set_hints.
SQL Server considers the hint when compiling the query.

Conceptually, SQL Server treats the Query Store Hint as though an OPTION clause had been added to the original SQL statement.

The application query remains unchanged.

The hint is stored separately inside the database and associated with the corresponding Query Store query record.

This persistence makes Query Store Hints powerful, but it also creates an important operational responsibility:

Every Query Store Hint should be documented, monitored, and periodically reevaluated.

A hint that improves performance today might become inappropriate after changes to:

Data volume.
Data distribution.
Indexes.
Statistics.
Hardware resources.
Database compatibility level.
SQL Server updates.
Application workload patterns.

Query Store Hints should not become permanent configurations that remain in the database after everyone has forgotten why they were added.

Requirements

Before using Query Store Hints, verify the SQL Server platform, Query Store status, and required permissions.

Supported Platforms

For on-premises environments, Query Store Hints were introduced in SQL Server 2022.

They are also supported on applicable Azure SQL platforms.

Query Store Must Be Enabled

Query Store must be enabled for the target database.

It should normally be operating in READ_WRITE mode when Query Store Hints are being managed.

Check the current Query Store state:

SELECT
    actual_state_desc,
    desired_state_desc,
    readonly_reason,
    current_storage_size_mb,
    max_storage_size_mb
FROM sys.database_query_store_options;

A healthy writable state normally shows:

actual_state_desc  = READ_WRITE
desired_state_desc = READ_WRITE

If Query Store is disabled, enable it using:

ALTER DATABASE [YourDatabaseName]
SET QUERY_STORE = ON
(
    OPERATION_MODE = READ_WRITE
);

Replace:

YourDatabaseName

with the actual database name.

Query Store can move into READ_ONLY mode for several reasons, including reaching its configured maximum storage size.

For this reason, always verify the actual state instead of assuming that Query Store is writable.

Required Permissions

Applying or removing a Query Store Hint requires the ALTER permission on the target database.

In SQL Server 2022 and later, querying sys.query_store_query_hints requires the VIEW SERVER PERFORMANCE STATE permission.

Note: Permissions for other Query Store catalog views may differ depending on the SQL Server version and the specific view being accessed.

Why `query_id` Is Critical

Query Store Hints are not applied using:

The stored procedure name.
The application name.
The login name.
A fragment of the SQL text.
The execution plan ID.

They are applied using the Query Store:

query_id

The relationship can be simplified as follows:

Query Text
    ↓
Query Store Query
    ↓
query_id
    ↓
Execution Plans
    ↓
Runtime Statistics
    ↓
Query Store Hint

The query_id connects a query to:

Its stored SQL text.
Its execution plans.
Its runtime history.
Its wait statistics.
Any forced execution plan.
Any Query Store Hint.

Finding the correct query_id is therefore one of the most important steps in the entire process.

Applying a hint to the wrong query_id can affect a completely different query.

Practical Example: Testing `MAXDOP 1`

Consider an application that executes the following query:

SELECT
    sod.ProductID,
    SUM(sod.LineTotal) AS TotalSales
FROM Sales.SalesOrderDetail AS sod
GROUP BY
    sod.ProductID;

Assume that Query Store analysis shows that the query sometimes uses an inefficient parallel execution plan.

For demonstration purposes, we want to test the query with:

OPTION (MAXDOP 1)

This example does not mean that parallelism is automatically a performance problem.

Parallel execution can significantly improve performance for analytical and reporting queries.

MAXDOP 1 should only be considered when testing shows that serial execution is more appropriate for the specific query and surrounding workload.

The purpose of this example is to demonstrate the Query Store Hint workflow, not to recommend MAXDOP 1 as a universal tuning solution.

Step 1: Make Sure the Query Is Captured

A query must exist in Query Store before a Query Store Hint can be associated with it.

Execute the target query:

SELECT
    sod.ProductID,
    SUM(sod.LineTotal) AS TotalSales
FROM Sales.SalesOrderDetail AS sod
GROUP BY
    sod.ProductID;

In a real production system, the application might already have executed the query many times.

Query Store captures information asynchronously, so a newly executed query might not appear immediately in the catalog views.

In a controlled test environment, you can flush the in-memory Query Store information:

EXEC sys.sp_query_store_flush_db;

This can make recently collected Query Store information available for investigation.

It should not be executed repeatedly in production merely to refresh a monitoring query.

Step 2: Find the Correct `query_id`

Search Query Store using a distinctive fragment of the query text:

SELECT
    q.query_id,
    q.object_id,
    q.query_hash,
    qt.query_sql_text,
    q.initial_compile_start_time,
    q.last_compile_start_time
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
    ON q.query_text_id = qt.query_text_id
WHERE qt.query_sql_text LIKE N'%SUM(sod.LineTotal)%'
  AND qt.query_sql_text NOT LIKE N'%sys.query_store_query_text%';

An example result might look like this:

query_id   query_sql_text
--------   -------------------------------------------------------
123        SELECT sod.ProductID, SUM(sod.LineTotal) AS TotalSales...

Before applying a hint, verify that:

The SQL text is the intended statement.
You are connected to the correct database.
The result is not the Query Store search statement itself.
The query belongs to the expected stored procedure or batch.
There are no other relevant query_id values for equivalent query text.
The query context is correct.

A logically similar query can appear under more than one query_id.

This can happen because of differences in:

SQL text.
Parameterization.
Batch context.
Database context.
Object context.
Query compilation settings.

Never assume that the first result returned by the search is automatically the correct target.

Step 3: Capture a Performance Baseline

Do not apply a Query Store Hint before collecting baseline information.

Without a baseline, it is impossible to prove whether the hint improved or degraded performance.

The following query summarizes runtime statistics by execution plan:

DECLARE @QueryId bigint = 123;

SELECT
    q.query_id,
    p.plan_id,
    p.is_forced_plan,
    SUM(rs.count_executions) AS execution_count,
    CAST
    (
        SUM(rs.avg_duration * rs.count_executions)
        / NULLIF(SUM(rs.count_executions), 0)
        / 1000.0
        AS decimal(18,2)
    ) AS weighted_avg_duration_ms,
    CAST
    (
        SUM(rs.avg_cpu_time * rs.count_executions)
        / NULLIF(SUM(rs.count_executions), 0)
        / 1000.0
        AS decimal(18,2)
    ) AS weighted_avg_cpu_ms,
    CAST
    (
        MAX(rs.max_duration) / 1000.0
        AS decimal(18,2)
    ) AS max_duration_ms
FROM sys.query_store_query AS q
INNER JOIN sys.query_store_plan AS p
    ON p.query_id = q.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON rs.plan_id = p.plan_id
WHERE q.query_id = @QueryId
GROUP BY
    q.query_id,
    p.plan_id,
    p.is_forced_plan
ORDER BY
    weighted_avg_duration_ms DESC;

A useful performance baseline should include more than average duration.

Depending on the query, collect:

Execution count.
Average duration.
Maximum duration.
Average CPU time.
Logical reads.
Physical reads.
Memory grant information.
Wait statistics.
Number of execution plans.
Execution-plan shape.
Workload concurrency.
Representative parameter values.

The baseline should reflect normal workload conditions whenever possible.

Step 4: Apply the Query Store Hint

After confirming the correct query_id, apply the hint:

EXEC sys.sp_query_store_set_hints
    @query_id = 123,
    @query_hints = N'OPTION (MAXDOP 1)';

The general syntax is:

EXEC sys.sp_query_store_set_hints
    @query_id = <QueryStoreQueryId>,
    @query_hints = N'OPTION (<SupportedQueryHints>)';

For example:

EXEC sys.sp_query_store_set_hints
    @query_id = 123,
    @query_hints = N'OPTION (RECOMPILE)';

Multiple compatible hints can be included in a single call:

EXEC sys.sp_query_store_set_hints
    @query_id = 123,
    @query_hints = N'OPTION (RECOMPILE, MAXDOP 1)';

When testing a performance change, however, start with the smallest possible modification.

Applying several hints simultaneously makes it difficult to identify which hint caused the improvement or regression.

Important: A New Hint Replaces the Existing Definition

A Query Store query has one stored Query Store Hint definition for the applicable scope.

Calling sys.sp_query_store_set_hints again does not append a second independent hint.

The new hint text replaces the previous definition.

Assume the current hint is:

OPTION (MAXDOP 1)

Then the DBA executes:

EXEC sys.sp_query_store_set_hints
    @query_id = 123,
    @query_hints = N'OPTION (RECOMPILE)';

The resulting Query Store Hint becomes:

OPTION (RECOMPILE)

It does not automatically become:

OPTION (MAXDOP 1, RECOMPILE)

To preserve both hints, they must be supplied together:

EXEC sys.sp_query_store_set_hints
    @query_id = 123,
    @query_hints = N'OPTION (MAXDOP 1, RECOMPILE)';

This replacement behavior is particularly important when Query Store Hints are managed by:

Multiple DBAs.
Automated tuning scripts.
Deployment pipelines.
Operational support teams.

Always review the existing hint before changing it.

Step 5: Verify the Stored Hint

Query the sys.query_store_query_hints catalog view:

SELECT
    query_hint_id,
    query_id,
    query_hint_text,
    last_query_hint_failure_reason,
    last_query_hint_failure_reason_desc,
    query_hint_failure_count,
    source,
    source_desc
FROM sys.query_store_query_hints
WHERE query_id = 123;

An expected result might look like this:

query_id                  123
query_hint_text           OPTION (MAXDOP 1)
failure_reason            NULL
failure_reason_desc       NULL
query_hint_failure_count  0
source_desc               User

The most important columns are described below.

`query_hint_text`

Shows the currently stored Query Store Hint.

For this example:

OPTION (MAXDOP 1)

`last_query_hint_failure_reason`

Shows the most recent error number generated when SQL Server could not apply the hint.

A successful hint normally shows:

NULL

`last_query_hint_failure_reason_desc`

Provides a description of the most recent hint-application failure.

`query_hint_failure_count`

Shows how many times SQL Server failed to apply the hint.

For a successfully applied hint, the expected value is:

`source_desc`

Shows the source of the hint.

A manually created Query Store Hint normally appears as:

User

The existence of a row in sys.query_store_query_hints proves that the hint was stored.

It does not, by itself, prove that SQL Server successfully applied the hint during compilation.

This is why the failure-related columns must also be checked.

Step 6: Execute the Query Again

Execute the original application query again:

SELECT
    sod.ProductID,
    SUM(sod.LineTotal) AS TotalSales
FROM Sales.SalesOrderDetail AS sod
GROUP BY
    sod.ProductID;

The SQL statement remains unchanged.

The application does not include:

OPTION (MAXDOP 1)

SQL Server applies the behavior through Query Store.

Capture the actual execution plan and verify whether the query is now using a serial plan.

You can also inspect the execution-plan XML for Query Store Hint attributes.

Depending on the SQL Server version and plan format, the plan can contain information such as:

QueryStoreStatementHintText
QueryStoreStatementHintId
QueryStoreStatementHintSource

These attributes provide additional evidence that SQL Server compiled the query using a Query Store Hint.

However, verifying the plan shape is not enough.

The real questions are:

Did execution duration improve?
Did CPU consumption decrease?
Did logical reads change?
Did the query begin waiting on different resources?
Did concurrency improve?
Did the query become slower for other parameter values?
Did limiting parallelism increase elapsed time?
Did the change negatively affect another part of the workload?
Is the improvement stable across multiple Query Store intervals?

A different execution plan is not automatically a better execution plan.

Step 7: Compare Performance After the Hint

After allowing the query to execute under representative workload conditions, review the Query Store runtime statistics again.

Use the same measurement method that was used to capture the baseline.

Compare:

Before the Hint
vs.
After the Hint

A simple comparison can include:

Metric	Before Hint	After Hint	Result
Average duration	Record value	Record value	Improved or regressed
Average CPU	Record value	Record value	Improved or regressed
Maximum duration	Record value	Record value	Improved or regressed
Logical reads	Record value	Record value	Improved or regressed
Execution count	Record value	Record value	Comparable workload
Main waits	Record value	Record value	Changed or unchanged

Be careful when comparing Query Store intervals.

An interval containing five executions should not be compared directly with another interval containing thousands of executions without considering the workload difference.

Also verify that the parameter distribution is representative.

A hint that improves one parameter value might degrade performance for another.

What Happens When SQL Server Cannot Apply the Hint?

A Query Store Hint can fail for several reasons:

The hint syntax is invalid.
The hint is not supported.
The hint conflicts with another optimizer directive.
The optimizer cannot produce a valid execution plan using the hint.
The query is not eligible for the selected hint.
The query uses an unsupported parameterization pattern.
The hint conflicts with an existing query configuration.

In many cases, SQL Server allows the application query to continue executing even when the Query Store Hint cannot be applied.

The query might execute without the requested hint while the failure information is recorded in:

sys.query_store_query_hints

This behavior helps protect application availability.

However, it also means that DBAs should never assume that a stored hint is automatically active.

Use the following query to identify Query Store Hints with recorded failures:

SELECT
    query_hint_id,
    query_id,
    query_hint_text,
    last_query_hint_failure_reason,
    last_query_hint_failure_reason_desc,
    query_hint_failure_count,
    source_desc
FROM sys.query_store_query_hints
WHERE query_hint_failure_count > 0
   OR last_query_hint_failure_reason IS NOT NULL
ORDER BY
    query_hint_failure_count DESC;

This query should be included in regular Query Store monitoring when Query Store Hints are used in production.

Examples of Query Store Hints

Query Store Hints can apply several useful optimizer hints, including:

MAXDOP
RECOMPILE
OPTIMIZE FOR UNKNOWN
MAX_GRANT_PERCENT
MIN_GRANT_PERCENT
HASH JOIN
MERGE JOIN
LOOP JOIN
FORCE ORDER
KEEP PLAN
KEEPFIXED PLAN
NO_PERFORMANCE_SPOOL
USE HINT

Not every traditional SQL Server hint is supported through Query Store Hints.

Examples that require special consideration include:

OPTIMIZE FOR (@Variable = Value)
MAXRECURSION
USE PLAN
Table hints such as FORCESEEK or INDEX

The supported list can depend on the SQL Server platform and version.

Always verify the selected hint against the official documentation for the environment being managed.

In the next article in this series, we will examine the most important Query Store Hints individually and discuss when each one should and should not be used.

Query Store Hint Versus Plan Forcing

Query Store Hints and Query Store plan forcing both influence query execution, but they solve different problems.

Plan Forcing

Plan forcing tells SQL Server:

Try to reproduce this specific historical execution plan.

This approach is useful when:

A previously efficient plan already exists.
A new plan caused a performance regression.
The DBA wants SQL Server to return to the known plan.

Query Store Hint

A Query Store Hint tells SQL Server:

Compile the query while applying this optimizer behavior.

Examples include:

Use a maximum DOP of 1.
Recompile the query for every execution.
Optimize for an unknown parameter value.
Limit the memory grant.
Prefer a particular join strategy.

The optimizer can still generate a new execution plan, but it must consider the applied hint.

The difference can be summarized as follows:

Plan Forcing
=
Attempt to reproduce a specific plan shape.

Query Store Hint
=
Influence the rules used to generate a plan.

A more detailed comparison between plan forcing and Query Store Hints will be covered in a later article.

When Query Store Hints Are Useful

Query Store Hints can be useful in several practical scenarios.

1. The Application Cannot Be Modified

The query might be generated by:

Vendor software.
An ORM.
A reporting system.
A legacy application.
A packaged product.

2. An Emergency Performance Fix Is Required

A critical production query might need immediate stabilization before a permanent application change can complete the normal development and release process.

3. One Query Needs Different Behavior

A single query might need:

A lower MAXDOP.
A memory-grant limit.
Recompilation.
A different optimizer compatibility behavior.

Changing a server-wide or database-wide setting would affect too many other queries.

4. A Migration Caused a Regression

After upgrading SQL Server or changing the database compatibility level, one query might begin behaving differently.

A targeted Query Store Hint can provide temporary stabilization while the root cause is investigated.

5. A DBA Wants to Test an Optimizer Behavior

Query Store Hints can be used to test a possible optimization without immediately modifying the application source code.

The DBA can then compare Query Store runtime statistics before and after the change.

When Query Store Hints Should Not Be the First Fix

Query Store Hints are powerful, but they should not replace proper investigation.

Before applying a hint, investigate possible root causes such as:

Missing indexes.
Ineffective indexes.
Outdated statistics.
Poor cardinality estimates.
Incorrect data types.
Implicit conversions.
Non-SARGable predicates.
Parameter sensitivity.
Excessive query complexity.
Blocking.
Lock contention.
Memory pressure.
Storage latency.
Inappropriate database configuration.
Available Intelligent Query Processing features.

A hint can improve the visible symptom while leaving the underlying design problem unresolved.

For example, forcing serial execution might reduce parallelism-related waits but increase total elapsed time.

Similarly, adding RECOMPILE might solve a parameter sensitivity problem but create excessive compilation CPU for a high-frequency query.

The correct hint should always be selected based on evidence rather than assumptions.

Production Safety Checklist

Before applying a Query Store Hint in production:

Confirm the correct database.
Confirm the correct query_id.
Review the exact SQL text.
Check for multiple related query_id values.
Capture a performance baseline.
Review all existing execution plans.
Check for an existing Query Store Hint.
Test with production-like data.
Test multiple representative parameter values.
Measure duration, CPU, reads, waits, and concurrency.
Prepare a rollback command.
Document the reason for the hint.
Record the owner and implementation date.
Define a monitoring period.
Schedule a future review date.
Reevaluate the hint after major environment changes.

Remember:

A Query Store Hint can affect every execution associated with the targeted query_id.

The change is not limited to the DBA’s test session.

Removing the Query Store Hint

Every production change should have a rollback path.

To remove the Query Store Hint:

EXEC sys.sp_query_store_clear_hints
    @query_id = 123;

Verify that the hint has been removed:

SELECT
    query_id,
    query_hint_text,
    source_desc
FROM sys.query_store_query_hints
WHERE query_id = 123;

After the hint is cleared, the query should no longer appear in this view for that query_id.

The application query remains unchanged throughout the entire process.

Complete Demonstration Script

The following script summarizes the complete workflow:

/*==============================================================
  1. Verify Query Store state
==============================================================*/

SELECT
    actual_state_desc,
    desired_state_desc,
    readonly_reason,
    current_storage_size_mb,
    max_storage_size_mb
FROM sys.database_query_store_options;


/*==============================================================
  2. Find the target query_id
==============================================================*/

SELECT
    q.query_id,
    q.object_id,
    q.query_hash,
    qt.query_sql_text,
    q.initial_compile_start_time,
    q.last_compile_start_time
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
    ON q.query_text_id = qt.query_text_id
WHERE qt.query_sql_text LIKE N'%SUM(sod.LineTotal)%'
  AND qt.query_sql_text NOT LIKE N'%sys.query_store_query_text%';


/*==============================================================
  3. Capture the baseline
==============================================================*/

DECLARE @QueryId bigint = 123;

SELECT
    q.query_id,
    p.plan_id,
    p.is_forced_plan,
    SUM(rs.count_executions) AS execution_count,
    CAST
    (
        SUM(rs.avg_duration * rs.count_executions)
        / NULLIF(SUM(rs.count_executions), 0)
        / 1000.0
        AS decimal(18,2)
    ) AS weighted_avg_duration_ms,
    CAST
    (
        SUM(rs.avg_cpu_time * rs.count_executions)
        / NULLIF(SUM(rs.count_executions), 0)
        / 1000.0
        AS decimal(18,2)
    ) AS weighted_avg_cpu_ms,
    CAST
    (
        MAX(rs.max_duration) / 1000.0
        AS decimal(18,2)
    ) AS max_duration_ms
FROM sys.query_store_query AS q
INNER JOIN sys.query_store_plan AS p
    ON p.query_id = q.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON rs.plan_id = p.plan_id
WHERE q.query_id = @QueryId
GROUP BY
    q.query_id,
    p.plan_id,
    p.is_forced_plan
ORDER BY
    weighted_avg_duration_ms DESC;


/*==============================================================
  4. Check for an existing Query Store Hint
==============================================================*/

SELECT
    query_hint_id,
    query_id,
    query_hint_text,
    last_query_hint_failure_reason,
    last_query_hint_failure_reason_desc,
    query_hint_failure_count,
    source_desc
FROM sys.query_store_query_hints
WHERE query_id = @QueryId;


/*==============================================================
  5. Apply the Query Store Hint
==============================================================*/

EXEC sys.sp_query_store_set_hints
    @query_id = 123,
    @query_hints = N'OPTION (MAXDOP 1)';


/*==============================================================
  6. Verify the stored hint and failure status
==============================================================*/

SELECT
    query_hint_id,
    query_id,
    query_hint_text,
    last_query_hint_failure_reason,
    last_query_hint_failure_reason_desc,
    query_hint_failure_count,
    source_desc
FROM sys.query_store_query_hints
WHERE query_id = 123;


/*==============================================================
  7. Roll back when required
==============================================================*/

EXEC sys.sp_query_store_clear_hints
    @query_id = 123;

Do not execute this script directly in production without:

Replacing the example query_id.
Confirming the target SQL statement.
Capturing a performance baseline.
Reviewing the existing hint.
Preparing a validation plan.
Preparing the rollback command.

Final Thoughts

Query Store is more than a historical performance recorder.

With Query Store Hints, it can also become a controlled mechanism for influencing the compilation behavior of individual queries.

The complete workflow is:

Query Store captures the problem.
The DBA identifies the query.
The DBA applies a targeted hint.
SQL Server compiles the query using the hint.
Query Store measures the result.
The DBA keeps, changes, or removes the hint.

The greatest advantage of Query Store Hints is their ability to address query-level performance problems without immediately changing application code.

Their greatest risk comes from the same capability.

A Query Store Hint can quickly improve a critical query, but it can also enforce behavior that becomes inappropriate as the system changes.

For this reason, every Query Store Hint should be:

Evidence-based.
Carefully tested.
Easy to roll back.
Clearly documented.
Continuously monitored.
Periodically reevaluated.

Query Store Hints should be treated as controlled performance configurations, not forgotten emergency fixes.

Continue the Query Store Series

This article is Part 4 of the Query Store series:

Part 1: Introduction to SQL Server Query Store.
Part 2: Performance Regression Detection and Execution Plan Analysis.
Part 3: Detecting Unstable Execution Plans and Parameter Sniffing.
Part 4: Fixing Query Performance with Query Store Hints.

In the next article, we will examine the most useful Query Store Hints—including RECOMPILE, OPTIMIZE FOR UNKNOWN, MAXDOP, and memory-grant hints—and explain when each one should and should not be used.

SQL Server Parameter Sniffing: Detecting Unstable Execution Plans and Choosing the Right Fix

mohamed Tayel — Mon, 13 Jul 2026 13:05:39 +0000

A stored procedure may execute in milliseconds for one parameter value and take several seconds for another, even though the SQL statement itself has not changed.

One of the most common causes of this behavior in SQL Server is parameter sniffing.

Parameter sniffing is not always a defect. It is a normal SQL Server optimization behavior that often improves performance by allowing the Query Optimizer to create an execution plan based on the parameter values supplied during compilation.

The problem appears when different parameter values require significantly different execution strategies.

For example:

One customer may have five orders.
Another customer may have five million orders.
One execution may benefit from an Index Seek and Nested Loops.
Another may require a scan, parallelism, and a Hash Join.

If SQL Server compiles and caches one plan, then reuses it for both workloads, one of those executions may perform poorly.

This article explains how parameter sniffing works, how to reproduce it, how to identify it using Query Store and execution plans, and how to choose the most appropriate solution.

What Is Parameter Sniffing?

When SQL Server compiles a parameterized query or stored procedure, it examines the parameter values supplied during that compilation.

It uses those values to estimate:

How many rows will be returned
Which indexes should be used
Which join algorithms are appropriate
Whether parallel execution is beneficial
How much memory the query requires
The most efficient order for accessing tables

This behavior is known as parameter sniffing.

Consider the following stored procedure:

CREATE OR ALTER PROCEDURE dbo.GetCustomerOrders
    @CustomerId INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        OrderId,
        CustomerId,
        OrderDate,
        TotalAmount
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId;
END;
GO

The first time SQL Server executes this procedure, it may compile an execution plan using the supplied @CustomerId.

That plan is then stored in the plan cache and may be reused for future executions.

For example:

EXEC dbo.GetCustomerOrders
    @CustomerId = 10;
GO

If customer 10 has only five orders, SQL Server may choose:

Index Seek
    ↓
Key Lookup
    ↓
Nested Loops

That may be an excellent plan for a small number of rows.

Later, the same procedure may be executed for a customer with millions of orders:

EXEC dbo.GetCustomerOrders
    @CustomerId = 5000;
GO

SQL Server may reuse the plan created for customer 10.

The Index Seek and repeated Key Lookups that worked well for five rows may become extremely expensive for millions of rows.

The query has not changed.

The data has not necessarily changed.

The parameter value changed, but SQL Server reused the same cached plan.

Why Parameter Sniffing Is Usually Helpful

Parameter sniffing exists for a good reason.

Without it, SQL Server would have less information when estimating the number of rows returned by a query.

Suppose a table contains the following distribution:

Customer	Number of Orders
Customer 10	5
Customer 20	12
Customer 30	42
Customer 5000	5,000,000

If SQL Server compiles the procedure for customer 10, it can estimate that only a small number of rows will be returned.

That information may allow it to select an efficient Index Seek.

If the same procedure is normally executed for customers with small order volumes, the cached plan will perform well most of the time.

Parameter sniffing becomes a problem when:

Data distribution is highly skewed.
Different parameter values return very different numbers of rows.
The first compiled parameter is not representative of the normal workload.
A cached plan remains in use for a long period.
The query requires different join or index strategies for different parameter groups.
Statistics do not accurately represent the current data distribution.
A plan is compiled during an unusual workload condition.

The issue is therefore not parameter sniffing itself.

The issue is using one execution plan for workloads that require different plans.

Creating a Parameter Sniffing Demo

The following example creates a simplified orders table with heavily skewed data.

Create the Table

DROP TABLE IF EXISTS dbo.Orders;
GO

CREATE TABLE dbo.Orders
(
    OrderId BIGINT IDENTITY(1,1) NOT NULL,
    CustomerId INT NOT NULL,
    OrderDate DATETIME2 NOT NULL,
    TotalAmount DECIMAL(18,2) NOT NULL,

    CONSTRAINT PK_Orders
        PRIMARY KEY CLUSTERED (OrderId)
);
GO

Create an index on CustomerId:

CREATE NONCLUSTERED INDEX IX_Orders_CustomerId
ON dbo.Orders (CustomerId)
INCLUDE
(
    OrderDate,
    TotalAmount
);
GO

Insert Small Customers

The following script creates many customers with a relatively small number of orders:

WITH Numbers AS
(
    SELECT TOP (100000)
        ROW_NUMBER() OVER
        (
            ORDER BY
                (SELECT NULL)
        ) AS NumberValue
    FROM sys.all_objects AS A
    CROSS JOIN sys.all_objects AS B
)
INSERT INTO dbo.Orders
(
    CustomerId,
    OrderDate,
    TotalAmount
)
SELECT
    ((NumberValue - 1) % 1000) + 1,
    DATEADD
    (
        DAY,
        -(NumberValue % 365),
        SYSUTCDATETIME()
    ),
    CAST
    (
        10 + (NumberValue % 500)
        AS DECIMAL(18,2)
    )
FROM Numbers;
GO

Insert a High-Volume Customer

Now create a customer with a much larger number of orders:

WITH Numbers AS
(
    SELECT TOP (1000000)
        ROW_NUMBER() OVER
        (
            ORDER BY
                (SELECT NULL)
        ) AS NumberValue
    FROM sys.all_objects AS A
    CROSS JOIN sys.all_objects AS B
)
INSERT INTO dbo.Orders
(
    CustomerId,
    OrderDate,
    TotalAmount
)
SELECT
    5000,
    DATEADD
    (
        MINUTE,
        -NumberValue,
        SYSUTCDATETIME()
    ),
    CAST
    (
        50 + (NumberValue % 1000)
        AS DECIMAL(18,2)
    )
FROM Numbers;
GO

Update statistics after loading the data:

UPDATE STATISTICS dbo.Orders
WITH FULLSCAN;
GO

Check the data distribution:

SELECT
    CustomerId,
    COUNT_BIG(*) AS OrderCount
FROM dbo.Orders
GROUP BY CustomerId
ORDER BY OrderCount DESC;
GO

The result should show that customer 5000 has significantly more orders than the other customers.

Create the Stored Procedure

CREATE OR ALTER PROCEDURE dbo.GetCustomerOrders
    @CustomerId INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        OrderId,
        CustomerId,
        OrderDate,
        TotalAmount
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId;
END;
GO

Enable performance statistics before testing:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

In SQL Server Management Studio, also enable:

Include Actual Execution Plan

Test 1: Compile the Plan for a Small Customer

Clear the procedure plan from the cache:

EXEC sys.sp_recompile
    N'dbo.GetCustomerOrders';
GO

Execute the procedure for a small customer:

EXEC dbo.GetCustomerOrders
    @CustomerId = 10;
GO

SQL Server may choose a plan based on the assumption that only a small number of rows will be returned.

Possible operators include:

Nonclustered Index Seek
Key Lookup
Nested Loops
Serial execution

This plan may be extremely efficient for customer 10.

Now execute the same procedure for the high-volume customer:

EXEC dbo.GetCustomerOrders
    @CustomerId = 5000;
GO

SQL Server may reuse the plan compiled for customer 10.

The execution may now require:

A very high number of logical reads
Repeated lookups
Increased CPU time
Longer duration
More expensive row-by-row processing

Test 2: Compile the Plan for the Large Customer

Recompile the stored procedure:

EXEC sys.sp_recompile
    N'dbo.GetCustomerOrders';
GO

Execute it first for the large customer:

EXEC dbo.GetCustomerOrders
    @CustomerId = 5000;
GO

SQL Server may now choose a different plan.

Possible characteristics include:

Index Scan or Clustered Index Scan
Parallel execution
Larger memory grants
Different join or access strategies

Now execute the same procedure for a small customer:

EXEC dbo.GetCustomerOrders
    @CustomerId = 10;
GO

The plan compiled for the high-volume customer may be inefficient for a customer that returns only a few rows.

A scan that is reasonable for one million rows may be unnecessary for five rows.

This demonstrates the central problem:

The optimal execution plan depends on the parameter value.

How to Confirm Parameter Sniffing

A slow stored procedure is not automatically a parameter sniffing problem.

You should collect evidence before applying a fix.

Common indicators include:

The same procedure is sometimes fast and sometimes slow.
Performance changes after recompilation.
Performance changes after restarting SQL Server.
Different parameter values produce very different row counts.
The cached plan contains a parameter value that is not representative.
Estimated and actual row counts differ significantly.
Query Store shows multiple plans for the same query.
The execution plan is efficient for one value but inefficient for another.
Clearing or recompiling the plan temporarily resolves the problem.

Inspecting the Compiled Parameter Value

The actual execution plan may show both:

The parameter value used during compilation
The parameter value used during the current execution

In the execution plan properties, you may find information similar to:

Parameter List

Parameter Compiled Value: 10
Parameter Runtime Value: 5000

This is an important clue.

It means the current execution is using a plan compiled for a different parameter value.

However, this alone does not prove that parameter sniffing caused the slowdown.

You must also verify that the plan is inappropriate for the current value.

Checking the Cached Execution Plan

You can inspect cached plans using dynamic management views.

SELECT
    DB_NAME(QueryText.dbid) AS DatabaseName,
    QueryStats.execution_count,
    QueryStats.total_elapsed_time,
    QueryStats.total_worker_time,
    QueryStats.total_logical_reads,
    QueryStats.last_execution_time,
    QueryText.text AS QueryText,
    QueryPlan.query_plan
FROM sys.dm_exec_query_stats AS QueryStats
CROSS APPLY
    sys.dm_exec_sql_text
    (
        QueryStats.sql_handle
    ) AS QueryText
CROSS APPLY
    sys.dm_exec_query_plan
    (
        QueryStats.plan_handle
    ) AS QueryPlan
WHERE QueryText.text LIKE
    N'%GetCustomerOrders%'
ORDER BY
    QueryStats.last_execution_time DESC;
GO

This query can help you review:

Execution count
Total elapsed time
CPU time
Logical reads
Cached plan XML
Last execution time

Keep in mind that plan-cache data is temporary.

It may disappear after:

SQL Server restarts
Plan recompilation
Memory pressure
Plan eviction
Database changes
Manual cache clearing

For historical analysis, Query Store is usually more reliable.

Detecting Parameter-Sensitive Behavior with Query Store

Query Store records:

Query text
Query plans
Runtime statistics
Execution history
Plan changes
Performance over time

This makes it especially valuable for identifying unstable queries.

The following query lists plans captured for a query:

DECLARE @QueryId BIGINT = 123;

SELECT
    QueryPlan.query_id,
    QueryPlan.plan_id,
    QueryPlan.is_forced_plan,
    QueryPlan.count_compiles,
    QueryPlan.first_execution_time,
    QueryPlan.last_execution_time,
    QueryPlan.force_failure_count,
    QueryPlan.last_force_failure_reason_desc,
    QueryPlan.query_plan
FROM sys.query_store_plan AS QueryPlan
WHERE QueryPlan.query_id = @QueryId
ORDER BY QueryPlan.plan_id;
GO

The following query compares plan runtime statistics:

DECLARE @QueryId BIGINT = 123;

SELECT
    QueryPlan.plan_id,
    QueryPlan.is_forced_plan,
    SUM(RuntimeStats.count_executions)
        AS ExecutionCount,
    CAST
    (
        SUM
        (
            RuntimeStats.avg_duration
            * RuntimeStats.count_executions
        )
        / NULLIF
        (
            SUM(RuntimeStats.count_executions),
            0
        )
        AS DECIMAL(18,2)
    ) AS WeightedAverageDurationMicroseconds,
    MAX(RuntimeStats.max_duration)
        AS MaximumDurationMicroseconds,
    CAST
    (
        SUM
        (
            RuntimeStats.avg_cpu_time
            * RuntimeStats.count_executions
        )
        / NULLIF
        (
            SUM(RuntimeStats.count_executions),
            0
        )
        AS DECIMAL(18,2)
    ) AS WeightedAverageCpuMicroseconds,
    CAST
    (
        SUM
        (
            RuntimeStats.avg_logical_io_reads
            * RuntimeStats.count_executions
        )
        / NULLIF
        (
            SUM(RuntimeStats.count_executions),
            0
        )
        AS DECIMAL(18,2)
    ) AS WeightedAverageLogicalReads,
    MAX(RuntimeStats.last_execution_time)
        AS LastExecutionTime
FROM sys.query_store_plan AS QueryPlan
INNER JOIN sys.query_store_runtime_stats AS RuntimeStats
    ON QueryPlan.plan_id = RuntimeStats.plan_id
WHERE QueryPlan.query_id = @QueryId
GROUP BY
    QueryPlan.plan_id,
    QueryPlan.is_forced_plan
ORDER BY
    WeightedAverageDurationMicroseconds;
GO

A parameter-sensitive query may show:

Multiple plans
Large performance differences between plans
Highly variable duration
High maximum duration despite an acceptable average
Different CPU and logical-read profiles

Why Estimated Rows Matter

Execution-plan selection depends heavily on cardinality estimation.

Consider the following values:

Estimated Rows: 10
Actual Rows:    1,000,000

SQL Server selected its plan expecting ten rows.

The actual execution returned one million rows.

This difference can cause SQL Server to choose:

Nested Loops instead of Hash Join
Repeated Key Lookups
A small memory grant
Serial execution
An inefficient join order
Operators that spill to tempdb

The reverse problem can also occur:

Estimated Rows: 1,000,000
Actual Rows:    10

SQL Server may request excessive memory, use parallelism unnecessarily, or scan a large portion of an index for a tiny result set.

Parameter sniffing can therefore affect much more than index selection.

It can influence the entire execution strategy.

Common Solutions for Parameter Sniffing

There is no single solution that is correct for every parameter sniffing problem.

The correct choice depends on:

Query frequency
Data distribution
Compilation cost
Number of parameter patterns
Performance requirements
SQL Server version
Ability to change application code
Acceptable operational risk

The following sections describe the most common approaches.

Solution 1: OPTION (RECOMPILE)

OPTION (RECOMPILE) tells SQL Server to compile a new plan each time the query runs.

CREATE OR ALTER PROCEDURE dbo.GetCustomerOrders
    @CustomerId INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        OrderId,
        CustomerId,
        OrderDate,
        TotalAmount
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId
    OPTION (RECOMPILE);
END;
GO

Each execution is optimized for its current parameter value.

Advantages

Uses the current parameter value.
Avoids reuse of an unsuitable cached plan.
Often effective for highly skewed data.
Simple to implement.
Useful for infrequently executed reporting queries.

Disadvantages

Adds compilation CPU cost.
The plan is not reused.
May be unsuitable for very frequently executed queries.
Reduces the usefulness of some cached-plan statistics.
Does not fix indexing, statistics, or query-design problems.

Good Use Cases

OPTION (RECOMPILE) is often appropriate when:

The query runs relatively infrequently.
Parameter values produce very different result sizes.
Compilation cost is low compared with execution cost.
Accurate row estimates are important.
Query duration is much greater than compile duration.

Solution 2: WITH RECOMPILE

A stored procedure can also be created with WITH RECOMPILE.

CREATE OR ALTER PROCEDURE dbo.GetCustomerOrders
    @CustomerId INT
WITH RECOMPILE
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        OrderId,
        CustomerId,
        OrderDate,
        TotalAmount
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId;
END;
GO

This recompiles the entire procedure for every execution.

This is broader than adding OPTION (RECOMPILE) to one statement.

For procedures containing multiple queries, statement-level recompilation is often more targeted and less expensive.

Solution 3: OPTIMIZE FOR a Specific Value

You can instruct SQL Server to optimize the query for a specific parameter value.

CREATE OR ALTER PROCEDURE dbo.GetCustomerOrders
    @CustomerId INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        OrderId,
        CustomerId,
        OrderDate,
        TotalAmount
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId
    OPTION
    (
        OPTIMIZE FOR
        (
            @CustomerId = 10
        )
    );
END;
GO

Advantages

Creates predictable plan behavior.
May stabilize a workload with one representative parameter.
Avoids compilation on every execution.

Disadvantages

The selected value may become unrepresentative.
Data distribution can change.
One plan may still be unsuitable for other parameter values.
Requires ongoing review.

Good Use Cases

This option may be appropriate when:

One parameter pattern represents most executions.
A known value creates a safe and stable plan.
Occasional slower executions are acceptable.
The data distribution is relatively stable.

Solution 4: OPTIMIZE FOR UNKNOWN

OPTIMIZE FOR UNKNOWN tells SQL Server not to optimize for the supplied parameter value.

Instead, it uses more generalized statistical estimates.

CREATE OR ALTER PROCEDURE dbo.GetCustomerOrders
    @CustomerId INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        OrderId,
        CustomerId,
        OrderDate,
        TotalAmount
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId
    OPTION
    (
        OPTIMIZE FOR UNKNOWN
    );
END;
GO

Advantages

Reduces dependency on the first compiled parameter.
Can provide more predictable performance.
Avoids per-execution recompilation.
Useful when no single parameter is representative.

Disadvantages

Produces a generic plan.
The generic plan may not be optimal for any parameter group.
Can still perform poorly with heavily skewed data.
May reduce performance for both small and large values.

Good Use Cases

This can be useful when:

Most parameter values have similar selectivity.
A stable average plan is acceptable.
The first parameter is frequently unrepresentative.
Consistency is more important than the fastest possible execution.

Solution 5: Use Local Variables

A common workaround is to assign the parameter to a local variable.

CREATE OR ALTER PROCEDURE dbo.GetCustomerOrders
    @CustomerId INT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @LocalCustomerId INT;

    SET @LocalCustomerId = @CustomerId;

    SELECT
        OrderId,
        CustomerId,
        OrderDate,
        TotalAmount
    FROM dbo.Orders
    WHERE CustomerId = @LocalCustomerId;
END;
GO

This can prevent SQL Server from using the original parameter value during optimization.

However, it often produces behavior similar to OPTIMIZE FOR UNKNOWN.

Advantages

Simple to implement.
May reduce instability caused by an unusual compiled value.

Disadvantages

Hides useful information from the optimizer.
May produce inaccurate estimates.
Often results in a generic plan.
Can create a different performance problem.
Usually not the preferred long-term solution.

Local variables should not be used automatically as a standard fix.

They should be tested against realistic workloads.

Solution 6: Dynamic SQL

Dynamic SQL can create separate cached plans for different query shapes or conditions.

CREATE OR ALTER PROCEDURE dbo.GetCustomerOrders
    @CustomerId INT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @SqlCommand NVARCHAR(MAX);

    SET @SqlCommand =
        N'
        SELECT
            OrderId,
            CustomerId,
            OrderDate,
            TotalAmount
        FROM dbo.Orders
        WHERE CustomerId = @CustomerId;
        ';

    EXEC sys.sp_executesql
        @SqlCommand,
        N'@CustomerId INT',
        @CustomerId = @CustomerId;
END;
GO

Simply converting a query to dynamic SQL does not automatically solve parameter sniffing.

However, dynamic SQL is useful when you intentionally generate different query forms for different parameter patterns.

For example:

CREATE OR ALTER PROCEDURE dbo.GetCustomerOrders
    @CustomerId INT
AS
BEGIN
    SET NOCOUNT ON;

    IF @CustomerId = 5000
    BEGIN
        SELECT
            OrderId,
            CustomerId,
            OrderDate,
            TotalAmount
        FROM dbo.Orders
        WHERE CustomerId = @CustomerId
        OPTION (RECOMPILE);

        RETURN;
    END;

    SELECT
        OrderId,
        CustomerId,
        OrderDate,
        TotalAmount
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId;
END;
GO

Different branches can produce separate cached statements and execution plans.

Advantages

Allows different strategies for different parameter groups.
Useful for optional search filters.
Can avoid one-size-fits-all plans.
Supports targeted query generation.

Disadvantages

Adds code complexity.
Requires careful parameterization.
Poor implementation may create SQL injection risk.
Can create excessive plan-cache entries.
Requires more testing and maintenance.

Always use sp_executesql and parameters rather than concatenating untrusted values into SQL strings.

Solution 7: Separate Procedures or Query Branches

When the workload naturally contains distinct parameter groups, separate procedures or branches may be clearer.

For example:

CREATE OR ALTER PROCEDURE dbo.GetSmallCustomerOrders
    @CustomerId INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        OrderId,
        CustomerId,
        OrderDate,
        TotalAmount
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId;
END;
GO

CREATE OR ALTER PROCEDURE dbo.GetLargeCustomerOrders
    @CustomerId INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        OrderId,
        CustomerId,
        OrderDate,
        TotalAmount
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId
    OPTION (RECOMPILE);
END;
GO

Application or procedure logic can route requests to the appropriate path.

Advantages

Clear separation of workload patterns.
Allows independent tuning.
Easier to test each workload type.
Supports different indexes, hints, or strategies.

Disadvantages

Increases code and maintenance.
Requires reliable classification logic.
Classification thresholds may change as data grows.
Adds application or procedural complexity.

Solution 8: Query Store Plan Forcing

If Query Store contains a stable historical plan, it can be forced.

EXEC sys.sp_query_store_force_plan
    @query_id = 123,
    @plan_id = 456;
GO

Advantages

Fast production stabilization.
Does not require application-code changes.
Easy to reverse.
Uses a previously captured execution plan.

Disadvantages

One forced plan may not suit all parameters.
It may hide the parameter-sensitive nature of the query.
The plan may become unsuitable after data growth.
Plan forcing may fail after schema or index changes.
It is often a mitigation rather than a permanent fix.

Plan forcing is appropriate when one plan is consistently acceptable across the workload.

It is risky when small and large parameters genuinely require different plans.

Solution 9: Query Store Hints

Query Store hints can apply supported query hints without modifying application code.

For example, a Query Store hint can apply OPTION (RECOMPILE):

EXEC sys.sp_query_store_set_hints
    @query_id = 123,
    @query_hints = N'OPTION(RECOMPILE)';
GO

Or apply OPTIMIZE FOR UNKNOWN:

EXEC sys.sp_query_store_set_hints
    @query_id = 123,
    @query_hints =
        N'OPTION(OPTIMIZE FOR UNKNOWN)';
GO

To remove the hint:

EXEC sys.sp_query_store_clear_hints
    @query_id = 123;
GO

Advantages

Does not require changing application code.
Easy to apply and reverse.
Useful during production incidents.
Can support controlled testing.

Disadvantages

Still requires careful monitoring.
Not every query hint is supported.
Can be forgotten if not documented.
May become inappropriate after workload changes.

Solution 10: Parameter Sensitive Plan Optimization

Modern SQL Server versions can support Parameter Sensitive Plan Optimization, commonly referred to as PSP optimization.

Instead of using one plan for every parameter value, SQL Server may maintain multiple plan variants for different parameter ranges.

Conceptually, SQL Server can create:

One plan for low-selectivity values
Another plan for medium-selectivity values
Another plan for high-selectivity values

For example:

Customer with 5 orders
    → Small-result plan

Customer with 50,000 orders
    → Medium-result plan

Customer with 5,000,000 orders
    → Large-result plan

This directly addresses the one-plan-fits-all limitation.

Dispatcher Plan

PSP optimization can use a dispatcher expression to determine which plan variant should be selected for the current parameter value.

The dispatcher routes the query to the appropriate plan based on parameter boundaries derived from statistics.

Advantages

Supports multiple plans for one parameterized query.
Reduces the need for manual recompilation.
Improves performance for skewed data.
Preserves plan reuse.
Handles different parameter groups automatically.

Considerations

PSP optimization is not a replacement for:

Accurate statistics
Appropriate indexing
Good query design
Query Store monitoring
Workload testing

It may also not apply to every query or every parameter-sensitive scenario.

Why Clearing the Plan Cache Is Not a Real Fix

During troubleshooting, administrators may run commands such as:

DBCC FREEPROCCACHE;
GO

This may temporarily improve performance because SQL Server compiles a new plan.

However, clearing the entire plan cache is not a good production solution.

It affects every cached plan on the SQL Server instance and may cause:

A compilation storm
Increased CPU usage
Temporary performance degradation
Loss of useful cached plans
Unpredictable workload behavior

Even clearing one procedure plan is only a troubleshooting step:

EXEC sys.sp_recompile
    N'dbo.GetCustomerOrders';
GO

The new plan may again be compiled for an unrepresentative parameter.

Recompilation proves that plan selection affects performance, but it does not necessarily resolve the underlying design problem.

Why Updating Statistics May Help

Parameter sniffing issues are often made worse by outdated or sampled statistics.

Update the relevant statistics:

UPDATE STATISTICS dbo.Orders
WITH FULLSCAN;
GO

Or update one statistic:

UPDATE STATISTICS dbo.Orders
    IX_Orders_CustomerId
WITH FULLSCAN;
GO

Improved statistics can help SQL Server better understand data distribution.

However, updating statistics may also cause recompilation.

The newly compiled plan could be better or worse depending on the next parameter value.

Statistics updates are therefore important, but they are not always a complete solution for highly skewed workloads.

Indexing and Parameter Sniffing

An indexing problem can look like a parameter sniffing problem.

For example, this index:

CREATE NONCLUSTERED INDEX IX_Orders_CustomerId
ON dbo.Orders (CustomerId);
GO

may require Key Lookups for:

SELECT
    OrderId,
    CustomerId,
    OrderDate,
    TotalAmount
FROM dbo.Orders
WHERE CustomerId = @CustomerId;

For five rows, the Key Lookup may be inexpensive.

For one million rows, it may be extremely costly.

A covering index may help:

CREATE NONCLUSTERED INDEX IX_Orders_CustomerId_Covering
ON dbo.Orders (CustomerId)
INCLUDE
(
    OrderDate,
    TotalAmount
);
GO

However, indexes have costs:

Additional storage
Increased write overhead
More maintenance
Longer index-rebuild operations
Potential plan changes

The correct solution may involve both plan management and index improvement.

Parameter Sniffing and Optional Search Parameters

Search procedures with optional filters are especially vulnerable to unstable plans.

Consider:

CREATE OR ALTER PROCEDURE dbo.SearchOrders
    @CustomerId INT = NULL,
    @StartDate DATE = NULL,
    @EndDate DATE = NULL
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        OrderId,
        CustomerId,
        OrderDate,
        TotalAmount
    FROM dbo.Orders
    WHERE
        (
            @CustomerId IS NULL
            OR CustomerId = @CustomerId
        )
        AND
        (
            @StartDate IS NULL
            OR OrderDate >= @StartDate
        )
        AND
        (
            @EndDate IS NULL
            OR OrderDate < DATEADD
            (
                DAY,
                1,
                @EndDate
            )
        );
END;
GO

Different parameter combinations may require completely different plans.

Examples include:

EXEC dbo.SearchOrders
    @CustomerId = 10;

EXEC dbo.SearchOrders
    @StartDate = '2026-01-01',
    @EndDate = '2026-12-31';

EXEC dbo.SearchOrders;

The first execution may require a selective Index Seek.

The second may require a date-range scan.

The third may return the entire table.

One cached plan is unlikely to be optimal for all combinations.

Dynamic SQL or separate query branches are often more appropriate for this design.

How to Choose the Correct Fix

Use the following guidance as a starting point.

Scenario	Possible Approach
Query runs infrequently and parameters vary greatly	`OPTION (RECOMPILE)`
One parameter value represents most executions	`OPTIMIZE FOR`
Generic stable performance is acceptable	`OPTIMIZE FOR UNKNOWN`
Distinct parameter groups require different logic	Separate branches or procedures
Optional search filters create many query shapes	Parameterized dynamic SQL
Application code cannot be changed	Query Store hints
A stable historical plan works for all values	Query Store plan forcing
Modern SQL Server with eligible parameter-sensitive query	PSP optimization
Estimates are wrong because of stale statistics	Update statistics
Key Lookups or scans are inherently expensive	Review indexes and query design

The correct choice should be based on measured performance rather than preference.

Recommended Investigation Process

A disciplined parameter sniffing investigation should follow these steps.

1. Confirm the Performance Variation

Collect:

Duration
CPU time
Logical reads
Physical reads
Execution count
Parameter values
Execution times

Do not assume parameter sniffing based only on one slow execution.

2. Compare Parameter Values

Identify whether slow and fast executions use different parameter patterns.

Determine whether those parameters return significantly different row counts.

3. Review the Execution Plan

Check:

Compiled parameter value
Runtime parameter value
Estimated rows
Actual rows
Index access
Join types
Memory grant
Parallelism
Spills
Key Lookups

4. Test Recompilation

Recompile the query or procedure and test different parameter values.

If performance changes depending on which parameter is used during compilation, parameter sensitivity is likely.

5. Review Query Store

Check whether the query has:

Multiple plans
Plan regressions
Unstable duration
Different CPU profiles
Different logical-read profiles

6. Test Candidate Solutions

Test solutions using representative values:

Small-result parameter
Medium-result parameter
Large-result parameter
Common parameter
Rare parameter
Peak concurrency

7. Measure the Overall Workload

Do not optimize one execution while making the wider workload worse.

Review:

Compilation CPU
Throughput
Blocking
Memory usage
Plan-cache usage
tempdb impact
Concurrent executions

8. Document the Decision

Record:

Affected procedure or query
Root cause
Parameter patterns
Baseline measurements
Selected solution
Deployment date
Rollback method
Review date

Production Checklist

Before applying a parameter sniffing fix:

Confirm that performance varies by parameter value.
Capture actual execution plans.
Review compiled and runtime parameter values.
Compare estimated and actual rows.
Verify statistics freshness.
Review relevant indexes.
Test several representative parameters.
Check Query Store history.
Measure compilation cost.
Define rollback steps.

After applying the fix:

Monitor duration and CPU time.
Monitor logical reads.
Check Query Store for new regressions.
Review execution count and plan usage.
Watch for compilation overhead.
Check memory grants and tempdb spills.
Validate application timeout rates.
Review the solution after data growth or deployment changes.

Common Mistakes

Mistake 1: Assuming Every Slow Procedure Has Parameter Sniffing

Slow performance may instead be caused by:

Missing indexes
Blocking
Outdated statistics
Storage latency
Memory pressure
Poor query design
Implicit conversions
tempdb contention

Parameter sniffing should be proven, not guessed.

Mistake 2: Clearing the Entire Plan Cache

This can cause server-wide instability and does not prevent the issue from returning.

Mistake 3: Forcing the Fastest-Looking Plan

A plan that is fast for one parameter may be poor for another.

Stability across representative values matters more than one successful test.

Mistake 4: Using Local Variables Automatically

Local variables may hide useful parameter information and create generic estimates.

Mistake 5: Ignoring Execution Count

A plan executed once should not automatically be preferred over a plan with thousands of stable executions.

Mistake 6: Treating Plan Forcing as Permanent

Forced plans require monitoring and periodic review.

Mistake 7: Testing with Only One Parameter

Parameter-sensitive queries must be tested using multiple data distributions.

Final Example: A Controlled Branching Strategy

Suppose customer 5000 represents a special high-volume account.

The procedure can route that workload separately:

CREATE OR ALTER PROCEDURE dbo.GetCustomerOrders
    @CustomerId INT
AS
BEGIN
    SET NOCOUNT ON;

    IF @CustomerId = 5000
    BEGIN
        SELECT
            OrderId,
            CustomerId,
            OrderDate,
            TotalAmount
        FROM dbo.Orders
        WHERE CustomerId = @CustomerId
        OPTION (RECOMPILE);

        RETURN;
    END;

    SELECT
        OrderId,
        CustomerId,
        OrderDate,
        TotalAmount
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId;
END;
GO

This is only an example.

In a real system, the routing decision should not normally depend on one hard-coded customer.

A better design may classify workloads based on:

Customer category
Estimated data volume
Tenant size
Date-range size
Business rules
Configuration data

The main idea is that clearly different workloads may deserve clearly different execution strategies.

Conclusion

Parameter sniffing is a normal SQL Server behavior that becomes problematic when one cached execution plan is reused across parameter values with significantly different data distributions.

The typical pattern is:

SQL Server compiles a plan using the first parameter value.
The plan is cached.
Future executions reuse the plan.
A different parameter returns a very different number of rows.
The reused plan becomes inefficient.

A successful investigation should focus on evidence:

Compiled and runtime parameter values
Estimated and actual rows
Query Store plan history
Logical reads
CPU time
Execution duration
Data distribution
Plan stability across multiple parameters

Possible solutions include:

OPTION (RECOMPILE)
WITH RECOMPILE
OPTIMIZE FOR
OPTIMIZE FOR UNKNOWN
Dynamic SQL
Separate procedure branches
Query Store plan forcing
Query Store hints
Parameter Sensitive Plan Optimization
Statistics and index improvements

The best solution is not the one that makes one execution fastest.

The best solution is the one that delivers predictable, maintainable, and resource-efficient performance across the real production workload.

Parameter sniffing should therefore be treated as a workload-design and plan-stability problem, not simply as a bad-plan problem.

Distributed Systems

mohamed Tayel — Tue, 19 May 2026 10:33:05 +0000

tags: distributedsystems, beginners, architecture, webdev
series: Fundamentals of Distributed Systems
cover_image:

You built a small web app. It runs on one machine. It works.

Then traffic grows. Or you hear someone in a meeting say "microservices." Or your boss talks about scaling. And suddenly there's a whole new world of words: replication, sharding, consensus, CAP theorem.

This series is for that moment. We'll go through it slowly, with simple pictures and everyday examples — no fancy terms, no scary code. By the end you'll understand what people mean by "distributed systems" and why everyone makes such a big deal about them.

🙏 Credit where it's due — This series is my attempt to internalize and share what I learned from Mahmoud Youssef's excellent course, Fundamentals of Distributed Systems on Udemy. The course material, structure, and topic flow are his work; the explanations, code examples, and diagrams in these articles are my own rewrite in my own words. If you find this useful, please consider taking the course — it goes much deeper than these articles can.

This first article is the map: what changes when you go from one machine to many, and the six big challenges you'll meet along the way.

Let's start.

So… what is a distributed system?

Here's the simple version:

A distributed system is a group of computers working together so the user thinks it's just one computer.

That's it. That's the whole idea.

Think about ordering food on an app. You tap a button. Two hundred milliseconds later you see "Order placed."

In those 200 milliseconds, a lot happened:

A server picked you out of millions of users
A payment system charged your card (maybe from a different country)
The order was saved on multiple computers
The restaurant's screen got a notification
Your phone got a confirmation

Many machines just had a tiny conversation to make your single tap work. That conversation is the hard part.

Why bother with multiple machines?

One machine is simpler. So why does anyone leave?

1. Growth. Every machine has a limit. The best CPU, the most RAM — there's always a ceiling. When your traffic passes that ceiling, you have no choice but to add more machines.

2. Safety. Machines die. Hard drives fail, power goes out, someone trips on a cable. If everything lives on one machine, when it dies, your app dies. With many machines, the others keep going.

3. Speed for everyone. A user in Japan asking a server in the US waits a long time — light takes time to travel. To feel "instant" worldwide, you need machines close to people.

That's the trade-off. You get growth, safety, and speed. In return, you inherit every problem we're about to talk about. Going distributed is not free.

Now, the six challenges.

Challenge 1: The network can't be trusted

Inside one computer, calling code is like talking to someone in the same room. You speak, they hear. Simple.

The moment two computers need to talk over a network, it's like texting. And texts can:

Get lost — never arrive at all
Arrive late — 5 seconds after you gave up waiting
Arrive twice — the same message appears two times
Arrive in the wrong order — your second message reaches them first

This isn't theoretical. There's actually a famous puzzle called the Two Generals Problem that proves something brutal: when two parties talk over an unreliable channel, neither can ever be 100% sure the other got the message. Not "hard" — actually impossible.

So when your code talks to another machine, you have to plan for the network lying to you. Every time.

Challenge 2: Many copies, one truth

When you have many machines, you usually have many copies of the same data. That's good — if one machine dies, the others still have it.

But it creates a new problem: how do you make sure all the copies agree?

Imagine a teacher saying "everyone write X equals 5 in your notebook." If one student is daydreaming, their notebook is wrong. Now whoever reads from that student gets the wrong answer.

The standard trick is simple:

One machine is the boss. Everybody listens to the boss.
The boss sends the change to everyone else.
The boss waits until most of them confirm they got it.
Only then does the boss say "done."

That "most of them" rule (called a majority or quorum) is the magic. It guarantees that even if some machines crash or miss the message, the system still knows what the truth is.

You're already using this without knowing — every modern database does it.

Challenge 3: How do you grow?

When traffic grows, you have two choices.

Option A: Make the machine bigger. Stronger CPU, more memory, faster disk. Your code doesn't change at all. Easy.

The problem? There's a biggest machine on Earth, and one day you'll hit it. Plus, it's still one machine — when it dies, everything dies with it.

Option B: Add more machines. Split the work across many.

The good news: almost no limit. One machine dies? The others keep going.

The bad news: now your code has to deal with all the headaches from this article. Coordination, copying data, splitting work, network problems — they all become real.

Most real systems do a bit of both: bigger machines where it makes sense, more machines for everything else.

Challenge 4: Some parts get hammered, others nap

When your data outgrows one machine, you split it across many. This is called partitioning (or sharding — same thing, different name).

It works beautifully — until traffic isn't evenly spread.

Imagine a supermarket with three cashiers. Sounds great, right? Now imagine every customer for some reason chose Cashier #2. Cashier #1 is reading a book. Cashier #3 is looking at the ceiling. Cashier #2 has a line out the door.

The total work isn't too much. It's just stuck in one place.

This is super common in real systems. One viral post, one huge customer, one trending topic — and suddenly one machine is melting while the rest are bored. The problem isn't more traffic. It's uneven traffic.

Fixing it is its own art — we'll spend a whole article on it later.

Challenge 5: Testing is genuinely harder

In a small app, when something breaks, you can usually figure it out. Open the logs, find the error, fix it.

In a distributed system, bugs are sneaky. They hide. They show up:

Only when many users hit the app at once
Only on one specific machine
Only at 3 AM during a holiday
Only when the network is slow that day

To catch them before they reach real users, we test on three levels:

Many small tests that check tiny pieces of code. Cheap and fast.
Some medium tests that check whether pieces work together. A bit slower.
A few big tests that try the whole app like a real user would. Slow and expensive.

The trick is having lots of small ones and very few big ones. The opposite (lots of big tests) makes everything slow and people stop running them.

Challenge 6: You can't fix what you can't see

Writing code is half the work. The other half is keeping it running 24/7 across all those machines.

For that, you need three things:

Logs — a written record of what happened. "User clicked button," "Database returned error," "Payment failed." Like a diary.

Numbers — how the system is doing right now. How fast? How busy? How many errors? Like a car dashboard.

Alerts — a phone call when something breaks. Because if nobody's watching, the numbers don't matter.

The basic rule of running things: if you can't see it, you can't fix it.

Quick recap

Six challenges. None go away, all are solvable.

#	Challenge	The short version
1	Network	Messages can be lost, late, doubled, or out of order.
2	Copies	Many copies of data must agree.
3	Growth	Bigger machine vs. more machines.
4	Uneven load	Splitting only works if traffic splits evenly too.
5	Testing	Bugs hide in timing. Use small, medium, and big tests.
6	Watching	You need logs, numbers, and alerts.

The honest truth: distributed systems aren't magic. They're a list of trade-offs. Once you see the trade-offs clearly, the choices start making sense.

What's next

Next article we'll dig into the first piece of the puzzle: how services talk to each other. Sometimes they wait for an answer (like a phone call). Sometimes they leave a message and move on (like a voicemail). When to use which? That's part 2.

After that: how data is stored, what "eventually consistent" means, how to keep things running when machines fail, and how to actually watch all of this in real time.

If anything here felt unclear — perfect. That means we have something good to dig into next. Drop a comment with what you want explored first.

See you in part 2. 👋

Follow the series — every article is simple language, lots of pictures, no scary code.

Key Components of Semantic Kernel

mohamed Tayel — Wed, 10 Sep 2025 11:15:50 +0000

Now that we know what Semantic Kernel is all about, it’s time to explore its key components. The Semantic Kernel library is built around several building blocks that can be used independently or in combination, depending on your application’s needs.

At a high level, the components include:

The Kernel
Services
Plugins
Functions
Prompts and prompt templates
Memory
Filters

Let’s go step by step.

1. The Kernel – The Orchestrator

As the name suggests, the kernel is the central component of Semantic Kernel. It acts as an orchestrator between your application and AI models.

Key responsibilities include:

Managing services and plugins – AI models, logging mechanisms, and other integrations are registered here.
Dependency injection container – The kernel holds everything your app needs (services, plugins, utilities) and makes them accessible at runtime.
Orchestrating tasks – Whether it’s a prompt execution, API call, or workflow, the kernel ensures the right components are available and used efficiently.

Getting started with the kernel is straightforward:

Install the NuGet package – Since Semantic Kernel is a .NET library, you can add it via NuGet.
Choose an AI model – It supports OpenAI GPT, Azure OpenAI, and other providers. Depending on your use case, you might need text completion, chat models, or image generation.
Configure API keys – Each provider requires authentication. Your API key ensures secure communication between your app and the AI service.

Once the kernel is set up, you’re ready to extend it with services and plugins.

2. Services – Beyond Just AI Models

Services represent the capabilities your kernel can use.

AI models – Examples include chat completion, text generation, image creation, or speech-to-text.
Utilities – Logging, HTTP clients, or custom application services.

This design follows the .NET service provider pattern, making Semantic Kernel feel natural for developers already familiar with dependency injection.

3. Plugins – Extending Functionality

While services bring in external capabilities, plugins allow you to extend Semantic Kernel’s functionality with custom logic.

Examples include:

Fetching live data from an external API.
Retrieving records from a database.
Triggering workflows inside your application.

With plugins, Semantic Kernel becomes more than just a bridge to AI—it becomes a hub where AI and business logic meet.

4. Functions, Prompts, and Templates

A function in Semantic Kernel represents a single task—like asking an LLM to summarize text or translate a sentence. Functions can be packaged into plugins or used standalone.

To power these functions, we rely on prompts and prompt templates:

Prompts: Natural language instructions given to the model.
Prompt templates: Reusable structures where parameters can be dynamically inserted.

This modularity makes prompts flexible, reusable, and maintainable across your application.

5. Memory

Semantic Kernel includes memory components, allowing you to persist context between interactions. This enables experiences like:

Personalized conversations.
Context-aware responses.
Long-term knowledge retention.

6. Filters

Filters act as middleware for AI operations. They allow you to inspect, validate, or transform requests and responses before they are executed or returned.

Putting It All Together – Example

Once you initialize the kernel, you can register services like this:

var kernel = Kernel.CreateBuilder()
    .AddOpenAIChatCompletion("gpt-4", apiKey)
    .Build();

Now, you can build a simple console chatbot:

while (true)
{
    Console.Write("User: ");
    var input = Console.ReadLine();

    var response = await kernel.InvokePromptAsync(input);
    Console.WriteLine("AI: " + response);
}

This tiny app shows the power of Semantic Kernel: a few lines of code, and you have a working AI-powered chatbot.

And remember—you’re not limited to text. Semantic Kernel can integrate:

Text-to-image: Generate images from descriptions.
Image-to-text: Describe images (e.g., anomaly detection).
Text-to-audio: Turn text into speech.
Audio-to-text: Transcribe speech into text.

Conclusion

The key components of Semantic Kernel—kernel, services, plugins, functions, prompts, memory, and filters—work together to make AI integration into .NET apps both powerful and flexible.

By combining these elements, you can build applications that not only interact with AI but also integrate seamlessly with your business logic, external systems, and real-world workflows.

This is only the beginning. As you continue exploring, you’ll see how these components unlock new possibilities in AI-powered application design.

Understanding Semantic Kernel

mohamed Tayel — Wed, 10 Sep 2025 10:26:01 +0000

Artificial Intelligence (AI) is no longer optional—it’s becoming a core part of modern applications. From natural language chat to image generation, developers are looking for ways to seamlessly integrate AI into their apps. But if you’ve tried, you probably know how challenging it can be.

Different AI providers (OpenAI, Azure AI, Anthropic’s Claude, and others) all have their own APIs, request formats, authentication rules, and response handling. Switching between providers often means rewriting large parts of your codebase.

That’s where Semantic Kernel comes in.

What Is Semantic Kernel?

Semantic Kernel is an open-source SDK created by Microsoft that makes it easier to embed AI into your .NET applications. At its core, it’s a middleware library that abstracts away the complexities of working with different AI models.

Think of it as a bridge between your app and modern AI providers. Instead of learning each API separately, you only need to learn Semantic Kernel once—and you’ll be able to plug in multiple AI services with minimal changes.

And here’s the best part: while Semantic Kernel is primarily designed for C# and .NET, it also supports Java and Python, giving teams flexibility across different tech stacks.

Why Semantic Kernel Matters

Without Semantic Kernel:

Each AI provider requires custom integration.
Switching providers means rewriting code.
Your app becomes tightly coupled to one AI service.

With Semantic Kernel:

You interact with AI models in a standardized way.
Switching from OpenAI to Claude (or vice versa) takes just a configuration change.
You can extend and customize easily through plugins.

Core Features of Semantic Kernel

Semantic Kernel provides out-of-the-box support for many AI-powered tasks:

Text Generation – Create summaries, content, or natural language output.
Chat Completion – Power chatbots and conversational apps.
Image Generation – Generate or edit images through AI models.
Plugins – Extend functionality with custom logic.
Memory and Logging – Manage contextual memory and monitor execution.

These features are all available through a unified API, so you don’t need to worry about provider-specific details.

The Three Big Benefits

When you start working with Semantic Kernel, three advantages quickly become clear:

Abstraction

You don’t need to learn every AI provider’s quirks.
Semantic Kernel gives you a higher-level, unified way to work with models.

Integration

Core AI tasks like text, chat, and images are already supported.
You can start building quickly without reinventing the wheel.

Extensibility

Add your own plugins or connect new AI services.
Designed to grow with future AI advancements.

A Practical Path

The best way to get started with Semantic Kernel is simple:

Begin with basic console applications to learn the principles.
Explore its core functionalities like text, chat, and plugins.
Move on to real applications and advanced concepts like agents, memory, and logging.

By the end of that journey, you’ll have a solid understanding of Semantic Kernel and how to integrate it effectively into your .NET projects.

Wrapping Up

Semantic Kernel provides a powerful, flexible, and extensible foundation for bringing AI into your .NET applications. Whether you’re building intelligent chatbots, automating workflows, or experimenting with image generation, this library makes integration faster and easier—while keeping your options open for the future.

With abstraction, integration, and extensibility at its core, Semantic Kernel lets you focus on building great apps instead of wrestling with provider-specific details.

If you’re ready to explore the future of AI-powered .NET development, Semantic Kernel is the perfect place to start.

Generative AI: What It Is, How It Works, and Why It Matters

mohamed Tayel — Mon, 19 May 2025 12:08:46 +0000

📌 Introduction: The Shocking Microsoft Decision

In a surprising move, Microsoft announced the layoff of thousands of employees across different departments, stating that many tasks previously performed by humans could now be handled more efficiently using generative AI technologies.

This wasn't just a cost-cutting measure—it signaled a major shift in how companies around the world are thinking: how can modern technology be used to reduce expenses and boost productivity?

This event made me ask a simple but important question:

"What exactly is AI? And can it really replace humans?"

In this article, I’ll walk you through the answers in a simple, clear way, and explain:

What AI is and its types
How it works
Why it’s both powerful and risky

🤖 What’s the Difference Between AI and AGI?

AI (Artificial Intelligence) is any system that can perform "intelligent" tasks such as analyzing data, generating content, or understanding speech.
AGI (Artificial General Intelligence) is the dream of many scientists: a system that can think, learn, and act like a human across various domains.

Right now, what we have is narrow AI, such as:

ChatGPT (understands and responds to questions)
DALL·E (generates images)
Copilot (assists in writing code)

AGI doesn’t exist yet.

🧠 How Does AI Work?

Traditional programming uses explicit instructions: “If X happens, do Y.”

But with Machine Learning, we teach the computer using data and examples.

Example:
If you want a program to recognize the handwritten number 3, you don’t write all the rules manually. Instead, you provide thousands of examples of the number 3, and the model learns the pattern and applies it to new images.

That’s how most modern AI learns—by example.

🎨 What Is Generative AI?

Generative AI is the type of AI that creates new content like text, images, or even code.

Popular tools include:

ChatGPT → generates text
DALL·E → creates images
Copilot → suggests code

It works like a massively advanced version of autocomplete.

Example:
"It’s going to be a beautiful..." → it likely predicts "day" based on the most common usage in its training data.

But this also leads to a big problem...

⚠️ What Are AI Hallucinations?

Sometimes AI makes up facts that sound believable but are entirely false.

Example:
I asked ChatGPT to write a blog post about the metaverse, and it mentioned a device called the “DreamView XR‑2023” with detailed specs.
That device doesn’t exist!

This is called a "hallucination"—the model creates content based on patterns, not facts.

That’s why you should always:

Verify important information
Never rely solely on AI for critical facts

✍️ How to Write Better Prompts (Prompt Engineering)

AI responds based on your input.

Instead of saying:
"Write me a thank-you email."

Say:
"Write a short, friendly thank-you email to my manager after a successful project."

The more specific you are, the better the results.

⚖️ Who’s Responsible? The Ethics of AI

Models can learn biases from old data (e.g., favoring men in hiring)
Some models give results without clear reasoning (the “black box” issue)
Deepfake technology can create dangerously realistic fake videos

This raises serious questions:

Who is accountable when AI gets it wrong?
How can we ensure transparency?

That’s why organizations like:

OpenAI
IEEE Ethics
Partnership on AI

...are working to establish ethical guidelines for AI.

🧭 Are We Ready?

AI isn’t coming—it’s already here.
And Microsoft won’t be the last to make bold decisions based on it.

The future belongs to those who:

Understand AI
Know how to use it wisely
And know how to protect themselves from its risks

Start today. Learn. Be ready for what’s next.

Singleton vs. Monostate in C#: Understand the Difference with a Real-World Story and Code Examples

mohamed Tayel — Tue, 06 May 2025 11:27:10 +0000

Learn the difference between Singleton and Monostate design patterns in C# through a simple real-world story. Discover when to use each, how they work, and see full code examples to help you write cleaner, more maintainable code.

When designing applications in C#, we sometimes need to ensure that a class behaves in a global or shared way — whether that means only one instance exists, or that all instances share the same state.

Two popular design patterns for this are the Singleton and the Monostate. They might seem similar at first, but they’re actually quite different under the hood. Let’s explore both using a story you’ll never forget.

🏢 The Singleton Story: "One Manager for the Entire Office"

Imagine you work in a big company. There’s only one office manager, and her name is Sarah.

Everyone in the company talks to Sarah when they need:

Time off approvals
Issue resolutions
General help

It doesn’t matter if you’re on the first or tenth floor — there’s just one Sarah, and she’s the only one you deal with. Even if someone tries to "create a new manager," the system will always redirect to the same Sarah.

That’s how the Singleton Pattern works in C#.

✅ Singleton Characteristics

Only one instance exists across the entire application.
Controlled through a static property.
Used when only one shared object is needed (like configuration, logging, etc.).

🧪 Singleton in C\

public class OfficeManager
{
    private static readonly OfficeManager _instance = new OfficeManager();

    private OfficeManager() { }

    public static OfficeManager Instance => _instance;

    public void ApproveLeave() => Console.WriteLine("Leave approved by Sarah.");
}

Usage:

OfficeManager.Instance.ApproveLeave();

🏠 The Monostate Story: "Many Receptionists, One Phone Line"

Now picture a different scenario: A company has many receptionists at different desks.

Here’s the catch: They all share the same internal phone line.

So even if you speak to Receptionist A or Receptionist B, your message will go through the same shared communication system. It’s like they all have different faces, but the same brain for phone handling.

That’s the Monostate Pattern.

✅ Monostate Characteristics

You can create multiple instances, but they all share the same state.
Achieved using static fields in the class.
Looks like a normal class, but behaves like a Singleton.

🧪 Monostate in C\

public class Receptionist
{
    private static string _sharedPhoneLine;

    public string PhoneLine
    {
        get => _sharedPhoneLine;
        set => _sharedPhoneLine = value;
    }
}

Usage:

var receptionist1 = new Receptionist();
receptionist1.PhoneLine = "Extension 101";

var receptionist2 = new Receptionist();
Console.WriteLine(receptionist2.PhoneLine); // Output: Extension 101

Even though receptionist2 is a different instance, it sees the same phone line!

📊 Singleton vs. Monostate: Key Differences

Feature	Singleton	Monostate
Instance Count	Only one instance	Multiple instances
State Sharing	Instance-level	Shared static state
Flexibility	Rigid (can't inherit or test easily)	More flexible and test-friendly
Appearance	Feels like a global service	Feels like a regular object
Use Case	Configs, Logging, Caching	Shared state without limiting instances

🎯 When to Use Each?

✅ Use Singleton when:
- You want a single, global object (like a logger or configuration manager).
- You need to enforce that only one instance can ever exist.
✅ Use Monostate when:
- You want multiple instances that share the same state.
- You care about testability, inheritance, or flexibility.

🧠 Final Thoughts

Both Singleton and Monostate aim to provide shared behavior — but they approach it differently.

If you like to control instance creation, Singleton is your choice.

If you prefer to keep the illusion of multiple objects, while maintaining shared state, Monostate fits the bill.

Specification Pattern P3

mohamed Tayel — Sun, 02 Feb 2025 20:38:21 +0000

🔹 Why Use the Specification Pattern?

❌ Problem Without It

Our Generic Repository is limited to basic CRUD.
We cannot filter products dynamically (e.g., Get all Nike products).
We cannot sort (e.g., Order by Price).
We cannot paginate (e.g., 10 products per page).

✅ Solution: Use the Specification Pattern

The Specification Pattern allows us to encapsulate filtering logic in reusable objects.
It makes our repository flexible, reusable, and scalable.
It avoids repository bloat by keeping filtering logic separate.

📌 Step 3.1: Create `ISpecification<T>` (Defines the Contract)

📂 Core/Specifications/ISpecification.cs

using System;
using System.Collections.Generic;
using System.Linq.Expressions;

namespace Core.Specifications
{
    public interface ISpecification<T>
    {
        Expression<Func<T, bool>>? Criteria { get; }
        List<Expression<Func<T, object>>> Includes { get; }
        Expression<Func<T, object>>? OrderBy { get; }
        Expression<Func<T, object>>? OrderByDescending { get; }
        int? Take { get; }
        int? Skip { get; }
        bool IsPagingEnabled { get; }
    }
}

🔹 What This Does

✔ Defines filtering (Criteria), sorting (OrderBy), and pagination (Skip, Take).

✔ Includes related entities (Includes) for eager loading.

📌 Step 3.2: Create `BaseSpecification<T>` (Stores Filtering Logic)

📂 Core/Specifications/BaseSpecification.cs

using System;
using System.Collections.Generic;
using System.Linq.Expressions;

namespace Core.Specifications
{
    public class BaseSpecification<T> : ISpecification<T>
    {
        public Expression<Func<T, bool>>? Criteria { get; }
        public List<Expression<Func<T, object>>> Includes { get; } = new();
        public Expression<Func<T, object>>? OrderBy { get; private set; }
        public Expression<Func<T, object>>? OrderByDescending { get; private set; }
        public int? Take { get; private set; }
        public int? Skip { get; private set; }
        public bool IsPagingEnabled { get; private set; }

        public BaseSpecification(Expression<Func<T, bool>>? criteria = null)
        {
            Criteria = criteria;
        }

        public void AddInclude(Expression<Func<T, object>> includeExpression)
        {
            Includes.Add(includeExpression);
        }

        public void ApplyPaging(int skip, int take)
        {
            Skip = skip;
            Take = take;
            IsPagingEnabled = true;
        }

        public void ApplyOrderBy(Expression<Func<T, object>> orderByExpression)
        {
            OrderBy = orderByExpression;
        }

        public void ApplyOrderByDescending(Expression<Func<T, object>> orderByDescExpression)
        {
            OrderByDescending = orderByDescExpression;
        }
    }
}

🔹 What This Does

✔ Stores filtering conditions dynamically.

✔ Supports sorting, eager loading, and pagination.

📌 Step 3.3: Create `SpecificationEvaluator<T>` (Applies the Specification)

📂 Infrastructure/Data/SpecificationEvaluator.cs

using System.Linq;
using Core.Specifications;
using Microsoft.EntityFrameworkCore;

namespace Infrastructure.Data
{
    public class SpecificationEvaluator<T> where T : class
    {
        public static IQueryable<T> GetQuery(IQueryable<T> inputQuery, ISpecification<T> spec)
        {
            var query = inputQuery;

            // Apply Filtering
            if (spec.Criteria != null)
            {
                query = query.Where(spec.Criteria);
            }

            // Apply Sorting
            if (spec.OrderBy != null)
            {
                query = query.OrderBy(spec.OrderBy);
            }
            else if (spec.OrderByDescending != null)
            {
                query = query.OrderByDescending(spec.OrderByDescending);
            }

            // Apply Pagination
            if (spec.IsPagingEnabled)
            {
                query = query.Skip(spec.Skip!.Value).Take(spec.Take!.Value);
            }

            // Apply Includes (Eager Loading)
            query = spec.Includes.Aggregate(query, (current, include) => current.Include(include));

            return query;
        }
    }
}

🔹 What This Does

✔ Applies filtering (Where).

✔ Applies sorting (OrderBy, OrderByDescending).

✔ Applies pagination (Skip, Take).

✔ Applies eager loading (Include).

📌 Step 3.4: Modify `GenericRepository<T>` to Support Specifications

📂 Infrastructure/Data/GenericRepository.cs

using System.Collections.Generic;
using System.Threading.Tasks;
using Core.Entities;
using Core.Interfaces;
using Core.Specifications;
using Microsoft.EntityFrameworkCore;

namespace Infrastructure.Data
{
    public class GenericRepository<T> : IGenericRepository<T> where T : BaseEntity
    {
        private readonly StoreContext _context;

        public GenericRepository(StoreContext context)
        {
            _context = context;
        }

        public async Task<T?> GetByIdAsync(int id)
        {
            return await _context.Set<T>().FindAsync(id);
        }

        public async Task<IReadOnlyList<T>> ListAllAsync()
        {
            return await _context.Set<T>().ToListAsync();
        }

        public async Task<IReadOnlyList<T>> ListAsync(ISpecification<T> spec)
        {
            return await ApplySpecification(spec).ToListAsync();
        }

        public async Task<T?> GetEntityWithSpec(ISpecification<T> spec)
        {
            return await ApplySpecification(spec).FirstOrDefaultAsync();
        }

        private IQueryable<T> ApplySpecification(ISpecification<T> spec)
        {
            return SpecificationEvaluator<T>.GetQuery(_context.Set<T>().AsQueryable(), spec);
        }

        public void Add(T entity)
        {
            _context.Set<T>().Add(entity);
        }

        public void Update(T entity)
        {
            _context.Set<T>().Attach(entity);
            _context.Entry(entity).State = EntityState.Modified;
        }

        public void Remove(T entity)
        {
            _context.Set<T>().Remove(entity);
        }

        public async Task<bool> SaveAllAsync()
        {
            return await _context.SaveChangesAsync() > 0;
        }
    }
}

🔹 What This Does

✔ Supports filtering, sorting, and pagination via Specifications.

✔ No need to modify the repository for future queries.

📌 Step 3.5: Create `ProductsWithFiltersSpecification`

📂 Core/Specifications/ProductsWithFiltersSpecification.cs

namespace Core.Specifications
{
    public class ProductsWithFiltersSpecification : BaseSpecification<Product>
    {
        public ProductsWithFiltersSpecification(string? brand, string? type)
            : base(x =>
                (string.IsNullOrEmpty(brand) || x.Brand == brand) &&
                (string.IsNullOrEmpty(type) || x.Type == type))
        {
        }
    }
}

🔹 What This Does

✔ Filters products dynamically based on Brand & Type.

📌 Step 3.6: Use the Specification in the Controller

📂 API/Controllers/ProductsController.cs

[HttpGet]
public async Task<ActionResult<List<Product>>> GetProducts(string? brand, string? type)
{
    var spec = new ProductsWithFiltersSpecification(brand, type);
    var products = await _repo.ListAsync(spec);
    return Ok(products);
}

✔ Dynamically filters products based on request parameters.

🚀 Step 3 Complete!

✅ We implemented the Specification Pattern step by step.

✅ Now, our repository supports dynamic filtering, sorting, and pagination.

✅ The API can filter products by brand & type dynamically.

🔹 Next Steps

🚀 Extend the Specification Pattern to support sorting & pagination.

🚀 Test the API to confirm the Specification Pattern is working.

❓ Need More Clarification?

Let me know if any step needs more explanation! 😊

Specification Pattern P2

mohamed Tayel — Sun, 02 Feb 2025 20:28:52 +0000

🚀 Step 2: Implementing a Generic Repository (Introducing Category Repository)

💡 Goal:

In this article, we will refactor our Product Repository by implementing a Generic Repository Pattern. Additionally, we will introduce the Category Repository, demonstrating how the Generic Repository improves code reusability and maintainability.

📂 Updated Project Structure (With Generic Repository)

📂 ProductApp
 ├── 📂 Core                 # Domain Layer
 │    ├── 📂 Entities
 │    │    ├── BaseEntity.cs
 │    │    ├── Product.cs
 │    │    ├── Category.cs
 │    ├── 📂 Interfaces
 │    │    ├── IRepository.cs  # Generic Repository Interface
 │    │    ├── IProductRepository.cs
 │    │    ├── ICategoryRepository.cs
 │
 ├── 📂 Infrastructure       # Data Access Layer
 │    ├── 📂 Data
 │    │    ├── Repository.cs  # Generic Repository Implementation
 │    │    ├── ProductRepository.cs
 │    │    ├── CategoryRepository.cs
 │    │    ├── StoreContext.cs
 │
 ├── 📂 API                  # Presentation Layer
 │    ├── 📂 Controllers
 │    │    ├── ProductsController.cs
 │    │    ├── CategoriesController.cs
 │    ├── 📂 DTOs
 │    │    ├── ProductDto.cs
 │    │    ├── CategoryDto.cs

1️⃣ Define the Base Entity

📂 Core/Entities/BaseEntity.cs

namespace Core.Entities
{
    public abstract class BaseEntity
    {
        public int Id { get; set; }
    }
}

2️⃣ Define the Generic Repository Interface

📂 Core/Interfaces/IRepository.cs

using System.Collections.Generic;
using System.Threading.Tasks;

namespace Core.Interfaces
{
    public interface IRepository<T> where T : BaseEntity
    {
        Task<T?> GetByIdAsync(int id);
        Task<List<T>> ListAllAsync();
        void Add(T entity);
        void Update(T entity);
        void Remove(T entity);
        Task<bool> SaveAllAsync();
    }
}

3️⃣ Implement the Generic Repository

📂 Infrastructure/Data/Repository.cs

using System.Collections.Generic;
using System.Threading.Tasks;
using Core.Entities;
using Core.Interfaces;
using Microsoft.EntityFrameworkCore;

namespace Infrastructure.Data
{
    public class Repository<T> : IRepository<T> where T : BaseEntity
    {
        private readonly StoreContext _context;
        private readonly DbSet<T> _dbSet;

        public Repository(StoreContext context)
        {
            _context = context;
            _dbSet = _context.Set<T>();
        }

        public async Task<T?> GetByIdAsync(int id)
        {
            return await _dbSet.FindAsync(id);
        }

        public async Task<List<T>> ListAllAsync()
        {
            return await _dbSet.ToListAsync();
        }

        public void Add(T entity)
        {
            _dbSet.Add(entity);
        }

        public void Update(T entity)
        {
            _dbSet.Update(entity);
        }

        public void Remove(T entity)
        {
            _dbSet.Remove(entity);
        }

        public async Task<bool> SaveAllAsync()
        {
            return await _context.SaveChangesAsync() > 0;
        }
    }
}

4️⃣ Define the `Category` Entity

📂 Core/Entities/Category.cs

namespace Core.Entities
{
    public class Category : BaseEntity
    {
        public string Name { get; set; } = string.Empty;
    }
}

5️⃣ Implement the `ICategoryRepository` Interface

📂 Core/Interfaces/ICategoryRepository.cs

using Core.Entities;

namespace Core.Interfaces
{
    public interface ICategoryRepository : IRepository<Category>
    {
    }
}

6️⃣ Implement the `CategoryRepository`

📂 Infrastructure/Data/CategoryRepository.cs

using Core.Entities;
using Core.Interfaces;

namespace Infrastructure.Data
{
    public class CategoryRepository : Repository<Category>, ICategoryRepository
    {
        public CategoryRepository(StoreContext context) : base(context) { }
    }
}

7️⃣ Register the Repositories in `Program.cs`

📂 API/Program.cs

using Infrastructure.Data;
using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.
builder.Services.AddControllers();
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();

// Register StoreContext with Dependency Injection
builder.Services.AddDbContext<StoreContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

// Register Repositories
builder.Services.AddScoped(typeof(IRepository<>), typeof(Repository<>));
builder.Services.AddScoped<IProductRepository, ProductRepository>();
builder.Services.AddScoped<ICategoryRepository, CategoryRepository>();

var app = builder.Build();

// Apply Migrations Automatically (Optional: Good for development)
using var scope = app.Services.CreateScope();
var services = scope.ServiceProvider;
var context = services.GetRequiredService<StoreContext>();
context.Database.Migrate();

app.UseSwagger();
app.UseSwaggerUI();
app.UseHttpsRedirection();
app.UseAuthorization();
app.MapControllers();
app.Run();

8️⃣ Create and Apply Migrations

Run the following commands in the API project folder:

# Create a Migration
dPM> dotnet ef migrations add AddCategoryEntity
No project was found. Change the current working directory or use the --project option.
# Apply the Migration to Update the Database
dotnet ef database update

🚀 Step 2 is Complete

✔ We refactored the Product Repository using a Generic Repository.\
✔ We introduced the Category Repository, demonstrating reusability.\
✔ The Generic Repository now enables easy expansion for future entities.\
✔ We added and applied migrations to update the database.

🔜 What’s Next?

In the next article, we will introduce the Specification Pattern to handle complex queries efficiently. This will allow filtering, sorting, and pagination without cluttering our repositories.

Stay tuned! 🚀

Specification Pattern P1

mohamed Tayel — Sun, 02 Feb 2025 20:23:02 +0000

🚀 Step 1: Case Study Without Generic Repository & Specification Pattern

💡 Goal: Implement a simple Product API using a separate repository for products.

📂 Project Structure (Without Generic Repository & Specification Pattern)

📂 ProductApp
 ├── 📂 Core                 # Domain Layer
 │    ├── 📂 Entities
 │    │    ├── Product.cs
 │    ├── 📂 Interfaces
 │    │    ├── IProductRepository.cs
 │
 ├── 📂 Infrastructure       # Data Access Layer
 │    ├── 📂 Data
 │    │    ├── ProductRepository.cs
 │    │    ├── StoreContext.cs
 │
 ├── 📂 API                  # Presentation Layer
 │    ├── 📂 Controllers
 │    │    ├── ProductsController.cs
 │    ├── 📂 DTOs
 │    │    ├── ProductDto.cs

1️⃣ Install Required NuGet Packages

Before proceeding, install the necessary NuGet packages for Entity Framework Core and SQL Server support:

dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet add package Microsoft.EntityFrameworkCore.Tools

2️⃣ Create the `Product` Entity

📂 Core/Entities/Product.cs

namespace Core.Entities
{
    public class Product
    {
        public int Id { get; set; }
        public string Name { get; set; } = string.Empty;
        public string Brand { get; set; } = string.Empty;
        public string Type { get; set; } = string.Empty;
        public decimal Price { get; set; }
    }
}

3️⃣ Create the `ProductDto` for API Response

📂 API/DTOs/ProductDto.cs

namespace API.DTOs
{
    public class ProductDto
    {
        public int Id { get; set; }
        public string Name { get; set; } = string.Empty;
        public string Brand { get; set; } = string.Empty;
        public string Type { get; set; } = string.Empty;
        public decimal Price { get; set; }
    }
}

4️⃣ Create the `IProductRepository` Interface

📂 Core/Interfaces/IProductRepository.cs

using System.Collections.Generic;
using System.Threading.Tasks;
using Core.Entities;

namespace Core.Interfaces
{
    public interface IProductRepository
    {
        Task<Product?> GetByIdAsync(int id);
        Task<List<Product>> ListAllAsync();
        void Add(Product product);
        void Update(Product product);
        void Remove(Product product);
        Task<bool> SaveAllAsync();
    }
}

5️⃣ Implement the `ProductRepository`

📂 Infrastructure/Data/ProductRepository.cs

using System.Collections.Generic;
using System.Threading.Tasks;
using Core.Entities;
using Core.Interfaces;
using Microsoft.EntityFrameworkCore;

namespace Infrastructure.Data
{
    public class ProductRepository : IProductRepository
    {
        private readonly StoreContext _context;

        public ProductRepository(StoreContext context)
        {
            _context = context;
        }

        public async Task<Product?> GetByIdAsync(int id)
        {
            return await _context.Products.FindAsync(id);
        }

        public async Task<List<Product>> ListAllAsync()
        {
            return await _context.Products.ToListAsync();
        }

        public void Add(Product product)
        {
            _context.Products.Add(product);
        }

        public void Update(Product product)
        {
            _context.Products.Update(product);
        }

        public void Remove(Product product)
        {
            _context.Products.Remove(product);
        }

        public async Task<bool> SaveAllAsync()
        {
            return await _context.SaveChangesAsync() > 0;
        }
    }
}

6️⃣ Create the `StoreContext` (DbContext)

📂 Infrastructure/Data/StoreContext.cs

using Core.Entities;
using Microsoft.EntityFrameworkCore;

namespace Infrastructure.Data
{
    public class StoreContext : DbContext
    {
        public StoreContext(DbContextOptions<StoreContext> options) : base(options) { }

        public DbSet<Product> Products { get; set; }
    }
}

7️⃣ Create the API Controller: `ProductsController`

📂 API/Controllers/ProductsController.cs

using API.DTOs;
using Core.Entities;
using Core.Interfaces;
using Microsoft.AspNetCore.Mvc;

namespace API.Controllers
{
    [ApiController]
    [Route("api/[controller]")]
    public class ProductsController : ControllerBase
    {
        private readonly IProductRepository _productRepository;

        public ProductsController(IProductRepository productRepository)
        {
            _productRepository = productRepository;
        }

        [HttpGet]
        public async Task<ActionResult<IEnumerable<ProductDto>>> GetProducts()
        {
            var products = await _productRepository.ListAllAsync();
            return Ok(products.Select(p => new ProductDto
            {
                Id = p.Id,
                Name = p.Name,
                Brand = p.Brand,
                Type = p.Type,
                Price = p.Price
            }));
        }

        [HttpGet("{id}")]
        public async Task<ActionResult<ProductDto>> GetProduct(int id)
        {
            var product = await _productRepository.GetByIdAsync(id);
            if (product == null) return NotFound();

            return Ok(new ProductDto
            {
                Id = product.Id,
                Name = product.Name,
                Brand = product.Brand,
                Type = product.Type,
                Price = product.Price
            });
        }

        [HttpPost]
        public async Task<ActionResult<ProductDto>> CreateProduct(ProductDto productDto)
        {
            var product = new Product
            {
                Name = productDto.Name,
                Brand = productDto.Brand,
                Type = productDto.Type,
                Price = productDto.Price
            };

            _productRepository.Add(product);
            await _productRepository.SaveAllAsync();

            return CreatedAtAction(nameof(GetProduct), new { id = product.Id }, productDto);
        }

        [HttpPut("{id}")]
        public async Task<IActionResult> UpdateProduct(int id, ProductDto productDto)
        {
            var product = await _productRepository.GetByIdAsync(id);
            if (product == null) return NotFound();

            product.Name = productDto.Name;
            product.Brand = productDto.Brand;
            product.Type = productDto.Type;
            product.Price = productDto.Price;

            _productRepository.Update(product);
            await _productRepository.SaveAllAsync();

            return NoContent();
        }

        [HttpDelete("{id}")]
        public async Task<IActionResult> DeleteProduct(int id)
        {
            var product = await _productRepository.GetByIdAsync(id);
            if (product == null) return NotFound();

            _productRepository.Remove(product);
            await _productRepository.SaveAllAsync();

            return NoContent();
        }
    }
}

8️⃣ Register `StoreContext` in `Program.cs`

📂 API/Program.cs

using Infrastructure.Data;
using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.
builder.Services.AddControllers();
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();

// Register StoreContext with Dependency Injection
builder.Services.AddDbContext<StoreContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

// Register Repository
builder.Services.AddScoped<IProductRepository, ProductRepository>();

var app = builder.Build();

app.UseSwagger();
app.UseSwaggerUI();
app.UseHttpsRedirection();
app.UseAuthorization();
app.MapControllers();
app.Run();

🎯 Final Steps: Running the API

Run the following commands to apply migrations:

dotnet ef migrations add InitialCreate --project ../Infrastructure/Data.csproj --startup-project API/ProductAPI.csproj
dotnet ef database update --project ../Infrastructure/Data.csproj --startup-project API/ProductAPI.csproj

Start the API:

dotnet run

Open Swagger UI at https://localhost:5001/swagger to test the endpoints.

🚀 Step 1 is Complete

✔ We implemented a Product API using a traditional repository.

✔ The API can Create, Read, Update, and Delete (CRUD) products.

Next Steps: Implement the Generic Repository to improve code reusability. 🚀

Using a Custom Type as a Dictionary Key in C#

mohamed Tayel — Sun, 02 Feb 2025 08:47:30 +0000

In C#, dictionaries rely on keys behaving correctly when it comes to equality and hashing. Standard types like string, int, and float work seamlessly as dictionary keys because Microsoft has ensured they implement proper equality comparison and hashing. However, when using a custom type as a key, you need to manually define how equality and hashing should work.

In this article, we will:

Create a custom type (CountryCode) and use it as a dictionary key.
Understand why overriding Equals and GetHashCode is necessary.
Implement a console application that demonstrates these concepts.

1. Creating a Custom Type as a Dictionary Key

Let's assume we want to store country details in a dictionary where the key is a custom type (CountryCode) instead of a string. The CountryCode type will wrap a string representing a country's ISO code (e.g., "USA", "FRA", "DZA").

Defining the CountryCode Class

using System;
using System.Collections.Generic;

public class CountryCode
{
    public string Code { get; }

    public CountryCode(string code)
    {
        if (string.IsNullOrWhiteSpace(code) || code.Length != 3)
        {
            throw new ArgumentException("Country code must be exactly 3 characters.", nameof(code));
        }

        Code = code.ToUpper(); // Normalize case
    }

    public override string ToString() => Code;

    // Override Equals for value comparison
    public override bool Equals(object obj)
    {
        if (obj is CountryCode other)
        {
            return string.Equals(this.Code, other.Code, StringComparison.OrdinalIgnoreCase);
        }
        return false;
    }

    // Override GetHashCode to match Equals behavior
    public override int GetHashCode()
    {
        return StringComparer.OrdinalIgnoreCase.GetHashCode(Code);
    }

    // Overload == and != for consistency
    public static bool operator ==(CountryCode left, CountryCode right)
    {
        if (left is null) return right is null;
        return left.Equals(right);
    }

    public static bool operator !=(CountryCode left, CountryCode right)
    {
        return !(left == right);
    }
}

Key Takeaways:

Constructor validation: Ensures the country code is always 3 characters long.
Overriding ToString(): Makes CountryCode display its value properly.
Overriding Equals(): Compares values instead of object references.
Overriding GetHashCode(): Ensures correct dictionary behavior.
Overloading == and !=: Makes equality checks consistent.

2. Using CountryCode as a Dictionary Key

Now, let's define a dictionary that maps country codes to country names.

Defining the Country Class

public class Country
{
    public string Name { get; }
    public CountryCode Code { get; }

    public Country(string name, string code)
    {
        Name = name;
        Code = new CountryCode(code);
    }

    public override string ToString() => $"{Name} ({Code})";
}

This class represents a country with a name and an ISO country code.

3. Implementing a Dictionary with Custom Keys

Now, we will create a dictionary where the key is CountryCode and the value is a Country object.

Building the Dictionary

class Program
{
    static void Main()
    {
        // Dictionary with CountryCode as the key
        Dictionary<CountryCode, Country> countries = new Dictionary<CountryCode, Country>
        {
            { new CountryCode("USA"), new Country("United States", "USA") },
            { new CountryCode("FRA"), new Country("France", "FRA") },
            { new CountryCode("DZA"), new Country("Algeria", "DZA") }
        };

        Console.Write("Enter a country code: ");
        string userInput = Console.ReadLine();

        // Convert user input to CountryCode
        CountryCode inputCode = new CountryCode(userInput);

        // Lookup country
        if (countries.TryGetValue(inputCode, out Country foundCountry))
        {
            Console.WriteLine($"Country found: {foundCountry}");
        }
        else
        {
            Console.WriteLine("Country not found.");
        }
    }
}

4. Testing the Application

Example Run:

Enter a country code: dza
Country found: Algeria (DZA)

This works even though we entered "dza" in lowercase because our Equals method ignores case.

What Happens if We Don't Override `Equals` and `GetHashCode`?

If we remove these overrides, the dictionary lookup will fail. The dictionary won't recognize "dza" and "DZA" as the same key, even though they contain the same value.

5. Summary

Why Use a Custom Type as a Key?
- Provides better control over data validation and formatting.
- Helps prevent accidental mistakes (e.g., using "USA " instead of "USA").
What Issues Can Occur?
- Dictionaries compare keys using Equals() and GetHashCode().
- Reference types compare object instances by default, not values.
How to Fix These Issues?
- Override Equals() to compare values.
- Override GetHashCode() to ensure hashing is consistent.
- Use a case-insensitive StringComparer to handle input variations.
- Overload == and != to ensure equality checks work properly.

By following these steps, we ensure that our dictionary functions correctly even when using custom types as keys.

Understanding Dictionary Enumeration and Sorting in C#

mohamed Tayel — Sun, 02 Feb 2025 08:39:38 +0000

Introduction

Dictionaries in C# are widely used for key-value storage and fast lookups. However, one common mistake developers make is assuming that dictionaries maintain a fixed order when enumerating. In this article, we will explore:

How dictionary enumeration works.
The limitations of relying on dictionary order.
How SortedDictionary<TKey, TValue> and SortedList<TKey, TValue> help maintain sorted order.
The best approach to sorting a dictionary by a custom property.
A complete console application demonstrating these concepts step by step.

1. Dictionary Enumeration: The Problem

A Dictionary<TKey, TValue> does not guarantee order when enumerating items. Let’s demonstrate this behavior with a simple example:

Step 1: Create a Console Application and Define a Dictionary

using System;
using System.Collections.Generic;

class Program
{
    static void Main()
    {
        Dictionary<string, string> countries = new Dictionary<string, string>
        {
            { "US", "United States" },
            { "GB", "United Kingdom" },
            { "DE", "Germany" },
            { "FR", "France" },
            { "JP", "Japan" }
        };

        Console.WriteLine("Enumerating Dictionary:");
        foreach (var kvp in countries)
        {
            Console.WriteLine($"{kvp.Key}: {kvp.Value}");
        }
    }
}

Expected Output (Order May Vary)

Enumerating Dictionary:
GB: United Kingdom
US: United States
JP: Japan
DE: Germany
FR: France

The order of enumeration is not guaranteed and may change across different runs. This is because Dictionary<TKey, TValue> is optimized for fast lookups, not ordering.

2. Using SortedDictionary

If we need to maintain a sorted order, we can use SortedDictionary<TKey, TValue>. This collection automatically sorts its items by key.

Step 2: Replace Dictionary with SortedDictionary

using System;
using System.Collections.Generic;

class Program
{
    static void Main()
    {
        SortedDictionary<string, string> sortedCountries = new SortedDictionary<string, string>
        {
            { "US", "United States" },
            { "GB", "United Kingdom" },
            { "DE", "Germany" },
            { "FR", "France" },
            { "JP", "Japan" }
        };

        Console.WriteLine("\nEnumerating SortedDictionary:");
        foreach (var kvp in sortedCountries)
        {
            Console.WriteLine($"{kvp.Key}: {kvp.Value}");
        }
    }
}

Output (Always Sorted by Key)

Enumerating SortedDictionary:
DE: Germany
FR: France
GB: United Kingdom
JP: Japan
US: United States

Key Takeaway:

✅ SortedDictionary automatically maintains key-based sorting.

❌ However, if we need sorting based on values (e.g., country names), SortedDictionary won’t help.

3. Using SortedList

Another option is SortedList<TKey, TValue>. It functions similarly to SortedDictionary, but stores data in an internal array instead of a balanced tree.

Step 3: Replace SortedDictionary with SortedList

using System;
using System.Collections.Generic;

class Program
{
    static void Main()
    {
        SortedList<string, string> sortedListCountries = new SortedList<string, string>
        {
            { "US", "United States" },
            { "GB", "United Kingdom" },
            { "DE", "Germany" },
            { "FR", "France" },
            { "JP", "Japan" }
        };

        Console.WriteLine("\nEnumerating SortedList:");
        foreach (var kvp in sortedListCountries)
        {
            Console.WriteLine($"{kvp.Key}: {kvp.Value}");
        }
    }
}

Output (Always Sorted by Key)

Enumerating SortedList:
DE: Germany
FR: France
GB: United Kingdom
JP: Japan
US: United States

Comparison Between SortedDictionary and SortedList

| Feature | SortedDictionary | SortedList |
|---------|-----------------|------------|
| Sorting | By key (auto) | By key (auto) |
| Memory Usage | Higher (tree structure) | Lower (array-based) |
| Insert/Delete Performance | Faster for frequent changes | Slower for frequent changes |
| Lookup Speed | O(log n) | O(log n) |

Key Takeaway:

✅ SortedList uses less memory but performs worse when inserting/removing items frequently.

✅ SortedDictionary is better for frequent modifications but uses more memory.

4. Sorting by a Custom Property (Best Approach)

If we want to sort by country name (value) instead of the key, we cannot rely on SortedDictionary or SortedList. Instead, we should sort a list manually.

Step 4: Sorting by Country Name

using System;
using System.Collections.Generic;
using System.Linq;

class Program
{
    static void Main()
    {
        Dictionary<string, string> countries = new Dictionary<string, string>
        {
            { "US", "United States" },
            { "GB", "United Kingdom" },
            { "DE", "Germany" },
            { "FR", "France" },
            { "JP", "Japan" }
        };

        Console.WriteLine("\nSorting by Country Name:");
        var sortedByValue = countries.OrderBy(kvp => kvp.Value).ToList();

        foreach (var kvp in sortedByValue)
        {
            Console.WriteLine($"{kvp.Key}: {kvp.Value}");
        }
    }
}

Output (Sorted by Country Name)

Sorting by Country Name:
DE: Germany
FR: France
GB: United Kingdom
JP: Japan
US: United States

Key Takeaway:

✅ Sorting a list manually using OrderBy(kvp => kvp.Value) is the best way to sort dictionaries by value.

❌ SortedDictionary and SortedList only sort by key, not value.

Conclusion

Dictionary<TKey, TValue> does not guarantee order when enumerating.
SortedDictionary<TKey, TValue> and SortedList<TKey, TValue> maintain sorted order, but only by key.
If sorting by value is needed, use LINQ’s OrderBy on a list.
SortedDictionary vs. SortedList:
- SortedDictionary is better for frequent insertions/deletions.
- SortedList is more memory-efficient but slower for updates.

Final Thoughts

✅ Use Dictionary<TKey, TValue> for fast lookups.

✅ Use SortedDictionary<TKey, TValue> for ordered keys.

✅ Use OrderBy(kvp => kvp.Value) when sorting by values.

DEV Community: mohamed Tayel

SQL Server Query Store Hints: Fix Query Performance Without Changing Application Code

The Real-World Problem: Queries You Cannot Easily Change

What Is a Query Store Hint?

How Query Store Hints Work

Requirements

Supported Platforms

Query Store Must Be Enabled

Required Permissions

Why query_id Is Critical

Practical Example: Testing MAXDOP 1

Step 1: Make Sure the Query Is Captured

Step 2: Find the Correct query_id

Step 3: Capture a Performance Baseline

Step 4: Apply the Query Store Hint

Important: A New Hint Replaces the Existing Definition

Step 5: Verify the Stored Hint

query_hint_text

last_query_hint_failure_reason

last_query_hint_failure_reason_desc

query_hint_failure_count

source_desc

Step 6: Execute the Query Again

Step 7: Compare Performance After the Hint

What Happens When SQL Server Cannot Apply the Hint?

Examples of Query Store Hints

Query Store Hint Versus Plan Forcing

Plan Forcing

Query Store Hint

When Query Store Hints Are Useful

1. The Application Cannot Be Modified

2. An Emergency Performance Fix Is Required

3. One Query Needs Different Behavior

4. A Migration Caused a Regression

5. A DBA Wants to Test an Optimizer Behavior

When Query Store Hints Should Not Be the First Fix

Production Safety Checklist

Removing the Query Store Hint

Complete Demonstration Script

Final Thoughts

Continue the Query Store Series

SQL Server Parameter Sniffing: Detecting Unstable Execution Plans and Choosing the Right Fix

What Is Parameter Sniffing?

Why Parameter Sniffing Is Usually Helpful

Creating a Parameter Sniffing Demo

Create the Table

Insert Small Customers

Insert a High-Volume Customer

Create the Stored Procedure

Test 1: Compile the Plan for a Small Customer

Test 2: Compile the Plan for the Large Customer

How to Confirm Parameter Sniffing

Inspecting the Compiled Parameter Value

Checking the Cached Execution Plan

Detecting Parameter-Sensitive Behavior with Query Store

Why Estimated Rows Matter

Common Solutions for Parameter Sniffing

Solution 1: OPTION (RECOMPILE)

Advantages

Disadvantages

Good Use Cases

Solution 2: WITH RECOMPILE

Solution 3: OPTIMIZE FOR a Specific Value

Advantages

Disadvantages

Good Use Cases

Solution 4: OPTIMIZE FOR UNKNOWN

Advantages

Disadvantages

Good Use Cases

Solution 5: Use Local Variables

Advantages

Disadvantages

Solution 6: Dynamic SQL

Advantages

Disadvantages

Solution 7: Separate Procedures or Query Branches

Advantages

Disadvantages

Solution 8: Query Store Plan Forcing

Why `query_id` Is Critical

Practical Example: Testing `MAXDOP 1`

Step 2: Find the Correct `query_id`

`query_hint_text`

`last_query_hint_failure_reason`

`last_query_hint_failure_reason_desc`

`query_hint_failure_count`

`source_desc`