Questions tagged [parameter-sniffing]

Use this tag for questions involved a sudden degradation in query performance related to query plan changes based on supplied parameters.

Filter by
Sorted by
Tagged with
0
votes
0answers
17 views

How to solve Parameter Sniffing problem in a query with 'in' operator

I have a parameter sniffing problem in this Azure SQL Server query with in operator: Select Col1, Col2, ..., Coln from T1 Join T2 On T1.Id = T2.T1Id Where T2.Col2 in ('uniqueIdentifier_1'...
0
votes
0answers
50 views

If there is no end-user input, is it safe to un-parameterize a parameterized query to avoid parameter sniffing?

AFAIK, a parameterized query is commonly used to prevent SQL injection attacks. What if there is no end-user input that can cause SQL injection? In this case, is it safe to un-parameterize a ...
1
vote
1answer
119 views

Parameter Sniffing - Running query in code vs in management studio

I just had a problem at work where a stored proc, being called by some C# code, was very slow. When I ran the same stored proc with the same parameters in Management Studio, it was very fast. After a ...
1
vote
0answers
33 views

Different query plans for same In line Function [duplicate]

I am facing a potential parameter sniffing issue with an In Line function in sql server 2008. Basically Given the below three queries (same function): First Query: DECLARE @SQL NVARCHAR(1000) ...
4
votes
1answer
206 views

How to find inputs that caused different query plans in Query Store

Query store shows that a completely parameterized query has multiple query plans: How can I find the input that is causing such long response times? Why would the same parameterized query end up ...
7
votes
2answers
591 views

Parameter sniffing = On with Parameterization = forced. Which takes precedence?

These two settings seem to contradict each other. One forces plan parameterization so that only one plan gets created. The other allows for multiple plans If you have Parameterization = forced, ...
0
votes
1answer
36 views

Stored Proc Logical Reads Spiking Daily At The Same Time [closed]

I have a procedure that I optimized in a dev environment. It is a heavy lifter and started at 14 millions logical reads with a run time of 30-40 seconds. I got it down to 96k reads and 8 seconds. ...
0
votes
1answer
832 views

how to add option(recompile) within this stored procedure?

In my current environment, I have many instances of stored procedures, just like the one shown below, where a bunch of parameters are passed to the procedure, and then within the procedure a select ...
1
vote
3answers
150 views

Is it a parameter sniffing problem?

I have a stored procedure which is called by Console Application (C#), the sp do nothing much just checking the existing of the record and update it: ALTER PROCEDURE [dbo].[sp_Update_StaffTable] ...
1
vote
1answer
73 views

SQL Server 2008 ignoring QUERYTRACEON 4136 and ARITHABORT ON

A slow Stored Procedure is extremely faster (going from 2 secs to 8 millisecs) with QUERYTRACEON 4136 and ARITHABORT ON, on the local SQL Server 2008 I use to develop. The "fixed" SP, on another SQL ...
0
votes
0answers
36 views

Will Parameter Sniffing happen? [duplicate]

I recently learned about the concept of parameter sniffing and I am having a confusion about a specific scenario where I am not able to understand the behavior of SQL Server. Anybody who can help me ...
0
votes
1answer
878 views

SET ARITHABORT, .NET, and explanations inconsistency?

A few days ago I happen to have stumbled on a lot of cases in a new DB I've been given where ARITHABORT set to OFF literally devastate performances. Being my first time facing this specific problem I ...
1
vote
0answers
339 views

Poor Query performance when scalar variables used in Query [duplicate]

Facing a strange issue that a query runs incredibly slow (worse) when scalar variables are used as parameter. Actual Query (Faster) - 9 secs select sum(v.sale_value) as col_0_0_, sum(v.vat_value)...
2
votes
1answer
2k views

Same query different execution plans

I am trying to optimize performance for a server and this particular query was causing huge reads from database, in turn causing timeout for queries. This query is generated from EF6 in Asp.Net MVC. ...
78
votes
4answers
30k views

Why is my query suddenly slower than it was yesterday?

[Salutations] (check one) [ ] Well trained professional, [ ] Casual reader, [ ] Hapless wanderer, I have a (check all that apply) [ ] query [ ] stored procedure [ ] database thing maybe that ...
0
votes
1answer
918 views

Does oracle database suffer from parameter sniffing issue

Does oracle database suffer from parameter sniffing issue in stored procedures? Considering there are millions of pages written on parameter sniffing issue, I think that this is more of a bug than a ...
3
votes
1answer
906 views

Does parameter sniffing exist for views?

I've heard of and encountered parameter sniffing in stored procedures before. But today I ran into the first instance of what I can only assume is parameter sniffing in a view. I have a query that ...
7
votes
2answers
909 views

Why does this OPTIMIZE FOR UNKNOWN improve my query by several seconds?

Ok, so I have a non-stored-procedure query that we're using in an SSRS report. This query has been hellishly slow (I've had the original version of this query running for the past two hours, still not ...
5
votes
1answer
2k views

Disable parameter sniffing for better performance - good idea?

I have an optimized query in SQL Server 2012 that takes consistently ~1-3 seconds to run. It works fast when I run it in SSMS, but when application does it (through sp_executesql), it is really slow, ...
4
votes
1answer
241 views

Execution plan caching of ITVF called with inline vs dynamic SQL

I would like someone to explain to me how each of the two queries below would generate their execution plan in SQL Server where my function [dbo].[GetAdditionalProjectDatesForCalendar] is an ITVF (...
3
votes
1answer
388 views

Methods to identify/recognise 'bad' parameter sniffing in SQL Server

There's lots of superb information out there about the certain approaches that can be used to mitigate bad parameter sniffing, but there's not a lot of information about how to go about identifying a ...
8
votes
2answers
485 views

Parameter sniffing work arounds

I have used two methods to get around parameter sniffing issues in the past: 1) Use WITH RECOMPILE 2) Reassign parameter values to local variables and use those instead of the parameters From what ...