请各位帮我分析一下dbcc tranceon(3604,302,310) 的结果
<p>select count(*) from T_Basic a,T_EStatus b where a.EID =b.EID</p><p>下面是我执行这条语句的跟踪结果，T_Basic和T_EStatus 都 有150万条数据，两张表的EID都是索引，大概执行15秒。</p><p>******************************* <br/>Beginning selection of qualifying indexes for table 'T_Basic', <br/>correlation name 'a', varno = 0, objectid 1856006612. <br/> The table (Datarows) has 461020 rows, 106683 pages, <br/> The table's Data Page Cluster Ratio 0.999990 </p><p>Table scan cost is 461020 rows, 107416 pages, <br/> using data prefetch (size 16K I/O), <br/> in data cache 'bizcache' (cacheid 1) with MRU replacement</p><p><br/>Index selection finished for table 'T_Basic' (objectid 1856006612). </p><p><br/>The best qualifying index is 'T_18560066121' (indid 5) <br/> costing 6570 pages, <br/> with an estimate of 461020 rows to be returned per scan of the table, <br/> using index prefetch (size 16K I/O) on leaf pages, <br/> in index cache 'bizcache' (cacheid 1) with LRU replacement</p><p>Index covers query. <br/>Search argumE selectivity is 1.000000. </p><p>******************************* </p><p>******************************* <br/>Beginning selection of qualifying indexes for table 'T_EStatus', <br/>correlation name 'b', varno = 1, objectid 2080007410. <br/> The table (Datarows) has 1419841 rows, 57705 pages, <br/> The table's Data Page Cluster Ratio 0.999723 <br/> The table has 20 partitions. <br/> The largest partition has 57666 pages. <br/> The partition skew is 19.514721. </p><p><br/>Table scan cost is 1419841 rows, 59081 pages, <br/> using data prefetch (size 16K I/O), <br/> in data cache 'bizcache' (cacheid 1) with MRU replacement</p><p>Index selection finished for table 'T_EStatus' (objectid 2080007410). </p><p>The best qualifying index is 'id_Eid' (indid 3) <br/> costing 21347 pages, <br/> with an estimate of 1419841 rows to be returned per scan of the table, <br/> using index prefetch (size 16K I/O) on leaf pages, <br/> in index cache 'bizcache' (cacheid 1) with LRU replacement <br/>Index covers query. <br/>Search argumE selectivity is 1.000000. </p><p>******************************* </p><p>******************************* <br/>Beginning selection of qualifying indexes for table 'T_Basic', <br/>correlation name 'a', varno = 0, objectid 1856006612. <br/> The table (Datarows) has 461020 rows, 106683 pages, <br/> The table's Data Page Cluster Ratio 0.999990 </p><p><br/>Table scan cost is 461020 rows, 107416 pages, <br/> using data prefetch (size 16K I/O), <br/> in data cache 'bizcache' (cacheid 1) with MRU replacement </p><p>Selecting best index for the JOIN CLAUSE: <br/> T_Basic.EID = b.EID </p><p>Estimated selectivity for EID, <br/> selectivity = 0.000002. <br/>Unique nonclustered index found, returns 1 row, 4 pages </p><p>Estimating selectivity of index 'T_18560066121', indid 5 <br/> scan selectivity 0.000002, filter selectivity 0.000002 <br/> 1 rows, 4 pages, index height 3, <br/> Data Row Cluster Ratio 0.997759, <br/> Index Page Cluster Ratio 0.932301, <br/> Data Page Cluster Ratio 0.991322 </p><p>The best qualifying Nested Loop join index is 'T_18560066121' (indid 5) <br/> costing 4 pages, <br/> with an estimate of 1 rows to be returned per scan of the table, <br/> using no index prefetch (size 2K I/O) on leaf pages, <br/> in index cache 'bizcache' (cacheid 1) with LRU replacement <br/>Index covers query. <br/>Join selectivity is 0.000002. </p><p>If this access path is selected for a merge join, this table will be sorted. </p><p>******************************* </p><p>******************************* <br/>Beginning selection of qualifying indexes for table 'T_EStatus', <br/>correlation name 'b', varno = 1, objectid 2080007410. <br/> The table (Datarows) has 1419841 rows, 57705 pages, <br/> The table's Data Page Cluster Ratio 0.999723 <br/> The table has 20 partitions. <br/> The largest partition has 57666 pages. <br/> The partition skew is 19.514721. </p><p><br/>Table scan cost is 1419841 rows, 59081 pages, <br/> using data prefetch (size 16K I/O), <br/> in data cache 'bizcache' (cacheid 1) with MRU replacement </p><p>Selecting best index for the JOIN CLAUSE: <br/> T_EStatus.EID = a.EID </p><p>Estimated selectivity for EID, <br/> selectivity = 0.000001. </p><p>Estimating selectivity of index 'id_Eid', indid 3 <br/> scan selectivity 0.000001, filter selectivity 0.000001 <br/> 1 rows, 4 pages, index height 3, <br/> Data Row Cluster Ratio 0.489111, <br/> Index Page Cluster Ratio 0.976601, <br/> Data Page Cluster Ratio 0.350660 </p><p>The best qualifying Nested Loop join index is 'id_Eid' (indid 3) <br/> costing 4 pages, <br/> with an estimate of 1 rows to be returned per scan of the table, <br/> using no index prefetch (size 2K I/O) on leaf pages, <br/> in index cache 'bizcache' (cacheid 1) with LRU replacement <br/>Index covers query. <br/>Join selectivity is 0.000001. </p><p>If this access path is selected for a merge join, this table will be sorted. </p><p>******************************* </p><p><br/>QUERY IS CONNECTED <br/>Number of tables in join: 2 <br/>Number of tables considered at a time: 2 <br/>Table count setting: 0 (default value used) </p><p> 0 - 1 - </p><p>NEW PLAN (total cost = 3780590): </p><p>varno=0 (T_Basic) indexid=5 (T_18560066121) <br/>path=0x2981B4EC pathtype=sclause <br/>method=NESTED ITERATION <br/>scanthreads=1 <br/>outerrows=1 rows=461020 joinsel=1.000000 scanpgs=6570 <br/>index_prefetch=YES index_iosize=16 index_bufreplace=LRU <br/>scanlio=6570 scanpio=1300 <br/>corder=1 </p><p>varno=1 (T_EStatus) indexid=3 (id_Eid) <br/>path=0x2679B000 pathtype=join <br/>method=NESTED ITERATION <br/>scanthreads=1 <br/>outerrows=461020 rows=462472 joinsel=0.000001 jnpgs_per_scan=4 <br/>index_prefetch=YES index_iosize=16 index_bufreplace=LRU <br/>scanlio=1844080 scanpio=3105 <br/>corder=3 </p><p>jnvar=0 refcost=0 refpages=0 reftotpages=16920 ordercol=3 ordercol=1 </p><p> 1 - 0 - </p><p>TOTAL # PERMUTATIONS: 2 </p><p>TOTAL # PLANS CONSIDERED: 12 </p><p>CACHE USED BY THIS PLAN: </p><p> CacheID = 1: (2K) 0 (4K) 0 (8K) 0 (16K) 27917 </p><p>FINAL PLAN (total cost = 3780590): </p><p><br/>varno=0 (T_Basic) indexid=5 (T_18560066121) <br/>path=0x2981B4EC pathtype=sclause <br/>method=NESTED ITERATION <br/>scanthreads=1 <br/>outerrows=1 rows=461020 joinsel=1.000000 scanpgs=6570 <br/>index_prefetch=YES index_iosize=16 index_bufreplace=LRU <br/>scanlio=6570 scanpio=1300 <br/>corder=1 </p><p>varno=1 (T_EStatus) indexid=3 (id_Eid) <br/>path=0x2679B000 pathtype=join <br/>method=NESTED ITERATION <br/>scanthreads=1 <br/>outerrows=461020 rows=462472 joinsel=0.000001 jnpgs_per_scan=4 <br/>index_prefetch=YES index_iosize=16 index_bufreplace=LRU <br/>scanlio=1844080 scanpio=3105 <br/>corder=3 </p><p>jnvar=0 refcost=0 refpages=0 reftotpages=16920 ordercol=3 ordercol=1 </p> 如何优化，是要根据你的主要业务来定，而不定某一句随便的SQL 嘛目的？ 感觉查询有点慢，想找一下原因，看能否优化一下。 也没有都１５０Ｗ嘛，一个 4x W，一个 14x W，一对多，<br/> 这是我用的另一个测试库做的，理论上这两张表数据行数应该是一样的，现在真实库中大概都有140多万条。这条语句还是最简单的，查询起来都很慢，复杂的查询就更慢了。
页:
[1]