[Free] 2019(Nov) EnsurePass Microsoft 70-765 Dumps with VCE and PDF 51-60

Get Full Version of the Exam

Question No.51

You are the administrator of a Microsoft SQL Server 2014 server.

Some applications consume significant resources. You need to manage the server workload by restricting resource-intensive applications

You need to dynamically limit resource consumption. What should you do?

  1. Configure Resource Pools, Workload Groups, and Classifier Function, and then enable the Resource Governor

  2. Set up Service Broker to ensure that application are not allowed to consume more than the specified amount of resource

  3. Create a new rule for each application that sets the resource limit allowed

  4. Create a new plan Guide with a Scope Type of sql and define the resource limits for each application

Correct Answer: A


In the SQL Server Resource Governor, a resource pool represents a subset of the physical resources of an instance of the Database Engine. Resource Governor enables you to specify limits on the amount of CPU, physical IO, and memory that incoming application requests can use within the resource pool. Each resource pool can contain one or more workload groups. When a session is started, the Resource Governor classifier assigns the session to a specific workload group, and the session must run using the resources assigned to the workload group.


https://docs.microsoft.com/en-us/sql/relational-databases/resource-governor/resource-governor- resource-pool

Question No.52

You administer a Windows Azure SQL Database database named Inventory that contains a stored procedure named p_AddInventory.

Users need to be able to SELECT from all tables in the database and execute the stored procedure.

You need to grant only the necessary permissions. What should you do?

  1. Grant EXECUTE permission on p_AddInventory to all users. Grant VIEW DEFINITION to all users.

  2. Grant EXECUTE permission on p_AddInventory to all users. Add all users to the db_datawriter role.

  3. Add all users to the db_owner role.

  4. Grant EXECUTE permission on p_AddInventory to all users. Add all users to the db_datareader role.

Correct Answer: D


Members of the db_datareader fixed database role can run a SELECT statement against any table or view in the database.



Question No.53

You administer a Microsoft SQL Server 2014 failover cluster.

You need to ensure that a failover occurs when the server diagnostics returns query_processing error.

Which server configuration property should you set?

  1. SqlOumperDumpFlags

  2. FailureConditionLevel

  3. HealthCheckTimeout

  4. SqlDumperDumpPath

Correct Answer: B


Use the FailureConditionLevel property to set the conditions for the Always On Failover Cluster Instance (FCI) to fail over or restart.

The failure conditions are set on an increasing scale. For levels 1-5, each level includes all the conditions from the previous levels in addition to its own conditions.


The system stored procedure sp_server_diagnostics periodically collects component diagnostics on the SQL instance. The diagnostic information that is collected is surfaced as a row for each of the following components and passed to the calling thread. The system, resource, and query process components are used for failure detection. The io_subsytem and events components are used for diagnostic purposes only.


https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/configure- failureconditionlevel-property-settings

Question No.54

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?


  2. SELECT COUNT(*)FROM PurchasesWHERE PurchaseTime = GETDATE()

  3. SELECT COUNT(*)FROM PurchasesWHERE CONVERT(VARCHAR, PurchaseTime, 112)



Correct Answer: D


To compare a time with date we must use gt;= and gt; operators, and not the = operator.

Question No.55

You administer a Microsoft SQL Server 2014 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?

  1. STATS



  4. IN IT

Correct Answer: A


STATS is a monitoring option of the BACKUP command.

STATS [ =percentage ]

Displays a message each time another percentage completes, and is used to gauge progress. If percentage is omitted, SQL Server displays a message after each 10 percent is completed.

The STATS option reports the percentage complete as of the threshold for reporting the next interval. This is at approximately the specified percentage; for example, with STATS=10, if the amount completed is 40 percent, the option might display 43 percent. For large backup sets, this is not a problem, because the percentage complete moves very slowly between completed I/O calls.



Question No.56

You administer a Microsoft SQL Server 2014 database.

