Microsoft 70-464 Guidance 2021

NEW QUESTION 1

Your company has a main office in London and a branch office in New York.
Your network contains a server named Server5 that has SQL Server 2012 installed. Server5 contains a database name ContentDB and a table named ContentTable.
You add an additional server named Server9 that runs SQL Server 2012.
You need to create a distributed partitioned view. The solution must minimize the amount of network traffic. What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

• A. Create the view on Server5.
• B. Add Server9 as a linked server.
• C. Create the view on Server9.
• D. Add the Customers table to Server9.
• E. Add Server9 as a Distributor.
• F. Remove the Customers table from Server5.

Answer: ABCD

NEW QUESTION 2

You have a SQL Server 2012 environment that contains two servers. The servers are configured as shown in the following table.

After the failover is complete, a user receives the following error message when connecting to DB1 on Server2:
"Msg 916, Level 14, State 1, Line 1
The server principal "Account1" is not able to access the database "DB1" under the current security context." You verify that there is a server login for Account1 on Server2.
You need to ensure that Account1 can connect to DB1. What should you do?
More than one answer choice may achieve the goal. Select the BEST answer.

• A. Update the SID for Account1 on DB1.
• B. Add Account1 to the db_datareader role.
• C. Create a new database user on DB1.
• D. Implement Windows authentication.

Answer: B

NEW QUESTION 3

You need to monitor the health of your tables and indexes in order to implement the required index maintenance strategy.
What should you do?

• A. Query system DMVs to monitor avg_chain_length and max_chain_lengt
• B. Create alerts to notify you when these values converge.
• C. Create a SQL Agent alert when the File Table: Avg time per file I/O request value is increasing.
• D. Query system DMVs to monitor total_bucket_coun
• E. Create alerts to notify you when this value increases.
• F. Query system DMVs to monitor total_bucket_coun
• G. Create alerts to notify you when this value decreases.

Answer: A

Explanation:
From scenario:
* You need to anticipate when POSTransaction table will need index maintenance.
* The index maintenance strategy for the UserActivity table must provide the optimal structure for both maintainability and query performance.

NEW QUESTION 4

You have a SQL Server 2012 instance that hosts a single-user database.
The database does not contain user-created stored procedures or user-created functions. You need to minimize the amount of memory used for query plan caching.
Which advanced server option should you modify?

• A. Scan for Startup Procs
• B. Enable Contained Databases
• C. Optimize for Ad hoc Workloads
• D. Allow Triggers to Fire Others

Answer: C

NEW QUESTION 5

You have a SQL Server database.
The recovery model is set to full. The transaction log is backed up every night. You discover that the transaction log never decreases in size.
You execute the DBCC SHRINKFILE statement for the transaction log and you discover that the transaction log is unaffected.
You need to ensure that you can reduce the size of the transaction log. What should you do first?

• A. Truncate the transaction log
• B. Kill long-running transactions
• C. Change the recovery model to bulk-logged
• D. Perform a full backup

Answer: A

Explanation:
The transaction log must be truncated before running the DBCC SHRINKFILE operation.

NEW QUESTION 6

You plan to deploy two stored procedures name USP_1 and USP_2 that read data from a database. Your company identifies the following requirements for each stored procedure:
You need to identify which isolation level you must set for each stored procedure. The solution must minimize the number of locks.
Which isolation level should you identify?
To answer, drag the appropriate isolation level to the correct stored procedure in the answer area. (Answer choices may be used once, more than once, or not at all.)

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Box 1: read uncommitted
READ UNCOMMITTED is the least restrictive isolation level because it ignores locks placed by other transactions. Transactions executing under READ UNCOMMITTED can read modified data values that have not yet been committed by other transactions; these are called "dirty" reads.
Box 2: SERIALIZABLE
Places a range lock on the data set, preventing other users from updating or inserting rows into the data set until the transaction is complete. This is the most restrictive of the four isolation levels. Because concurrency is lower, use this option only when necessary. This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.
References: https://msdn.microsoft.com/en-us/library/tcbchxcb(v=vs.110).aspx

