Online Access Free 070-762 Practice Test

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some questions sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You need to collect data from the following two sources:
* The performance counters of the operating system
* Microsoft SQL Server events
You must analyze the two datasets side-by side by using a single tool.
Solution: You use dynamic management views and SQL Server Profiler to collect performance data. You use SQL Server Management Studio (SSMS) to analyze the data.
Does this meet the goal?

• A.Yes
• B.No

You have a data warehouse fact table that has a clustered columnstore index.
You have multiple CSV files that contain a total of 3 million rows of data.
You need to upload the data to the fact table. The solution must avoid the delta group when you import the data.
Which solution will achieve the goal in the least amount of time?

• A.Load the source data to the fact table by using the bulk insert statement and specify the Tablock option.
• B.Load the source data to a staging table. Load the data to the fact table by using the insert_select statement and specify the Tablock option on the staging table.
• C.Load the source data to the fact table by running bcp.exe and specify the _ Tablock option.
• D.Load the source data to a staging table that has a clustered index on the primary key. Copy the data to the fact table by using the insert_select statement.

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.Create an Extended Event.
• B.Create a SQL Profiler trace.
• C.Create a sys.dm_exec_sessions query.
• D.Create a PerformanceMonitor Data Collector Set.
• E.Create a sys.dm_os_wait_stats query.
• F.Create a sys.dm_os_memory_objects query.
• G.Create a sp_configure 'max server memory' query.
• H.Create a sys.dm_os_waiting_tasks query.

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 and optimize the SQL Server to maximize throughput, response time, and overall SQL performance.
You need to identify previous situations where a modification has prevented queries from selecting data in tables.
What should you do?

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

Note: This question is part of a series of questions that present the same scenario. Each question in this series contains a unique solution. Determine whether the solution meets the stated goals.
The Account table was created by using the following Transact-SQL statement:

There are more than 1 billion records in the Account table. The Account Number column uniquely identifies each account. The ProductCode column has 100 different values. The values are evenly distributed in the table. Table statistics are refreshed and up to date.
You frequently run the following Transact-SQL SELECT statements:

You must avoid table scans when you run the queries.
You need to create one or more indexes for the table.
Solution: You run the following Transact-SQL statement:

Does the solution meet the goal?

• A.Yes
• B.No