1
vote
2answers
54 views

Should I include as much information as possbile to help the optimiser?

Reading the article "Dynamic Search Conditions in T‑SQL" I saw the following statement in the "Using sp_executesql" section. Double Feature You may wonder why there are two conditions on the ...
0
votes
0answers
97 views

How can I get rid of the “No Join Predicate” warning

I have this fairly straight-forward snippet of a query: DECLARE @ClientGUID numeric(16) = 9000011916900200; SELECT ch.GUID, c.LastName, c.FirstName, mrn.ClientIDCode AS MRN FROM Client c INNER ...
3
votes
1answer
396 views

Why does SQL Server run a subquery for each row of the table it's qualifying?

This query runs in ~21 seconds (execution plan): select a.month , count(*) from SubqueryTest a where a.year = (select max(b.year) from SubqueryTest b) group by a.month When the subquery ...
0
votes
1answer
380 views

Query performance and join hints, plan cost, and duration

Having some trouble identifying why a query's duration would decrease when using OPTION (HASH JOIN) or OPTION (MERGE JOIN), although plan cost increases. Background I have a reporting database using ...
28
votes
3answers
2k views

Unexpected scans during delete operation using WHERE IN

I've got a query like the following: DELETE FROM tblFEStatsBrowsers WHERE BrowserID NOT IN ( SELECT DISTINCT BrowserID FROM tblFEStatsPaperHits WITH (NOLOCK) WHERE BrowserID IS NOT NULL ) ...
4
votes
1answer
565 views

Does SQL Server evaluate functions once for every row?

I have a query like this: SELECT col1 FROM MyTable WHERE DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) BETWEEN col2 AND col3 ; This gives a tooltip on the execution plan ...