NEW QUESTION 7

You need to implement a change to usp_ExportOpenings that meets the integration requirements.
What should you modify in usp_ExportOpenings? (Each correct answer presents part of the solution. Choose all that apply?)

• A. To the end of line 04, add [Opening].
• B. To the end of line 05, add [Opening! title].
• C. To line 10, add FOR XML RAW.
• D. To line 10, add FOR XMLEXPLICIT.
• E. To line 10, add FOR XML AUTO.
• F. To the end of line 04, add [Opening!ELEMENT].
• G. To the end of line 06, add [Opening!salary!ELEMENT].
• H. To the end of line 05, add [Opening!title!ELEMENT].
• I. To the end of line 06, add [Opening! salary].

Answer: ABEI

Explanation:
The AUTO mode generates nesting in the resulting XML by using heuristics based on the way the SELECT statement is specified. You have minimal control over the shape of the XML generated. The nested FOR XML queries can be written to generate XML hierarchy beyond the XML shape that is generated by AUTO mode heuristics.

NEW QUESTION 8

You need to implement a solution that addresses the performance issues of the usp_GetOrdersByProduct stored procedure.
Which statement should you execute?

• A. Option A
• B. Option B
• C. Option C
• D. Option D

Answer: C

NEW QUESTION 9

You have a SQL Server 2012 database named Database1. You execute the following code:

You insert 3 million rows into Sales.
You need to reduce the amount of time it takes to execute Proc1. What should you do?

• A. Option A
• B. Option B
• C. Option C
• D. Option D

Answer: A

NEW QUESTION 10

You plan to create a stored procedure that inserts data from an XML file to the OrderDetails table. The following is the signature of the stored procedure:

The following is the XSD file used to create the ValidateOrder schema collection:

You develop a code segment that retrieves the number of items and loops through each item. Each time the loop runs, a variable named @itemNumber is incremented.
You need to develop a code segment that retrieves the product ID of each item number in the loop. Which code segment should you develop?

• A. SET @productID = @items.value'/Root/Product/productID', int)
• B. SET @productID = @items.value'/Root/Product['+ @itemNumber+ ']/@productID', int)
• C. SET @productID = @items.value'/Root/Product['+ @itemNumber+ ']/productID', int)
• D. SET @productID = @items.value'/Root/Product/@productID', int)

Answer: B

NEW QUESTION 11

You need to add a new column named Confirmed to the Attendees table. The solution must meet the following requirements:
Have a default value of false.
Minimize the amount of disk space used.
Which code block should you use?

• A. ALTER TABLE AttendeesADD Confirmed bit DEFAULT 0;
• B. ALTER TABLE AttendeesADD Confirmed char(l) DEFAULT '1';
• C. ALTER TABLE AttendeesADD Confirmed bit DEFAULT 1;
• D. ALTER TABLE AttendeesADD Confirmed char(l) DEFAULT ‘1’;

Answer: A

Explanation:
http://msdn.microsoft.com/en-us/library/ms177603.aspx

NEW QUESTION 12

You administer a SQL Server 2014 instance.
The server is capable of 10000 IO/second (IOPS). During the time period when the second process executes, the disk IO can reach 7000 IOPS, and CPU use can average 30% over the eight processors.
The first process summarizes the day's activity executed by a login of [SummaryReportLogin]. The second process submits transactions executed by a login of [ETLLogin].
A Resource Governor classifier function has been created to return WG_Low for connections from the [ETLLogin] and [SummaryReportLogin].
You need to set up the Resource Group and Workgroup Pools on the instance. You have the following requirements:
Both processes must never use more than 50 percent of the CPU at any one time.
The number of active queries that these processes can execute simultaneously should be limited to a maximum of 10.
The SummaryReportLogin process must always achieve the minimum IOPS required to be minimally affected during executing the ETLLogin processes.
Develop the solution by selecting and arranging the required code blocks in the correct order. You may not need all of the code blocks.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Box 1:

Box 2:

Box 3:

Note:
CREATE WORKLOAD RESOURCE POOL
* Resource pools. A resource pool, represents the physical resources of the server. You can think of a pool as a virtual SQL Server instance inside of a SQL Server instance.
* Workload groups. A workload group serves as a container for session requests that have similar classification criteria. A workload allows for aggregate monitoring of the sessions, and defines policies for the sessions. Each workload group is in a resource pool.
* CAP_CPU_PERCENT =value
Specifies a hard cap on the CPU bandwidth that all requests in the resource pool will receive. Limits the maximum CPU bandwidth level to be the same as the specified value. value is an integer with a default setting of 100. The allowed range for value is from 1 through 100.
* MIN_IOPS_PER_VOLUME =value
Specifies the minimum I/O operations per second (IOPS) per disk volume to reserve for the resource pool.
* GROUP_MAX_REQUESTS =value
Specifies the maximum number of simultaneous requests that are allowed to execute in the workload group. value must be a 0 or a positive integer.

NEW QUESTION 13

You need to identify which long running transactions use an index. Which dynamic management view should you use?

• A. sys.dm_exec_query_optimizer_info
• B. sys.dm_exec_connections
• C. sys.dm_exec_query_stats
• D. sys.dm_exec_sessions

Answer: A

NEW QUESTION 14

You need to implement a solution that addresses the page split issues. Which statement should you execute?

• A. ALTER INDEX IX_Orders_ShipDate ON OrdersREBUILD WITH (PAD_INDEX=OFF, DROP_EXISTING = ON);
• B. ALTER INDEX IX_Orders_ShipDate ON OrdersREBUILD WITH (FILLFACTOR=50, DROP_EXISTING = ON);
• C. ALTER INDEX IX_Orders_ShipDate ON OrdersREBUILD WITH (FILLFACTOR = 0, DROP_EXISTING = ON);
• D. ALTER INDEX IX_Orders_ShipDate ON OrdersREBUILD WITH (PAD_INDEX=ON, DROP_EXISTING = ON);

Answer: B

NEW QUESTION 15

Your company has a SQL Azure subscription.
You implement a database named Database1. Database1 has two tables named Table1 and Table2. You create a stored procedure named sp1. Sp1 reads data from Table1 and inserts data into Table2. A user named User1 informs you that he is unable to run sp1.
You verify that User1 has the SELECT permission on Table1 and Table2.
You need to ensure that User1 can run sp1. The solution must minimize the number of permissions assigned to User1.
What should you do?

• A. Change sp1 to run as the saUser.
• B. Grant User1 the EXECUTE permission on sp1.
• C. Add User1 to the db_datawriter role.
• D. Grant User1 the INSERT permission on Table2.

Answer: B

Explanation:
http://msdn.microsoft.com/en-us/library/ms191291.aspx

NEW QUESTION 16

You have a Microsoft SQL Azure database that contains a table named Employees.

You create a non-clustered index named EmployeeName on the name column.

You write the following query to retrieve all of the employees that have a name that starts with the letters
JOH:
You discover that the query performs a table scan.
You need to ensure that the query uses EmployeeName. What should you do?

• A. Recreate EmployeeName as a unique index
• B. Recreate EmployeeName as a clustered index
• C. Replace LEFT(name,3) = 'JOH' by using name like 'JOH%'
• D. Replace LEFT(name,3) = 'JOH' by using substring(name, 1, 3) = 'JOH'

Answer: C

NEW QUESTION 17

You have the following stored procedure.

The stored procedure takes much longer to execute than expected.
While reviewing the execution plan of the stored procedure, you discover the following predicate for a Clustered Index Scan operator.

You need to resolve the performance issue. What should you do?

