Microsoft SQL Server 2000 Super Management Manual (36)

Almost all components in the system can lead to bottlenecks. The bottleneck can be caused by a component, such as a disk, or caused by a set of components, such as an I / O subsystem, or a combination of different elements. For example, you may first detect an I / O subsystem bottleneck. The method of solving this problem is to support the I / O number of I / O (hardware solutions) in the problem system, or reduce the occurrence of I / O through optimization low efficiency queries (soft solution) ), Or both. When I / O issues are resolved, it may be found that the CPU bottleneck is now generated, and the speed of the CPU or the number of CPUs is needed.

Discover the problem to determine if there is a problem with the system, first observe the performance of the system. For example, if the user is executing the database query or modification, it is slower than the expected reaction time. This is the general situation of performance issues or bottlenecks. Maybe you will notice that when you perform some kind of query, all other operations on the system will perform slower than usual. Therefore, it will focus on the optimization query caused by problems, or when the system is allowed to access the system, try to find the query to find the reason. Another way to determine if there is a problem, is a regular testing and monitoring system. Tools that can be used include Windows 2000 System Monitor and SQL Server Enterprise Manager. This section will learn how to use these two tools to check the status of the system, and will learn the SP_WH 0-7356-1266-8 pre-depiction of the SQL Server process.

Description

Refer to Chapter 35 if you need to use SQL Profiler and SQL Query Analyzer to detect and SQL statements.

System Monitor Windows2000 System Monitor not only supports Windows2000 counters, but also supports SQL Server counters. These counter monitor system functions change over time to determine the status in the system, such as the percentage of CPUs, or the rate ratio of SQL Server. (This chapter also provides information about a particular performance counter.) You can observe the monitor at any time or record the data into the file, and then look at.

To use the System Monitor monitoring system, please follow the following steps:

Press Start / Program Set / System Management Tool / Efficacy to enter the system monitor window. Specify whether you use the counter data in a chart format, report format, or record data format, or select the appropriate button on the tool column to view the data available before the data record file. Figure 36-1 shows a chart view in the system monitor. If you choose to view the record file, the dialog box will appear on the file you open.

Figure 36-1 Windows 2000 Efficiency Monitor

To add a viewed counter in the Entire Window, press the plus button in the tool column, which will appear in the new counter dialog, as shown in Figure 36-2. Click the computer counter to view the counter of the unit, or select the counter from the following computer, and select a remote computer name from the drop-down list to view the computer's counter.

Figure 36-2 "New Platters" dialogue

Select the SYSTEM object from the drop-down list of performance objects. These objects represent system components. The item counter you selected will appear on the list of left corners of the dialog. To observe the counter of all items, press all counters. If you want to monitor some specific counters, click the check from the list and select the counter on the list. Some counters are not only one, and these examples will appear on the list of the right corners of the dialog. To select one or more specific examples to view, click the list selection case, or select all the case items to view all examples. Press the new increase, the selected counter will increase the performance window. (If you select multiple examples of a counter, they will all add it.) You can continue to add the counter. Before you want to go back to the performance window, press to turn it off, you can see the performance information provided by the counter. Figure 36-3 shows the results of three counters: Context Switches / Sec, Total Server Memory (KB) and% Processor Time.

Figure 36-3 Instant System Monitor

To deposit the performance data into the record file, follow the procedure below:

Expand the effectiveness record file and alert in the left pane of the performance window. Press the right button on the counter record file and select the new record file setting from the quick function table. After the new record file is set, you enter your record file setting name here, as shown in Figure 36-4, finally determine.

Figure 36-4 "New Record Settings" dialog

After the new record file is named after the new record file is named. In general signages, press new. The new counter dialog box appears, select the counter you want to record, just as described in steps 3 ~ 5. General Signings also allow you to change the name of the record file and specify how long the performance data is checked once. Press a record file page sign to set additional properties of the record file. Figure 36-5 shows these settings, which will be added after its name, and set into two-bit file type.

