I read "Distributing SQL Server Execution Plans" from Grant Fritchey, and it helps me find out a lot why some queries are slow.
However, I am at a standstill with this case where a simple rewriting process is much faster.
This is my first attempt, and it takes 21 seconds. It uses a view:
SELECT *
FROM Table1 AS o JOIN(
SELECT col1
FROM Table1
GROUP BY col1
HAVING COUNT( * ) > 1
) AS i ON ON i.col1= o.col1
My second attempt is 3 times faster and just moves the view to the temporary table. Now it is 3 times faster:
SELECT col1
INTO
FROM Table1
GROUP BY col1
HAVING COUNT( * ) > 1
SELECT *
FROM Table1 AS o JOIN
My main interest is why moving from a view to a temporary table improves performance so much, and not how to make it even faster.
, - , , () .
Xml :
https://www.sugarsync.com/pf/D6486369_1701716_16980

1
2 , , " ", ( , !). , . sqlplan 2 , .
, , temp table. , ( ), .
A ( ) 2- , stat , .
2 stat , . @Grant , , ?
2
, , .
, . , , .
, :
, XML, , .
"" "EstimateRows"
- : SQL Sentry Plan Explorer
http://www.sqlsentry.net/
, . .

- : SSMS
http://www.ssmstoolspack.com/
,

,