You searched for the word(s):
Showing page 1 of 50 (493 total posts)
< 1 second(s)
great investigation. it looks like CE is doing what I described above. presumably this would not be invoked unless the plan cost was high, and perhaps also if it is known that the column in the next table was heavily skewed, making the effort worth while. If there is an index on the SARG column of the first table, followed by the join column, then ...
temp tables can be particularly troublesome. I have seen the optimized assumed that the join to a temp table column was many-to-many, example 1000 rows in the temp table, expected to match to 1:1 or 1:few,
but the optimizer assumed each row from temp mapped to 1000 in the permanent. Sometime just creating an index you don't need, but it fixed ...
This topic is titled to specifically consider only row estimation after joins, precluding discussion of row estimates at the source table, which has already been addressed in papers covering the new Cardinality Estimator in SQL Server 2014 and other statistics papers for previous versions of SQL Server.
There are certain situations in which ...
note to companies publishing TPC-H results on SQL Server. It might be wise to take up TK's offer. It would be hard to find better quality advise. I would be interested too. It would be even helpful if you provided the actual sqlplan files, if not generally, then just to us.
by my calculation, a 3X improvement in one of the 22 TPC-H queries (plus 2 refresh statements) contributes a 4.7% improvement in the Power test X^(1/24)
If two queries can be improved by 3X each (Q10 and Q4) then that's a 9.6% gain on Power.
Earlier I had commented on the TPC-H results
published in April of this year for SQL Server 2014
using clustered column store storage, noting that two of the 22 TPC-H queries did not perform
well in column store.
I had speculated on the reason without investigation (I should have learned by now not to do this),
that perhaps the cause ...
On the tpch_interim link
in the section
TPC-H SF 100 Execution Plans
you have to save target as.
the links have the *.html extension
but are actually sqlplan files
for example note the name
so save this as tpch_sf100_nonparallel_nc.sqlplan
Three TPC-H benchmark results were published in April of this year at SQL Server 2014 launch,
where the new updateable columnstore feature was used.
SQL Server 2012 had non-updateable columnstore that required the base table to exist in rowstore form.
This was not used in the one published TPC-H benchmark result on SQL Server 2012,
I would not recommend setting sys.configures max degree of parallelism to 1 even if certain queries are set specifically to a higher value.
I do agree with setting the system wide value low, but > 1.
It is not appreciated that certain query plans are actually more CPU efficient at DOP > 1. Not just run faster with lower elapsed time, but ...
Let me reiterate that: yes this information is available in WMI, but exposing it via DMV is better, especially since the SQL Server knows it.
for now, try appropriate variations of the this:
EXEC sys.xp_readerrorlog 0, 1, "detected", "socket"