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.
36
questions
2
votes
1
answer
108
views
Stored Procedure Generating Multiple Plans for Different Parameters in SQL Server
I have a stored procedure that is producing slightly different plans for different parameters. I would expect it to use the same plan that was generated when it was run the first time.
CREATE TABLE ...
- 435
2
votes
1
answer
290
views
Parameter Sniffing in SQL Server 2022
I'm working on the SQL Server performance area and I want to know that is the Parameter Sniffing problem resolved in SQL Server 2022 completely ?
What trace flags are useful in this area ?
Thanks in ...
- 159
4
votes
1
answer
368
views
Parameter Sniffing - Hints and fixes
I recently dealt with a problematic stored proc.
At times, runs beautifully fast, other times, runs very very long.
I determined that bad parameter sniffing was the cause.
Just for info - The ...
- 181
3
votes
1
answer
379
views
Performance problems in SQL Server. Parameter Sniffing or not in my scenario?
Here I am facing a performance problem that occurs with particular statements in a stored procedure, that has many statements, on occasion
SP is executed once a second, normally completes less than 50 ...
- 5,302
0
votes
0
answers
91
views
Improve a query parameter sniffing scenario
I have to admit I am a computer programmer, and not a DBA so I lack a few prerequisites to do this analysis work properly.
That said, I have optimized an ORM generated query, and now the performance ...
- 358
1
vote
0
answers
131
views
Stored procedure running indefinitely, same statements executed in batch completes in a second; WITH RECOMPILE has no effect
Why would a proc just run without terminating (increasing IO and CPU in sp_whoisactive, nothing blocking) when all the exact same statements run fine by themselves in SSMS in a few seconds, but the ...
- 6,612
1
vote
2
answers
316
views
Can parameter sniffing happen even after disabling it?
I'm having a very strange issue with an EF linq query, the query runs a dynamic sql, something like this:
EXEC sp_executesql N'select 1 from X',N'@parameter1 int',@parameter1 = 123
The query was ...
- 11
0
votes
1
answer
187
views
Does sp_recompile performance enhancement always link to parameter sniffing?
We have a production app which runs ok normally, but gave us very poor performance this week. As it was very urgent, I identified the problematic procedure and ran sp_recompile against it (forgot to ...
- 167
3
votes
2
answers
945
views
Parameter Sniffing and Multiple Plans in Cache
We have a multi-tenanted database. FirmID is the partition key and we have lots of different firms.
I am running into a parameter sniffing issue and I am having a heck of a time getting around it.
I ...
- 140
4
votes
1
answer
232
views
Date index SEEK is chosen when using OPTION RECOMPILE but not with OPTION OPTIMIZE FOR
I have a table containing 10 years worth of 'package scans'. Somebody scans a package and it records the date and username. Let's pretend for now that retaining 10 years of data actually has a purpose....
- 215
4
votes
3
answers
748
views
General Question on sp_recompile
For couple of scenarios, not many a times but few we have seen doing sp_recompile on a stored proc improving the performance.
Being a DBA i understand few of caveats involved when doing sp_recompile ...
- 2,169
0
votes
0
answers
26
views
Can parameter sniffing happen in other RDBMSs, not just SQL Server? [duplicate]
I just found out that when googling for the parameter sniffing, SQL server is also on the recommended search list but not other RDBMS such as Oracle, MySQL, PostgreSQL. Even in this community, ...
- 499
5
votes
2
answers
2k
views
Does changing a parameter value in a stored procedure before the query affect the cardinality estimate?
I routinely "scrub" the parameters of my stored procedures at the top before I run the query like this:
-- Scrub params
SET @SearchText = NULLIF(@SearchText, '')
SET @ThreadId = NULLIF(@...
- 163
0
votes
1
answer
311
views
Azure SQL database query performance degradation
I have a Azure SQL database (compatibility level = 150). I'm running into a performance problem executing a database query.
So after I've rebuilt all my indexes, the query runs fast. When testing, ...
- 1
0
votes
0
answers
39
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
0
answers
81
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 ...
- 499
1
vote
1
answer
367
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 ...
- 13
4
votes
1
answer
615
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 ...
- 143
7
votes
2
answers
928
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, ...
- 297
0
votes
1
answer
106
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. ...
- 61
0
votes
1
answer
2k
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 ...
- 15.8k
1
vote
3
answers
225
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]
...
- 13
1
vote
1
answer
186
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 ...
- 288
0
votes
0
answers
40
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
1
answer
2k
views
SET ARITHABORT, .NET, and explanations inconsistency? [duplicate]
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 ...
- 288
1
vote
0
answers
350
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)...
- 299
3
votes
1
answer
5k
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.
...
- 149
86
votes
4
answers
47k
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 ...
- 38.2k
1
vote
1
answer
2k
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 ...
- 93
4
votes
1
answer
2k
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
2
answers
2k
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 ...
- 1,513
5
votes
1
answer
3k
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, ...
- 163
4
votes
1
answer
420
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 (...
- 301
4
votes
1
answer
614
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 ...
- 650
1
vote
1
answer
672
views
Using of OPTIMIZE FOR hints in a sql query
As far as I know, the OPTIMIZE FOR hint helps to avoid parameter sniffing.
For such a stored procedure:
SELECT * FROM Table WHERE param=@param1
We have two parameters S1 with high selectivity and S2 ...
- 143
8
votes
2
answers
653
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 ...
- 459