SQL Server Error Association

Many of SQL Server's weird problems are caused by incorrect Association, which has been improved from 2000 to 2005, but 2005 of query optimization engines are still "silly ".

1. Question 1
Symptom: A stored procedure can be called by a service program for a long time and cannot end. The database server displays that the stored procedure has been waiting for a certain statement, and the database server has sufficient memory, almost no CPU consumption. Take out the stored procedure and execute it directly in the query analyzer. The parameters are exactly the same as those when the program is called, and the results are correct and ended immediately.
Solution: blocking and other reasons are excluded, because everything is normal during execution in the query analyzer, and no problems can be identified from the execution plan and other aspects, as well as disk IO and other reasons, I can't think of anything else. Finally, it is suspected that the SQL Server query engine JOIN method is incorrect. After HASH JOIN is forced, the program call will return to normal.
Doubt: no difference has been found between Program Calling and direct execution using the query analyzer, which will affect the SQL Server query optimization decision-making.

2. Question 2
Symptom: The row_number function is used for paging a non-complex query. When executed, all eight CPUs on the server are 100%, which cannot be completed for a long time (several minutes. Use a temporary table to achieve the same effect, completed in seconds
Statement 1:
SELECT
ROW_NUMBER () OVER (order by COLUMNNAME1 ASC) as FC_ROWNUMBER
, COUNT (1) OVER () AS FC_COUNT
, * FROM (
SELECT Product as COLUMNNAME1, product description as COLUMNNAME2, storage date as COLUMNNAME7, expected quantity as COLUMNNAME8
, As COLUMNNAME9, row status as COLUMNNAME10, supplier as COLUMNNAME11, supplier name as COLUMNNAME12
From v _ goods receipt details Query
Where warehouse receiving date> = '2017-7-1'
) TT_MAINKEY_TMP
Execution Plan:

Statement 2:
SELECT * FROM (
SELECT
ROW_NUMBER () OVER (order by COLUMNNAME1 ASC) as FC_ROWNUMBER
, COUNT (1) OVER () AS FC_COUNT
, * FROM (
SELECT Product as COLUMNNAME1, product description as COLUMNNAME2, storage date as COLUMNNAME7, expected quantity as COLUMNNAME8
, As COLUMNNAME9, row status as COLUMNNAME10, supplier as COLUMNNAME11, supplier name as COLUMNNAME12
From v _ goods receipt details Query
Where warehouse receiving date> = '2017-7-1'
) TT_MAINKEY_TMP
) TT_RET_TMP
WHERE FC_ROWNUMBER BETWEEN 1 AND 1000
Order by COLUMNNAME1 ASC
Execution Plan:

Simply using SQL as a subquery and packing it out, the entire query plan is different. Inaccurate statistics and index fragmentation are excluded.
In the two query plans, clustered index scanning is used for the three tables, basically because the Association Algorithms are different.
Because the parallel query is used, the data in the three tables has hundreds of thousands and hundreds of thousands, and the nested loop needs to be executed hundreds of thousands of times, so a single query results in 100,000 of all CPUs. It is estimated that the high CPU is caused by the Lazy Spool operation.

Solution:
Force hash join, or add an index to avoid SQL Server errors, or use a temporary table to bypass