Microsoft Exam 70-762 Developing SQL Databases
Skills measured
Design and implement database objects
Design and implement a relational database schema
Design tables and schemas based on business requirements
Lesson 1: Systematically Approaching Design Stages
Improve the design of tables by using normalization
Lesson 2: Designing a Normalized Database
Write table create statements
NULL vs NOT NULL
Understand use of schemas
Temporal Tables
Create Table (non-azure)
Create Table (azure)
Determine the most efficient data types to use
Understand differences between exact and approximate numeric fields
Data Types (Transact-SQL)
Understand the differences between character string and Unicode character strings
Data Types (Transact-SQL)
Understand hierarchyid data type
Understand rowversion data type
Understand table data type
Understand xml data type
Understand uniqueidentifier data type
Design and implement indexes
Clustered and Nonclustered Indexes described
Design new indexes based on provided tables, queries, or plans
SQL Server Index Design Guide
Filtered indexes
Distinguish between indexed columns and included columns
Create Indexes with Included Columns
Implement clustered index columns by using best practices
Create Clustered Indexes
Recommend new indexes based on query plans
Design and implement views
Views
Design a view structure to select data based on user or business requirements
CREATE VIEW (Transact-SQL)
Identify the steps necessary to design an updateable view
Updatable Views
Implement partitioned views
Using Partitioned Views
Implement indexed views
Create Indexed Views
Implement columnstore indexes
Columnstore indexes – Design Guidance
Determine use cases that support the use of columnstore indexes
Columnstore Indexes Guide
Very high level of compression which reduces storage costs
Improved performance
Use a clustered columnstore index to store fact tables and large dimension tables for data warehousing workloads
Use a nonclustered columnstore index to perform analysis in real-time on an OLTP workload
Columnstore Index: Differences between Clustered/Nonclustered Columnstore Index
Identify proper usage of clustered and non-clustered columnstore indexes
Columnstore Indexes for Data Warehousing
Design standard non-clustered indexes in conjunction with clustered columnstore indexes
Columnstore Indexes for Data Warehousing
Implement columnstore index maintenance
SQL 2016: Columnstore row group Merge policy and index maintenance improvements
Implement programmability objects
Ensure data integrity with constraints
Unique Constraints and Check Constraints
Define table and foreign key constraints to enforce business rules
Constraints
Allowing Null Values
CHECK Constraints
UNIQUE Constraints
PRIMARY KEY Constraints
FOREIGN KEY Constraints
Write Transact-SQL statements to add constraints to tables
column_constraint (Transact-SQL)
table_constraint (Transact-SQL)
Identify results of Data Manipulation Language (DML) statements given existing tables and constraints
Data Manipulation Language (DML) Statements (Transact-SQL)
Identify proper usage of PRIMARY KEY constraints
Primary Key Constraints
Create stored procedures
Create Views and Stored Procedures
Design stored procedure components and structure based on business requirements
CREATE PROCEDURE (Transact-SQL)
Implement input and output parameters
Passing Parameters
Implement table-valued parameters, implement return codes
Use Table-Valued Parameters (Database Engine)
Implement return codes
RETURN (Transact-SQL)
Streamline existing stored procedure logic
Implement error handling and transaction control logic within stored procedures
TRY…CATCH (Transact-SQL)
@ERROR (Transact-SQL). Not recommended for new projects
BEGIN TRANSACTION (Transact-SQL)
COMMIT TRANSACTION (Transact-SQL)
ROLLBACK TRANSACTION (Transact-SQL)
SAVE TRANSACTION (Transact-SQL)
Create triggers and user-defined functions
DDL Triggers
Design trigger logic based on business requirements
CREATE TRIGGER (Transact-SQL)
Determine when to use Data Manipulation Language (DML) triggers, data definition (DDL) triggers or logon triggers
DDL Triggers
DML Triggers
Logon Triggers
Recognize results based on execution of AFTER or INSTEAD OF triggers
Types of DML Triggers
Design scalar-valued and table-valued user-defined functions based on business requirements
Create User-defined Functions (Database Engine)
Identify differences between deterministic and non-deterministic functions
Deterministic and Nondeterministic Functions
Manage database concurrency
Implement transactions
Identify DML statement results based on transaction behavior
Recognize differences between and identify usage of explicit and implicit transactions
Transaction Statements (Transact-SQL)
Implement savepoints within transactions
SAVE TRANSACTION (Transact-SQL)
Determine the role of transactions in high-concurrency databases
SQL Server Transaction Locking and Row Versioning Guide
Manage isolation levels
Identify differences between Read Uncommitted, Read Committed, Repeatable Read, Serializable, and Snapshot isolation levels
SET TRANSACTION ISOLATION LEVEL (Transact-SQL)
Define results of concurrent queries based on isolation level
SQL Server Isolation Levels By Example
Identify the resource and performance impact of given isolation levels
READ UNCOMMITTED – Fast as locking contention is minimised. Potential to read uncommitted transactions, dirty reads, that may be rolled back.
READ COMMITTED – Can be blocked, and can block other transactions, hence can be slower than READ UNCOMMITTED. Dirty Reads are prevented, and so the data integrity is improved.
REPEATABLE READ – As READ COMMITTED but more restrictive in that it prevents rows read from being updated until the current transaction is committed, so likely to increase blocking.
SNAPSHOT – Specifies that data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction.
SERIALIZABLE – As REPEATABLE READ but more restrictive in that it prevents rows read from being inserted that cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes, so likely to increase blocking.
Optimize concurrency and locking behavior
Troubleshoot locking issues
SQL Server Transaction Locking and Row Versioning Guide
Identify lock escalation behaviors
Lock Escalation
Capture and analyze deadlock graphs
Analyze Deadlocks with SQL Server Profiler
Identify ways to remediate deadlocks
Minimizing Deadlocks
Implement memory-optimized tables and native stored procedures
Introduction to Memory-Optimized Tables
Define use cases for memory-optimized tables versus traditional disk-based tables
In-Memory OLTP (In-Memory Optimization)
Optimize performance of in-memory tables by changing durability settings
Introduction to Memory-Optimized Tables
Determine best case usage scenarios for natively compiled stored procedures
Best Practices for Calling Natively Compiled Stored Procedures
Enable collection of execution statistics for natively compiled stored procedures.
Monitoring Performance of Natively Compiled Stored Procedures
Optimize database objects and SQL infrastructure
Optimize statistics and indexes
Determine the accuracy of statistics and the associated impact to query plans and performance
Managing SQL Server Statistics
Design statistics maintenance tasks
Database Properties (Options Page) – Automatic
Auto Create Statistics
Auto Update Statistics
UPDATE STATISTICS (Transact-SQL)
SQL Server Statistics: Maintenance and Best Practices
Use dynamic management objects to review current index usage and identify missing indexes
sys.dm_db_index_usage_stats (Transact-SQL) – Current index usage
Finding Missing Indexes(Transact-SQL)
Consolidate overlapping indexes
An Approach to SQL Server Index Tuning
Analyze and troubleshoot query plans
Best Practice with the Query Store
Capture query plans using extended events and traces
How do I obtain a Query Execution Plan?
Extracting ShowPlan XML from SQL Server Extended Events
Identify poorly performing query plan operators
Ten Common Threats to Execution Plan Quality
Create efficient query plans using Query Store
Monitoring Performance By Using the Query Store
Compare estimated and actual query plans and related metadata
Execution Plan Basics
Configure Azure SQL Database Performance Insight
Azure SQL Database Query Performance Insight
Manage performance for database instances
Performance Center for SQL Server Database Engine and Azure SQL Database
Manage database workload in SQL Server
Resource Governor
Design and implement Elastic Scale for Azure SQL Database
Scaling out with Azure SQL Database
Select an appropriate service tier or edition
SQL Server 2016 SP1 editions
SQL Database options and performance: Understand what’s available in each service tier
Optimize database file and tempdb configuration
Using Files and Filegroups
FileTables (SQL Server)
TEMPDB Enhancements in SQL Server 2016
Installs a file for each logical process core up to a maxumum of 8. This helps reduce contention.
Enables recommended trace flags automatically
Trace Flag 1118 reduces Shared Global Allocation Map (SGAM) contention.
Trace Flag 1117 strictly forces all data files within the filegroup to grow at the same time.
Optimize memory configuration
Server Memory Server Configuration Options
Monitor and diagnose scheduling and wait statistics using dynamic management objects
SQL Server Performance Tuning Using Wait Statistics: A Beginner’s Guide
Troubleshoot and analyze storage, IO, and cache issues
A DBA guide to SQL Server performance troubleshooting – Part 1 – Problems and performance metrics
Monitor Azure SQL Database query plans
Monitoring Azure SQL Database using dynamic management views
Monitoring Performance By Using the Query Store
Monitor and trace SQL Server baseline performance metrics
Monitor operating system and SQL Server performance metrics
The Accidental DBA (Day 21 of 30): Essential PerfMon counters
Compare baseline metrics to observed metrics while troubleshooting performance issues
Monitoring Performance By Using the Query Store
Since the query store retains multiple execution plans per query, it can enforce policies to direct the query processor to use a specific execution plan for a query.Common scenarios for using the Query Store feature are:
Quickly find and fix a plan performance regression by forcing the previous query plan. Fix queries that have recently regressed in performance due to execution plan changes.
Determine the number of times a query was executed in a given time window, assisting a DBA in troubleshooting performance resource problems.
Identify top n queries (by execution time, memory consumption, etc.) in the past x hours.
Audit the history of query plans for a given query.
Analyze the resource (CPU, I/O, and Memory) usage patterns for a particular database.
The query store contains two stores; a plan store for persisting the execution plan information, and a runtime stats store for persisting the execution statistics information.
Available in all SQL editions including localdb.
The SQL Server 2016 Query Store: Overview and Architecture
Identify differences between performance monitoring and logging tools, such as perfmon and dynamic management objects
Monitor Azure SQL Database performance; determine best practice use cases for extended events
Monitoring database performance in Azure SQL Database
Monitoring Azure SQL Database using dynamic management views
Determine best practice use cases for extended events
Extended Events
Distinguish between Extended Events targets
Migrating from SQL Trace to Extended Events
Comparing Extended Events vs SQL Trace – or why SQL Trace & Profiler are just a thing of the past 🙂
Compare the impact of Extended Events and SQL Trace
Performance overhead of tracing with Extended Event targets vs SQL Trace under CPU Load
Define differences between Extended Events Packages, Targets, Actions, and Sessions
SQL Server Extended Events Packages
Targets for Extended Events in SQL ServerAn item that receives the output data from a captured event. The target displays the data to you.
etw_classic_sync_target – inter-operate with Event Tracing for Windows (ETW) to monitor system activity
event_counter – counts how many times each specified event occurs
event_file – disk file
histogram – bucketizer
pair_matching – enables you to detect start events that occurs without a corresponding end event
ring buffer – FIFO or FIFO per event
View the Extended Events Equivalents to SQL Trace Event Classes
SQL Server Extended Events Sessions