Microsoft SQL Server 2000 best practices for index fragmentation (Part 1)

Microsoft SQL Server 2000 best practices for index fragmentation

Source: Microsoft technet
Author: Mike ruthruff
Time: February 1, 2003

Summary as Microsoft SQL Server 2000 maintains indexes to reflect
Up & #100; ates to their underlying tables, these indexes can become
Fragmented. Depending on workload characteristics, this fragmentation
Can ......

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

Abstract: Since SQL Server
2000 in response to data updates, indexes on the table need to be maintained, so these indexes form fragments. Based on the workload characteristics, these fragments will affect the corresponding performance. This White Paper can help you decide
Whether fragments need to be organized to improve performance. SQL Server 2000 provides commands for sorting indexes. Here we will compare the two Commands: DBCC
Dbreindex and DBCC indexdefrag.

Directory

Overview
Understand fragments
Factors to consider before organizing fragments
Small-scale environment vs. Large-scale environment
Determine when to perform index fragmentation
DBCC dbreindex vs. DBCC indexdefrag
Conclusion
More information
Appendix A: test environment

Overview

This White Paper provides information on determining whether to perform index fragmentation in a production environment to improve performance. In addition, this article compares Microsoft SQL Server
Two commands used for index fragmentation in 2000: DBCC dbreindex and DBCC
Indexdefrag. This comparison includes the test results of different databases and hardware environments. For more information about the test environment, see section 34. Small-scale environment.
Large scale environment & #34; and Appendix.

Note: fragment does not improve performance under any circumstances. Each scenario is different. Because of this, whether to perform fragment depends on the analysis results.

The White Paper describes the importance of index fragmentation and the general processing process. The key points of this article are listed below:

Before Sorting index fragments, make sure that system resources, such as physical disk fragments and unreasonable infrastructure, have a negative impact on performance.
DBCC showcontig can display the number of index fragments. When running this command, pay special attention to
Fragmentation and page density.
Determining whether to fragment is required is important to evaluate the work type. Not all situations can benefit from fragment. Disk I/O is the most important performance indicator for read operations. Test display
Compared with many online transaction processing systems (OLTP: online transaction ),
Processing), to benefit more from fragment.
Fragments will affect disk performance and SQL Server pre-read management (read-ahead)
Manager. Windows Performance Monitor has several key metrics that can be used to support this view.
Deciding whether to use DBCC dbreindex or DBCC indexdefrag depends on your requirements and hardware environment.
DBCC dbreindex brings the side effects of updating statistics, while DBCC
Indexdefrag does not. You can run up & #100; ate after DBCC indexdefrag is executed.
Statistics to increase its impact.

Understand fragments

When the logical order of the page where the index is located is based on the primary keyword and the physical order in the data file does not match, fragments are generated. All leaf pages contain pointers to the previous and next pages. In this way
Form a double-stranded table. Ideally, the physical order of pages in the data file will match the logical order. The performance of the entire disk is significantly improved when the physical sequence matches the logical sequence. For some specific queries,
This will bring excellent performance. When the physical sorting and logical sorting do not match, the disk performance will become inefficient because the head must move forward and backward to search for the index, instead of searching in a single direction.
. Fragmentation affects I/O performance, but it does not affect data pages in the SQL server data buffer.

When an index is created for the first time, there are no or few fragments. Over time, data will be inserted, updated, and deleted, and shards on indexes related to the data will increase. SQL
Server provides the following commands:

CR & #101; dro after ate index & #112; Index Command
CR without the dro & #112; _ existing Option & #101; ate Index Command
DBCC indexdefrag
DBCC dbreindex

This article uses the DBCC indexdefrag and DBCC dbreindex commands for testing. These commands can be executed in both online and offline scenarios. DBCC
Dbreindex creates an index according to CR & #101; ate index; therefore, DBCC
Dbreindex execution result and CR & #101; ate
The results of the Index Command are similar. The test results and function descriptions of all these commands are described later in this article.

Factors to consider before organizing fragments

System resource problems

Before sorting the index shards, make sure that any system performance problems are irrelevant to system resource restrictions. The detailed discussion in this regard is beyond the scope of this article, but some more common resource problems and I/O Sub-
System performance, memory usage, and CPU usage. For more in-depth discussions on these types of resources, see the "More Information" section at the end of this article.

Physical disk fragments

