How to improve SQL Server performance
First: Improve data access speed by maintaining the index of the table
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 northwind
Declare @Table_ID INT
Set @ Table_ID = Object_ID ('Orders')
DBCC Showcontig (@table_id)
This command displays the blocking case of the ORDERS table in the Northwind database, as follows:
DBCC Showcontig Scanning 'ORDERS' TABLE ...
Table: 'Orders' (21575115); Index ID: 1, Database ID: 6
Table Level Scan Performed.
- Pages Scanned .................................... 20
- extents scanned .........................................................................................................
- extent switches ...................................................................................
- AVG. Pages per eXtent ...................................................................................................................................
- Scan Density [Best Count: Actual Count] .......: 60.00% [3: 5]
- Logical Scan Fragmentation ..................................................................................................................... %
- Avg. Bytes Free Per Page .............................................................
- AVG. Page Density (Full) .........................................
DBCC Execution completed. If DBCC Printed Error Messages, 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 of each line
Information description
Pages Scanned table or number of pages in the index
Extents scanned table or number of long zone pages in an index
Extent Switch DBCC travel from a region to another
Number of regions
AVG. Pages Per Extent Related Areas
Scan Density Best Count is the ideal area when continuous links
[Best Count: Actual Count] Domain change, Actual Count is the actual area
Domain change number, scan density is 100%
Indicates that there is no block.
Logical Scan Fragmentation Scanning Co-Pages Percentage
Extent Scan Fragmentation is not actually adjacent and included in the link
Page number
Avg. BYTES Free Per Page Scan the average free byte number in the page
AVG. Page Density (FULL) Average page density, indicating how full
From the execution result of the above command, Best Count is 3 and actual count is 5 This indicates that the ORDERS table has a block index. The cluster index of the table is reconstructed by DBCC DBREINDEX.
Similarly, enter the command in Query Analyzer:
Use northwind
DBCC DBREINDEX ('northwind.dbo.orders', pk_orders, 90)
Results of the:
DBCC Execution completed. If DBCC Printed Error Messages, Contact Your System Administrator.
DBCC DBREINDEX Parameter Description: The first parameter is 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.
Then use DBCC ShowContig to view the results of the reconstructed cluster index:
Use northwind
Declare @Table_ID INT
Set @ Table_ID = Object_ID ('Orders')
DBCC Showcontig (@table_id)
The return result is as follows:
DBCC Showcontig Scanning 'ORDERS' TABLE ...
Table: 'Orders' (21575115); Index ID: 1, Database ID: 6Table Level Scan Performed.
- Pages Scanned ..................................: 22
- extents scanned ...............................................................
- extent switches ..............................: 2
- AVG. Pages Per eXtent ...........................................................................
- Scan Density [Best Count: Actual Count] .......: 100.00% [3: 3]
- Logical Scan Fragmentation ..................: 0.00%
- extent scan fragmentation ...........................................
- Avg. Bytes Free Per Page ...................... 869.2
- AVG. Page Density (Full) ......................................26%
DBCC Execution completed. If DBCC Printed Error Messages, Contact Your System Administrator.
Through the result, we can see that SCAN Denity is a 100% table without the blocking of the restructuring. If the cluster index of the reconstruction table can be reconstructed with all indexes of the table if the cluster index scan Denity is less than 100%. The command is as follows:
- ISE Northwind
--DBCC DBREINDEX ('Northwind.dbo.orders',', 90)
Use 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 your job to help you reconstruct your index when your system is relatively idle.
