1
vote
1answer
76 views

Query Plan Different When Using Compiled Values

I have pulled out an execution plan from the plan cache and from the XML pulled out the compiled values. Then I have run the query in SSMS using the compiled values and the query plans are different ...
1
vote
1answer
142 views

Understanding execution plans using TVPs

I have some slow running queries (8 seconds across 1M records grouped by months) and have been trying to make sense of the execution plans. We use around 10 TVPs to send in a set of filters that the ...
2
votes
1answer
90 views

Is there any difference on performance when choosing DISTINCT or GROUP BY to bring distinct values?

I would like to know if there is any difference concerning performance when choosing DISTINCT or GROUP BY to bring distinct rows from a query. I've tried comparing the execution plans, but they seem ...
7
votes
1answer
142 views

Query plan cache bloated by ad-hoc queries, even with “Optimize for Ad-hoc Workloads”

I've been noticing what I thought to be unusual issues with our query plan cache, where the plans in the cache were never more than a day old. Through running the following query (courtesy of ...
3
votes
1answer
71 views

Every query plan statistic says my query should be faster, but it is not

I have a bit of a odd situation. I have two queries that produce the same result, according to SSMS and SQL Sentry Plan Explorer the first query should cost 95% and the 2nd query should cost 5%. ...
8
votes
1answer
264 views

Re-run a specific actual query plan

I have captured an actual query plan for a specific query. After this I've changed a few things around (including updating the statistics) and re-ran that specific query. Now the actual query plan ...
4
votes
1answer
161 views

Why clustered index scan?

I just started to learn to optimize my queries and to analyze their query plans. I thought this query would generate nonclustered index seek + key lookup. SELECT ct.* FROM Person.ContactType AS ct ...
3
votes
1answer
190 views

Optimizing complex join predicates [closed]

Table Definitions CREATE TABLE [dbo].[Pin_Mtg] ( [MtgId] bigint NULL, [CntyCd] char(5) NOT NULL, [BatchDt] int NOT NULL, [BatchSeq] int NOT NULL, [MtgSeq] tinyint NOT NULL, ...
2
votes
2answers
74 views

On which server does a query execute?

Given two Microsoft SQL servers: Say ServerA has a view titled "View1": SELECT * FROM ServerB.database.dbo.Table1 WHERE Column1 IN (1,2,3) and a stored procedure "sp_Foo": SELECT * FROM View1 WHERE ...
2
votes
1answer
107 views

Execution Plan not matching Stored Proc

Below is a stored proc to count the number of widgets made in a month. If no widgets are made, no records will exists. The execution plan shows an INNER JOIN of the M and A tables, where in the ...
8
votes
1answer
193 views

Are the cost percentages in this SQL Server plan over 100% for a valid reason?

I'm looking through the plan cache, looking for low-hanging optimization fruit and came across this snippet: Why are many of the costs listed above 100% ? Shouldn't that be impossible?
17
votes
2answers
2k views

Optimising plans with XML readers

Executing the query from here to pull the deadlock events out of the default extended events session SELECT CAST ( REPLACE ( REPLACE ( XEventData.XEvent.value ...
9
votes
3answers
637 views

Why are there execution plan differences between OFFSET … FETCH and the old-style ROW_NUMBER scheme?

The new OFFSET ... FETCH model introduces with SQL Server 2012 offers simple and faster paging. Why are there any differences at all considering that the two forms are semantically identical and very ...
9
votes
1answer
562 views

Forcing an index spool

I know its something that should be avoided for performance reasons, but am trying to show a condition where it appears as a demo on how to make sure it does not appear. However, I end up with a ...