Want to Pass 70-762 Exam In Next HOURS? Get it now →
April 6, 2018

What Update 70-762 Is?

Cause all that matters here is passing the Microsoft 70-762 exam. Cause all that you need is a high score of 70-762 Developing SQL Databases (beta) exam. The only one thing you need to do is downloading Actualtests 70-762 exam study guides now. We will not let you down with our money-back guarantee.

Q11. You are experiencing performance issues with the database server.

You need to evaluate schema locking issues, plan cache memory pressure points, and backup I/O problems.

What should you create?

A. a System Monitor report

B. a sys.dm_tran_database_transaction dynamic management view query

C. an Extended Events session that uses Query Editor

D. an Activity Monitor session in Microsoft SQL Management Studio.

Answer: D

Explanation:

References: https://msdn.microsoft.com/en-us/library/hh212951.aspx


Q12. Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets the stated goals.

You have a database that contains a table named Employees. The table stores information about the employees of your company.

You need to implement and enforce the following business rules:

Solution: You implement cascading referential integrity constraints on the table. Does the solution meet the goal?

A. Yes

B. No

Answer: A

Explanation:

References: https://technet.microsoft.com/en-us/library/ms186973(v=sql.105).aspx


Q13. Note: This question is part of a series of questions that use the same or similar answer choices. An Answer choice may be correct for more than one question in the series. Each question independent of the other questions in this series. Information and details provided in a question apply only to that question.

You are a database developer for a company. The company has a server that has multiple physical disks. The disks are not part of a RAID array. The server hosts three Microsoft SQL Server instances. There are many SQL jobs that run during off-peak hours.

You observe that many deadlocks appear to be happening during specific times of the day. You need to monitor the SQL environment and capture the information about the

processes that are causing the deadlocks. What should you do?

A. A. Create a sys.dm_os_waiting_tasks query.

B. Create a sys.dm_exec_sessions query.

C. Create a PerformanceMonitor Data Collector Set.

D. Create a sys.dm_os_memory_objects query.

E. Create a sp_configure ‘max server memory’ query.

F. Create a SQL Profiler trace.

G. Create a sys.dm_os_wait_stats query.

H. Create an Extended Event.

Answer: F

Explanation:

Toview deadlock information, the Database Engine provides monitoring tools in the form of two trace flags, and the deadlock graph event in SQL Server Profiler.

Trace Flag 1204 and Trace Flag 1222

When deadlocks occur, trace flag 1204 and trace flag 1222 return information that is captured in the SQL Server error log. Trace flag 1204 reports deadlock information formatted by each nodeinvolved in the deadlock. Trace flag 1222 formats deadlock information, first by processesand then by resources. It is possible to enable both trace flags to obtain two representations of the same deadlock event.

References:https://technet.microsoft.com/en-us/library/ms178104(v=sql.105).aspx


Q14. HOTSPOT

You create a database table named FactSales by running the following Transact-SQL statements:

You must optimize the indexes without making changes to the ix_FactSales_EmployeeKey index.

You need to implement a columnstore index for the table.

How should you complete the Transact-SQL code? To answer, select the appropriate Transact-SQL segments in the answer area.

NOTE: Each correct selection is worth one point.

Answer:


Q15. Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.

You have a database named DB1 that contains the following tables: Customer, CustomerToAccountBridge, and CustomerDetails. The three tables are part of the Sales schema. The database also contains a schema named Website. You create the Customer table by running the following Transact-SQL statement:

The value of the CustomerStatus column is equal to one for active customers. The value of the Account1Status and Account2Status columns are equal to one for active accounts. The following table displays selected columns and rows from the Customer table.

You plan to create a view named Website.Customer and a view named Sales.FemaleCustomers.

Website.Customer must meet the following requirements:

1. Allow users access to the CustomerName and CustomerNumber columns for active customers.

2. Allow changes to the columns that the view references. Modified data must be visible through the view.

3. Prevent the view from being published as part of Microsoft SQL Server replication. Sales.Female.Customers must meet the following requirements:

