Questions tagged [plan-guides]
Questions about the plan guide feature of SQL Server. Plan guides are used to affect query optimization choices without changing the statement text.
20
questions
-2
votes
1
answer
42
views
Why force plan is not working with failure "NO_PLAN"?
We have query which is inserting data to heap:
INSERT INTO [heap]
(
80 COLUMNS...
)
SELECT
14 columns
65 NULL VALUES
, '...
- 295
0
votes
0
answers
17
views
sp_get_query_template doesn't work with ODBC timestamp
I have the following query:
SELECT COUNT(*) FROM (SELECT DISTINCT USER_NAME FROM LICENSE WHERE RESTRICTED_USE = 'T' AND ALLOCATED = 'T' UNION ALL SELECT USER_NAME FROM LICENSE WHERE RESTRICTED_USE = '...
0
votes
1
answer
89
views
SQL Plan Guides Use Same Plan Handle
Have a table that is written to a lot but the data is then removed by the app side and stays relatively constant. It is hit by multiple queries that are the same except for the IN clause, there can ...
- 427
0
votes
2
answers
539
views
why are estimated rows exponentially increasing with each join?
summary
We have an Object-Oriented database schema that we query with Hibernate with:
1 parent table (NAL_Actions)
7 child tables (NAL_*)
If I want to query a couple of these "action"-...
- 101
0
votes
1
answer
79
views
Does plan recompilation happens to plan guide?
Does plan recompilation happens for plan guide due to the, for example, auto update stats?
P.S Do not take into account adding a hint OPTION (RECOMPILE) in the plan guide
- 1,253
19
votes
1
answer
1k
views
Forced plans on readable secondaries
If a plan is forced on the primary in an Availability Group, is it applied to queries run on a secondary?
I'm looking for answers that cover both possibilities for plan forcing:
Plan Guides
Query ...
- 82.9k
6
votes
1
answer
1k
views
Exclamation point on plan guide
I created a plan guide using the following query:
EXEC sp_create_plan_guide
@name = N'Entity_Property fix',
@stmt = N'SELECT ID, ENTITY_NAME, ENTITY_ID, PROPERTY_KEY, CREATED, UPDATED, json_value ...
- 163
3
votes
1
answer
287
views
Unable to use plan guide with sp_execute
I have an application that's querying a 10 million rows table.
The query is filtering by an integer column which is indexed. This column contains only NULLs and the query always looks for non-NULL ...
- 602
2
votes
0
answers
132
views
Plan cache mystery
I work on SQL Server 2016 and i had severe performance issues with few queries.
So I posted several posts in here and got solution/suggestion etc and greatly improved the execution time and learned a ...
- 1,295
2
votes
2
answers
670
views
Does Query Plan cache gets cleared by itself?
I have been working on query plan stored in plan cache and every time i search for a particular query with the below sql query,
SELECT cp.objtype AS ObjectType,
OBJECT_NAME(st.objectid,st.dbid) AS ...
- 1,295
2
votes
0
answers
414
views
Plan Guide for the LINQ-SQL Query
I have a query which is slow and it takes 15-20 sec when i run from the application.So i captured that query using SQL profiler and executed on SSMS with OPTION(RECOMPILE) and the query runs faster in ...
- 1,295
1
vote
0
answers
201
views
Troubleshooting non-working Plan Guide in SQL Server 2008 R2
I have problems with a Plan Guide I have created in Microsoft SQL Server 2008 R2. The Plan guide is purposed for a performance issue in Microsoft Dynamics AX 2009 and forces a specific question to use ...
- 11
2
votes
0
answers
122
views
SQL Plan Guides
I have one SQL Server 2016 instance that every night BizTalk inserts messages into the database. BizTalk is managed by a third party company, and the way they are inserting the messages is causing a ...
- 39
7
votes
1
answer
826
views
SQL Server Plan Guide
I have a query that needs a plan guide, but I am having a hard time setting it up.
Query below from the procedure cache...
(@state nvarchar(14),
@jobName nvarchar(18),
@jobGroup nvarchar(28),
@...
- 315
0
votes
2
answers
232
views
How to manipulate the query plan so that I can have more control on memory grants?
Let's say I have a complex query and for this query there are huge
Differences between the estimated and actual execution plans.
These differences are causing spillage into tempDb as described here.
...
- 15.8k
3
votes
2
answers
1k
views
SQL Guide Plan not being used?
I'm try to get force Parameterization on a simple adhoc SQL query.As explained in this article https://www.simple-talk.com/sql/performance/fixing-cache-bloat-problems-with-guide-plans-and-forced-...
- 689
3
votes
1
answer
347
views
Plan guide validation with fn_validate_plan_guide gives false positives
When validating a plan guide for a piece of SQL in a stored procedure that references a temporary table named "#test" the function fn_validate_plan_guide returns the error: Invalid object name '#test'....
- 5,715
9
votes
2
answers
3k
views
Why is my plan guide not being used?
We recently ran into the tipping point issue and some of our report queries that used to complete execution within a couple of seconds are now taking more than 2 min because the query optimizer simply ...
- 2,427
2
votes
0
answers
551
views
Multi-Statement Template Plan Guide
A piece of, um, software seems to be issuing ~20k queries of this form every few seconds:
DELETE FROM macVendor WHERE companyId=N' 08-00-'; INSERT INTO macVendor (companyId,organization) VALUES (N' ...
- 131
5
votes
1
answer
2k
views
Creating plan guide for query called through sp_executesql
To make a long story short, I have a view called vwRelatives which uses CTE recursion to build family trees. It's meant to be queried for a single person at a time.
This runs in about a quarter ...
- 9,638