1.DBCC Indexdefrag, DBCC DBREINDEX2. FillFactor 100 can only be used when the INSERT or UPDATE statement does not appear (for example, a read-only table). If FillFactor is 100, SQL Server will create an index of 100% filling of the leaf-level page. If INSERT or UPDATE is executed after creating a FillFactor for a 100% index, you will split each time INSERT operation and possibly page each UPDATE operation. 3. DBCC ShowContig statistic is lower index, the higher the efficiency. (Specific reference SQL Server online help) 4. The SQL Server 7.0 is best not to create or delete online, organize indexes, due to the resource involved in locking operations when the 7.0 is required, resulting in content blocking, etc. And 2000 is relatively better, so so far, no cases that cannot be used due to the intensiveness of the index, resulting in the corresponding database table that causes the index. 5. Creating an index typically has two purposes: maintaining the uniqueness of the index columns and provides strategies for the quick access table. 6. How does Microsoft SQL Server determine whether the use of non-polymerization is not only an index or full table scan? A. Why don't you use an index? Index is a B tree. Microsoft SQL Server When using a non-aggregated index, you must start from a non-aggregated index node, (Simply understand as starting from "root", in fact optimized) layer, until On behalf of the leaves node of the data, the pointer to the data page is obtained, and the corresponding data page is found via the pointer. (Of course, if you are looking for things in the index, this step can be saved) assume that the index is a fork tree, the total data is the X line, and an index node can be placed in an index page and uses a non-aggregated index. The overhead of the data is to read log (a, x) (A is the base) index page 1 data page assumes that each data page can put B line data so full table scanning overhead is read X / B data page (here The read is "logical reading") It can be seen that when the number of rows read exceeds a certain degree, the overhead of the full table scan will significantly lower expenditures using non-aggregated index retrieval, how to determine whether the index is used? To give an example: 10,000 lines in the table, 200 bytes per line, index is 4 fork, the data page size is 2K, the filling factor 50% full table scanned to read 2000 (data) pages and use non-aggregated indexes Retrieve, each line is 7 pages (6 index page 1 data page) If you want to read the number of rows> 286 (2000/7), you should use a full mete scan to transform whether the problem should be used to use the index. How many rows of lines are read out? How to estimate the number of rows to read? This is to read the statistics of the index. The statistics description is "a few index nodes." The database engine is generally unique index that does not use non-polymerization without statistics. Because once it is misuse, it is possible to overhead more than a full scale. Statistics In the distribution page, the database engine will automatically build a distribution page when you build an index, but when you modify the data, you don't maintain the distribution page, so you need to rebuild the distribution page D. Why do you say Microsoft? SQL Server 7.0 is doing this in this regard? In the low version of the database, there is a statement of Update Statistics, its rebuild distribution page, and when the variation ratio of the data field exceeds 10% to 20%, you should run this statement.
This means that the database administrator has a lot of things to do, such as backup data, truncated logs, rebuild distribution page, etc. If you have read the SQL7 online book, there is a stored procedure called sp_autostats displays or changes the Automatic Update STATISTICS STATISTICS, or for all indexes and statistics for a given table in the current database. That is, in the default, the database is automatically updated, that is, SQL7 knows whether it should be adopted Non-aggregated indexes, while other databases don't know if they should be adopted, there is no use of such indexes 7.a. There is a large number of repetition values and often have a range query and sort, grouping, or columns that are frequently accessed, A clustered index can be considered. B. When you build an index on a table that is often inserted, use the FillFactor to reduce the page split while increasing the occurrence of deadlocks. If you build an index on a read-only table, you can set the FillFactor to 100. C. Always access multiple columns simultaneously, and each column contains a repetition value to consider establishing a composite index to overwrite one or a set of queries, and put the query references the most frequent column as the leader, if it is possible to make a key query form overlay query . D. When selecting an index key, try to select a column using a small data type as a key to enable each index page to accommodate as many index keys and pointers as possible. In this way, the index page that must be traversed by a query must be traversed. Lower minimize. In addition, the integer is used as much key value as much as possible because it provides access speed than any data type.
8. Inappropriate indexes not only do not make up, but will reduce the performance performance of the system. Because a large number of indexes are more time than if there is no index, there is no index. For example, the index established in the case is inappropriate: a. Little or from untrusted columns in queries will not benefit from indexing, because index rarely or never search for rows based on these columns. B. There are only two or three values, such as men and women (yes or no), never get the benefits from the index. In addition, in view of the index accelerating the query speed, the characteristics of the data update speed are slowed down. It can be built on a segment, and its non-clustered index is built on another segment, and these two segments are respectively improved on separate physical devices.
9.9CBS on an article about the index, you can use the DBCC Showcontig's SQL Server online help. http://dev.9cbs.net/develop/Article/13/13919.shtm