You need to ensure that the size of the transaction log file does not exceed 2 GB. What should you do?

  1. Execute sp_configure #39;max log size#39;, 2G.

  2. use the ALTER DATABASE…SET LOGFILE command along with the maxsize parameter.

  3. In SQL Server Management Studio, right-click the instance and select Database Settings. Set the maximum size of the file for the transaction log.

  4. in SQL Server Management Studio, right-click the database, select Properties, and then click Files. Open the Transaction log Autogrowth window and set the maximum size of the file.

Correct Answer: B


You can use the ALTER DATABASE (Transact-SQL) statement to manage the growth of a transaction log file

To control the maximum the size of a log file in KB, MB, GB, and TB units or to set growth to UNLIMITED, use the MAXSIZE option. However, there is no SET LOGFILE subcommand.



Question No.57

You administer a Microsoft SQL Server 2014 database that contains a table named AccountTransaction.

You discover that query performance on the table is poor due to fragmentation on the IDX_AccountTransaction_AccountCode non-clustered index. You need to defragment the index. You also need to ensure that user queries are able to use the index during the defragmenting process.

Which Transact-SQL batch should you use?

  1. ALTER INDEX IDX_AccountTransaction_AccountCode ONAccountTransaction.AccountCode


  2. ALTER INDEX ALL ON AccountTransaction REBUILD

  3. ALTER INDEX IDX_AccountTransaction_AccountCode ONAccountTransaction.AccountCode REBUILD

  4. CREATE INDEX IDXAccountTransactionAccountCode ONAccountTransaction.AccountCode WITH DROP EXISTING

Correct Answer: A


Reorganize: This option is more lightweight compared to rebuild. It runs through the leaf level of the index, and as it goes it fixes physical ordering of pages and also compacts pages to apply any previously set fillfactor settings. This operation is always online, and if you cancel it then it#39;s able to just stop where it is (it doesn#39;t have a giant operation to rollback).



Question No.58

You administer a Microsoft SQL Server 2014 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 identify which queries are taking longer than 1 second to run over an extended period of time.

What should you do?

  1. 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.

  2. Use sp_configure to set a value for blocked process threshold. Create an extended event session.

  3. 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.

  4. Run the sp_who command from a query window.

  5. Run the DBCC TRACEON 1222 command from a query window and review the SQL Server event log.

Correct Answer: A

Question No.59

You administer a Microsoft SQL Server 2014 failover cluster that contains two nodes named Node A and Node B. A single instance of SQL Server is installed on the cluster.

An additional node named Node C has been added to the existing cluster.

You need to ensure that the SQL Server instance can use all nodes of the cluster. What should you do?

  1. Run the New SQL Server stand-alone installation Wizard on Node C.

  2. Run the Add Node to SQL Server Failover Cluster Wizard on Node C.

  3. Use Node B to install SQL Server on Node C.

  4. Use Node A to install SQL Server on Node C.

Correct Answer: B


To add a node to an existing SQL Server failover cluster, you must run SQL Server Setup on the node that is to be added to the SQL Server failover cluster instance. Do not run Setup on the active node.

The Installation Wizard will launch the SQL Server Installation Center. To add a node to an existing failover cluster instance, click Installation in the left-hand pane. Then, select Add node to a SQL Server failover cluster.



Question No.60

You administer a Microsoft SQL Server 2014 database that contains a table named OrderDetail.

You discover that the NCI_OrderDetail_CustomerID non-clustered index is fragmented. You need to reduce fragmentation. You need to achieve this goal without taking the index offline.

Which Transact-SQL batch should you use?

  1. CREATE INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID WITH DROP EXISTING

  2. ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REORGANIZE


  4. ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REBUILD

Correct Answer: B


REORGANIZE specifies to reorganize the index leaf level. The REORGANIZE operation is always performed online. This means long-term blocking table locks are not held and queries or updates to the underlying table can continue during the ALTER INDEX REORGANIZE transaction.



Get Full Version of the Exam
70-765 Dumps
70-765 VCE and PDF