26,060 reputation
656103
bio website
location United Kingdom
age
visits member for 2 years, 6 months
seen Apr 3 at 19:03

Email: stackoverflowmartinsmith@gmail.com


Jan
23
comment Why does SQL Server run a subquery for each row of the table it's qualifying?
By the way what is the point of this table? Is the pk_id column referenced elsewhere? If not you should drop it and just have year,month. But in that case as there are no other columns it seems a bit of a waste of space and you could just have a table with a row per month,year and a count column that you increment.
Jan
23
comment Why does SQL Server run a subquery for each row of the table it's qualifying?
@RBarryYoung - Paul White looks at this specifically in his answer here. Does that help?
Jan
23
comment Why does SQL Server run a subquery for each row of the table it's qualifying?
@RBarryYoung - Because it is under a TOP 1 so they get scaled down for a row goal. SQL Server estimates that the TOP will stop requesting rows after the first row is received. In fact as the first 4,424,803 rows out of the index scan have the same year it takes many more than that.
Jan
23
revised Why does SQL Server run a subquery for each row of the table it's qualifying?
added 140 characters in body
Jan
23
comment Why does SQL Server run a subquery for each row of the table it's qualifying?
@RBarryYoung - Well the plan with the variable isn't great either! If there were only a handful of duplicates for the TOP 1 this would be the best plan. The bug to me is that it doesn't look at average selectivity for that column when estimating rows for TOP 1 WITH TIES
Jan
23
revised Why does SQL Server run a subquery for each row of the table it's qualifying?
added 454 characters in body
Jan
23
answered Why does SQL Server run a subquery for each row of the table it's qualifying?
Jan
23
comment Why does subquery use parallelism and join doesn't?
@crokusek - Unless I am misunderstanding you. You would need OPTION (RECOMPILE). For the following code DECLARE @id int = (SELECT id FROM location WHERE location = 'Derby');SELECT * FROM testruns WHERE location_id = @id OPTION (RECOMPILE) I see correct actual vs estimated rows. Actual execution plan
Jan
23
awarded  Enlightened
Jan
23
awarded  Nice Answer
Jan
23
comment Why does subquery use parallelism and join doesn't?
@crokusek Yep. looks like same estimated rows out of the join as for the sub query in that singleton case.
Jan
23
comment Why does SQL Server run a subquery for each row of the table it's qualifying?
Can you supply the execution plans?
Jan
23
comment Why does subquery use parallelism and join doesn't?
@crokusek yes. Realised what you meant afterwards and deleted my comment! Does that increase the estimated number of rows for the join version to be same as the subquery as well? Not on PC at moment to test?
Jan
23
revised Why does subquery use parallelism and join doesn't?
deleted 6 characters in body
Jan
23
answered Why does subquery use parallelism and join doesn't?
Jan
22
comment How can a blocking_session_id be negative?
See redmondmag.com/articles/2013/11/21/negative-session-id.aspx?m=2
Jan
22
comment How to improve Procedure Cache Hit Ratio?
Server specifications? How much is "plenty of RAM"? 32 bit? 64 bit? Many adhoc queries?
Jan
22
comment Why does subquery use parallelism and join doesn't?
@AaronBertrand - I wonder if there are any statistics at all TBH. Perhaps auto create stats and auto update stats are turned off and this is the ensuing result.
Jan
22
comment Why does subquery use parallelism and join doesn't?
@AaronBertrand - Actually my above comment is incorrect. It is 26244 /3 = 8748. There are also 3 rows in the source table location. Just seems odd that the estimate for a complete join is much less than that for the join of a single row.
Jan
22
comment Why does subquery use parallelism and join doesn't?
@MikeFal - Because I haven't got a complete answer yet. The obvious difference is that the join from location to testruns is expected to emit one row in one plan and 8,748 in the other. I'm not sure why the huge discrepancy. 8,748 is 0.3 * 26244 (table cardinality)