Figure 36-5 New Record Archive Page in the Window

Press the scheduled page sign. In this page, you specify a start and end time for the record file. You can also choose to turn on a new record file, or execute the instruction when the current record is turned off. Press to determine the window and will archive the file information. If you choose to open a record now, the record will start when you click OK. The project of this record file will be displayed in the performance window, as shown in Figure 36-6. You can use the performance monitor to check the status of the system on a regular basis. Monitoring daily or weekly is a good idea so you can understand your system so that you can identify this phenomenon when an exception occurs. Save the performance data to the record file for later check, which is also a recommended method - record the information in the file, you can compare the performance information before and after the system changes, and determine if this change is good. You can also use these record files to compare how users and system activities are changed from one state to another. For example, you may notice that in recent days this month, users are more than other times. This way you will make sure your system can handle the load when these spikes.

Figure 36-6 Effective Window, display a project of a new counter record

Enterprise Manager

In addition to the automatic daily management functions, Enterprise Manager can be used to help monitor the SQL Server procedure and lock. (See Chapter 19 About Lock Information.) For example, you can collect information about program locking and object-locking - In these cases, an object can be a data sheet, a database or a temporary data sheet. To view these information, follow these steps.

Expand SQL Server / SQL Server Group / Manager / Current Activity in Enterprise Manager Window, as shown in Figure 36-7. The current activity data clip contains three data clamps: processor information, lock / processing sequence identification code and lock / object.

Figure 36-7 Expanding the "Current Activity" folder in ENTERPRISE MANAGER

Press processor information to view the user name and their program IDs currently connected to SQL Server; these user program status (execution, sleep, or background); which of them connects; what orders they have implemented and Application; waiting time (user spends the time waiting for the available resources); CPU, entity I / O, and memory usage of each program; and blocking status of each program (the program blocks others or is blocked by others ). To see these information, you must move the window roll to right. Figure 36-8 shows these information. Press the lock / processing sequence identification code, check the list of system program identification code (SPID) in the right pane, as shown in Figure 36-9. Press two on a SPID on the right pane to display the processing sequence detailed information dialogue, as shown in Figure 36-10. This dialog box displays the last T-SQL instruction executed by the program.

Figure 36-8 "Processor Information" in Enterprise Manager

Figure 36-9 Display SPID in the "Lock / Processing Code" pane

Figure 36-10 "Processing Details" dialogue

Expand the lock / processing sequence identification code folder to see the currently locked SPID, as shown in Figure 36-11.

Figure 36-11 Expand "Locking / Processing Document Identification Code" folder

Press a SPID in the left pane to view the specific lock information of the program, as shown in Figure 36-11. These information include locking types, locked modes, locked states, and locked owners. The locked type includes one of the following:

RID Data Column Locks Locks in the Key Index PAG Data or Index Page Lock Ext Extent Lock TAB Data Sheet Lock, Contains All Data and Index Page DB Database Lock Lock Mode of the Data Sheet You can be one of the following: S sharing lock X Exclusive Lock U Update Lock BU A Lockup IS intent (Intent) Shared IX intentions exclusive SIX has the structure description of SCH-M DDL operations (SCHEMA) Structure description lock

The locked state can be one of the following:

GRANT means that the lock will be granted to the program Wait indicate that the program is blocked by another program, and waits for a lock CNVT to indicate that the lock is converted into another lock type.

Expand the lock / object data clip, see a list of locked objects, as shown in Figure 36-12. Items that can be locked include data sheets, temporary storage tables, databases, and so on.

Figure 36-12 Expansion "Lock / Item" Data Clip

Press a locked database or data table name to view its lock information in the right pane, as shown in Figure 36-13. These information and the same content appear after selecting the SPID in the lock / processing sequence identification code folder, just the viewing angle.

Figure 36-13 Reviewing the lock information of the object

SP_WHO pre-store

You can also perform the following instructions in the Query Analyzer or OSQL prompt to see the information:

SP_Who Active

Go

