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
0 answers
62 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 ...
user avatar
  • 287
1 vote
0 answers
87 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 ...
user avatar
  • 6,592
1 vote
2 answers
171 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 ...
user avatar
0 votes
1 answer
100 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 ...
user avatar
  • 167
3 votes
2 answers
392 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 ...
user avatar
3 votes
1 answer
95 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....
user avatar
4 votes
3 answers
513 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 ...
user avatar
  • 2,137
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, ...
user avatar
3 votes
2 answers
1k 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(@...
user avatar
  • 143
0 votes
1 answer
164 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, ...
user avatar
  • 1
0 votes
0 answers
33 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'...
user avatar
0 votes
0 answers
75 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 ...
user avatar
1 vote
1 answer
288 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 ...
user avatar
4 votes
1 answer
416 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 ...
user avatar
  • 143
7 votes
2 answers
797 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, ...
user avatar
0 votes
1 answer
57 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. ...
user avatar
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 ...
user avatar
1 vote
3 answers
206 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] ...
user avatar
1 vote
1 answer
167 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 ...
user avatar
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 ...
user avatar
0 votes
1 answer
1k 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 ...
user avatar
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)...
user avatar
  • 278
3 votes
1 answer
4k 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. ...
user avatar
  • 149
82 votes
4 answers
42k 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 ...
user avatar
  • 34.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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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, ...
user avatar
  • 163
4 votes
1 answer
364 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 (...
user avatar
  • 291
3 votes
1 answer
531 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 ...
user avatar
  • 630
8 votes
2 answers
602 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 ...
user avatar