Database Administrators Stack Exchange
Database Administrators 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.

20 questions
20
votes
1 answer

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 Store Forced Plan I have read the following that…
Paul White
  • 94,921
  • 30
  • 437
  • 687
9
votes
2 answers

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 ignores the non clustered index on the search…
RK Kuppala
  • 2,447
  • 1
  • 22
  • 24
7
votes
1 answer

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), @oldState nvarchar(6)) UPDATE JOB_TRIGGERS SET…
Goforebroke
  • 315
  • 1
  • 7
6
votes
1 answer

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 FROM jirascheme.entity_property WHERE ENTITY_NAME=@P0…
FLeX
  • 163
  • 5
5
votes
1 answer

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 second: SELECT * FROM vwRelatives WHERE person_id =…
db2
  • 9,708
  • 4
  • 37
  • 58
3
votes
1 answer

Does plan guide consider spaces or not?

I'm reading the documentation to sp_create_plan_guide and I find it hard to understand. My questions are: Are the white spaces considered or not when matching? Is there any difference between white spaces and blank spaces? What about leading and…
Endrju
  • 237
  • 1
  • 7
3
votes
1 answer

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 values, so it should go fast. The thing is that the…
Mattia Nocerino
  • 502
  • 5
  • 19
3
votes
1 answer

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'. But the the plan guide still pushes the query…
James Anderson
  • 5,794
  • 2
  • 27
  • 43
2
votes
0 answers

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' 08-00-',N'TEKTRONIX INC.'); I don't know why,…
Fowl
  • 131
  • 4
2
votes
0 answers

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 lot. Recently I got to know that most of the…
user9516827
  • 1,345
  • 3
  • 19
  • 41
2
votes
2 answers

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 ObjectName, cp.usecounts AS…
user9516827
  • 1,345
  • 3
  • 19
  • 41
2
votes
0 answers

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 2 seconds. I checked the query plan cache and…
user9516827
  • 1,345
  • 3
  • 19
  • 41
2
votes
0 answers

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 lot of RESOURCE_SEMAPHORE_QUERY_COMPILE waits on…
Bruno Miguel
  • 39
  • 2
2
votes
2 answers

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-parameterization/ But even trying to do this with the…
davey
  • 679
  • 1
  • 12
  • 26
1
vote
0 answers

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 a TABLE HINT index. However, when I perform a…
user8217802
  • 11
  • 1
1
2