Physical Address
304 North Cardinal St.
Dorchester Center, MA 02124
Optimize query performance in Azure SQL Microsoft Quiz Answers
Get Optimize query performance in Azure SQL Microsoft Quiz Answers
Analyze individual query performance and determine where improvements can be made. Explore performance-related Dynamic Management Objects. Investigate how indexes and database design affect queries.
This learning path helps prepare you for Exam DP-300: Administering Microsoft Azure SQL Solutions.
Prerequisites:
- Ability to use tools for running queries against a Microsoft SQL database, either on-premises on cloud-based
- Ability to write code in the SQL language, particularly the Microsoft T-SQL dialect, at a basic level.
- Basic understanding of structure and usage of SQL Server indexes
- Basic understanding of relational database concepts
Module 1: Explore query performance optimization
Read and understand various forms of execution plans. Compare estimated vs actual plans. Learn how and why plans are generated. Understand the purpose and benefits of the Query Store.
Learning objectives:
After completing this module, you will be able to:
- Generate and save execution plans
- Compare the different types of execution plans
- Understand how and why query plans are generated
- Explain the purpose and benefits of the Query Store
- Investigate the available reports and data in the Query Store
Prerequisites:
- Ability to use tools for running queries against a Microsoft SQL database, either on-premises on cloud-based.
- Ability to write code in the SQL language, particularly the Microsoft T-SQL dialect, at a basic level.
- Basic understanding of structure and usage of SQL Server indexes.
- Basic understanding of relational database concepts.
This module is part of these learning paths:
Quiz 1: Knowledge check
Q1. Which type of execution plan is stored in the plan cache?
- Estimated execution plan
- Actual execution plan
- Live Query Stats
Q2. Which DMV should you use to find index utilization?
- sys.dm_db_index_usage_stats
- sys.dm_db_missing_index_details
- sys.dm_exec_query_plan_stats
Q3. Which of the following wait types would indicate excessive CPU consumption?
- SOS_SCHEDULER_YIELD
- RESOURCE_SEMAPHORE
- PAGEIOLATCH_SH
Q4. Which isolation level should you choose if you want to prevent users reading data from blocking users writing data?
- Serializable
- Read Committed Snapshot Isolation
- Repeatable Read
Module 2: Explore performance-based design
Explore normalization for relational databases. Investigate the impact of proper datatype usage. Compare types of indexes.
Learning objectives:
After completing this module, you will be able to:
- Explore normal forms and how they affect database design
- Choose appropriate datatypes for your data
- Evaluate appropriate index types
Prerequisites:
- Ability to use tools for running queries against a Microsoft SQL database, either on-premises on cloud-based.
- Ability to write code in the SQL language, particularly the Microsoft T-SQL dialect, at a basic level.
- Basic understanding of structure and usage of SQL Server indexes.
- Basic understanding of relational database concepts.
This module is part of these learning paths:
Quiz 1: Knowledge check
Q1. What type of database design should you use for a data warehouse when you want to reduce the data volume of your dimensions?
- Snowflake schema
- Star Schema
- Third normal form
Q2. What is the minimum number of rows you need to bulk insert into a columnstore index?
- 102,400
- 1,000,000
- 1000
Q3. Which index type is recommended for queries that run large aggregation workloads?
- Columnstore index
- Clustered index
- Nonclustered index
Module 3: Evaluate performance improvements
Evaluate possible changes to indexes. Determine the impact of changes to queries and indexes. Explore Query Store hints.
Learning objectives:
After completing this module, you will be able to:
- Determine when changing indexes or defining new ones can affect performance
- Evaluate wait statistics as an aid in finding areas for performance improvement
- Understand how query hints work, and when to use them
Prerequisites:
- Ability to use tools for running queries against a Microsoft SQL database, either on-premises on cloud-based.
- Ability to write code in the SQL language, particularly the Microsoft T-SQL dialect, at a basic level.
- Basic understanding of structure and usage of SQL Server indexes.
- Basic understanding of relational database concepts.
This module is part of these learning paths:
Quiz 1: Knowledge check
Q1. What type of index is best used on a data warehouse fact table?
- Clustered Columnstore
- Nonclustered Columnstore
- Clustered b-tree
Q2. Which DMV provides information about server level wait statistics?
- sys.dm_db_index_physical_stats
- sys.dm_os_wait_stats
- sys.dm_exec_session_wait_stats
Q3. Which DMV can you use to capture the last Actual Execution Plan for a given query?
- sys.dm_exec_cached_plans
- sys.dm_exec_query_plan
- sys.dm_exec_query_plan_stats
Conclusion:
I hope this Optimize query performance in Azure SQL Microsoft Quiz Answers would be useful for you to learn something new from this problem. If it helped you then don’t forget to bookmark our site for more Coding Solutions.
This Problem is intended for audiences of all experiences who are interested in learning about Data Science in a business context; there are no prerequisites.
Keep Learning!