The result of executing this instruction in Query Analyzer is shown in Figure 36-14. If a program is locked, the BLK field will display the SPID that is locked.

When the user complains that the transaction is too slow, you can perform this instruction to find a problem. Many times you will find that most of the blocked programs are blocked by another program. Once the problem that generates a problem, you can determine the reason for the blockade.

Figure 36-14 Executing the results of sp_who

You should always monitor lock to determine if some programs occupy a lock time too long, and whether the program is often blocked by other programs (they have WAIT status). But usually complaining of users is too slow due to the problem of blockade problems. If the blockade occurs too frequently or too long, you may have to make sure which program has exclusive lock or data table lock. These locks may be caused by blocking, and monitor the SQL statement executed by the program. Then, if you might try to optimize these statements, the faster release lock or avoids the information table lock. In general, a program waits for a lot of time when a lock is available, and the program uses more time to complete. Therefore, reducing locking competition can improve the response time of the transaction.

relevant information

For more information on locking, see and Index Display Lock, and select Display Lock Information in the topic dialogue found.

General Effective Bottlenecks You have learned how to use effective monitoring tools such as system monitors, Enterprise Manager, Query Analyzer and Profiler, and you are also ready to deal with efficacy bottlenecks. In this section, we will see some very common performance bottlenecks and different solutions. Most of these bottlenecks are interrelated, and a bottleneck may disguise the form of another bottleneck. You must look for bottlenecks caused by hardware and software - a number of performance problems that occur in the bottleneck. Hardware that may cause bottlenecks include CPUs, memory and I / O subsystems; can result in a bottleneck's software including SQL Server applications and SQL statements. In the following chapter, we will test each problem in more detail. CPU

An ordinary functional problem is lacking horsepower. The processing capability of the system depends on the number, type, and speed of the CPU in the system. If you don't have enough CPU capabilities, you may not be able to handle the transaction to make user satisfaction. To use the system monitor to determine the percentage of the CPU, check the% Processor Time Counter in the Processor object (select all CPUs in the multiprocessor system). If your CPU is executed in 75% or higher (75% is the maximum required for the rules set by Chapter 6), you can also encounter a CPU bottleneck. If you have seen a stable CPU usage below 60%, you can still benefit from the addition of faster CPUs or more CPUs in the system. Before deciding to upgrade your CPU, identify other features of the system. For example, if your SQL statement is low, you may have a lot of non-necessary procedures, while optimizing these statements can help you reduce CPU utilization. Or assuming that the SQL Server data quickly disabled is less than 90%. You may need to increase your memory to the data (which is added to increase the Max Server Memory parameter or increase the system's entity memory), which allows for more information to deposit and less physical disk I / O operation, Resulting in a lower CPU usage. Sometimes you only increase the processor, you can get better system performance, especially from a single processor system. However, not all applications can accommodate the size of the multiprocessor system. SQL Server itself has a good delay, but not all executed SQL statements require ductility. A program can only be executed on a CPU at a time, and a single CPU for a single SQL statement is enough. For the extension of the performance, your SQL Server must perform a lot of SQL statements at the same time - multiple statements can be executed on different CPUs simultaneously, so multiple CPUs can handle these statements at the same time.

In general, you can improve efficiency through using more and faster CPUs. However, in some cases, while increasing the faster CPU, you must expand the memory and I / O capacity, otherwise you may only transfer this bottleneck to another, and may even be a new CPU. For example, if you have a CPU bottleneck, you solve this problem by adding a CPU, but you may find more work in your system, thus causing more disk I / O, then you An I / O bottleneck will be found. In addition, make sure you check that you decide to increase the CPU's quick-tap size. The larger the CPU, the better the performance, especially in the multiprocessor system.

Simply put, if you decide you need more processing power, you can increase the CPU or replace the existing CPU with faster. For example, if your system has two CPUs, it can be extended to four, then two kinds of CPUs can be added, or the four new faster CPUs can be replaced. If your system has the maximum number of CPUs, you need more processing capabilities, so you can replace them with faster CPUs. For example, let us assume that you have four 200 MHz CPUs. You can replace them with a faster CPU, such as 500 MHz. Faster CPU will perform faster completion programs.

