Configuring SQL Server for Optimization - Microsoft SQL Server 2012 Performance Tuning - page 407

3. Execute again the same query that we ran in step 1, and see the difference in the

result set:
sp_configure
GO

4. Execute the following query to set a recovery time in minutes, for the server:

EXEC sp_configure 'recovery interval', 5
RECONFIGURE WITH OVERRIDE
GO

5. To set the memory for the index creation task, execute the following query.

EXEC sp_configure 'index create memory (KB)',1024
RECONFIGURE WITH OVERRIDE
GO

Please note that the value I have provided here is just an example;
it may vary from environment to environment.

How it works...

Before we move further, we would like to clarify one statement given from step 2 onwards; we
have used the statement RECONFIGURE WITH OVERRIDE . This statement asks SQL Server
to accept the value; if it is in the correct data type format, given in the sp_configure stored
procedure and forces it to reconfigure the option with the provided value.

In step 1, we executed the sp_configure stored procedure, which shows a list of instance-
level configuration along with its value. The list consists of basic options. If you want to display
the advanced options list, provide Show Advanced Option with the value 1 (by default, it is 0),
which is executed in step 2.

We executed the same SQL statement in step 3 that we executed in step 1, but this time, it
will show all options, as we set the Show Advanced Option value to 1 in step 2.

Step 4 sets the value for recovery interval to five minutes. It means that recovery should
take up to five minutes. SQL Server issues CHECKPOINT so that it can maintain a recovery
interval time. At the time CHECKPOINT runs, it transfers all data from data pages to the disk,
writes all committed transactions to disk from the log file, and rolls back all the uncommitted
transactions, so the question of data integrity doesn't arise.

The index creation operation is one of the heaviest operations, especially when the table is
large, with millions of rows, though SQL Server manages memory dynamically and efficiently by
itself This nature of SQL Server may affect the performance of the server adversely sometimes,
when we have some other services running on the server simultaneously, such as those for file
server, domain controller, IIS server, and so on. At that time, we may need to control the memory
consumed by the index creation operation, which you can perform by the query given in step 5.