性能问题

银铠圣袍 · 2007-6-15 11:08:14

马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。

您需要登录才可以下载或查看，没有账号？站点注册

×

12.5.0的查询计划

QUERY PLAN FOR STATEMENT 1 (at line 1).

    STEP 1
        The type of query is INSERT.
        The update mode is direct.
        Worktable2 created for REFORMATTING.

        FROM TABLE
            FeeType
            ft
        Nested iteration.
        Using Clustered Index.
        Index : PK_FEETYPE
        Forward scan.
        Positioning at start of table.
        Using I/O Size 2 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.
        TO TABLE
            Worktable2.

    STEP 2
        The type of query is INSERT.
        The update mode is direct.
        Worktable3 created for REFORMATTING.

        FROM TABLE
            VendorFee
            vf
        Nested iteration.
        Using Clustered Index.
        Index : PK_VENDORFEE
        Forward scan.
        Positioning at start of table.
        Using I/O Size 2 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.
        TO TABLE
            Worktable3.

    STEP 3
        The type of query is INSERT.
        The update mode is direct.
        Worktable1 created for ORDER BY.

        FROM TABLE
            OtherFee
            o
        Nested iteration.
        Using Clustered Index.
        Index : PK_OTHERFEE
        Forward scan.
        Positioning at start of table.
        Using I/O Size 2 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.

        FROM TABLE
            Vendor
            v
        Nested iteration.
        Using Clustered Index.
        Index : PK_VENDOR
        Forward scan.
        Positioning by key.
        Keys are:
            VendorID ASC
        Using I/O Size 2 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.

        FROM TABLE
            Stores
            s
        Nested iteration.
        Using Clustered Index.
        Index : PK_STORES
        Forward scan.
        Positioning by key.
        Keys are:
            StoreID ASC
        Using I/O Size 2 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.

        FROM TABLE
            Worktable2.
        Nested iteration.
        Using Clustered Index.
        Forward scan.
        Positioning by key.
        Using I/O Size 2 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.

        FROM TABLE
            Worktable3.
        Nested iteration.
        Using Clustered Index.
        Forward scan.
        Positioning by key.
        Using I/O Size 2 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.

        FROM TABLE
            UserBuyer
            u
        Nested iteration.
        Using Clustered Index.
        Index : PK_USERBUYER
        Forward scan.
        Positioning by key.
        Keys are:
            Buyer ASC
        Using I/O Size 2 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.

        FROM TABLE
            Buyer
            b
        Nested iteration.
        Using Clustered Index.
        Index : PK_BUYER
        Forward scan.
        Positioning by key.
        Keys are:
            Buyer ASC

        Run subquery 1 (at nesting level 1).
        Using I/O Size 2 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.
        TO TABLE
            Worktable1.

    STEP 4
        The type of query is SELECT.
        This step involves sorting.

        FROM TABLE
            Worktable1.
        Using GETSORTED
        Table Scan.
        Forward scan.
        Positioning at start of table.
        Using I/O Size 2 Kbytes for data pages.
        With MRU Buffer Replacement Strategy for data pages.

NESTING LEVEL 1 SUBQUERIES FOR STATEMENT 1.

QUERY PLAN FOR SUBQUERY 1 (at nesting level 1 and at line 10).

Correlated Subquery.
Subquery under an EXPRESSION predicate.

    STEP 1
        The type of query is SELECT.
        Evaluate Ungrouped COUNT AGGREGATE.

        FROM TABLE
            ST_Vendor
            a
        Nested iteration.
        Using Clustered Index.
        Index : PK_ST_VENDOR
        Forward scan.
        Positioning by key.
        Keys are:
            VendorID ASC
        Using I/O Size 2 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.

        FROM TABLE
            Stores
            b
        Nested iteration.
        Using Clustered Index.
        Index : PK_STORES
        Forward scan.
        Positioning by key.
        Keys are:
            StoreID ASC
        Using I/O Size 2 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.

END OF QUERY PLAN FOR SUBQUERY 1.

Server Message: Number 1562, Severity 10
Server 'FYMIS02', Line 1:
The sort for Worktable2 is done in Serial
Server Message: Number 1562, Severity 10
Server 'FYMIS02', Line 1:
The sort for Worktable3 is done in Serial
Server Message: Number 1562, Severity 10
Server 'FYMIS02', Line 1:
The sort for Worktable1 is done in Serial
Table: ST_Vendor scan count 1, logical reads: (regular=7 apf=0 total=7), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Stores scan count 24, logical reads: (regular=51 apf=0 total=51), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: OtherFee scan count 1, logical reads: (regular=73110 apf=0 total=73110), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Vendor scan count 20, logical reads: (regular=60 apf=0 total=60), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: UserBuyer scan count 20, logical reads: (regular=40 apf=0 total=40), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Buyer scan count 760, logical reads: (regular=1520 apf=0 total=1520), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Stores scan count 20, logical reads: (regular=42 apf=0 total=42), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: VendorFee scan count 1, logical reads: (regular=2011 apf=0 total=2011), physical reads: (regular=288 apf=0 total=288), apf IOs used=0
Table: FeeType scan count 1, logical reads: (regular=6 apf=0 total=6), physical reads: (regular=6 apf=0 total=6), apf IOs used=0
Table: Worktable1 scan count 0, logical reads: (regular=1050 apf=0 total=1050), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable2 scan count 20, logical reads: (regular=171 apf=0 total=171), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable3 scan count 0, logical reads: (regular=92068 apf=0 total=92068), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Total writes for this command: 2481
(760 rows affected)

银铠圣袍 · 2007-6-15 11:09:01

12.5.3的查询计划

QUERY PLAN FOR STATEMENT 1 (at line 1).

    STEP 1
        The type of query is INSERT.
        The update mode is direct.
        Worktable2 created for REFORMATTING.

        FROM TABLE
            VendorFee
            vf
        Nested iteration.
        Using Clustered Index.
        Index : PK_VENDORFEE
        Forward scan.
        Positioning at start of table.
        Using I/O Size 2 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.
        TO TABLE
            Worktable2.

    STEP 2
        The type of query is INSERT.
        The update mode is direct.
        Worktable1 created, in allpages locking mode, for ORDER BY.

        FROM TABLE
            OtherFee
            o
        Nested iteration.
        Using Clustered Index.
        Index : PK_OTHERFEE
        Forward scan.
        Positioning at start of table.
        Using I/O Size 2 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.

        FROM TABLE
            Vendor
            v
        Nested iteration.
        Using Clustered Index.
        Index : PK_VENDOR
        Forward scan.
        Positioning by key.
        Keys are:
            VendorID ASC
        Using I/O Size 2 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.

        FROM TABLE
            Stores
            s
        Nested iteration.
        Using Clustered Index.
        Index : PK_STORES
        Forward scan.
        Positioning by key.
        Keys are:
            StoreID ASC
        Using I/O Size 2 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.

        FROM TABLE
            FeeType
            ft
        Nested iteration.
        Using Clustered Index.
        Index : PK_FEETYPE
        Forward scan.
        Positioning at start of table.
        Using I/O Size 2 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.

        FROM TABLE
            Worktable2.
        Nested iteration.
        Using Clustered Index.
        Forward scan.
        Positioning by key.
        Using I/O Size 2 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.

        FROM TABLE
            UserBuyer
            u
        Nested iteration.
        Using Clustered Index.
        Index : PK_USERBUYER
        Forward scan.
        Positioning by key.
        Keys are:
            Buyer ASC
        Using I/O Size 2 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.

        FROM TABLE
            Buyer
            b
        Nested iteration.
        Using Clustered Index.
        Index : PK_BUYER
        Forward scan.
        Positioning by key.
        Keys are:
            Buyer ASC

        Run subquery 1 (at nesting level 1).
        Using I/O Size 2 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.
        TO TABLE
            Worktable1.

    STEP 3
        The type of query is SELECT.
        This step involves sorting.

        FROM TABLE
            Worktable1.
        Using GETSORTED
        Table Scan.
        Forward scan.
        Positioning at start of table.
        Using I/O Size 2 Kbytes for data pages.
        With MRU Buffer Replacement Strategy for data pages.
    STEP 1

NESTING LEVEL 1 SUBQUERIES FOR STATEMENT 1.

QUERY PLAN FOR SUBQUERY 1 (at nesting level 1 and at line 10).

Correlated Subquery.
Subquery under an EXPRESSION predicate.

    STEP 1
        The type of query is SELECT.
        Evaluate Ungrouped COUNT AGGREGATE.

        FROM TABLE
            ST_Vendor
            a
        Nested iteration.
        Using Clustered Index.
        Index : PK_ST_VENDOR
        Forward scan.
        Positioning by key.
        Keys are:
            VendorID ASC
        Using I/O Size 2 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.

        FROM TABLE
            Stores
            b
        Nested iteration.
        Using Clustered Index.
        Index : PK_STORES
        Forward scan.
        Positioning by key.
        Keys are:
            StoreID ASC
        Using I/O Size 2 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.

END OF QUERY PLAN FOR SUBQUERY 1.

Server Message: Number 1562, Severity 10
Server 'FYMIS03', Line 1:
The sort for Worktable2 is done in Serial
Server Message: Number 1562, Severity 10
Server 'FYMIS03', Line 1:
The sort for Worktable1 is done in Serial
Table: ST_Vendor scan count 1, logical reads: (regular=6 apf=0 total=6), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Stores scan count 24, logical reads: (regular=49 apf=0 total=49), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: OtherFee scan count 1, logical reads: (regular=48445 apf=0 total=48445), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Vendor scan count 20, logical reads: (regular=80 apf=0 total=80), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: UserBuyer scan count 20, logical reads: (regular=60 apf=0 total=60), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Buyer scan count 780, logical reads: (regular=1560 apf=0 total=1560), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Stores scan count 20, logical reads: (regular=41 apf=0 total=41), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: VendorFee scan count 1, logical reads: (regular=2003 apf=0 total=2003), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: FeeType scan count 20, logical reads: (regular=60 apf=0 total=60), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable1 scan count 0, logical reads: (regular=1079 apf=0 total=1079), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable2 scan count 0, logical reads: (regular=92833 apf=0 total=92833), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Total writes for this command: 2698
(780 rows affected)

flybean · 2007-6-15 11:15:37

查询计划不一样哟

账号		站点注册	自动登录	找回密码
密码			站点注册