CAPITULO VI. RESULTADOS DEL SOFTWARE DE OPTIMIZACIÓN
Gráfica 7. Productividad Promedio de Ordenes por Hora del Día (Sábado)
Prior to completing each task in the following practices, list the steps you would take to accomplish the task. After completing the task, assess how accurately you predicted the nec-essary steps.
■ Practice 1 Add an additional Database Engine instance to SQL-A, SQL-B, and SQL-CORE named DEVELOPMENT.
■ Practice 2 Install WSRM on SQL-B. Configure three process-matching criteria for each instance installed on the server. Create but do not apply a custom resource allocation policy that assigns 30% processor resources to each of the three process-matching criteria representing Database Engine instances.
Answers
This section contains the answers to the lesson review questions and solutions to the case scenarios in this chapter.
Lesson 1
1. Correct Answer: A
A. Correct: The ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = AUTO statement will configure the instance to use all available processors.
B. Incorrect: The ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = 2,3 statement will configure the instance to use processors 2 and 3 instead of all avail-able processors.
C. Incorrect: The ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = 0,1 statement will configure the instance to use processors 0 and 1 instead of all avail-able processors.
D. Incorrect: The ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = 0,4 statement will configure the instance to use processors 0 and 4 instead of all avail-able processors.
2. Correct Answers: A and D
A. Correct: You need to execute the statement:
RECONFIGURE;
GO
To reconfigure the instance to use the new maximum server memory setting.
B. Incorrect: You should not execute the statement:
EXEC sys.sp_configure 'min server memory', 1024;
GO
This statement configures the minimum rather than the maximum instance memory.
C. Incorrect: You should not execute the statement:
EXEC sys.sp_configure 'min server memory', 4096;
GO
This statement configures the minimum rather than the maximum instance memory.
D. Correct: You need to execute the statement:
EXEC sys.sp_configure 'max server memory', 4096;
GO
This statement correctly configures the maximum instance memory.
Answers CHAPTER 2 101 3. Correct Answer: D
A. Incorrect: Minimum server memory enables you to specify a minimum amount of memory allocated to the instance.
B. Incorrect: Maximum server memory enables you to specify a maximum amount of memory allocated to the instance.
C. Incorrect: Processor affinity enables you to bind instance CPU traffic to specific processes but not to disk I/O activity.
D. Correct: I/O affinity enables you to bind disk input/output operations to a specific processor.
4. Correct Answer: B
A. Incorrect: The master system database records all system-level information.
B. Correct: The model system database is used as a template for all newly created databases.
C. Incorrect: The msdb system database is used by SQL Server Agent for scheduling alerts and jobs.
D. Incorrect: The tempdb system database holds temporary objects and intermedi-ate result sets.
5. Correct Answer: A
A. Correct: You use sp_configure to enable Database Mail on an SQL instance.
B. Incorrect: Use the sp_rename stored procedure to change the name of a user-created object in the current database.
C. Incorrect: Use the sp_monitor stored procedure to display statistics about SQL Server.
D. Incorrect: Use the sp_depends stored procedure to display information about database object dependencies.
Lesson 2
1. Correct Answer: B
A. Incorrect: You must run the sp_cycle_agent_errorlog stored procedure from the msdb system database rather than from the master system database.
B. Correct: You must run the sp_cycle_agent_errorlog stored procedure from the msdb system database to rotate the SQL Server Agent error log file.
C. Incorrect: You must run the sp_cycle_agent_errorlog stored procedure from the msdb system database rather than from the model system database.
D. Incorrect: You must run the sp_cycle_agent_errorlog stored procedure from the msdb system database rather than from the tempdb system database.
2. Correct Answer: A
A. Correct: Resource Governor enables you to allocate session requests to different resources based on the characteristics of the session request properties.
B. Incorrect: WSRM enables you to allocate different resources to different instances but does not enable you to allocate different resources based on session requests.
C. Incorrect: Processor affinity enables you to configure the instance to use specific processors but does not enable you to differentiate resources based on session request properties.
D. Incorrect: I/O affinity enables you to configure disk I/O to occur by using a spe-cific processor but does not allow you to differentiate resources based on session request properties.
3. Correct Answer: C
A. Incorrect: You can deploy a maximum of 50 SQL Server 2012 Database Engine instances on a non-clustered server running Windows Server 2008 R2 Enterprise.
Ten instances is fewer than 50.
B. Incorrect: You can deploy a maximum of 50 SQL Server 2012 Database Engine instances on a non-clustered server running Windows Server 2008 R2 Enterprise.
A 25-instance limit applies to deployment on clustered hosts. Twenty-five instances in a non-clustered environment is fewer than 50.
C. Correct: You can deploy a maximum of 50 SQL Server 2012 Database Engine instances on a non-clustered server running Windows Server 2008 R2 Enterprise.
D. Incorrect: You can deploy a maximum of 50 SQL Server 2012 Database Engine instances on a non-clustered server running Windows Server 2008 R2 Enterprise.
One hundred instances exceeds this value.
4. Correct Answer: B
A. Incorrect: The maximum resource value assigned to the third pool is 100%; the sum of the minimum resource values assigned to the other pools is 50%. Thirty percent is less than this value.
B. Correct: The maximum resource value assigned to the third pool is 100%; the sum of the minimum resource values assigned to the other pools is 50%.
C. Incorrect: The maximum resource value assigned to the third pool is 100%; the sum of the minimum resource values assigned to the other pools is 50%. You can-not assign 70% to this pool.
D. Incorrect: The maximum resource value assigned to the third pool is 100%; the sum of the minimum resource values assigned to the other pools is 50%. You can-not assign 100% to this pool.
Answers CHAPTER 2 103 5. Correct Answer: B
A. Incorrect: You can use Resource Governor to assign resources only from within an instance. You cannot use it to assign resources across instances.
B. Correct: You can use WSRM to assign processor resources to different instances.
C. Incorrect: Although you can use processor affinity to assign resources, by using processors it is only possible to assign resources to instances in lots of 25% of the total CPU bandwidth. This does not meet the requirements of the question; hence, you should use WSRM.
D. Incorrect: I/O affinity enables you to assign dedicated processors to disk I/O. You can’t use this for distributing CPU bandwidth across instances.
Case Scenario 1
1. Use the following Transact-SQL code when connected to Instance-A in SQL Server Management Studio to configure the instance to use a minimum server memory of 512 MB and a maximum server memory of 8192 MB.
EXEC sys.sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sys.sp_configure 'min server memory', 512;
GO
EXEC sys.sp_configure 'max server memory', 8192;
GO
RECONFIGURE;
GO
2. Use the following Transact-SQL code when connected to Instance-A in SQL Server Management Studio to configure processor affinity for CPU 0 and 1.
ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = 0,1
3. Use the following Transact-SQL code when connected to Instance-A in SQL Server Management Studio to set the default fill factor to 95%.
sp_configure 'show advanced options', 1;
GO
Case Scenario 2
1. Use the following command-line command to install the MELBOURNE instance and set the account ADATUM\sam_abolrous as the SQL Server Administrator:
Setup.exe /qs /Action=Install /Features=SQLEngine /InstanceName=Melbourne /SQLSYSADMINACCOUNTS="adatum\sam_abolrous" /IAcceptSQLServerLicenseTerms 2. You should use the following statement, remembering that the stored procedure
needs to be executed from the msdb system database, to cycle the agent error log:
USE msdb;
GO
EXEC dbo.sp_cycle_agent_errorlog;
GO
3. You should use the following statement to cycle the SQL Server error log:
EXEC sp_cycle_errorlog;
GO
105
C H A P T E R 3