Implementing a SQL Data Warehouse

Here you have the best Microsoft 70-767 practice exam questions

  • You have 81 total questions to study from
  • Each page has 5 questions, making a total of 17 pages
  • You can navigate through the pages using the buttons at the bottom
  • This questions were last updated on February 17, 2025
Question 1 of 81

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 question 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 have a Microsoft Azure SQL Data Warehouse instance that must be available six months a day for reporting.

You need to pause the compute resources when the instance is not being used.

Solution: You use SQL Server Configuration Manager.

Does the solution meet the goal?

    Correct Answer: B

    To manage compute resources for an Azure SQL Data Warehouse, such as pausing them when not in use, you need to use specific management tools provided by Azure, such as the Azure portal, PowerShell, or REST APIs. SQL Server Configuration Manager is not an appropriate tool for this task as it is designed for managing SQL Server services and network configuration on Windows, not for managing Azure SQL Data Warehouse instances. Therefore, the solution of using SQL Server Configuration Manager does not meet the goal.

Question 2 of 81

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 question 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 have a Microsoft Azure SQL Data Warehouse instance that must be available six months a day for reporting.

You need to pause the compute resources when the instance is not being used.

Solution: You use the Azure portal.

Does the solution meet the goal?

    Correct Answer: A

    A

    To pause a SQL Data Warehouse database, use any of these individual methods.

    Pause compute with Azure portal -

    Pause compute with PowerShell -

    Pause compute with REST APIs -

    Note: To pause a database:

    1. Open the Azure portal and open your database. Notice that the Status is Online.

    2. To suspend compute and memory resources, click Pause, and then a confirmation message appears. Click yes to confirm or no to cancel.

    References:

    https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-manage-compute-overview https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-manage-compute-portal#pause-compute-bk

Question 3 of 81

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 question 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 have a data warehouse that stores information about products, sales, and orders for a manufacturing company. The instance contains a database that has two tables named SalesOrderHeader and SalesOrderDetail. SalesOrderHeader has 500,000 rows and SalesOrderDetail has 3,000,000 rows.

Users report performance degradation when they run the following stored procedure:

You need to optimize performance.

Solution: You run the following Transact-SQL statement:

Does the solution meet the goal?

    Correct Answer: B

    Creating statistics with a sample size of 100 rows from a table containing 500,000 rows is inadequate for the query optimizer to generate an effective execution plan. The sample size is too small to accurately represent the data distribution within the table. For better performance, a larger sample size or full statistics should be created, or other optimizations such as indexing might be necessary to improve the query performance.

Question 4 of 81

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 question 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 have a data warehouse that stores information about products, sales, and orders for a manufacturing company. The instance contains a database that has two tables named SalesOrderHeader and SalesOrderDetail. SalesOrderHeader has 500,000 rows and SalesOrderDetail has 3,000,000 rows.

Users report performance degradation when they run the following stored procedure:

You need to optimize performance.

Solution: You run the following Transact-SQL statement:

Does the solution meet the goal?

    Correct Answer: B

    The proposed solution of creating statistics with a sample size of 0 percent will not optimize performance. When 0 percent is specified, the statistics object is created but does not contain any actual statistics data, which would not help in optimizing the query performance. Proper statistics are needed to help the query optimizer make informed decisions. Therefore, this solution does not meet the goal of optimizing performance.

Question 5 of 81

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 question 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 have a data warehouse that stores information about products, sales, and orders for a manufacturing company. The instance contains a database that has two tables named SalesOrderHeader and SalesOrderDetail. SalesOrderHeader has 500,000 rows and SalesOrderDetail has 3,000,000 rows.

Users report performance degradation when they run the following stored procedure:

You need to optimize performance.

Solution: You run the following Transact-SQL statement:

Does the solution meet the goal?

    Correct Answer: B

    Creating statistics with a sample size of 5 percent on a table with less than 1 billion rows is not sufficient according to best practices. The default sampling size for tables with less than 1 billion rows should be at least 20 percent to provide more accurate statistics for the query optimizer, thereby improving performance. Since the given solution uses only a 5 percent sample size, it does not meet the goal of optimizing performance effectively for the stored procedure.