Key figures for maintenance and performance

Key figures for maintenance and performance Many people know the importance of creating indexes on tables in SQL Server database. Indices to improve the performance of a database. However, while many people in their indexes on SQL Server tables, many people think they can not function properly to ensure queries efficiently as possible. I'll begin by providing a quick overview of how SQL Server 2000 stores data and indexes, how to improve performance. Then I will be very little time to explain why, when and how the indexes with DBCC DBCC INDEXDEFRAG SHOWCONTIG and research to ensure the most efficient manner.SQL Server 2000 stores the data in what is known as a group. A heap is a collection of pages, the data for the rows of a table. The data are not stored in a specific order and the pages are not in sequence. The data is only one real form or organization. If your SQL Server in this form is a table scan. This means that SQL Server starts reading at the top of the table and scans every page until you find data that meet the criteria of the query. If a table is very large, this could significantly queries.Indexes performance to speed up the recovery of data. It 'important to understand how data is used, the type of queries and the frequency of the requests are usually that the planning for the indices. An index is much more effective if the query results retu a small percentage of rows and the selectivity is high. High selectivity means a query to retu the least possible number of lines. As a general rule, indexes should be created on columns, which jointly investigated, including primary and foreign keys. It follows that the columns of some unique values should not be indexed, the number of rows in a query.There are two types of indices used to design non-clustered and clustered Indexes.A not clustered index stores data similar to l ' index of the text. The index is in a different position compared to real data. The structure creates an index with a pointer that indicates the actual position of the data. Non-clustered indexes should be based on columns where the selectivity of research ranges from highly selective to unique. These indices are useful if they are several possibilities for research is desired.A clustered index stores data similar to a telephone directory in which all people with the same last name are grouped together. SQL Server quickly create a table with a clustered index while the index determines the order in which rows of a table. Cluster indexes are useful for columns searched frequently for ranges of values, or you order.Each sorted table can have only one clustered index, but up to 249 clustered indexes per table. For more information on how to cluster and non-clustered index and store data visit I could go on about how SQL Server 2000 stores data in a heap and in an Index architecture, I will now discuss with the maintenance of indexes and SHOWCONTIG DBCC DBCC INDEXDEFRAG . Once indices have been created, it is important indices provide the best performance possible. If the indexes are not maintained, over time the data become fragmented. Fragmentation is the inefficient use of pages within an index *. There are a number of tools available to help with the optimization of the indexes to ensure they run, but only SHOWCONTIG and DBCC DBCC INDEXDEFRAG in this command DBCC article.The fragmentation is SHOWCONTIG information on data and indexes within a given table and will also determine whether the data and index pages are full. If a page is full, SQL Server must split the page to make room for new rows. This declaration must be heavily modified on the basis of tables, the imported data or tables that seem to cause poor query performance. If the statement is executed, this is what is retued: Statistics Description Pages Scanned Number of pages in the table or the number of scanned index.Extents dimensions in the table or the number of switches index.Extent DBCC statement to a certain extent, in a while another went through the pages of the table or index.Avg. Number of pages per volume of pages to fit on the side chain.Scan Density [Best Count: Actual number] Best count is the ideal number of extent changes if everything contiguously linked. Actual count is the actual number of extent changes. The number density scanning of 100, when all together, if you have less than 100, some fragmentation exists. Scan density is a percentage.Logical Scan Fragmentation Percentage of out-of-pages to scan leaf pages of an index. This figure is not relevant to heaps and text indexes. One to make a page for the next page in an IAM is a different page than the page to the next page pointer in leaf page.Extent Scan Fragmentation Percentage of out-of-extensions to scan leaf pages of an index. This figure is not relevant to heaps. An out-of-Order is a measure of the extent to which the current page for an index is not physically the next extent after the extent of the previous page for a index.Avg. Bytes free per page number of bytes on the pages scanned. The higher the number, the smaller the number of pages full. Lower numbers are better. This number is also on the line size, a wide range of sizes can lead to greater number.Avg. Page Density (full) page average density (in percentage). This value takes into account the size of the line so that it is a more accurate indication of how full your pages. The higher the percentage, the better.The command DBCC INDEXDEFAG reconstruction of a specific index or all indexes for a given table. This command also allows the use of FILLFACTOR the option reduces the number of plots per page or index page. With the option FILLFACTOR increases the performance and hinterland insert statements. If a page is full of data, SQL Server must split the page to make room for new lines. The specification allows FILLFACTOR a space to store the data for the pages and adds updates.Let 's see an example: Run the query on a table with the name of State: nbspDBCC & SHOWCONTIG (State) WITH ALL_INDEXESDBCC SHOWCONTIG scan " ; State "table .. . Table: 'member' (786101841), index ID: 2, database ID: 14LEAF level of pages scanned digitized .- ...................... .. ........: 192 - Extents Scanned ..............................: 26 - scale switch .. ............................: 187 - Avg. Pages to fit ........................: 7.4-Scan Density [Best Count: Actual number ].......: 12 , 77% [24:188] - Logical Scan Fragmentation ..................: 48.96% - Extent Scan Fragmentation .......... ....... ..: 96.15% - Average. Bytes Free per Page .....................: 6721.0 media. Page Density (16,96% ).....................: full DBCC execution completed. If DBCC printed error messages, contact your system administrator.Running the query to a table named provider: nbspDBCC & SHOWCONTIG (ISP) with the scanning ALL_INDEXESDBCC SHOWCONTIG 'provider' table ... Table: 'provider' (850102069), index ID: 2, database ID: 14LEAF level of pages scanned digitized .- ...................... ..........: 3 - Extents Scanned .. ............................: 1 - Scope Switches ............... .. .............: 0 - Media. Pages to fit ........................: 3,0-Scan Density [Best Count: Actual number ].......: 100 , 00% [1:1] - Logical Scan Fragmentation ..................: 33.33% - Extent Scan Fragmentation .......... ....... ..: 0.00% - Average. Bytes Free per Page .....................: 5596.0 media. Page Density (30.86% ).....................: full DBCC execution completed. If DBCC printed error messages, contact your system administrator.There are a couple of things to take note of will and help to determine whether or not to index pages are full, or if they strongly fragmented.The abundance index of pages can be determined from a reading of "Medium. Bytes free per page "and" average. Page Density (full) "statistics. The "average. Bytes free per page "value is low, and" avg. Page Density (full) "value is high. You'll notice that the two tables probably very crowded pages.The fragmentation level of an index can be determined by comparing the values of "change volume" and "Extents Scanned" and with a clear understanding Logical Scan Fragmentation "and" Extent Scan the scope of fragmentation "of values. The "switch levels" should be almost equal to "Extents Scanned." On the basis of the examples above, this is as it should be similar. Logical Scan Fragmentation "and" scale-Scan Fragmentation "values give a good overview in a table of the level of fragmentation. These values should be as close to zero (10% can be accepted). The 'member' table is highly fragmented and the provider table is slightly fragmented based on the data above.These problems can be solved by deleting and recreating a clustered index with the FILLFACTOR option. In addition, the command DBCC INDEXDEFRAG compact an index, taking into account its FILLFACTOR improving statistics.After run the following query: nbspDBCC & DBREINDEX (State,'', 80) and nbspDBCC DBREINDEX (provider,'', 80) Running Requests & nbspDBCC SHOWCONTIG (State) WITH ALL_INDEXES & nbspDBCC SHOWCONTIG (ISP) with the scanning ALL_INDEXESDBCC SHOWCONTIG 'member' table ... Table: 'member' (786101841), index ID: 2, database ID: 14LEAF level of pages scanned digitized .- ...................... ..........: 41 - Extents Scanned ............ ..................: 6 - Scope Switches ......................... .. ...: 5 - Media. Pages to fit ........................: 6.8-Scan Density [Best Count: Actual number ].......: 100 , 00% [6:6] - Logical Scan Fragmentation ..................: 0.00% - Extent Scan Fragmentation .......... ....... ..: 0.00% - Average. Bytes Free per Page .....................: 1657.0 media. Page Density (79.53% ).....................: full DBCC execution completed. If DBCC printed error messages, contact your system scan administrator.DBCC SHOWCONTIG "suppliers" table ... Table: 'provider' (850102069), index ID: 2, database ID: 14LEAF level of pages scanned Scan .- .... ............................: 2 - Extents Scanned ................. .............: 2 - Scope 1 Switches ............................. .: - Avg. Pages to fit ........................: 1.0-Scan Density [Best Count: Actual number ].......: 50 , 00% [1:2] - Logical Scan Fragmentation ..................: 0.00% - Extent Scan Fragmentation .......... ....... ..: 0.00% - Average. Bytes Free per Page .....................: 4346.0 media. Page Density (46.31% ).....................: full DBCC execution completed. If DBCC printed error messages, contact your system administrator.As result of the execution of DBCC SHOWCONTIG INDEXDEFRAG and DBCC commands, we were able to diagnose and too much on reducing the fragmentation of the "State" and "supplier" tables. The member table is almost perfect, and the 'provider' table shows great improvement. This leads to an extraordinary increase in query performance for the fight against these tables .* For more information on the fragmentation at this link: Harris is a specialist, with the support Orchi Web, Inc. - a company that provides managed hosting services for clients who develop and implement their applications on Microsoft Windows platforms. The author Desiree Harris is a specialist, with the support Orchi Web, Inc. - a company that provides managed hosting services for clients who develop and implement their applications on Microsoft Windows platforms.