17th-Configuring SQL Server (4)-Optimizing configuration of SQL Server instances

Original: 17th--Configuring SQL Server (4)--Optimizing configuration of SQL Server instances

Objective:

sp_configure can be used to manage and optimize SQL Server resources, and most configurations can be implemented using the graphical interface of SQL Server Managementstudio.

Preparatory work:

To view the configuration of the current instance of SQL Server, you can also use the following query to implement:

SELECT  *from    sys.configurationsorder by name

Here are the results of this machine:

Steps:

1. Execute the following statement to see the configuration at the instance level:

sp_configure GO

2. In order to see all the visible configurations, you can use the following statement:

sp_configure ' show advanced options ', 1; Goreconfigurego

3. Now to execute the statement of step one, you can see that the result set has changed:

sp_configure GO

4, execute the statement, the recovery interval increased:

sp_configure ' recovery interval ', 5; RECONFIGURE with OVERRIDE GO

5. Configure the amount of memory used in the index creation task:

sp_configure ' index create memory (KB) ', 1024; RECONFIGURE with OVERRIDE GO

Analysis:

First clarify a statement that appears after step two,RECONFIGURE withOVERRIDE . This statement tells SQL Server to accept this value, and if the value is correct, it forces sp_configure to configure the value to SQL Server.

In step 1, the sp_configure stored procedure shows the available values at the instance level, which contains the underlying configuration, and if you want to see more detail, use the statement in step 2 to set the value of showadvanced option from 0 to 1.

In step 3, you can see that there are more configurations because the advanced options have been turned on in step 2.

In step 4, the recovery interval is set to 5 minutes, which means that it occurs every 5 minutes. SQL Server periodically executes checkpoint and writes all dirty pages to the data page of the hard disk.

In the fifth step, the memory consumed by the index creation is modified, and the index creation operation is one of the most resource-intensive operations, especially when the table is very large, affecting the available memory of SQL Server. This is especially true when there are other memory-consuming software on the server where SQL Servers reside.

Expand your knowledge:

Creating an index on a giant table is not only time-consuming and resource-intensive, but it can cause the log file to grow wildly over a short period of time.

17th-Configuring SQL Server (4)-Optimizing configuration of SQL Server instances