• A. Change the @FileName parameter from nvarchar(512) to varchar(512).
• B. Change the FileName column from varchar(512) to nvarchar(512).
• C. Add a NOLOCK query hint to the SELECT statement.
• D. Convert the table to a memory-optimized table.
• E. Add a FORCESEEK query hint to the SELECT statement.

Answer: A

Explanation:
When using a variable, make sure that the datatype matches the column's datatype. We suspect that the issue is that the variable is NVARCHAR (512) whilst the table column is VARCHAR (512). This is indicated by the CONVERT_IMPLICIT operator in the execution plan.
References:
https://sqlserverperformance.wordpress.com/2009/02/02/beware-of-convert_implicit-in-a-sql-execution-plan/

NEW QUESTION 18

You plan to create a new table that will contain a column named Salary. Salary will contain highly sensitive data.
Salary must meet the following requirements:
Contain numeric data.
Contain only encrypted data that remains encrypted in memory.
You need to identify which encryption type and data type must be used for Salary. Which encryption type and data type should you identity?
To answer, drag the appropriate encryption type and data type to the correct identifier in the answer area.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:

NEW QUESTION 19

You need to modify the function in CountryFromID.sql to ensure that the country name is returned instead of the country ID.
Which line of code should you modify in CountryFromID.sql?

• A. 04
• B. 05
• C. 06
• D. 19

Answer: D

Explanation:
http://msdn.microsoft.com/en-us/library/ms186755.aspx http://msdn.microsoft.com/en-us/library/ms191320.aspx

NEW QUESTION 20

You need to provide referential integrity between the Sessions table and Speakers table. Which code segment should you add at line 47 of Tables.sql?

• A. Option A
• B. Option B
• C. Option C
• D. Option D

Answer: B

Explanation:
http://msdn.microsoft.com/en-us/library/ms189049.aspx http://msdn.microsoft.com/en-us/library/ms179610.aspx http://msdn.microsoft.com/en-us/library/ff878370.aspx

NEW QUESTION 21

You need to recommend a solution that meets the concurrency problems. What should you include in the recommendation?

• A. Modify the stored procedures to use the SERIALIZABLE isolation level.
• B. Modify the order in which usp_AcceptCandidate accesses the Applications table and the Candidatestable.
• C. Modify the order in which usp_UpdateCandidate accesses the Applications table and the Candidates table.
• D. Modify the stored procedures to use the REPEATABLE READ isolation level.

Answer: C

NEW QUESTION 22

You are planning two stored procedures named SProc1 and SProc2. You identify the following requirements:
SProc1 must return a table.
SProc2 must return a status code.
You need to identify which options must be implemented to meet each stored procedure requirement. Which options should you identify?
To answer, drag the appropriate option to the correct requirement in the answer area. (Answer choices may be used once, more than once, or not at all.)

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:

NEW QUESTION 23

You have an index for a table in a SQL Azure database. The database is used for Online Transaction Processing (OLTP).
You discover that the index consumes more physical disk space than necessary. You need to minimize the amount of disk space that the index consumes.
What should you set from the index options?

• A. STATISTICS_NORECOMPUTE = OFF
• B. FILLFACTOR = 80
• C. FILLFACTOR = 0
• D. STATISTICS_NORECOMPUTE = ON

Answer: C

Explanation:
http://msdn.microsoft.com/en-us/library/ms177459.aspx http://msdn.microsoft.com/en-us/library/ms188783.aspx

NEW QUESTION 24

You have a Microsoft SQL Azure database. You have the following stored procedure:

You discover that the stored procedure periodically fails to update HR.Employees.
You need to ensure that HR.Employees is always updated when up_employees executes.
The solution must minimize the amount of time required for the stored procedure to execute and the number of locks held.
What should you do?

• A. Add the following line of code to line 05:SET TRANSACTION ISOLATION LEVEL SNAPSHOT
• B. Add the following line of code to line 13: WITH (UPDLOCK)
• C. Add the following line of code to line 05:SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
• D. Add the following line of code to line 08: WITH (UPDLOCK)

Answer: D

NEW QUESTION 25
......