Database Administrators Stack Exchange
Database Administrators Stack Exchange

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.

Parameter Sniffing is a term mostly associated with SQL Server. It's often used to describe the phenomenon of a query plan that was cached with one set of parameters, that performs poorly with another set of parameters. It can happen to stored procedures, parameterized code, and queries called with sp_executesql, etc.

See this Q&A for guidance on collecting information for assistance: - Why is my query suddenly slower than it was yesterday?

39 questions
90
votes
4 answers

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 was running fine (if applicable) [ ] yesterday [ ] in…
Erik Reasonable Rates Darling
  • 45,549
  • 14
  • 145
  • 532
9
votes
3 answers

Performance issues in parameterized queries accessing a view in SQL Server

I am currently facing an issue with parameterized queries in SQL Server that I do not understand where it is rooted in. I broke it down to a simple example: Let's assume a table that holds data about some child entity as well as the parent_id and a…
jkohl
  • 93
  • 5
9
votes
2 answers

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 I understand, the end result of both of these is…
Abe Miessler
  • 479
  • 1
  • 8
  • 18
7
votes
1 answer

Execution slow in stored procedure; fast when run ad hoc

I am dealing with an issue that I can't seem to solve. I was given this stored procedure that has speed issues. It takes 35 seconds or so. When run adhoc, it completes instantly. This made me think it was something to do with parameter sniffing. I…
user290775
7
votes
2 answers

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, should parameter sniffing be false, or does one take…
Darkwing
  • 299
  • 1
  • 2
  • 7
7
votes
2 answers

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 done), in an effort to improve it I rewrote it…
Der Kommissar
  • 1,523
  • 1
  • 16
  • 27
5
votes
1 answer

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 parameters in the proc are datetime and the query uses…
Sicilian-Najdorf
  • 381
  • 4
  • 13
5
votes
3 answers

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 and why it would have worked in improving with…
BeginnerDBA
  • 2,230
  • 3
  • 27
  • 55
5
votes
2 answers

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(@ThreadId, 0) SET @Hashtag = NULLIF(@Hashtag, '') But then from…
adam0101
  • 163
  • 1
  • 5
5
votes
1 answer

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, about 13 minutes for larger data. After some…
jahav
  • 163
  • 1
  • 5
4
votes
1 answer

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. I have a page to show a summary for the past…
Simon_Weaver
  • 225
  • 2
  • 7
4
votes
1 answer

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 with different query plans? Where can I learn more…
Vlad
  • 143
  • 3
4
votes
1 answer

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 selects from a number of tables and a view. It's…
Corpulent Brony
  • 39
  • 2
4
votes
1 answer

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 (inline table-valued function): Dynamic…
skeletank
  • 301
  • 2
  • 9
4
votes
1 answer

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 bad parameter sniffing problem. Assuming you're…
Fza
  • 652
  • 1
  • 9
  • 19
1
2 3