Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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.

6
votes
1answer
472 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 ...
3
votes
1answer
69 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 ...
2
votes
0answers
95 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 ...
2
votes
2answers
71 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 ...
2
votes
0answers
108 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
vote
0answers
80 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 ...
3
votes
0answers
95 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 ...
6
votes
1answer
375 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), @...
0
votes
2answers
144 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. ...
1
vote
2answers
248 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-...
3
votes
1answer
182 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'....
8
votes
2answers
2k 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
votes
0answers
322 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' ...
5
votes
1answer
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 ...