Most SQL Server Tables require an index to improve data access speed. If there is no index, SQL Server wants to perform a table to scan each record in the read table to find the data. The index can be divided into cluster index and non-clustered indexes, and clusters indexes by rearrange data to improve data access speed, rather than clustering indexes to improve data indexing by the data pointers in the maintenance table.
Indexed architecture:
Why is the index of the constant maintenance table? First, give a brief introduction to the architecture of the index. SQL Server stores data in the database file in the hard disk. By default, these pages and their data are not organized. In order to make the confusion becomes an orderly, an index is generated. After generating an index, there is an index page and a data page, and the data page saves the data information written by the user. The index page is stored in the data value list (keyword) of the column (keyword) and the address pointer in the record in the index table. The index is divided into cluster index and non-clustered index, and the cluster index is essentially sorted in the table, as if it is the index directory of the dictionary. Non-clustered indexes are not sorted, it only saves the pointer address of the data. Insert data into a table with clustered index, when the data page reaches 100%, because the page does not have a new record in the page, the page will occur, and the SQL Server will move about half of the data from full page to empty The page is generated for two and more full pages. This has a lot of data space. The cluster index is a two-way linked list, saved the previous page in each page, the next page address, and the data moved after the paged data, because the new page may be in any place in the database file, so the page link is not necessarily pointed out. The next physical page of the disk, the link may point to another area, which forms a block, thereby slowing down the speed of the system. For tables with clustered indexes and non-clustered indexes, the keywords of non-clustered indexes are pointing to the cluster index, rather than pointing to the data page.
In order to overcome the negative impact of the data block, the index of the reconstructed table is required. This is very time consuming, so it can only be done when needed. You can determine whether the index of the reconstructed table can be determined by DBCC ShowContig. The following is an example of how to use DBCC ShowContig and DBCC Redbindex. Northwind data comes with SQL Server as an example
Query Analyzer Enter command with SQL Server:
USE PUBS
Declare @Table_ID INT
Set @ Table_ID = Object_ID ('TBLDLVINFOBACK')
DBCC Showcontig (@table_id)
This command displays the blockage of the TBLDLVINFOBACK table in the PUBS database, as follows:
DBCC Showcontig is scanning 'TBLDLVINFOBACK' table ... Table: 'TBLDLVINFOBACK' (1797581442); Index ID: 0, Database ID: 5 The Table level scan is executed. - Scanning page number ..................................................... - Scanned extension panel number. ................................: 24659- Expand the number of switches on the panel ......... ...................................................................... ...: 8.0- Scanning Density [Best Value: Actual Value] ................................................................................................................................................................................................. ........................................................................ ................ 374.6- Average page density (complete) ........................ : 95.37% DBCC is executed. If DBCC outputs an error message, contact your system administrator. By analyzing these results, you can know if the index of the table needs to be reconstructed. Table 1.1 describes the meaning description of each line
Pages Scanned Table or Index Overpage Extents Scanned Tables or Indexes Extent Switches DBCC Travel from a region to another AVG. Pages Per Extent Related Areas Scan Density Best Count is the ideal area of continuous links [Best Count: Actual Count] Domain change, Actual Count is the actual area change, and Scan Density indicates that there is no block. Logical Scan Fragmentation Scanning Cochlor Page Percentage Extent Scan Fragmentation Not actually neighboring and containing all links in the link AVG. The average page density, indicating that the page has multiple execution results from the above command, Best Count is 3 and actual count is 5 This indicates that the ORDERS table requires a block index. The cluster index of the table is reconstructed by DBCC DBREINDEX. Also entered in the command Query Analyzer: use northwinddbcc dbreindex ( 'northwind.dbo.orders', pk_orders, 90) execution results:. DBCC execution completed If DBCC printed error messages, contact your system administrator.Dbcc dbreindex parameters: a first The parameters are to be reconstructed. The second parameter is an index recognition that needs reconstruction, '' means all indexes. The third parameter is the padding factor, the larger the fill factor, the more the page is.
And then use the result of the reconstruction view DBCC SHOWCONTIG cluster index: use northwinddeclare @table_id intset @ table_id = object_id ( 'orders') dbcc showcontig (@table_id) returns the following results: DBCC SHOWCONTIG scanning 'Orders' table ... Table: 'Orders' (21575115); Index ID: 1, Database ID: 6TABLE Level Scan Performed.- Pages Scanned ............................. .....: 22- EXTENTS Scanned ........................................................... ........................................................................................... .......: 7.3- Scan Density [Best Count: actual count] ............00% [3: 3] - Logical Scan Fragmentation ......... ......: 0.00% - EXTENT Scan Fragmentation ............................................................... ................................................................................................................................ 89.26% DBCC Execution completed If we can see that the SCAN Denity is no need to reactivate the table index through the result. If the cluster index of the reconstruction table can be reconstructed with all indexes of the table. The command is as follows: - Use northwind - DBCC DBREINDEX ('northwind.dbo.orders', '', 90) Using job timing reconstruction index: If your database is very frequent, it is very easy to show the phenomenon of data block. So you can use jobs to help you reconstruct your index when your system is free.
Why is the index of the constant maintenance table? First, give a brief introduction to the architecture of the index. SQL Server stores data in the database file in the hard disk. By default, these pages and their data are not organized. In order to make the confusion becomes an orderly, an index is generated. After generating an index, there is an index page and a data page, and the data page saves the data information written by the user. The index page is stored in the data value list (keyword) of the column (keyword) and the address pointer in the record in the index table. The index is divided into cluster index and non-clustered index, and the cluster index is essentially sorted in the table, as if it is the index directory of the dictionary. Non-clustered indexes are not sorted, it only saves the pointer address of the data. Insert data into a table with clustered index, when the data page reaches 100%, because the page does not have a new record in the page, the page will occur, and the SQL Server will move about half of the data from full page to empty The page is generated for two and more full pages. This has a lot of data space. The cluster index is a two-way linked list, saved the previous page in each page, the next page address, and the data moved after the paged data, because the new page may be in any place in the database file, so the page link is not necessarily pointed out. The next physical page of the disk, the link may point to another area, which forms a block, thereby slowing down the speed of the system. For tables with clustered indexes and non-clustered indexes, the keywords of non-clustered indexes are pointing to the cluster index, rather than pointing to the data page. In order to overcome the negative impact of the data block, the index of the reconstructed table is required. This is very time consuming, so it can only be done when needed. You can determine whether the index of the reconstructed table can be determined by DBCC ShowContig. The following is an example of how to use DBCC ShowContig and DBCC Redbindex. Northwind data comes with SQL Server as an example
Query Analyzer Enter command with SQL Server:
USE PUBS
Declare @Table_ID INT
Set @ Table_ID = Object_ID ('TBLDLVINFOBACK')
DBCC Showcontig (@table_id)
This command displays the blockage of the TBLDLVINFOBACK table in the PUBS database, as follows:
DBCC Showcontig is scanning 'TBLDLVINFOBACK' table ... Table: 'TBLDLVINFOBACK' (1797581442); Index ID: 0, Database ID: 5 The Table level scan is executed. - Scanning page number ..................................................... - Scanned extension panel number. ................................: 24659- Expand the number of switches on the panel ......... ...................................................................... ...: 8.0- Scanning Density [Best Value: Actual Value] ................................................................................................................................................................................................. ........................................................................ ................ 374.6- Average page density (complete) ........................ : 95.37% DBCC is executed. If DBCC outputs an error message, contact your system administrator.
By analyzing these results, you can know if the index of the table needs to be reconstructed. Table 1.1 describes the meaning description of each line
Pages Scanned Table or Index Overpage Extents Scanned Tables or Indexes Extent Switches DBCC Travel from a region to another AVG. Pages Per Extent Related Areas Scan Density Best Count is the ideal area of continuous links [Best Count: Actual Count] Domain change, Actual Count is the actual area change, and Scan Density indicates that there is no block. Logical Scan Fragmentation Scanning Cochlor Page Percentage Extent Scan Fragmentation Not actually neighboring and containing all links in the link AVG. The average page density, indicating that the page has multiple execution results from the above command, Best Count is 3 and actual count is 5 This indicates that the ORDERS table requires a block index. The cluster index of the table is reconstructed by DBCC DBREINDEX. Also entered in the command Query Analyzer: use northwinddbcc dbreindex ( 'northwind.dbo.orders', pk_orders, 90) execution results:. DBCC execution completed If DBCC printed error messages, contact your system administrator.Dbcc dbreindex parameters: a first The parameters are to be reconstructed. The second parameter is an index recognition that needs reconstruction, '' means all indexes. The third parameter is the padding factor, the larger the fill factor, the more the page is.
