In figure 1, there are SQL statements , these 2 sql statments join 2 tables, one is on current database server, the other is on another database server. the first SQL statement runs by SQL Server linker server to join; the second SQL statement uses openquery and linked server to get same data. and in figure 2 to figure 4 show its execution plan and statistics information (IO/time). now I have some questions below.
1. can we use Estimated substree Cost to estimate the total query cost for the SQL execution cost ? if not, we use different ways to encode sql scripts to get same result, how to estimate which way (sql statement) best is? is there a parameter for this estimation under SQL server execution plan?
2. can we use the Query cost (relative to the batch) percentage to estimate which one best ?
3. which parameters are used to compute the Query cost for the execution plan
4. in figure 4, for second sql statement, the logic reads is 100518 and scan count is 23107, it logic reads and scan count is much higher than the first sql statement, why its query cost is 15% and the first sql statement is 85% ? noramlly, if there are many IOs, it will take more time, in this case, why its IO bigger but query cost ?
5. in the said 2 sql statements, it only joins 2 tables, without joining table worktable, why there is worktable under it statistics inforamtion?
6. in Figure 5, I just retrived data from one table, how to understand the relations among the part1 to part4? which is the detail information and which is the summary info? what are the relations of them ?
thanks everyone for your kind help and instruction!