Memory

Memory is one of the most critical components of SQL Server performance. It is very important to be due to the connection of memory and I / O subsystems. For example, in the I / O intensive system (system of a large number of I / O), the more the SQL Server is used to store the information, the less the entity I / O must be performed. This is because the information can be found in the information, not in the magnetic disc. SQL Server has a complex quick-tank that enhances system performance without accessing data from memory, without having to access disk, because access disks will provide lower performance. The more information you can access from your memory, the better your system performance, the less the implementation of the entity I / O. Memory access is much faster than entity disks. In some cases, the shortcomings of the number of memory may result in a significant magnetic disc bottleneck. This is because the system cannot effectively make quick break will result in more physical disk I / O operations. To see how much SQL Server is using how much system memory, use the system monitor to check the Total Server Memory (KB) counter in the SQL Server: Memory Manager object. If SQL Server is not used as your expected memory size, you may adjust the parameter settings of the memory (such as this chapter later "SQL Server Configuration Settings" section.).

To determine if you have enough quick-sump, use the Efficient Monitor to check the Buffer Cache Hit Ratio Counter in the SQL Server: Buffer Manager object. The usual rule is that you need to have 90% or higher. If this hits are less than 90%, add more memory. Note that in some cases, your system cannot reach 90%, it is due to the feature of the application. This may be because the same information pagination is rarely reuse, and the system often refreshes the old data paging to store new information paging.

Description

SQL Server 2000 dynamically disables the memory of the memory based on the system's memory and memory parameters. Some external resources (such as print programs and other applications, etc.) may cause SQL Server to release most of the memory to use other programs. Carefully monitor the system memory, if possible, separate SQL Server in its own system. For a detailed description of the use of memory parameters, see Chapter 30 and the section later.

The bottleneck of I / O subsystems in the I / O subsystem is the most prone to hardware problems in the database system. It is second only to write a poor SQL statement, and the inferior SQL statement is the biggest performance problem. Fortunately, the I / O subsystem is also one of the most easily solved performance issues. In many cases, adding a disk can completely eliminate this performance bottleneck. I / O subsystem issues from disk can only work at a specific rate. For example, a disk may have a processing capability of 85 times I / O per second. If the disk load is too much, the I / O of these disks will produce a column, and SQL Server will encounter longer I / O wait time. These longer I / O wait time may result in a locking occupied by excessive time, or in an idle state in which the intensity is waiting for resources. The final result will be damaged throughout the system, causing anger user to complain that their trading is too long.

In most cases, the occurrence of I / O sub-system performance is because the I / O subsystem does not have the correct plan to execute. The topic of the plan has been discussed in detail in Chapter 5 and 6, but let us review it here.

The size of the size is necessary, because the number of I / O can be performed per second is limited. When the transaction record file performs most sequencing I / O, it is like a transaction record file, and the disk may reach 150 times I / O per second without load over. On the other hand, when the data file performs random I / O, just like the data file, the same disk may only have 85 times I / O per second. If the system needs to perform higher I / O, the wait time will become very long. To determine if you excessively use a disk, use the Efficiency Monitor item PhysicalDisk and LogicalDisk monitor the counter (will be discussed later in this section). The counter collects information about entities and logical disc I / O behaviors, such as read and writes that occur in the disks per second. You must use the Windows NT / 2000 command Diskperf to start the disk performance counter. These counters are available when installing a job system, but you should know how to start and close the counter. The counter occupies system resources when collecting data, such as the CPU, so you should only use them when you want to monitor system I / O. You can use Windows NT / 2000 command Diskperf to start or close counters.

To know if Diskperf is started, click on the following instructions down under the command prompt character:

Diskperf

If Diskperf is already started, you will see this message: "The physical disk performance counter of this system is set to start." If Diskperf is not open, you will see this message: "The logic and physical disk performance counter of this system is now set to not start."