In some systems, disk fragmentation results in poor performance. To determine whether disk fragments exist, you can use Microsoft
Windows built-in system tools, or third-party tools to analyze SQL
The partition of the server. For small databases on the conventional I/O subsystem, we recommend that you first perform disk fragmentation before running the index fragmentation tool. For more intelligent disk subsystems
For large databases, such as the San (storage area network storage area networks) Environment, disk fragmentation is not necessary.

Query with poor execution

When examining any performance-related issues, you must be able to identify those queries with poor execution efficiency. Some information discussed here will also be used later. This information is used to determine which index fragments will be sorted.

You can use SQL profiler to identify poorly executed queries (for more information, see SQL
Server online help & #34; SQL profiler & #34; topic ). Run SQL
Profiler will incur overhead; however, only the following events can be monitored to collect the necessary information, and the impact on performance is as small as possible (generally, CPU usage less than 10%,
Of course there are some differences based on the situation ).

SQL
Profiler provides a trace template named sqlprofilertsql_duration to capture related events. It can be quickly used to identify execution efficiency
Poor query. You can also manually create an SQL Profiler trace to capture the following events:

Tsql: sqlbatchcompleted
Stored Procedures: RPC: Completed

Run SQL
The time length of profiler depends on the server workload. To make the trail more effective, you need to select representative task types, at least those jobs that can display low performance. When the trail is
After capturing, the field tracks the data in the column for the duration in the log. The data in this column is in milliseconds, indicating the time required for processing or querying each batch.

Identifies the query that causes the worst performance

Here are some suggestions that can identify the query that causes the worst performance:

Groups A trail Based on the query duration. Focus on the first 10 worst queries.
If a large number of stored procedures are used in an application, use the sqlprofilersp_counts template to identify the stored procedures that are most called. Focus on the most frequent calls, the same
It is also a storage process that causes poor performance.
Place the collected data in the SQL Server table. In this way, you can use a query table to analyze the performance in more detail (for example, average running time, maximum running time, and so on ).

Infrastructure

After finding the query with the longest running time and the worst performance, make sure that the database infrastructure is optimal for that query. For example, make sure that an appropriate index exists and is correctly used by that query.
. You can use the query analyzer to display and check the query plan to find the indexes used in the query task. When you use the query analyzer to display the query execution plan graphically, the previous data is warned
(For example, the table name is displayed in red ). Resolve these issues before organizing the fragments.

When checking the query plan, remember the following suggestions:

Find the steps to increase the sales volume in the execution plan. These steps are the most time-consuming part of the query. Solving the problems caused by these steps will greatly improve the performance.
Find out the steps to perform an index scan. Index scanning is the most profitable part from fragmentation. Note that the indexes used in index scanning with poor performance can be concentrated on these indexes during fragmentation.
Line.

You can use index tuning (index tuning)
To analyze the workload. Use the report generated by the index to determine whether to modify the infrastructure. Complete these changes before the fragment.

Small-scale environment vs. Large-scale environment

The test here is based on two servers, and the I/O subsystem between the two servers is very different. One server represents a small-scale environment, and the other represents a large-scale environment. Used to explain the test results.
The environment specifications are as follows.

Small-scale environment

In small-scale environments, the database size is between 10 Gbit/s and 20 Gbit/s. Data is distributed on two more physical disks. tempdb and database logs use RAID
0. The DSS database contains two file groups, each of which has one file. The OLTP database only contains one file group with one data file in it.

Large-scale Environment

In combination with the Hitachi Data System, you can use Hitachi freedom storage.
Lightning 9900 series lightning 9960
System to build a SAN Environment for data storage. The database used for testing is about 1 TB. Data is distributed across 64 physical disks and the Raid 1 + 0 structure is used. The
The disk consists of eight Luns (logical unit
Numbers) connection. The database contains a file group, which contains eight data files. Tempdb and database logs are separately stored on a group of disks, which are isolated from data files and 48 disks.
Used to store tempdb, and logs are distributed on eight disks. To quickly back up and restore fragmented database images, two Hitachi
Shadowimage copy data/log backup, lightning
9960 the system is used to synchronize online data and shadowimage backup data. In this environment, it is run twice on three shards, because the storage of large capacity needs to be maintained at each level (about
1.4 TB.

Effect of index fragmentation on Performance

The test results will be discussed in detail later. However, although fragmentation has a negative impact on both small-scale and large-scale environments, the impact on large-scale environments is undoubtedly much smaller. Because of its large scale
The environment obtained extremely high I/O performance from the San, so this conclusion should be correct: data is not only distributed across multiple disks, But san also provides a 16 GB Data Buffer. I/O
The benchmark test shows that the maximum read speed of 1 TB data is 354 MB/sec, while that of 71 MB/sec in small-scale environments.

Note: These values change according to the implementation steps and storage configuration of each user.

Obviously, the high-performance I/O Sub-system
Server performance is very favorable, but index fragmentation does improve the performance of all systems. When creating a database, consider the I/O subsystem with caution and make sure that the log files and data are as close as possible.
Database data files are isolated.

Determine when to perform index fragmentation

Consider the following important suggestions when you decide when to perform index fragmentation:

Identifies a fragmented index.
Understand which tasks will profit from the fragmentation.
Determine the I/O performance of the query.
Understand the impact of fragment and the SQL Server pre-read manager.

In the next section, the test results can be used to help you understand these suggestions.

Use DBCC showcontig to identify fragmented Indexes

Before deciding when to defragmentation, you must first determine which indexes have fragments. DBCC showcontig can be used to measure the fragmentation degree and page density level on the index (page
Density level ).

The following is an example of running DBCC showcontig:

DBCC showcontig scanning & #39; table_1 & #39; table...
Table: & #39; table_1 & #39; (453576654); index ID: 1, Database ID: 8
Table level scan completed MED.
-Pages scanned ................................ 48584
-Extents scanned .............................. 6090
-Extent switches .............................. 12325
-Avg. pages per extent ........................ 8.0
-Scan density [best count: actual count] ...... : 49.27% [6073: 12326]
-Logical scan fragmentation .................. 10.14%
-Extent scan fragmentation ................... 32.74%
-Avg. bytes free per page ..................... 1125.2
-Avg. Page density (full )..................... 86.10%
DBCC showcontig scanning & #39; table_1 & #39; table...
Table: & #39; table_1 & #39; (453576654); index ID: 2, Database ID: 8
Leaf level scan completed MED.
-Pages scanned ................................ 41705
-Extents scanned .............................. 5221
-Extent switches .............................. 6094
-Avg. pages per extent ........................ 8.0
-Scan density [best count: actual count] ...... : 85.55% [5214: 6095]
-Logical scan fragmentation .................. 7.80%
-Extent scan fragmentation ................... 6.63%
-Avg. bytes free per page ..................... 877.7
-Avg. Page density (full )..................... 83.20%

Pay special attention to the logical scan fragmentation and average when checking the running results of DBCC showcontig.
Page density. Logic scan fragmentattion indicates the percentage of unordered indexes (note:
The value is irrelevant to the heap and text indexes. The so-called heap indicates a table without clustered indexes .). Page density is a measure of the index leaf-level page filling. Please find SQL
For more information, see the "DBCC showcontig" topic of the server online help.

Analyze the output result of DBCC showcontig

When analyzing the output results of DBCC showcontig, consider the following:

Fragments affect I/O. Therefore, you need to focus on large indexes because these indexes are less likely to be cached by SQL Server. DBCC
The number of pages obtained by showcontig can be used to estimate the index size (8 KB for each page ). In general, there is no need to focus on indexes with fragmentation levels less than 1,000 pages. During testing, the package
Indexes containing more than 10,000 pages will affect the performance, especially those containing more pages (more than 50,000 pages), which will cause the maximum performance improvement.
Logical Scan
If the value of fragmentation is too high, the index scanning performance will be greatly reduced. During the test, clustering indexes with more than 10% logical fragments improve the performance after fragmentation.
20% of clustered indexes, especially performance improvement. Therefore, focus on the indexes with logical fragments greater than or equal to 20%. Note that this standard is meaningless for the heap (index id = 0.
Average page density (average page
Density) is too low, resulting in more pages to be read in the query. By reorganizing these pages, you can increase the average page density to complete the same query as long as you read a small number of pages. Generally
After data is imported, the table has a high page density. As data is inserted, the page density decreases, resulting in Split of leaf pages. When checking the average page density, remember that this value depends on the value of the fill factor set during table creation.
Although the scan density (SCAN
Density) can be used as a reference for fragment level, but this reference is invalid when the index spans multiple files. Therefore, scanning density should not be considered when checking indexes that span multiple files.

Monitoring fragmentation level

It is a good practice to regularly monitor the index fragmentation level. SQL Server online help & #34; DBCC
Showcontig & #34; in the topic, there is an example script for automatically capturing and recreating an index with a large degree of fragmentation. It is recommended that you use the interval]
]>