SQL Server Statistics Simple

When you want to empty the "table", or pour a lot of "information (Data;record)", or the Company "database" to use the new version of the big move ... And so on, not only to rebuild the index, but also to rebuild or update the "statistics". The "system" is correct and no, directly towed the "Performance (performance)" of SQL Server. It is not good to have a "system", but it is even worse to make the best program wrong.

SQL Server's search optimization program uses the index's "information" to get the "information" it needs to determine whether an index is useful.

If there is a linked fields bit (column) that is used in search, we do not index it beforehand, and SQL Server automatically builds the "system" for that linked fields bit. The custom-built "system" will be named after _wa_sys, as shown in Figure 1.

Map 1

----------------------------------------------------------------------------------------------------------

Manual Update "SYSTEM":

Through the CREATE STATISTICS directive, you can directly produce "system" information for linked fields bits that are not indexed, or through the sp_createstats system "stored procedures (stored procedure)", directly to the " All "information sheets, all linked fields bits are built".
Http://msdn.microsoft.com/zh-tw/library/ms188038.aspx
Http://msdn.microsoft.com/zh-tw/library/ms186834.aspx
(If the hyperlink is not connected, you can change ZH-TW to ZH-CN and try again)

Execute the update STATISTICS instruction, which can require a table of information, or directly specify the index or "system" to do updates, and the sp_updatestats system, can be set to the specific information table, all the relevant system updates.
Http://msdn.microsoft.com/zh-tw/library/hh510198.aspx
Http://msdn.microsoft.com/zh-tw/library/ms187348.aspx
Http://msdn.microsoft.com/zh-tw/library/ms173804.aspx

In general, we will rely on "auto-update", but in some cases, you may want to "update" the system immediately, for example:

* The purpose of the repository is "Data Warehouse", and there is no regular update of the information, only a batch update. After the batch has been updated, you need to perform a system update immediately.

* The key value in the index, there is a large number of additions, modifications or delete, and you are sure of the next work, will immediately use the index.

* After TRUNCATE table clears a table, re-enter or pour in the information, that is, the content is completely different, but you need to "access" the information table immediately.

----------------------------------------------------------------------------------------------------------

Auto Update "SYSTEM":

Through the graphics management interface, in the "Assets" window, cut to the "Options" tab, as shown in Figure 2.

Map 2

-----------------------------------------------------------------------------------------------------------

Through the "_wa_sys" of the graphics interface, you can see if there is a "system" named after it, and what linked fields bits have been used by the program to search, without establishing a usable index.

-----------------------------------------------------------------------------------------------------------

Use the command to understand the "system" information DBCC Show_statistics (name of the information table, index name, or linked fields bit name)
Http://msdn.microsoft.com/zh-tw/library/ms174384.aspx

Density (density), the lower the better, the lower the index density, indicating that the index is more "awareness." The closer the density is to 1, the less aware the index is, the less likely it is that the Search optimization tool will use this index. It also represents the existence of this index, which is not helpful to the inquiry, but only affects the performance of the write-in information.

There are books about "the system", and some of the books about "the efficiency of the library" are mostly mentioned. But with Mr. Zhang Limin, the following book, written in 2008, "sql the Server 2005 Performance Tuning Code" (Traditional Chinese), the content is most complete and detailed, the book also has a detailed explanation for the "auto-unsynchronized (asynchronous) update System" in Figure 2. However, this book has been in Taiwan for a long time and has been in the absolute version:
http://www.tenlong.com.tw/items/986181339X?item_id=37625
SQL Server 2005 Performance Tuning Classics (Microsoft SQL Server 2005 unleashed)
Isbn:986181339x
isbn-13:9789861813394

-----------------------------------------------------------------------------------------------------------

SQL Server Statistics Simple