2018 July New Micorsoft 70-462 Exam Dumps with PDF and VCE Free Updated Today! Following are some new 70-462 Real Exam Questions:
QUESTION 255
You administer a Microsoft SQL Server database server. One of the databases on the server supports a highly active OLTP application.
Users report abnormally long wait times when they submit data into the application.
You need to gather blocking data over an extended period of time. You also need to ensure minimum impact to server performance.
What should you do?
A. Run the sp_who2 command from a query window and examine the BlkBy column.
B. Use the Job Activity monitor to review all processes that are actively running.
Review the Job History to find out the duration of each step.
C. Use sp_configure to set a value for blocked process threshold.
Create an extended event session.
D. Use SQL Profiler to trace all queries that are processing on the server.
Filter queries that have a Duration value of more than 1,000 ms.
E. Run the DBCC TRACEON 1222 command from a query window and review the SQL Server event log.
Answer: E
Explanation:
You can track down queries that are causing deadlocks. On solution is using a trace flag to write the deadlock information to the error log. You can implement trace flag 1222 to do just that.
There are two ways to enable global trace flags. You can enable the trace flag when SQL Server starts by using the -T1222 startup option, or you can use the DBCC TRACEON(1222,-1) command after SQL Server has started.
References:
http://www.mssqltips.com/sqlservertip/2130/finding-sql-server-deadlocks-using-trace-flag-1222/
http://msdn.microsoft.com/en-us/library/ms188396.aspx
QUESTION 256
You have a default installation of SQL Server that hosts an Online Transaction Processing (OLTP) application.
Users report that they experience poor overall query performance for the application.
You query the wait statistics and discover that the two top waits are CXPACKET and SOS_SCHEDULER_YIELD.
You need to modify the SQL Server settings to resolve the issue causing the poor query performance.
Which two settings should you modify? Each correct answer presents part of the solution.
A. max degree of parallelism (MAXDOP)
B. Minimum Memory
C. optimize for ad hoc workloads
D. Boost SQL Server priority
E. cost threshold for parallelism
Answer: AE
Explanation:
A: Lower the MAXDOP.
When high CXPACKET values are encountered, a possible issue, even in case when parallelism is evenly distributed, is when the cost of creating the parallel plan is higher than the cost of the serialized thread.
This is often something that is overlooked and by the rule of thumb of reaching for altering of the Max Degree of Parallelism (MAXDOP), by setting it to 1 (each and every query will be processed by the single CPU core). Configuring MAXDOP settings to 1 should be the last resource used in troubleshooting excessive CXPACKET wait times.
When a high CXPACKET value is accompanied with a LATCH_XX and with PAGEIOLATCH_XX or SOS_SCHEDULER_YIELD, it is an indicator that slow/inefficient parallelism itself is the actual root cause of the performance issues. And in such a scenario if the LATCH_XX waits is ACCESS_METHODS_DATASET_PARENT or ACCESS_METHODS_SCAN_RANGE_GENERATOR class, then it is highly possible that the parallelism level is the bottleneck and the actual root cause of the query performance issue. This is a typical example when MAXDOP should be reduced.
E: The Cost Threshold for Parallelism (CTFP) value is in seconds and it means that for every query for which SQL Server estimates that running time will be longer than 5 seconds, a parallel plan will be created.
To prevent unwanted parallelism, the CTFP number could be increased and by the aforementioned rule of thumb, a minimum value of 25. Recent analysis indicates that 50 should be the optimal minimal number for modern computers.
References: https://www.sqlshack.com/troubleshooting-the-cxpacket-wait-type-in-sql-server/
QUESTION 257
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.
Incorrect Answers:
B: There is no ALTER INDEX ..ENABLE subcommand.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/enable-indexes-and-constraints
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql
QUESTION 258
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 sys.dm_os_wait_stats DMV into a table.
B. Use System Monitor to catch the Lock Waits/sec event.
C. Use sp_configure to set the blocked process threshold. Start a trace using SQL Server Profiler to catch the Blocked Process Report event.
D. Start a Trace using SQL Server Profiler to catch the Lock: Deadlock event.
Answer: D
Explanation:
The Lock:Deadlock event class is produced when an attempt to acquire a lock is canceled because the attempt was part of a deadlock and was chosen as the deadlock victim.
Use the Lock:Deadlock event class to monitor when deadlocks occur and which objects are involved. You can use this information to determine if deadlocks are significantly affecting the performance of your application. You can then examine the application code to determine if you can make changes to minimize deadlocks.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/event-classes/lock-deadlock-event-class
QUESTION 259
You have a SQL Server instance that uses database roles. The instance contains two databases named Customers and Sales.
A user named User1 has the required roles to access Customers.
You need to ensure that User1 can access Sales.
Which statement should you execute first?
A. ALTER ROLE…ADD MEMBER
B. CREATE USER
C. CREATE LOGIN
D. ALTER SERVER ROLE…ADD MEMBER
Answer: A
Explanation:The ALTER ROLE…ADD MEMBER command adds or removes members to or from a database role.
Example: This example creates a database role named Sales. It adds a database user named Barry to the membership.
CREATE ROLE Sales;
ALTER ROLE Sales ADD MEMBER Barry;
References:
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-role-transact-sql
QUESTION 260
You are planning to install a Microsoft SQL Server database server that will be used by a web application.
You need to minimize the attack surface area.
Which two actions should you perform? Each correct answer presents part of the solution.
A. Use a single user account to start all database services.
B. Install only the database engine and disable other features.
C. Install the database server on a server core installation of Windows Server 2008 R2.
D. Set the database service to start automatically.
E. Install the database server as a named instance.
Answer: BC
Explanation:
To reduce the attack surface area and to make sure you are not affected by undiscovered service vulnerabilities, disable any service that is not required Because Server Core has fewer system services running on it than a Full installation does, there’s less attack surface (that is, fewer possible vectors for malicious attacks on the server). This means that a Server Core installation is more secure than a similarly configured Full installation.
References:
https://msdn.microsoft.com/en-us/library/dd184076.aspx
https://msdn.microsoft.com/en-us/library/ff648664.aspx#c18618429_010
QUESTION 261
You install SQL Server on a new server named SQL1 and create several databases.
Users report that they fail to connect to any of the databases on SQL1.
You discover that the TCP connections are disabled for the database engine on SQL1.
You need to ensure that the users can connect to the databases on SQL1.
What should you use?
A. the SQL Server Installation Center
B. the sp_configure stored procedure
C. SQL Server Configuration Manager
D. the ODBC Data Source Administrator
Answer: C
Explanation:
To enable the TCP/IP network protocol
1. Start SQL Server Configuration Manager. Click Start, point to All Programs, and click Microsoft SQL Server. Click Configuration Tools, and then click SQL Server Configuration Manager.
2. In SQL Server Configuration Manager, in the console pane, expand SQL Server Network Configuration.
3. In the console pane, click Protocols for <instance_name>.
4. In the details pane, right-click TCP/IP, and then click Enable.
5. In the console pane, click SQL Server Services.
6. In the details pane, right-click SQL Server (<instance_name>), and then click Restart, to stop and restart the SQL Server service.
References:
https://technet.microsoft.com/en-us/library/hh231672(v=sql.110).aspx
QUESTION 262
You administer a Microsoft SQL Server environment. One of the SQL Server instances contains a database named Contoso.
You plan to migrate Contoso to Microsoft Azure SQL Database.
To do so, you need to implement a contained database.
Which two actions should you perform? Each correct answer presents part of the solution.
A. Set database containment to CONTAINED.
B. Enable database property contained database authentication.
C. Set database containment to PARTIAL.
D. Set database containment to CLOUD.
E. Enable server property contained database authentication.
F. Enable database property encryption enabled.
Answer: BC
Explanation:
Contained database authentication is defined on the database, not on the server.
A database is converted to a contained database by changing the CONTAINMENT option to partial.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/databases/migrate-to-a-partially-contained-database
QUESTION 263
Note: This question is part of a series of questions that use the same set of answer choices. An answer choice may be correct for more than one question in the series.
You administer a SQL Server database server that contains a database named SalesDb. SalesDb contains a schema named Customers that has a table named Regions. A user named UserA is a member of a role named Sales.
UserA is granted the Select permission on the Regions table. The Sales role is granted the Select permission on the Customers schema.
You need to ensure that UserA is disallowed to select from the Regions table.
Which Transact-SQL statement should you use?
A. DENY SELECT ON Object::Regions FROM Sales
B. DENY SELECT ON Schema::Customers FROM Sales
C. REVOKE SELECT ON Object::Regions FROM Sales
D. REVOKE SELECT ON Schema::Customers FROM Sales
E. DENY SELECT ON Object::Regions FROM UserA
F. DENY SELECT ON Schema::Customers FROM UserA
G. REVOKE SELECT ON Object::Regions FROM UserA
H. REVOKE SELECT ON Schema::Customers FROM UserA
I. EXEC sp_addrolemember ‘Sales’, ‘UserA’
J. EXEC sp_droprolemember ‘Sales’, ‘UserA’
Answer: C
QUESTION 264
You are creating an application that will connect to the AgentPortal database by using a SQL login named AgentPortalUser. Stored procedures in the database will use sp_send_dbmail to send email messages.
You create a user account in the msdb database for the AgentPortalUser login. You use the Database Mail Configuration Wizard to create a Database Mail profile. Security has not been configured for the Database Mail profile.
You need to ensure that AgentPortalUser can send email messages.
What should you do?
A. Configure the AgentPortalUser user as a member of SQLAgentOperatorRole in the msdb database.
B. Configure the Microsoft SQL Server Agent to use Database Mail.
C. Enable the guest user in the msdb database.
D. Configure the AgentPortalUser user as a member of DatabaseMailUserRole in the msdb database.
Answer: D
Explanation:
To send Database Mail, you must be a member of the DatabaseMailUserRole database role in the msdb database.
Incorrect Answers:
B: SQL Server Agent can be configured to use Database Mail. This is required for alert notifications and automatic notification when a job completes.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/database-mail/database-mail
QUESTION 265
You administer a Microsoft SQL Server environment. You purchase a new server and plan to migrate your database from an existing server to the new server.
You want to evaluate the new server’s performance based on the workloads of the existing server.
To do so, you need to run the workloads captured from the existing server against the new server from multiple computers.
Which SQL Server tool should you use?
A. Distributed Replay
B. Data Tools
C. Migration Assistant
D. Upgrade Advisor
Answer: A
Explanation:
With Distributed Replay, you can replay a workload from multiple computers and better simulate a mission- critical workload.
References: https://technet.microsoft.com/en-us/library/ff878183(v=sql.110).aspx
!!!RECOMMEND!!!
1.|2018 Latest 70-462 Exam Dumps (PDF & VCE) 313Q&As Download:
https://www.braindump2go.com/70-462.html
2.|2018 Latest 70-462 Exam Questions & Answers Download:
https://drive.google.com/drive/folders/1CzCDfoHpyjc9sRJ9CnfrmejAQqzb8KVQ?usp=sharing
3.|2018 Latest 70-462 Study Guide Video: