70-462 | Microsoft 70-462 Braindumps 2021

NEW QUESTION 1
You have a server named SQL1 that has SQL Server 2014 installed. SQL1 has 32 processor cores and 64 GB of RAM.
SQL1 hosts a database used for a public-facing marketing website. SQL1 performs Online Transaction Processing (OLTP) operations only.
Several of the queries that run on SQL1 use the 32 processor cores and complete in 30 ms.
You need to reduce the number of queries that use multiple cores. The solution must also reduce the number of processor cores used by the queries that require multiple processor cores.
Which two database settings should you modify? To answer, select the appropriate settings in the answer area.

Answer:

Explanation: Cost Threshold for Parallelism
The cost threshold for parallelism option specifies the threshold at which SQL Server creates and runs parallel plans for queries. SQL Server creates and runs a parallel plan for a query only when the estimated cost to run a serial plan for the same query is higher than the value set in cost threshold for parallelism. The cost refers to an estimated cost required to run the serial plan on a specific hardware configuration, and is not a unit of time. The cost threshold for parallelism option can be set to any value from 0 through 32767. The default value is 5.
Max Degree of Parallelism
When an instance of SQL Server runs on a computer that has more than one microprocessor or CPU, it detects the best degree of parallelism, that is, the number of processors employed to run a single statement, for each parallel plan execution. You can use the max degree of parallelism option to limit the number of processors to use in parallel plan execution.
References:
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-cost-threshold-for-parall

NEW QUESTION 2
You administer a Microsoft SQL Server database named Contoso on a server named Server01.
You need to write messages to the Application Log when users are added to or removed from a fixed server role in Server01.
What should you create?

• A. ADatabase Audit Specification
• B. A Policy
• C. An Alert
• D. A SQL Profiler Trace
• E. A Resource Pool
• F. An Extended Event session
• G. A Server Audit Specification

Answer: G

NEW QUESTION 3
You administer a Microsoft SQL Server 2012 instance.
You need to configure an existing SQL Authenticated Login that meets the following requirements:
✑ Enforces password policy
✑ No password expiration enforcement
✑ Resets the default database to AdventureWorks database
Which option or options should you choose in the Login Properties of the login for User1? To answer, configure the appropriate option or options in the dialog box in the answer area.

Answer:

Explanation:

NEW QUESTION 4
You administer a Microsoft Azure SQL Database database named Orders.
Users report that a query that filters on OrderDate is taking an exceptionally long time. You discover that an index named IX_OrderDate on the CustomerOrder table is disabled.
You need to ensure that the query can use the IX_OrderDate index. Which Transact-SQL command should you use?

• A. ALTER INDEX IX_OrderDate ON CustomerOrder DISABLE
• B. ALTER INDEX IX_OrderDate ON CustomerOrder ENABLE
• C. ALTER INDEX IX_OrderDate ON CustomerOrder REORGANIZE
• D. ALTER INDEX IX_OrderDate ON CustomerOrder REBUILD

Answer: B

Explanation: Enable a disabled index by using the Transact-SQL ALTER INDEX REBUILD command.

NEW QUESTION 5
You administer a Microsoft SQL Server database named Orders.
Users report that during peak usage periods, certain operations are taking more time than expected. Your initial analysis suggests that blocking is the cause.
You need to gather more data to be able to determine which processes are being blocked and to identify the root cause.
What should you do?

• A. Schedule a SQL Agent job to run every 60 seconds and insert the results of executing the sp_who2 stored procedure into a table.
• B. Use System Monitor to catch the Lock Wait Time event.
• C. Start a trace using SQL Server Profiler to catch the Lock: Timeout event.
• D. Use sp_configure to set the blocked process threshol
• E. Start a trace using SQL Server Profiler to catch the Blocked Process Report event.

Answer: D

NEW QUESTION 6
If you are restoring and recovering a database, which of the following keywords must you specify if you intend to roll forward a transaction log backup after restoring a database?

• A. NOFINISH
• B. NORECOVERY
• C. TRANSACTIONLOG RECOVER
• D. NEXTLOG

Answer: B

Explanation: References:
https://msdn.microsoft.com/en-us/library/ms186858%28v=sql.110%29.aspx

NEW QUESTION 7
Your database contains a table named Purchases. The table includes a DATETIME column named PurchaseTime that stores the date and time each purchase is made. There is a non-clustered index on the PurchaseTime column. The business team wants a report that displays the total number of purchases made on the current day. You need to write a query that will return the correct results in the most efficient manner.
Which Transact-SQL query should you use?

• A. SELECT COUNT(*)FROM PurchasesWHERE PurchaseTime = CONVERT(DATE, GETDATE())
• B. SELECT COUNT(*)FROM PurchasesWHERE PurchaseTime = GETDATE()
• C. SELECT COUNT(*)FROM PurchasesWHERE CONVERT(VARCHAR, PurchaseTime, 112) = CONVERT(VARCHAR, GETDATE(), 112)
• D. SELECT COUNT(*)FROM PurchasesWHERE PurchaseTime >= CONVERT(DATE, GETDATE())AND PurchaseTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))

Answer: D

Explanation: Topic 5, Exam Pool E (NEW)

NEW QUESTION 8
You administer a Microsoft SQL Server instance that has multiple databases. You have a two-node SQL Server failover cluster. The cluster uses a storage area network (SAN).
You discover I/O issues. The SAN is at capacity and additional disks cannot be added. You need to reduce the I/O workload on the SAN at a minimal cost.
What should you do?

• A. Move user databases to a local disk.
• B. Expand the tempdb data and log files
• C. Modify application code to use table variables
• D. Move the tempdb files to a local disk

Answer: D

NEW QUESTION 9
A table named Profits stores the total profit made each year within a territory. The Profits table has columns named Territory, Year, and Profit. You need to create a report that displays the profits made by each territory for each year and its previous year.
Which Transact-SQL query should you use?

• A. SELECT Territory, Year, Profit,LEAD(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PrevProfitFROM Profits
• B. SELECT Territory, Year, Profit,LAG(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PrevProfitFROM Profits
• C. SELECT Territory, Year, Profit,LAG(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PrevProfitFROM Profits
• D. SELECT Territory, Year, Profit,LEAD(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PrevProfitFROM Profits

Answer: C

NEW QUESTION 10
Which of the following conditions must exist prior to restoring a system database in SQL Server 2012?

• A. The hard drive must be reformatted.
• B. You must detach the user databases from the SQL 2012 instance.
• C. The SQL Server 2012 instance must be started in single-user mode.
• D. The SQL Server 2012 instance must be removed.

Answer: C

Explanation: References:
https://msdn.microsoft.com/en-us/library/ms188236(v=sql.110).aspx

NEW QUESTION 11
Note: This question is part of a series of questions that use the same set of answers choices. An answer choice may be correct for more than one question in the series.
You administer a Microsoft SQL Server server that hosts a transactional database and a reporting database. The transactional database is updated through a web application and is operational throughout the day. The reporting database is only updated from the transactional database.
The recovery model and backup schedule are configured as shown in the following table:

The differential backup of the reporting database fails. Then, the reporting database fails at 14:00 hours. You need to ensure that the reporting database is restored. You also need to ensure that data loss is minimal. What should you do?

• A. Restore the latest full backup, and restore the latest differential backu
• B. Then, restore the latest log backup.
• C. Perform a point-in-time restore.
• D. Restore the latest full backup.
• E. Restore the latest full backup, and restore the latest differential backu
• F. Then, restore each log backup taken before the time of failure from the most recent differential backup.
• G. Restore the latest full backu
• H. Then, restore the latest differential backup.
• I. Restore the latest full backu
• J. Then, restore each differential backup taken before the time of failure from the most recent full backup.
• K. Perform a page restore.
• L. Perform a partial restore.

Answer: C

NEW QUESTION 12
Which of the following permissions must a user have in order to query all of the built-in Dynamic Management views?

• A. VIEW ALL
• B. SELECT ALL VEWS
• C. VIEW SERVER INFO
• D. VIEW SERVER STATE

Answer: D

Explanation: References:
https://msdn.microsoft.com/en-us/library/ms188754%28v=sql.110%29.aspx

NEW QUESTION 13
Which of the following will show you a history of job errors that have occurred in jobs scheduled to run in an SQL Server 2012 installation?

• A. Look at the sys.all_job_hist table.
• B. In Management Studio, navigate to SQL Server Agent -> Error Logs and look at the log for the corresponding date.
• C. Open the c:errorjob.log file and search for the particular job and the errors you wish to view.
• D. You must use T-SQL to write a job-logging procedure to monitor jobs.

Answer: D

NEW QUESTION 14
You administer a Microsoft SQL Server 2012 database. You want to make a full backup of the database to a file on disk.
In doing so, you need to output the progress of the backup. Which backup option should you use?

• A. STATS
• B. COMPRESSION
• C. CHECKSUM
• D. IN IT

Answer: A

NEW QUESTION 15
You administer a Microsoft SQL Server instance. You use a two-node SQL Server failover cluster. Node B is primary, and Node A is secondary.
You need to install a security patch on both nodes.
You need to ensure that the following requirements are met:
✑ Both nodes receive the update.
✑ Downtime is minimized.
✑ No data is lost.
Which three actions should you perform in sequence? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)

Answer:

Explanation: Starting with the update to Microsoft SQL Server 2012 Service Pack 1, customers with SQL Server 2012 failover cluster instances can greatly benefit from the fully automated update process provided by Cluster-Aware Updating (CAU).
CAU causes minimal down time for SQL Server 2012 client connectivity. While the primary node is being updated, the SQL Server clustered role (also called the resource group) goes through two failovers. The first failover happens before updating the primary node when CAU brings the original primary node into maintenance mode. During the update process on the original primary node, the SQL clustered role is temporarily brought online on a new primary node. The second failover happens once the update process is complete on the original primary node, and CAU fails back the SQL Server clustered role to the original primary node and brings it online.

NEW QUESTION 16
You create an availability group named HaContoso that has replicas named Server01/HA, Server02/HA, and Server03/HA.
Currently, Server01/HA is the primary replica.
You need to ensure that the following requirements are met:
Backup operations occur on Server02/HA.
If Server02/HA is unavailable, backup operations occur on Server03/HA.
Backup operations do not occur on Server01/HA.
How should you configure HaContoso?

• A. Set the backup preference of HaContoso to Prefer Secondary.Set the backup priority of Server02/HA to 20.Set the backup priority of Server03/HA to 10.
• B. Set the backup preference of HaContoso to Secondary only.Set the backup priority of Server02/HA to 20.Set the backup priority of Server03/HA to 10.
• C. Set the backup preference of HaContoso to Secondary only.Set the backup priority of Server02/HA to 10.Set the backup priority of Server03/HA to 20.
• D. Set the exclude replica of Server01/HA to true.Set the backup priority of Server02/HA to 10.Set the backup priority of Server03/HA to 20.

Answer: B

NEW QUESTION 17
You administer a Microsoft SQL Server 2012 server along with a Microsoft Azure SQL Database database. For both servers, you need to grant users the ability to create logins and create databases.
You need to determine which permission to grant users in each instance.
Which permission or permissions should you choose? (To answer, drag the appropriate permission or permissions to their corresponding instance type or types in the answer area.
Answer choices may be used once, more than once, or not at all. Answer targets may be used once or not at all. Additionally, you may need to drag the split bar between panes or scroll to view content.)

Answer:

Explanation:

NEW QUESTION 18
A network administrator creates a virtual machine from a template that has SQL Server installed. You need to ensure that you can create SQL Server databases on the new virtual machine.
Which two actions should you perform to complete the server configuration? Each correct answer presents part of the solution.

• A. Execute the SP_ADDSERVER statement.
• B. Edit the Instance Names registry key.
• C. Execute the SP_SERVER_DIAGNOSTICS statement.
• D. Execute the SP_DROPSERVER statement.
• E. Run setup.exe and specify the upgrade option.

Answer: AD

Explanation: All you should need to do is run sp_dropserver then sp_addserver to change the server name after the template is renamed. The restart the SQL instance.
exec sp_dropserver 'OldserverName' go
exec sp_addserver 'NewServerName', 'LOCAL' go
References: https://serverfault.com/questions/377377/creating-a-sql-template-for-vmware