70-762 試験問題を無料オンラインアクセス

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 is independent of the other questions in this series. Information and details provided in a question apply only to that question.

You have a Microsoft SQL Server database named DB1 that contains the following tables:
You frequently run the following queries:

There are no foreign key relationships between TBL1 and TBL2.
You need to minimize the amount of time required for the two queries to return records from the tables.
What should you do?

• A.Drop existing indexes on TBL1 and then create a cluwered columnstore index. Create a nonclustered columnstore index on TBL1. Make no changes to TBL2.
• B.Drop existing indexes on TBL1 and then create a clustered columnstore index. Create a nonclustered columnstore index on TBL1. Create a nonclustered index on TBL2.
• C.Create an indexed view that combines columns from TBL1 and TBL2.
• D.Create a nonclustered index on tbl2 only.
• E.Create a clustered index on TBL1 Create a nonclustered index on tbl2 and add the most frequently queried columns as included columns.
• F.Create clustered indexes on TBL1 and TBL2.
• G.Create unique constraints on both TBL1 and TBL2. Create a partitioned view that combines columns from TBL1 and TBL2.
• H.Create check constraints on both TBL1 and tbl2. Create a partitioned view that combines columns from TBL1 and tbl2.

Case study
Background
You have a database named HR1 that includes a table named Employee.
You have several read-only, historical reports that contain regularly changing totals. The reports use multiple queries to estimate payroll expenses. The queries run concurrently. Users report that the payroll estimate reports do not always run. You must monitor the database to identify issues that prevent the reports from running.
You plan to deploy the application to a database server that supports other applications. You must minimize the amount of storage that the database requires.
Employee Table
You use the following Transact-SQL statements to create, configure, and populate the Employee table:

Application
You have an application that updates the Employees table. The application calls the following stored procedures simultaneously and asynchronously:
* UspA: This stored procedure updates only the EmployeeStatus column.
* UspB: This stored procedure updates only the EmployeePayRate column.
* The application uses views to control access to data. Views must meet the following requirements:
* Allow user access to all columns in the tables that the view accesses.
* Restrict updates to only the rows that the view returns.

You observe that the four indexes require a large amount of disk space. You must reduce the amount of disk space that the indexes are using.
You need to create a single index to replace the existing indexes.
Which three Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.
NOTE: More than one order of answer choices is correct. You will receive credit for any of the correct orders you select.

正解：

Explanation

Scenario: You observe that the four indexes require a large amount of disk space. You must reduce the amount of disk space that the indexes are using.
Current indexes:

You have a relational data warehouse that contains 1 YB of data.
You have a stored procedure named usp-sp1 that generates an HTML fragment. The HTML fragment contains color and font style.
You need to return the HTML fragment.
What should you do?

• A.Use the NOLOCK option
• B.Use the max worker threads option.
• C.Execute the DBCC UPDATEUSAGE statement.
• D.Use the OUTPUT parameters.
• E.Use a table-valued parameter.
• F.Set SET ALLOW_SNAPSHOT_SOLATION to on.
• G.Execute the ALTER TABLE T1 SET (LOCK_ESCALATION + AUTO ): statement.
• H.Set SET XACT_ABORT to on.

You are evaluating the performance of a database environment.
You must avoid unnecessary locks and ensure that lost updates do not occur.
You need to choose the transaction isolation level for each data scenario.
Which isolation level should you use for each scenario? To answer, drag the appropriate isolation levels to the correct scenarios. Each isolation may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

正解：

Explanation

Box 1: Readcommitted
Read Committed: A transaction T1 executing under this isolation level can only access committed data.
Pros: Good compromise between concurrency and consistency.
Cons: Locking and blocking. The data can change when accessed multiple times within the same transaction.
Box 2: Read Uncommitted
Read Uncommitted (aka dirty read): A transaction T1 executing under this isolation level can access data changed by concurrent transaction(s).
Pros: No read locks needed to read data (i.e. no reader/writer blocking). Note, T1 still takes transaction duration locks for any data modified.
Cons: Data is not guaranteed to be transactionally consistent.
Box 3: Serializable
Serializable: A transaction T1 executing under this isolation level provides the highest data consistency including elimination of phantoms but at the cost of reduced concurrency. It prevents phantoms by taking a range lock or table level lock if range lock can't be acquired (i.e. no index on the predicate column) for the duration of the transaction.
Pros: Full data consistency including phantom protection.
Cons: Locking and blocking. The S locks are held for the duration of the transaction that can lower the concurrency.
References:
https://blogs.msdn.microsoft.com/sqlcat/2011/02/20/concurrency-series-basics-of-transaction-isolation-levels/

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 must monitor the SQL Server instances in real time and optimize the server to maximize throughput, response time, and overall SQL performance.
You need to ensure that the performance of each instance is consistent for the same queried and query plans.
What should you do?

• A.Create a sys.dm_os_waiting_tasks query.
• B.Create a sp_configure 'max server memory' query.
• C.Create a Performance Monitor Data Collector Set.
• D.Create asys.dm_os_wait_stats query.
• E.Create an Extended Event.
• F.Create a sys.dm_os_memory_objects query.
• G.Create aSQL Profiler trace.
• H.Create a sys.dm_exec_sessions query.