All Questions
Tagged with parameter-sniffing sql-execution-plan
8 questions
0
votes
1
answer
185
views
Stored procedure has different plans in different databases, can't replicate better plan in main database
Can you point me in the right direction on where to look to figure out why one plan cache is different from the other one in another database. One database is older and has less data but the schema ...
- 23.8k
0
votes
1
answer
32
views
Does query form have an influence on getting into parameter sniffing?
Recently, one of my colleague working in SQL development got into a problem like this: a procedure ran fine on all environments, but production, which has the most resources. Typical case of parameter ...
- 23.2k
3
votes
1
answer
1k
views
Running SQL query with options(recompile) always faster
this is a possible duplicate of this question, though I think my example is a bit more in depth and I would appreciate more definite answers.
I'm trying to understand why running the simple query with ...
- 2,558
1
vote
2
answers
4k
views
Copy Stored Proc Execution Plan to Another Database
Setup:
Using SQL Server 2008 R2.
We've got a stored procedure that has been intermittently running very long. I'd like to test a theory that parameter sniffing is causing the query engine to choose ...
- 5,504
0
votes
1
answer
7k
views
SQL Server sp_recompile on a table
When sp_recompile is run against a table, I understand that all stored procedures and triggers dependent on that table will be recompiled.
What I don't understand is what parameters SQL Server uses ...
- 1,574
0
votes
2
answers
132
views
Getting around cached plans in SQL Server 2005
Can someone please explain why this works. Here is the scenerio. I have a stored proc and it starts to run slow. Then I pick a parameter and declare a variable to house its value and in the proc ...
- 1,038
12
votes
3
answers
37k
views
SQL poor stored procedure execution plan performance - parameter sniffing
I have a stored procedure that accepts a date input that is later set to the current date if no value is passed in:
CREATE PROCEDURE MyProc
@MyDate DATETIME = NULL
AS
IF @MyDate IS NULL SET @...
- 86.9k
1
vote
3
answers
1k
views
T-SQL Process Design and Execution Plan (UDF Parameter Sniffing?)
On SQL Server 2005, I have a complex multi-level allocation process which looks like this (pseudo-SQL):
FOR EACH @LVL_NUM < @MAX_LVL:
INSERT INTO ALLOCS
SELECT 'OUT', *
FROM BALANCES(@LVL_NUM)
...
- 89.8k