Last call to make your voice heard! Our 2022 Developer Survey closes in less than a week. Take survey.

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.

Filter by
Sorted by
Tagged with
0 votes
0 answers
40 views

MSSQL plan guide usage on AAG replica

I'm trying to use plan guide on read only replica. I read some articles ( for instance this one ) and concluded that this is impossible. But then tried to perform some tests and got pretty unclear ...
user avatar
  • 173
0 votes
2 answers
233 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"-...
user avatar
  • 101
0 votes
1 answer
55 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
user avatar
  • 1,163
16 votes
1 answer
752 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 ...
user avatar
  • 69.4k
6 votes
1 answer
814 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 ...
user avatar
  • 163
3 votes
1 answer
181 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 ...
user avatar
2 votes
0 answers
130 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 ...
user avatar
  • 1,285
2 votes
2 answers
359 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 ...
user avatar
  • 1,285
2 votes
0 answers
268 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 ...
user avatar
  • 1,285
1 vote
0 answers
147 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 ...
user avatar
2 votes
0 answers
117 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 ...
user avatar
7 votes
1 answer
709 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), @...
user avatar
0 votes
2 answers
199 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. ...
user avatar
3 votes
2 answers
947 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-...
user avatar
  • 659
3 votes
1 answer
282 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'....
user avatar
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 ...
user avatar
  • 2,397
2 votes
0 answers
477 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' ...
user avatar
  • 121
5 votes
1 answer
1k 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 ...
user avatar
  • 9,468