1. Allow users access to the CustomerName, Address, City, State and PostalCode columns.

2. Prevent changes to the columns that the view references.

3. Only allow updates through the views that adhere to the view filter.

You have the following stored procedures: spDeleteCustAcctRelationship and spUpdateCustomerSummary. The spUpdateCustomerSummary stored procedure was created by running the following Transacr-SQL statement:

You run the uspUpdateCustomerSummary stored procedure to make changes to customer account summaries. Other stored procedures call the spDeleteCustAcctRelationship to delete records from the CustomerToAccountBridge table.

When you start uspUpdateCustomerSummary, there are no active transactions. The procedure fails at the second update statement due to a CHECK constraint violation on the TotalDepositAccountCount column.

What is the impact of the stored procedure on the CustomerDetails table?

A. The value of the TotalAccountCount column decreased.

B. The value of the TotalDepositAccountCount column is decreased.

C. The statement that modifies TotalDepositAccountCount is excluded from the transaction.

D. The value of the TotalAccountCount column is not changed.

Answer: D


Q16. Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.

You have a database named DB1 that contains the following tables: Customer, CustomerToAccountBridge, and CustomerDetails. The three tables are part of the Sales schema. The database also contains a schema named Website. You create the Customer table by running the following Transact-SQL statement:

The value of the CustomerStatus column is equal to one for active customers. The value of

the Account1Status and Account2Status columns are equal to one for active accounts. The following table displays selected columns and rows from the Customer table.

You plan to create a view named Website.Customer and a view named Sales.FemaleCustomers.

Website.Customer must meet the following requirements:

1. Allow users access to the CustomerName and CustomerNumber columns for active customers.

2. Allow changes to the columns that the view references. Modified data must be visible through the view.

3. Prevent the view from being published as part of Microsoft SQL Server replication. Sales.Female.Customers must meet the following requirements:

1. Allow users access to the CustomerName, Address, City, State and PostalCode columns.

2. Prevent changes to the columns that the view references.

3. Only allow updates through the views that adhere to the view filter.

You have the following stored procedures: spDeleteCustAcctRelationship and spUpdateCustomerSummary. The spUpdateCustomerSummary stored procedure was created by running the following Transacr-SQL statement:

You run the spUpdateCustomerSummary stored procedure to make changes to customer

account summaries. Other stored procedures call the spDeleteCustAcctRelationship to delete records from the CustomerToAccountBridge table.

You must update the design of the Customer table to meet the following requirements.

1. You must be able to store up to 50 accounts for each customer.

2. Users must be able to retrieve customer information by supplying an account number.

3. Users must be able to retrieve an account number by supplying customer information. You need to implement the design changes while minimizing data redundancy.

What should you do?

A. Splitthe table into three separate tables. Include the AccountNumber and CustomerID columns in the first table. Include the CustomerName and Gender columns in the second table. Include the AccountStatus column in the third table.

B. Split the table into two separate tables. Include AccountNumber, CustomerID, CustomerName and Gender columns in the first table. Include the AccountNumber and AccountStatus columns in the second table.

C. Split the table into two separate tables, Include the CustomerID and AccountNumber columns in the first table. Include the AccountNumber, AccountStatus, CustomerName and Gender columns in the second table.

D. Split the table into two separate tables, Include the CustomerID, CustomerName and Gender columns in the first table. IncludeAccountNumber, AccountStatus and CustomerID columns in the second table.

Answer: D

Explanation:

Two tables is enough.CustomerID must be in both tables.


Q17. DRAG DROP

You are monitoring a Microsoft Azure SQL Database. The database is experiencing high CPU consumption.

You need to determine which query uses the most cumulative CPU.

How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment may be used once, more than one or not at all. You may need to drag the split bar between panes or scroll to view content.

Answer:

Explanation:

Box 1: sys.dm_exec_query_stats

sys.dm_exec_query_stats returns aggregateperformance statistics for cached query plans in SQL Server.

Box 2: highest_cpu_queries.total_worker_time DESC Sort ontotal_worker_time column

