Download Microsoft.70-764.CertDumps.2020-06-03.312q.tqb
| Vendor: | Microsoft |
| Exam Code: | 70-764 |
| Exam Name: | Administering a SQL Database Infrastructure |
| Date: | Jun 03, 2020 |
| File Size: | 9 MB |
Demo Questions
Question 1
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 support an application that stores data in a Microsoft SQL Server database. You have a query that returns data for a report that users run frequently.
The query optimizer sometimes generates a poorly-performing plan for the query when certain parameters are used. You observe that this is due to the distribution of data within a specific table that the query uses.
You need to ensure that the query optimizer always uses the query plan that you prefer.
Solution: You add the KEEPFIXED PLAN query hint to the query.
Does the solution meet the goal?
- Yes
- No
Correct answer: A
Explanation:
PLAN forces the query optimizer not to recompile a query due to changes in statistics. Specifying KEEPFIXED PLAN makes sure that a query will be recompiled only if the schema of the underlying tables is changed or if sp_recompile is executed against those tables. References: https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-2017 PLAN forces the query optimizer not to recompile a query due to changes in statistics. Specifying KEEPFIXED PLAN makes sure that a query will be recompiled only if the schema of the underlying tables is changed or if sp_recompile is executed against those tables.
References: https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-2017
Question 2
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 sales data. One fact table has 100 million rows.
You must reduce storage needs for the data warehouse.
You need to implement a solution that uses column-based storage and provides real-time analytics for the operational workload.
Solution: You remove any clustered indexes and load the table for processing.
Does the solution meet the goal?
- Yes
- No
Correct answer: B
Explanation:
You should use a column-store index References:https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-tables-overview You should use a column-store index
References:
https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-tables-overview
Question 3
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 server named Server1 that has Microsoft SQL Server installed.
Server1 has SQL Server Audit configured to send audit event records to a file.
You need to ensure that a database user named User1 can review the audit data.
Solution: You grant the VIEW ANY DEFINITION permission to User1.
Does this meet the goal?
- Yes
- No
Correct answer: B
Explanation:
Each feature and command for SQL Server Audit has individual permission requirements. Unless otherwise specified, viewing catalog views requires a principal to have one of the following:The VIEW SERVER STATE permission. The VIEW AUDIT STATE permission (gives only the principal access to the sys.server_audits catalog view). Membership in the sysadmin fixed server role. The CONTROL SERVER permission. The ALTER ANY AUDIT permission. A principal must have the VIEW SERVER STATE or ALTER ANY AUDIT permission to use the Dynamic Management Views. References:https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/create-a-server-audit-and-database-audit-specification?view=sql-server-ver15https://docs.microsoft.com/en-us/sql/t-sql/statements/create-server-audit-transact-sql?view=sql-server-ver15 Each feature and command for SQL Server Audit has individual permission requirements.
Unless otherwise specified, viewing catalog views requires a principal to have one of the following:
- The VIEW SERVER STATE permission.
- The VIEW AUDIT STATE permission (gives only the principal access to the sys.server_audits catalog view).
- Membership in the sysadmin fixed server role.
- The CONTROL SERVER permission.
- The ALTER ANY AUDIT permission.
A principal must have the VIEW SERVER STATE or ALTER ANY AUDIT permission to use the Dynamic Management Views.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/create-a-server-audit-and-database-audit-specification?view=sql-server-ver15
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-server-audit-transact-sql?view=sql-server-ver15
