|
Introduction
|
| |
Introduction & Disclaimers |
(03:36) |
|
| |
Optimizing a Database |
(03:42) |
|
| |
Some Common Problems |
(06:10) |
|
|
Designing for Performance
|
| |
Planning & Process Principles |
(04:02) |
|
| |
Design Principles |
(05:33) |
|
| |
Client/Server Interaction |
(05:04) |
|
|
Troubleshooting & Testing
|
| |
What to Look for |
(05:31) |
|
| |
Types of Testing |
(05:19) |
|
| |
SQLdiag Tool |
(04:18) |
|
|
Conducting an Audit
|
| |
Understanding Audits |
(03:55) |
| |
Components of Audits |
(03:27) |
| |
Examples of Components |
(04:33) |
| |
Creating Audits Through Procedures |
(04:15) |
|
Relationships & Joins
|
| |
Relationships |
(05:12) |
| |
Understanding Joins |
(05:39) |
| |
Optimizing Joins |
(06:05) |
| |
Joins & Indexing |
(02:15) |
|
Referential Integrity
|
| |
Proper Use of Integrity |
(02:39) |
| |
Integrity Continued |
(06:15) |
| |
Primary/Foreign/Candidate Keys |
(05:31) |
| |
Troubleshooting Integrity |
(05:03) |
|
Data Types & Validations
|
| |
Unions |
(02:02) |
| |
UNION in SQL |
(03:11) |
| |
Standards |
(05:30) |
| |
Validation |
(04:54) |
|
Creating a Data Model
|
| |
Conceptual/Logical/Physical Models |
(05:37) |
| |
Relational Modeling |
(04:41) |
| |
Entity Model |
(03:04) |
| |
Schemas |
(04:23) |
|
Database Normalization
|
| |
First Normal Form |
(04:31) |
| |
Second Normal Form |
(04:39) |
| |
Third Normal Form |
(03:33) |
| |
Denormalization |
(06:26) |
|
Temporary Tables
|
| |
Creating & Using Temporary Tables |
(03:54) |
| |
Heap & Memory Tables |
(03:08) |
| |
EXPLAIN Statement |
(05:16) |
|
Query/Views Optimization
|
| |
Introduction to Execution Plans |
(04:45) |
| |
Running the SQL Execution Plan |
(05:07) |
| |
Using Profiler for the Execution Plan |
(04:41) |
| |
SET SHOWPLAN_TEXT |
(04:15) |
|
Index Strategies
|
| |
Effective Indexes |
(06:31) |
| |
Short & Distinct Keys |
(04:25) |
| |
Looking at Indexes in SQL |
(03:18) |
| |
Optimizing & Indexes |
(05:35) |
|
Clustered Indexes
|
| |
Clustered vs. Non-Clustered Indexes |
(04:42) |
| |
Tips for Clustered Indexes |
(03:47) |
| |
Significance on Performance |
(05:57) |
|
More Tuning
|
| |
Eliminating Unwanted Data |
(05:10) |
| |
SHRINKFILE Examples |
(02:43) |
| |
Database Tuning Advisor |
(02:17) |
| |
DTA Example |
(05:50) |
|
Data Types & Quality
|
| |
Data Quality |
(05:16) |
| |
Overview of Data Types |
(03:08) |
| |
SQL vs. Oracle Data Types |
(03:28) |
| |
Examples in SQL |
(03:09) |
|
Best Practices
|
| |
Creating a Sustainable Database |
(05:08) |
| |
Documentation |
(05:04) |
| |
Most Common Practices |
(04:18) |
|
Stored Procedures & Triggers
|
| |
Stored Procedures/Functions/Triggers |
(04:53) |
| |
Writing Stored Procedures |
(05:18) |
| |
Creating a Function & When to Use |
(04:33) |
| |
Working with Triggers |
(04:43) |
|
Hardware Performance Concepts
|
| |
CPU & Input/Output |
(03:58) |
| |
Memory Management |
(03:57) |
| |
Network Infrastructure |
(04:52) |
|
Applying Performance Concepts
|
| |
Task Manager |
(03:31) |
| |
System vs. Performance Monitor |
(02:20) |
| |
Performance Monitor |
(05:13) |
| |
SQL Database I/O Report |
(02:24) |
| |
Examples for sys.dm_io_virtual_file_stats |
(03:04) |
|
Monitoring
|
| |
Event Viewer |
(05:49) |
| |
SQL Activity Monitor |
(05:41) |
| |
Locks & Deadlocks |
(04:47) |
| |
Monitoring Services |
(03:03) |
|
Database Security
|
| |
Privileges |
(03:56) |
| |
Role Based Authorization |
(04:24) |
| |
GRANT & REVOKE Statements |
(04:19) |
| |
Reviewing Security in SQL |
(03:08) |
|
Understanding Lifecycles
|
| |
Lifecycle Management |
(05:39) |
| |
The Database Lifecycle |
(05:22) |
| |
Using the SQL Management Plan |
(05:06) |
|
Using Baselines & Profiler
|
| |
Importance of Baselines |
(03:34) |
| |
SQL Profiler |
(05:00) |
| |
Trace & Other Profiler Tools |
(03:36) |
| |
Terminology in Work Files |
(01:45) |
|
Analyzing Slow Performance
|
| |
Database Testing |
(05:22) |
| |
Thoughts on Troubleshooting |
(04:57) |
| |
Generalized Tips |
(03:42) |
|
Optimizing Code
|
| |
Levels of Optimization |
(02:46) |
| |
UNION vs. OR Optimization |
(02:17) |
| |
JOIN vs. WHERE |
(02:14) |
| |
Weak Joins |
(02:52) |
| |
Coding Tips |
(04:35) |
|
Statistics
|
| |
Overview of Statistics |
(02:57) |
| |
Seeing Statistics in SQL |
(04:35) |
| |
Auditing |
(03:48) |
| |
Auditing in SQL |
(02:52) |
|
Other Components
|
| |
Database Consistency Checker (DBCC) |
(03:34) |
| |
DBCC in SQL |
(03:03) |
| |
System Catalog |
(02:28) |
| |
System Catalog in SQL |
(03:01) |
|
Checklists
|
| |
Architecture & Design |
(03:34) |
| |
Constraints & Influences |
(04:36) |
| |
Performance Tuning |
(05:17) |
| |
Index Checklist |
(03:52) |
| |
General Checklist |
(03:53) |
|
Odds & Ends
|
| |
Business Intelligence |
(04:16) |
| |
Archiving |
(05:15) |
| |
ADONET |
(03:29) |
|
Conclusion
|
| |
Wrap Up & Work Files |
(03:17) |
|
Credits
|
| |
About the Author |
(01:58) |
This Database Optimization course provides viewers with important techniques and checklists to configure, monitor, and manage their databases. You will learn how to develop a tuning strategy, explore the tuning and diagnostic tools, understand the relational database model and implications on performance, evaluate the application for efficient design, structure, and other factors affecting performance and evaluate hardware for effects on performance. Join us as we review best practices in designing, optimizing, and maintaining your database. You will benefit from this course whether you have some experience with optimizing databases or are just starting out. Work files are included. To begin learning today, simply click on the movie links.