Example: The following example returns information about the top five queries ranked by average CPU time.

Thisexample aggregates the queries according to their query hash so that logically equivalentqueries are grouped by their cumulative resource consumption.

USE AdventureWorks2012; GO

SELECT TOP 5 query_stats.query_hash AS "Query Hash", SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",

MIN(query_stats.statement_text) AS "Statement Text" FROM

(SELECT QS.*,

SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,

((CASE statement_end_offset

WHEN -1 THEN DATALENGTH(ST.text)

ELSE QS.statement_end_offset END

- QS.statement_start_offset)/2) + 1) AS statement_text FROM sys.dm_exec_query_stats AS QS

CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle)as ST) as query_stats GROUP BY query_stats.query_hash

ORDER BY 2 DESC;

References: https://msdn.microsoft.com/en-us/library/ms189741.aspx


Q18. You are developing an application that connects to a database.

The application runs the following jobs:

The READ_COMMITTED_SNAPSHOT database option is set to OFF, and auto-content is set to ON. Within the stored procedures, no explicit transactions are defined.

If JobB starts before JobA, it can finish in seconds. If JobA starts first, JobB takes a long time to complete.

You need to use Microsoft SQL Server Profiler to determine whether the blocking that you observe in JobB is caused by locks acquired by JobA.

Which trace event class in the Locks event category should you use?

A. LockAcquired

B. LockCancel

C. LockDeadlock

D. LockEscalation

Answer: A

Explanation:

The Lock:Acquiredevent class indicates that acquisition of a lock on a resource, such asa data page, has been achieved.

The Lock:Acquired and Lock:Released event classes can be used to monitor when objects are being locked, the typeof locks taken, and for how long the locks were retained. Locks retained for long periods of time may cause contention issues and should be investigated.


Q19. You have a database that is experiencing deadlock issues when users run queries. You need to ensure that all deadlocks are recorded in XML format.

What should you do?

A. Create a Microsoft SQL Server Integration Services package that uses sys.dm_tran_locks.

B. Enable trace flag 1224 by using the Database Cpmsistency Checker(BDCC).

C. Enable trace flag 1222 in thestartup options for Microsoft SQL Server.

D. Use the Microsoft SQL Server Profiler Lock:Deadlock event class.

Answer: C

Explanation:

When deadlocks occur, trace flag 1204 and trace flag 1222 return information that is capturedin the SQL Server error log.Trace flag 1204 reports deadlock information formatted by each node involved in the deadlock. Trace flag 1222 formats deadlock information, first by processes and then by resources.

The output format for Trace Flag 1222 only returns information in an XML-like format. References:https://technet.microsoft.com/en-us/library/ms178104(v=sql.105).aspx


Q20. HOTSPOT

You are maintaining statistics for a database table named tblTransaction. The table contains more than 10 million records.

You need to create a stored procedure that meets the following requirements:

- On weekdays, update statistics for a sample of the total number of records in the table.

- On weekends, update statistics by sampling all rows in the table. A maintenance task will call this stored procedure daily.

How should you complete the stored procedure? To answer, select the appropriate Transact-SQL segments in the answer area.

NOTE: Each correct selection is worth one point.

Answer:

Explanation:

Box 1: UPDATE STATISTICS Box 2: SAMPLE 20 PERCENT

UPDATE STATISTICS tablenameSAMPLE number { PERCENT | ROWS }

Specifies the approximate percentage or number of rows in the table or indexed view for the query optimizer to use when it updates statistics. For PERCENT, number can be from 0 through 100 and for ROWS, number can be from0 to the total number of rows.

Box 3: UPDATE STATISTICS Box 4: WITH FULLSCAN

FULLSCAN computes statistics by scanning all rows in the table or indexed view. FULLSCAN and SAMPLE 100 PERCENT have the same results. FULLSCAN cannot be used with the SAMPLE option.

References:https://msdn.microsoft.com/en-us/library/ms187348.aspx



see more free 70-762 exam dumps