Impact of Storage Methods on space usage and Performance Analysis

Compare clustered indexes (clustered) and non-clustered indexes (nonclustered) in storage mode --> Generate Test Data Using TravyLee: the data volume in all the tables below is 19329 (note that this is not a big data table). Now we are creating a table named ORDERS_C with the same structure as the first two tables, no index is used,

Use heap to store IF OBJECT_ID ('Orders _ C') is not nullddrop TABLE ORDERS_CGOCREATE TABLE ORDERS_C (id int identity (), UserId VARCHAR (5) not null, ordersId VARCHAR (8) not null, CreateDate DATETIME, ProductsId VARCHAR (5) DEFAULT ('a0001 '), ProductName VARCHAR (255) DEFAULT ('little F '), Amounts INT, others VARCHAR (255) DEFAULT ('None') ON [PRIMARY] GOINSERT ORDERS_C (UserId, OrdersId, CreateDate, Amounts) SELECT UserId, OrdersId, Creat EDate, Amounts from ordersgodbcc showcontig (ORDERS_C)/* dbcc showcontig is scanning the 'Orders _ C' table... TABLE: 'Orders _ C' (39671189); index ID: 0; Database ID: 1 scanned at the TABLE level. -Number of scanned pages ................................: 185-Number of scan zones ..............................: 27-Number of Zone switches ..............................: 26-average page number of each partition ........................: 6.9-scan density [optimal count: actual count] ......: 88.89% [24:27]-area scan fragmentation ..................: 11.11%-average number of available bytes per page .....................: 62.5-average page density (full ).....................: 99.23% DBCC execution is complete. If DBCC outputs an error message, contact the system administrator. */-- From the above results, we can find that this table uses 185 pages and 27 zones -- create an ORDERS_A table with clustered indexes, the data content IS the same as that of orders_c. IF OBJECT_ID ('Orders _ a') is not nullddrop TABLE orders_agcreate TABLE ORDERS_A (id int identity (), UserId VARCHAR (5) not null, ordersId VARCHAR (8) not null, CreateDate DATETIME, ProductsId VARCHAR (5) DEFAULT ('a0001 '), ProductName VARCHAR (255) DEFAULT ('little F '), Amounts INT, others VARCHAR (255) DEFAULT ('None'), CONSTRAINT PK_UserId_OrdersId _ A primary key clustered (UserId ASC, OrdersId ASC) ON [PRIMARY]) ON [PRIMARY] GOINSERT ORDERS_A (UserId, OrdersId, CreateDate, Amounts) SELECT UserId, OrdersId, CreateDate, amounts from orders -- use the dbcc showcontig command to view the storage space of this table. dbcc showcontig (ORDERS_A) WITH ALL_INDEXES/* dbcc showcontig is scanning the 'Orders _ a' table... TABLE: 'Orders _ a' (103671417); index ID: 1, Database ID: 1 a table-level scan has been performed. -Number of scanned pages ................................: 185-Number of scan zones ..............................: 26-Number of Zone switches ..............................: 25-average page number of each partition ........................: 7.1-scan density [optimal count: actual count] ......: 92.31% [24:26]-logical scan fragmentation ..................: 2.70%-scan fragments ..................: 7.69%-average number of available bytes per page .....................: 62.5-average page density (full ).....................: 99.23% DBCC execution is complete. If DBCC outputs an error message, contact the system administrator. */-- From the above results, we can find that this table uses 185 pages and 26 zones. -- I create a table with the same structure, but the primary key is listed on a non-clustered index, its storage structure IS a heap plus B tree IF OBJECT_ID ('Orders _ B ') is not nullddrop TABLE ORDERS_BGOCREATE TABLE ORDERS_ B (ID INT IDENTITY (), UserId VARCHAR (5) not null, OrdersId VARCHAR (8) not null, CreateDate DATETIME, ProductsId VARCHAR (5) DEFAULT ('a0001 '), ProductName VARCHAR (255) DEFAULT ('Sister F '), amounts INT, Others VARCHAR (255) DEFAULT ('None'), CONSTRAINT PK_UserId_Order SId_ B primary key nonclustered (UserId ASC, OrdersId ASC) ON [PRIMARY]) ON [PRIMARY] GOINSERT ORDERS_ B (UserId, OrdersId, CreateDate, Amounts) SELECT UserId, OrdersId, CreateDate, amounts from orders -- use the dbcc showcontig command to view the storage space of this table. dbcc showcontig (ORDERS_ B) WITH ALL_INDEXES/* dbcc showcontig is scanning the 'Orders _ B 'table... TABLE: 'Orders _ B '(183671702); index ID: 0; Database ID: 1 scanned at the TABLE level. -Number of scanned pages ................................: 185-Number of scan zones ..............................: 29-Number of Zone switches ..............................: 28-average page number of each partition ........................: 6.4-scan density [optimal count: actual count] ......: 82.76% [24:29]-area scan fragmentation ..................: 55.17%-average number of available bytes per page .....................: 62.5-average page density (full ).....................: 99.23% dbcc showcontig is scanning the 'Orders _ B 'table... table: 'Orders _ B '(183671702); index ID: 2, Database ID: 1 executed L EAF-level scanning. -Number of scanned pages ................................: 103-Number of scan zones ..............................: 19-Number of Area switches ..............................: 18-average page number of each partition ........................: 5.4-scan density [optimal count: actual count] ......: 68.42% []-logical scan fragmentation ..................: 6.80%-scan fragments ..................: 78.95%-average number of available bytes per page .....................: 47.4-average page density (full ).....................: 99.41% DBCC execution is complete. If DBCC outputs an error message, contact the system administrator. */-- From the preceding results, we can see that the number of pages used by ORDERS_ B is 185 + 103 = 288 + 19 = 48. -- below I will perform DBCC SHOWCONTIG operations on the three tables. after the data is summarized and compared as follows/* sort storage method-Use Page quantity-Number of use areas ------------------------------------------------------------------------- no index-185-27 has clustered index-185-26 -------------------------------------- ----------------------------------- Non-clustered index-288-48 rows */-- From the comparison of the data reflected in this table, we can more intuitively find that the establishment of clustered index does not increase the size of the table space -- and creating a non-clustered index adds a lot of space-so what are the efficiency of these three storage methods when performing queries? Next let's take a look -- first put forward a point of view: When a table changes frequently, if a clustered index is created on this table, it is easy to encounter page splitting. Therefore, building a clustered index will affect the performance. Based on this consideration, many databases-designers are reluctant to create clustered indexes on tables. However, the performance of a non-indexed table is unacceptable, in order to achieve good performance, the method of storing heap and tree like SQL Server determines that the design of creating non-clustered indexes is a waste of space, and the performance is not necessarily the best. The previous analysis shows that it is a waste of storage space, next we will analyze its performance using -- the most intuitive query: SELECT * FROM ORDERS_C -- SELECT * FROM ORDERS_A -- SELECT * FROM ORDERS_ B -- pay-as-you-go clustered index -- below we have filtered out the orders with userid = 1001 and OrdersId = 0112321 -- query one SELECTa. ID,. userId, B. userName,. ordersId,. createDate,. productName,. amountsFROM ORDERS_C ainner joinUSERS bONa. userId = B. userIdwhere. userId = '000000' and OrdersId = '000000' -- no index/* SQL Server Analysis and Compilation Time: CPU time = 0 ms, occupied time = 0 ms. 4. Table 'Orders _ C '. Scan count 1, logical reads 185, physical reads 0, pre-reads 0, lob logic reads 0, lob physical reads 0, and lob pre-reads 0. Table 'users '. Scan count 1, logical read 1, physical read 0, pre-read 0, lob logical read 0, lob physical read 0, lob pre-read 0. SQL Server execution time: CPU time = 15 ms, occupied time = 7 ms. */

Execution Plan diagram:

-- Query two SELECTa. ID,. userId, B. userName,. ordersId,. createDate,. productName,. amountsFROM ORDERS_A ainner joinUSERS bONa. userId = B. userIdwhere. userId = '000000' and OrdersId = '000000' -- clustered index/* SQL Server Analysis and Compilation Time: CPU time = 0 ms, occupied time = 0 ms. Table 'users '. Scan count 1, logical read 1, physical read 0, pre-read 0, lob logical read 0, lob physical read 0, lob pre-read 0. Table 'Orders _ '. Scan count 0, logical read 2, physical read 0, pre-read 0, lob logical read 0, lob physical read 0, lob pre-read 0. SQL Server execution time: CPU time = 0 ms, occupied time = 0 ms. */

Execution Plan Diagram

-- Query three SELECTa. ID,. userId, B. userName,. ordersId,. createDate,. productName,. amountsFROM ORDERS_ B ainner joinUSERS bONa. userId = B. userIdwhere. userId = '000000' and OrdersId = '000000' -- Additional clustered index/* SQL Server Analysis and Compilation Time: CPU time = 0 ms, occupation time = 3 ms. Table 'users '. Scan count 1, logical read 1, physical read 0, pre-read 0, lob logical read 0, lob physical read 0, lob pre-read 0. Table 'Orders _ B '. Scan count 0, logical read 3, physical read 0, pre-read 0, lob logical read 0, lob physical read 0, lob pre-read 0. SQL Server execution time: CPU time = 0 ms, occupied time = 0 ms. */

Execution Plan Diagram

GO-An Analysis of the Appeal Results and execution plan diagram is not difficult to see: -- the CPU overhead and I/O overhead of queries without any index are significantly higher than those with an index. -- compared with clustered indexes and non-clustered indexes, there is a performance gap -- the statement execution logic of clustered Indexes the number of reads is less than the number of logical reads when the non-clustered index query is performed. The cpu overhead and I/o overhead on the execution plan are displayed, less clustered indexes than non-clustered indexes -- the above is only a query validation, but you can also use insert, update, and delete verification -- the index will affect the insert performance, who will suffer more performance loss for these two indexes? If you are interested, test it.

I don't know how to upload images. There is an execution plan in the post.

http://bbs.csdn.net/topics/390275646?page=1#post-392870017