The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →When a SQL Server query is fast for some parameter values and slow for others, the likely issue is parameter sensitivity: one cached execution plan is being reused for inputs with materially different data distributions. First confirm that the same statement behaves differently across representative values; then check the plan history, SQL Server version, and database compatibility level before choosing a fix. On SQL Server 2022 (16.x) and later, eligible queries may benefit from Parameter Sensitive Plan optimization at compatibility level 160.
What parameter sniffing is—and when it becomes a problem
Parameter sniffing is normal SQL Server behavior. When it compiles a parameterized statement, SQL Server can use the current parameter values to estimate how many rows the query will process and choose an execution plan. It may cache that plan and reuse it on later executions.
That reuse is beneficial when the inputs have similar data distributions. It becomes a performance problem when a plan compiled for one value is a poor fit for another—for example, if one value matches a small number of rows while another matches a very large number. The more precise description of this problem is parameter sensitivity; a parameter-sensitive plan (PSP) is a plan problem caused by those differences. Microsoft describes this as a case where a cached plan may not be optimal for all parameter values (Microsoft Learn: Detectable types of query performance bottlenecks).
A slow execution alone does not establish parameter sensitivity. Blocking, resource pressure, I/O, stale statistics, or missing indexes can also make a query slow. Confirm the input-dependent pattern and rule out likely alternatives before changing plan behavior.
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Scan for outdated or missing drivers - takes under a minuteDriver Scan →Clear out junk files and repair common Windows errorsFree Scan →#1 Best Overall
- Read Before You Buy — No Video Output: These adapters support charging and USB 2.0 data transfer, but cannot transmit video signals. Except for standard USB webcams (which use USB data only), they are not compatible with HDMI/DisplayPort cables, video-capable USB-C hubs, or docking stations with video output.
- Convert USB-A Ports to USB-C: Designed to connect USB-C earphones, cables, flash drives, card readers, and other USB-C accessories to standard USB-A ports. Plug-and-play with no drivers or software required.
- Aluminum Alloy Housing: Built with a sturdy aluminum alloy shell that aids in heat dissipation and protects against daily wear and scratches. Designed to maintain a stable and secure connection.
- Compact & Travel-Friendly: The ultra-compact design allows the adapter to stay plugged into your device without blocking adjacent ports or adding bulk, reducing wear and tear on your original USB ports.
- 12-Month Warranty: Backed by a 12-month manufacturer warranty for peace of mind. Designed to meet strict quality control standards for reliable everyday performance.
How to diagnose a parameter-sensitive query
- Identify the affected statement. Pin down the query or stored-procedure statement with the latency or CPU regression. Capture its SQL text, representative parameter values, SQL Server version and build, and the database compatibility level. Use Query Store, when available, to compare runtime history and execution plans. Query Store can help reveal performance and plan changes, and Microsoft recommends it for insight into PSP behavior (Microsoft Learn: Query Store Hints; Microsoft Learn: ALTER DATABASE SCOPED CONFIGURATION).
- Compare meaningfully different inputs. Test values that return very different row counts or match differently distributed data. Compare their plans and, where possible, estimated rows with actual rows. Ask whether the same access path and join choices are sensible for both the small and large result sets. A repeatable mismatch between inputs is stronger evidence than one isolated slow run.
- Check other likely causes. Review statistics and index maintenance, and investigate blocking, I/O, and wider resource pressure. Microsoft notes that statistics or index maintenance may resolve a problem that otherwise appears to call for a hint (Microsoft Learn: Query Store Hints).
- Check eligibility before selecting a remedy. On SQL Server 2022 (16.x), verify that the affected database uses compatibility level 160 and that parameter sniffing has not been disabled for the relevant context. Do not assume an engine upgrade also changed a database’s compatibility level.
Use plan-cache removal only as a diagnostic
For a controlled diagnostic, removing the identified cached plan can force SQL Server to compile it again on the next execution. If the issue disappears after recompilation, that is evidence consistent with parameter sensitivity—not proof that all other causes have been excluded. Microsoft warns that clearing the entire plan cache removes all compiled plans; queries that need to rebuild plans can take longer during that one-time recompilation period (Microsoft Learn: Troubleshoot High CPU Usage Issues in SQL Server).
Prefer targeting a known bad plan handle or SQL handle, and do so only when you understand the immediate compile impact. A broad cache clear is not a durable repair: it affects unrelated queries too. Do not use DBCC FREEPROCCACHE as a permanent fix for one query.
Which fix should you choose?
The right remedy depends on whether distinct input ranges need distinct plans, how much compilation CPU the workload can tolerate, whether application SQL can change, and how much scope and ongoing maintenance a fix introduces. The options below differ in scope and behavior; none guarantees a better plan without validation against the affected workload.
Rank #2
- 5-in-1 USB-C Hub: Experience comprehensive connectivity featuring a Power Delivery input, two USB-A 2.0 ports, a USB-A 3.0 port, and an HDMI port. (Note: The USB-C power delivery input port is only for connecting an external wall charger to power your laptop and cannot power peripheral devices.)
- 90W Pass-Through Charging: Achieve optimal charging with 90W pass-through power to your laptop, supported by a total input of 100W, with the hub reserving 10W for operational efficiency. (Note: Wall charger not included.)
- Quick Data Transfers: Accelerate your productivity with rapid data transfers using a high-speed 5Gbps USB 3.0 port and two 480Mbps USB 2.0 ports.
- 4K HDMI Display: Enhance your visual experience with a hub capable of delivering 4K resolution at 30Hz in both mirror and extend modes. Please note that this hub is compatible with MacBook (macOS 12 and newer), Windows 10 and 11, ChromeOS, and laptops equipped with DP Alt Mode and Power Delivery. Note: This device is not compatible with Linux.
- What You Get: Anker USB-C Hub (5-in-1, 4K HDMI), welcome guide, 18-month warranty, and our friendly customer service.
| Remedy | When it may fit | Main trade-off | Scope and version considerations |
|---|---|---|---|
| Parameter Sensitive Plan optimization | Eligible parameterized queries on supported platforms with meaningfully different parameter ranges. | Allows multiple active plans for a qualifying query rather than forcing one plan to serve every value. | Introduced with SQL Server 2022 (16.x); for SQL Server, requires database compatibility level 160. Also applies to Azure SQL Database and Azure SQL Managed Instance, per Microsoft. Disabling parameter sniffing disables PSP for affected execution contexts. |
Statement-level OPTION (RECOMPILE) |
A particular statement needs a plan optimized for the current parameter values each time it runs. | Additional compilation CPU on executions; weigh it against the execution-time improvement and overall throughput. | Can be applied narrowly to the affected statement. Recompiling an entire procedure repeatedly is less efficient, according to Microsoft. |
OPTIMIZE FOR (@p = value) |
A known value represents the dominant or business-important workload. | Can remain a poor fit for materially different parameter values. | Targets compilation assumptions for the specified parameter; validate against the distribution of real workload values. |
OPTIMIZE FOR UNKNOWN |
No single parameter value represents the workload and a compromise plan is preferable. | Uses an average-density estimate rather than the sniffed value; the resulting plan is not guaranteed to be optimal. | Query-level choice that trades per-value specialization for a more general plan. |
| Disable parameter sniffing | A narrowly scoped query-level change is justified after testing, and other options are unsuitable. | Giving up parameter-specific estimates can hurt queries that benefit from them; broad disablement affects other workload. | Microsoft documents query-level, database-scoped, and server-level choices. On SQL Server 2022, it also disables PSP in affected contexts. |
| Query Store hint | A query-level hint is needed without changing application code. | Overrides normal optimizer behavior; may become suboptimal as data distributions change. | Applies to executions of the hinted query. Test the workload, confirm application of the hint, and reevaluate it after significant data shifts or migrations. |
| Targeted cached-plan removal | A temporary diagnostic or short-lived way to trigger recompilation while a durable fix is prepared. | Plan recompilation has an immediate cost; clearing the whole cache has wider impact and can temporarily lengthen executions. | Target a known plan or SQL handle where possible. Not a lasting query-performance strategy. |
Fixes to try, from automatic handling to targeted hints
1. Use PSP optimization when the query is eligible
Parameter Sensitive Plan optimization is designed to address cases where one cached plan is not suitable for all incoming parameter values. For qualifying parameterized queries, SQL Server can keep multiple active plans. Microsoft says PSP is on by default starting at compatibility level 160. SQL Server 2022 (16.x) and later support the feature; Microsoft also lists Azure SQL Database and Azure SQL Managed Instance (Microsoft Learn: ALTER DATABASE SCOPED CONFIGURATION; Microsoft Learn: Query Store Hints).
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Check the actual database compatibility level and use Query Store to gain insight into plan and performance behavior. If parameter sniffing has been disabled with trace flag 4136, database-scoped PARAMETER_SNIFFING = OFF, or the DISABLE_PARAMETER_SNIFFING query hint, PSP is disabled for the associated execution context (Microsoft Learn: ALTER DATABASE SCOPED CONFIGURATION).
2. Recompile only the statement that needs value-specific plans
OPTION (RECOMPILE) asks SQL Server to optimize the statement using its current parameter values when it executes. This can help when input values need different plans and the execution savings justify the extra compile work. Apply it to the affected statement where practical rather than recompiling the whole procedure on every call. Microsoft characterizes repeated procedure recompilation as less efficient than statement-level alternatives (Microsoft Learn: Troubleshoot High CPU Usage Issues in SQL Server).
Rank #3
- Sleek 7-in-1 USB-C Hub: Features an HDMI port, two USB-A 3.0 ports, and a USB-C data port, each providing 5Gbps transfer speeds. It also includes a USB-C PD input port for charging up to 100W and dual SD and TF card slots, all in a compact design.
- Flawless 4K@60Hz Video with HDMI: Delivers exceptional clarity and smoothness with its 4K@60Hz HDMI port, making it ideal for high-definition presentations and entertainment. (Note: Only the HDMI port supports video projection; the USB-C port is for data transfer only.)
- Double Up on Efficiency: The two USB-A 3.0 ports and a USB-C port support a fast 5Gbps data rate, significantly boosting your transfer speeds and improving productivity.
- Fast and Reliable 85W Charging: Offers high-capacity, speedy charging for laptops up to 85W, so you spend less time tethered to an outlet and more time being productive.
- What You Get: Anker USB-C Hub (7-in-1), welcome guide, 18-month warranty, and our friendly customer service.
Do not treat compilation as free: high execution frequency can make compile CPU a material part of workload cost. Compare query runtime and overall CPU under representative load before keeping the hint.
3. Choose a representative value or an average-density estimate
OPTIMIZE FOR (@p = value) directs the optimizer to use the specified value when it compiles the query. It can suit a workload dominated by a known value or range, but may harm performance for inputs that differ substantially. Test the choice against the actual mix of parameter values rather than only the value used in the hint.
Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Fix the driver behind crashes, sound loss and screen glitches3Repair Windows errors before they cause bigger problemsOPTIMIZE FOR UNKNOWN instead uses an average-density estimate rather than the current sniffed value. This can be a reasonable compromise when no one input represents the workload, but it is not a promise of an optimal plan. Microsoft documents both options as plan-shaping choices with workload trade-offs (Microsoft Learn: Troubleshoot High CPU Usage Issues in SQL Server).
Rank #4
- Dual Converters, Infinite Potential：Includes 2× USB C male to USB A female adapters and 2× USB A male to USB C female adapters. Perfect for a wide range of uses—tablets with Bluetooth keyboards, expand USB ports on macbook, and more. Two different converters for all your daily needs
- Next-Level 10Gbps & 3A Charging: No more slow 480Mbps, this usb to usb c adapter has a transfer speed of up to 10Gbps, allowing you to do more transferring in less time. This usb adapter fits both USB A and USB C charger, supporting up to 3A fast charging
- Upgraded Exquisite Craftsmanship: With an aluminum alloy housing and metal connector, the usbc to usb adapter is extremely durable and sturdy. Rigorously tested to withstand more than 10,000 times of plugging and unplugging, ensuring long-lasting performance
- Broad Compatible: The usb c to usb adapter widely supports all USB C/ USB A devices like laptops, tablets, cellphones, car chargers, and phone chargers. Such as compatible with MacBook Pro/Air 2023/2022, Thunderbolt 4/3 Devices,Apple MagSafe Watch 9/8/7/SE/Ultra, iPad Pro 2022/2021, Samsung Galaxy S23/S20/S10, and iPhone 17/16/15 Pro. Plug and play
- Please Note: To reach 10Gbps speed, keep the cable under 3.3 ft. For USB A Male to USB C adapters, try flipping the USB C connector. USB C Male to USB A adapters support bidirectional 10Gbps transfer within 3.3 ft
4. Apply a query-level disable only when a narrow test supports it
Microsoft documents USE HINT ('DISABLE_PARAMETER_SNIFFING') as a query-level option, as well as broader database-scoped and server-level ways to disable sniffing. Disabling sniffing changes estimates and behavior beyond simply correcting one bad plan, so prefer the narrowest scope that solves the demonstrated issue. On SQL Server 2022, disabling sniffing also removes PSP’s ability to supply multiple plans for affected contexts (Microsoft Learn: Troubleshoot High CPU Usage Issues in SQL Server; Microsoft Learn: ALTER DATABASE SCOPED CONFIGURATION).
5. Use Query Store hints as managed production controls
Query Store hints can apply query-level hints without changing application code, but they override the optimizer’s normal behavior. Before relying on a hint, review statistics and index maintenance and, where feasible, test a higher compatibility level. Microsoft advises testing hints and warns that a hint may become suboptimal when data distributions change (Microsoft Learn: Query Store Hints; Microsoft Learn: Query Store Hints Best Practices).
A Query Store hint affects all executions of its query, so load-test consequential changes and confirm that the hint was accepted and applied. Reevaluate it after migrations or meaningful data shifts. The Query Store RECOMPILE hint is not supported with forced parameterization; if it is specified alongside other valid hints, the engine ignores the unsupported RECOMPILE hint while applying the valid ones (Microsoft Learn: Query Store Hints Best Practices).
Recommended Free Tools
Best Value
- 5-in-1 Connectivity: Equipped with a 4K HDMI port, a 5 Gbps USB-C data port, two 5 Gbps USB-A ports, and a USB C 100W PD-IN port. Note: The USB C 100W PD-IN port supports only charging and does not support data transfer devices such as headphones or speakers.
- Powerful Pass-Through Charging: Supports up to 85W pass-through charging so you can power up your laptop while you use the hub. Note: Pass-through charging requires a charger (not included). Note: To achieve full power for iPad, we recommend using a 45W wall charger.
- Transfer Files in Seconds: Move files to and from your laptop at speeds of up to 5 Gbps via the USB-C and USB-A data ports. Note: The USB C 5Gbps Data port does not support video output.
- HD Display: Connect to the HDMI port to stream or mirror content to an external monitor in resolutions of up to 4K@30Hz. Note: The USB-C ports do not support video output.
- What You Get: Anker 332 USB-C Hub (5-in-1), welcome guide, our worry-free 18-month warranty, and friendly customer service.
Recompilation and cache clearing: what they do and do not do
Recompilation is an execution-plan action, not a general correction for bad estimates or data access. SQL Server also recompiles automatically in some circumstances, including relevant underlying changes or statistics changes. The stored procedure sp_recompile marks procedures, triggers, or functions that act on a table for recompilation on their next execution; it is not a recurring fix to apply blindly (Microsoft Learn: sys.sp_recompile).
Likewise, removing a bad cached plan can reveal whether a fresh compilation changes the behavior, but broad cache clearing forces unrelated plans to be rebuilt. Keep any cache action targeted and temporary, and use a tested query or configuration change for a lasting remedy (Microsoft Learn: Troubleshoot High CPU Usage Issues in SQL Server).
Validate the change against the full workload
- Test representative inputs, especially those with substantially different row counts or data distributions.
- Compare execution plans and query performance before and after the change, using Query Store where available.
- Check execution time and CPU together; a faster execution plan that imposes excessive compilation work may not improve throughput.
- Confirm that a query-level fix has not caused regressions for other parameter values.
- Revisit hints when statistics, indexes, data distribution, application behavior, or compatibility level changes.
Query Store is enabled by default for newly created SQL Server 2022 databases, but do not assume it is enabled in older databases or existing upgraded configurations. Check its availability in the affected database before relying on it for plan history (Microsoft Learn: ALTER DATABASE SCOPED CONFIGURATION; Microsoft Learn: Query Store Hints).
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