To start diskperf, click on the following instructions under the command prompt cell:

Diskperf -y

To diskperf, execute the instruction:

Diskperf -n

Before the DiskPerf takes effect, you must restart your computer. Type the following instructions to see more Diskperf options:

DISKPERF?

Counters with special importance are DISK WRITES / Sec, DISK READS / Sec, AVG. Disk Queue Length, AVG. Disk sec / write and avg. Disk sec / read. These counters can help you determine if the disk subsystem is overused. These counters are available in PhysicalDisk and LogicalDisk items. If you want to view the information provided by these counters in detail, when adding the counter, press in the new counter dialogue.

Let's take a look at an example of using a counter. Suppose your system has an I / O bottleneck, you will check AVG. Disk sec / transfer, avg. Disk sec / transfer, avg. Disk sec / transfer, avg. Disk sec / read and avg. Disk sec / write counter, because they need to perform read and write because they monitor disks During the amount, too long wait time indicates that you excessively use a disk or disk array. The general rules are between the normal readings of these counters from 1 to 15 seconds (0.001 to 0.015), but may reach millitcase during the spike (0.020). If you observe a value higher than 20 seconds, you may encounter an I / O subsystem effectiveness.

DISK WRITES / SEC and DISK READS / SEC are also checked. Let us assume that these values ​​are written 20 times per mock and 20 reads per second, with a total of 40 I / O, and capacity is 85 times per second I / O. At the same time, if your disk is waiting for a long time, the disk machine may fail. On the other hand, if the disk is per second, 100 seconds per second, and the waiting time is about 20 seconds or more, you need to add a disk to improve efficiency.

When using the RAID array, if you want to determine how much I / O on your system is being executed, you will divide the total number of I / Os seen in the Efficiency Monitor in the total number of disks in the array and RAID resources. The factor of occupied. The following table lists the number of entities I / O that generates read and write when using RAID technology. Table 36-1 Number of entities I / O execution per RAID level reading and writing

RAID level reading 0111 or 1012514

In general, the best way to correct the I / O subsystem bottleneck is to increase more disks. But remember to consider other possible causes of I / O bottlenecks, such as low-end cash rate, and too much I / O executes transactions. (In most cases, the speed of the fast tap is less than 90%.) If you find an I / O bottleneck, please review the description in Chapter 6 to determine the number of disks required for your system.

Faulty component

Sometimes your system may encounter the performance problem caused by faulty components. If the faulty component is not completely fade, it is only a problem that it is difficult to exclude, because the diversity and complexity of the problem and its solution have exceeded the scope of this book. But there are several tricks that identify faulty components:

Compare disks and arrays When viewing statistics in the performance monitor, a similar component is compared. If you find that the two disks perform I / O rates, it shows different wait time, so slower disks may encounter problems. Monitoring Indicators Network hubs typically have collision indicators. If you pay attention to a high collision of an abnormality in a network section, you may have a faulty component, perhaps a network card or a network cable. Understand your system, the more you spend on your system, you can understand the system's characteristics. You will find it when the system is not working properly. This is a good way to monitor system behavior using the Efficacy Monitor. Read record files Develop often check the habit of the Event Viewer system and application records. Review these record files every day can find problems before the problem is out of control.

application

Another system component that causes performance issues is the SQL Server application. These issues may occur in the application code or in the SQL statement when the application is executed. This section provides some tips and guidelines to address the performance issues related to the SQL Server application.

Best implementation plan

As we saw in Chapter 35, choosing a best implementation plan and data access method is important for queries. Unfortunately, there is no certain formula to determine the best plan. SQL Server automatically selects the calculation of the query optimizer as the best implementation plan. As you are familiar with different types of linkage operations, you may be able to determine the best implementation plan. You usually have to try a variety of different plans to find the best way.

Use index

