Linked Questions
10 questions linked to/from Why does SQL Server use a better execution plan when I inline the variable?
2
votes
2
answers
750
views
Why does a parameterised query lead to an index scan but hardcoded values leads to index seek? [duplicate]
I'm getting significantly different performance from a query that uses parameters vs the same query where the same values are hardcoded in the where clause.
It comes down to 2 child tasks in the ...
- 141
0
votes
0
answers
21
views
Local variables and their impact on execution plans in SQL Server Stored Procedure [duplicate]
If a parameter that is passed to stored procedure is assigned to a local variable and the local variable is used inside the stored procedure logic, will that affect the execution plan in any way? Is ...
- 319
5
votes
1
answer
691
views
How can I improve the performance of where clause with LIKE %abc?
I had below select statement that turned out very slow (41s)
SELECT DISTINCT a.Doc_Resource_ID
,a.Parent_ID
,a.Logical_Path
,a.lvl
,CAST(SUBSTRING(a.Security_Level, 21, ...
- 59
10
votes
1
answer
538
views
Why does a plan with FULL optimization show simple parameterization?
I read that only Trivial Plans can be Simple Parameterized, and that not all queries (even when the plan is Trivial) can be Simple Parameterized.
Then why is this plan showing Full Optimization, and ...
- 36.7k
5
votes
1
answer
502
views
Query plan not reused for identical queries sp_executesql vs adhoc
One of the queries in my application is timing out. With the SQL Server profiler I was able to get the exact faulty SQL code. It's an sp_executesql dynamic statement that runs in > 2 minutes in ...
- 150
2
votes
2
answers
810
views
WHERE Clause Compare VarChar(n) Column = VarChar(MAX) Variable Results in Inequality Comparison and Poor Query Performance
Earlier today I was working on a Reporting Stored Procedure which had poor performance. I was able to fix the issue by changing a static variable which held a string of ten characters or less from ...
- 1,001
0
votes
2
answers
806
views
SQL Server - Can I surgically remove a bad cached query plan or am I chasing the wrong idea?
Let me set the stage:
SQL Server > 2016
Several databases with the same schemas
Data is similar but not identical
The same indexes exist etc., all systems but one work as expected
Examining the ...
- 463
0
votes
2
answers
985
views
Unexpected index scan instead of seek on update
We have a third party application which is performing badly. An update statement is performing a scan instead of a seek, and is not suggesting a missing index. Why is this happening?
I was expecting ...
- 129
1
vote
2
answers
107
views
SQL Server estimating badly
I have a heap with over a million inserts daily. This table is a "staging table" where messages are received and sent to differens queues that are processed by the application.
A stored proc ...
- 13
0
votes
1
answer
43
views
Average Density & cardinality estimation
In a large web application we had a query that was running slow in code, that seemed to be working blazing fast in SSMS. After checking the basics like the same SET options, we found that there was ...
- 113