As we saw in Chapter 17 and 35, the correct index is very important for good performance. Use the index to find the required information, which requires only 10 to 20 I / O operations, while scans the required information through the information sheet, you may need thousands of I / O operations. But you must use the index with caution. Remember, when using INSERT, UPDATE, or DELETE to modify the various information of the data sheet, the index will automatically update, display the changed information, thereby generates more than usual I / O operations. Be careful not to establish too many indexes, otherwise the resource occupation used to maintain these indexes may affect the effectiveness of the data modification.

Use the pre-stored program

As we saw in Chapter 21, the pre-depreciation procedure is used to perform pre-packaged and pre-compiled SQL statements on the server. From the application call pre-save program, it is helpful because of the improvement of SQL statement on the server, and the traffic of the network is also reduced. Since the pre-deployment execution is on the server, the number of data transmitted between the user and the server can reduce the number of data, which you can statter and data filtering in the pre-program, without performing these actions in the application. SQL Server Configuration Set SQL Server 2000 Use virtual way to adjust the configuration, but some adjustment parameters can still be used to change your system operations and execution. In this section, you will learn how to set these options and how they affect your system. In most cases, it is not necessary to change these parameters, but understand what they are and what they do makes you have chance to decide whether to change them. You can set with Enterprise Manager or sp_configure. To use Enterprise Manager, press Right-click on the server name you want to set and select the content from the quick function table, display the SQL Server property window. This window contains 9 tabs, and each tab has options you can set. These tags and their options will be explained below. When these options are set with SP_Configure, some options are set to advance (Advanced). (Which of the following sections will be explained.) You must set the Show Advanced Options option to 1 (enabled), so you can use sp_configure to change a progress option. This option is set to 0 (disabled) under the preset. (When you use Enterprise Manger to set the advancement option, you don't need to worry about this option.) To set Show Advance Options, use the following statement:

SP_Configure "Show Advanced Options", 1

Go

Typically, use sp_configure to set an option, use the following syntax:

SP Configure "option name", Value

The AffInity Mask option The Affinity Mask option is used to specify which CPUs can be performed on the multiprocessor environment. The preset value is 0, specifies that the Windows 2000 scheduling algorithm is determined to perform readiness. A non-zero value sets a bitmap to define the CPU that can perform SQL Server. Ten-carry value 1 (or binary bitmap value 00000001) Indicates that only CPUs 1, 2 (or 00000011) specify only CPUs 2, 3 (or 00000011) specify using CPU 1 and CPU 2, and the like. This option is the advancement option, indicating that Show Advanced Options is 1 when using sp_configure to set this option. It can also be set with Enterprise Manager. To do this, select the processor tab in the SQL Server property window, in the processor control area, select the Nuclear Acquisition next to the CPU you want SQL Server. Press the set and determine to store changes. You must stop and restart SQL Server to take effect. On the specified SQL Server system, you must set an AffInity Mask option to allow SQL Server to use all CPUs. On the SQL Server system that is not specified (including other handlers that require CPU time), you may have to try to set AffInity Mask so that SQL Server will leave a CPU without using the rest of the CPU. Lightweight POOLING Options LightWeight POOLING Options Use to set SQL Server Use the LightWeight to perform auxiliary or fine mode (Fibers). Using fine mode can reduce content switches, allow SQL Server to control the scheduling of the Windows NT or Windows 2000. If your application is executed in the multiprocessor system, and you see a lot of content switches, you may need to set the lightweight poibility parameter to 1, which will enable lightweight poibility, then monitor the number of content switches, check whether they It has been reduced. The preset value is 0, which disables fine mode. The LightWeight POOLING option is also a progress option. When using sp_configure to set this option, you must set Show AdvanceD Options to 1. It can also be set with Enterprise Manager. Select the processor tab in the SQL Server property window, select the option to enable this option using the Windows NT Fibers to enable this option using the Windows NT Fibers to enable this option, or clear this checkup. Press the set, press OK, then stop and restart SQL Server so that this option takes effect. Max Server Memory option SQL Server dynamically assigns a memory. To specify that SQL Server assigns the maximum number of memory (units MB) to the buffer set, you can set the Max Server Memory option. Because SQL Server requires some time to release the memory, if you have other applications to regularly need memory, you should set Max Server Memory so that SQL Server will keep a part of memory for other applications.

The preset value is 2147483647, which means that the SQL Server requesting system can provide memory as much as possible, and when other applications require memory and release memory, the memory is dynamically allocated and released. It is recommended to use this setting in the SQL Server system. If you want to change this setting, calculate the maximum number of memory you can give SQL Server, subtracting the memory required by Windows 2000 from all entity memory, plus other non-SQL Server uses the required memory. This is a progressive option, when using sp_configure to set this option, Show Advanced Options must be set to 1. To use Enterprise Manager to set this option, select the Memory Tag in the SQL Server property window and adjust the maximum (MB) slider. Then press the dynamically set SQL Server's memory. The changes to this option will take effect immediately, and do not need to stop and restart SQL Server. (If you press the quantitative memory, you can set the fixed value of the memory. This can limit the amount of SQL Server assigning the memory, and after the memory is allocated to a fixed value, the memory is no longer released.) MIN Server The MEMORY Options MIN Server Memory option specifies the minimum memory number assigned to the SQL Server buffer center, in MB. Setting this parameter is useful in the system of SQL Server to keep excessive memory for other applications. For example, the server is used as a database server, and is also used as a print and file service. In this environment, SQL Server may release too much memory to these other applications. This will slow down the user's response time. The preset value of MIN Server Memory is 0, allowing SQL Server dynamic allocation and release memory. It is recommended to use this setting, but if your server is not only used as SQL Server, you may need to change these settings. This is a progressive option, when using sp_configure to set this option, Show Advanced Options must be set to 1. You can use Enterprise Manager to set this option, select the memory tab in the SQL Server property window, adjust the minimum (MB) slider, and press the dynamically set the memory of SQL Server. The changes to this option will take effect immediately, and do not need to stop and restart SQL Server. The Recovery Interval Options Recovery Interval option defines the maximum time that the system replies in the fault event is minutes. SQL Server uses this setting and built-in algorithm to determine the frequency of the automatic checkpoint, which will be completed within the specified number of minutes. According to how much work in the system, SQL Server determines how long it should have between the two checkpoints. If there is a lot of work to be executed, the frequency of checkpoints will be more than usual. The less work is performed, the less time from the failure reply. In addition, the longer the reply interval, the longer the interval time allowed between the checkpoints. Increase the response interval by reducing the number of checkpoints will increase efficiency (because the checkpoint causes a large number of disk writes, this will make the user trading time slow a few seconds), but it also adds the time required to reply to SQL Server. The preset value is 0, specifies the SQL Server to determine the interval - a reply time of approximately 1 minute. Increasing the Recovery Interval option is risky.

It is usually used for 5 to 15 minutes of value, but this is entirely dependent on whether you can wait for 5 to 15 minutes' risk to wait for 5 to 15 minutes. Reply. Generally speaking, you might want to add Recovery Interval to reduce the frequency of each checkpoint and their large number of writes, thus allowing users to be more freely executing their transactions in the I / O subsystem without interrupting. This is a progressive option, when using sp_configure to set this option, Show Advanced Options must be set to 1. To use Enterprise Manager to set this option, select the Database Setting Tab in the SQL Server property window and type a value in the recovery interval (minute) text block. The changes to this option will take effect immediately, and do not need to stop and restart SQL Server. This chapter summarizes in this chapter, you have learned the general function of DBA will encounter. You also see how to monitor your system using system monitors and Enterprise Manger and help position performance bottlenecks. In addition, you have learned how to detect and address general system performance issues. This book allows you to learn how to manage SQL Server 2000, which is to manage SQL Server 2000 and why manage SQL Server 2000. You should now effectively manage and adjust your SQL Server system, and can perform daily management tasks smoothly and effectively. We hope that you can read this book, just like we have fun when writing this book.