SQL Server: Myths and Misconceptions
There is so much information online about SQL Server, however much of it is inaccurate to some degree. In this course, you will learn the truth behind more than 80 myths and misconceptions about SQL Server that can cause you performance and availability problems - from someone who wrote the SQL Server code. This course is perfect for developers and DBAs who have been struggling to make sense of conflicting information about SQL Server. The information in the course applies to all versions from SQL Server 2005 onwards.
You are currently not signed in. Please sign in to access subscriber-only content.
Currently using: {{getCurrentPlayerName()}} [Change]
| expand all | collapse all | Progress | Duration | |
|---|---|---|---|
 Introduction |
|
00:09:51 | |
|
Introduction
|
|
01:41 | |
|
Why is Some Information Incorrect?
|
|
02:22 | |
|
Why Does Incorrect Information Cause Problems
|
|
01:54 | |
|
Summary
|
|
01:31 | |
|
Course Structure
|
|
02:23 | |
|
Performance Myths and Misconceptions |
|
00:51:34 | |
|
Introduction
|
|
01:01 | |
|
Controlling Instant File Initialization
|
|
02:27 | |
|
Demo: Enabling Instant File Initialization
|
|
02:47 | |
|
Temporary Table Abuse
|
|
02:05 | |
|
Demo: Using Temporary Tables
|
|
02:54 | |
|
Tempdb Sizing
|
|
01:50 | |
|
Data Compression
|
|
02:29 | |
|
Data File Shrinking
|
|
02:40 | |
|
Demo: Effect of Data File Shrinking
|
|
02:22 | |
|
Updating Index Key Columns
|
|
02:07 | |
|
Demo: Updating Index Key Columns
|
|
02:37 | |
|
Snapshot Isolation
|
|
02:41 | |
|
Checkpoints
|
|
01:25 | |
|
NULL Bitmap
|
|
02:40 | |
|
Tempdb Data Files
|
|
03:01 | |
|
Solid State Disk Uses
|
|
02:16 | |
|
Disk Queue Length
|
|
01:24 | |
|
Page Life Expectancy Threshold
|
|
02:24 | |
|
NUMA Effect on Page Life Expectancy
|
|
01:26 | |
|
CXPACKET Waits
|
|
02:36 | |
|
ASYNC_NETWORK_IO Waits
|
|
02:01 | |
|
Demo: Wait Statistics
|
|
03:00 | |
|
Overriding MAXDOP
|
|
01:21 | |
|
Transaction Log Myths and Misconceptions |
|
00:41:34 | |
|
Introduction
|
|
00:58 | |
|
Log Truncation/Clearing
|
|
01:42 | |
|
Transaction Log Analysis
|
|
01:50 | |
|
Demo: Analyzing the Transaction Log
|
|
05:42 | |
|
TRUNCATE TABLE Logging
|
|
02:40 | |
|
Efficient Logging of Index Operations
|
|
01:52 | |
|
Demo: Showing Efficient Logging
|
|
02:43 | |
|
Multiple Log Files
|
|
01:41 | |
|
Instant File Initialization
|
|
01:47 | |
|
Transaction Log Sizing
|
|
03:25 | |
|
Log Record Removal
|
|
01:26 | |
|
Log Record Movement
|
|
01:23 | |
|
Using BULK_LOGGED to Reduce Log Backup Size
|
|
02:21 | |
|
Demo: Log Backup Size Using BULK_LOGGED
|
|
02:25 | |
|
Log Backup Size vs. Log Size
|
|
01:51 | |
|
Disaster Recovery and BULK_LOGGED
|
|
01:37 | |
|
Demo: Disaster Recovery and BULK_LOGGED
|
|
06:11 | |
|
Indexing Myths and Misconceptions |
|
00:18:23 | |
|
Introduction
|
|
00:56 | |
|
FILLFACTOR Usage
|
|
01:57 | |
|
Clustered Index Rebuilds
|
|
01:49 | |
|
Nonclustered Index Choice
|
|
02:11 | |
|
SSDs and Fragmentation
|
|
02:08 | |
|
Removing Heap Fragmentation
|
|
01:37 | |
|
GUIDs as a Cluster Key Choice
|
|
02:07 | |
|
Demo: GUID Cluster Keys
|
|
04:21 | |
|
Rebuilding Indexes and Statistics
|
|
01:17 | |
|
Locking Myths and Misconceptions |
|
00:21:31 | |
|
Introduction
|
|
00:42 | |
|
Online Index Operations and Blocking
|
|
02:04 | |
|
Demo: Locks held by Online Index Rebuild
|
|
04:14 | |
|
Backups and Blocking
|
|
02:00 | |
|
DBCC CHECKDB and Blocking
|
|
01:53 | |
|
Lock Escalation
|
|
01:55 | |
|
Demo: Lock Escalation
|
|
05:32 | |
|
Invalid Blocking and Deadlocks
|
|
01:42 | |
|
NOLOCK and READ UNCOMMITTED
|
|
01:29 | |
|
Transact-SQL Myths and Misconceptions |
|
00:19:08 | |
|
Introduction
|
|
00:44 | |
|
Nested Transactions
|
|
02:12 | |
|
Demo: Nested Transactions
|
|
02:47 | |
|
DDL Triggers
|
|
01:28 | |
|
SELECT COUNT(*)
|
|
02:19 | |
|
Demo: SELECT COUNT (*)
|
|
02:50 | |
|
Physical Record Location
|
|
02:05 | |
|
DEMO: Physical Record Location
|
|
02:54 | |
|
Running DMVs in 80 Compatibility Mode
|
|
01:49 | |
|
Backup Myths and Misconceptions |
|
00:18:44 | |
|
Introduction
|
|
00:45 | |
|
Concurrent Data and Log Backups
|
|
01:21 | |
|
Data Backups and Log Clearing
|
|
01:07 | |
|
Demo: When Does the Log Clear
|
|
04:40 | |
|
Backup Completion Guarantees
|
|
02:14 | |
|
Differential Backups
|
|
01:07 | |
|
Planning a Backup Strategy
|
|
01:39 | |
|
Restarting the Log Backup Chain
|
|
01:11 | |
|
Demo: Log Backup Chain
|
|
02:20 | |
|
Replacing Backups with Database Snapshots
|
|
02:20 | |
|
Restore Myths and Misconceptions |
|
00:14:55 | |
|
Introduction
|
|
00:55 | |
|
Mix-and-Match Filegroups
|
|
01:53 | |
|
Demo: Filegroup Restores
|
|
02:53 | |
|
Using WITH STOPAT for Data Backups
|
|
01:19 | |
|
Restoring to Older Versions of SQL Server
|
|
02:05 | |
|
Demo: Down-level Restore
|
|
02:13 | |
|
Restoring to Non-Enterprise Editions
|
|
02:16 | |
|
32-Bit vs. 64-Bit Compatibility
|
|
01:21 | |
|
Corruption Myths and Misconceptions |
|
00:21:19 | |
|
Introduction
|
|
00:58 | |
|
Detaching and Attaching a SUSPECT Database
|
|
01:55 | |
|
Offloading Consistency Checks
|
|
01:54 | |
|
Causing Corruption Using T-SQL
|
|
01:07 | |
|
Enabling Page Checksums
|
|
01:44 | |
|
DBCC CHECKDB vs. BACKUP ... WITH CHECKSUM
|
|
01:13 | |
|
Restarting SQL Server to Fix Corruption
|
|
01:17 | |
|
Disappearing Corruptions
|
|
02:13 | |
|
Corruption-Free Guarantee
|
|
01:00 | |
|
Repair Can Fix Everything
|
|
02:04 | |
|
Repairing System Databases/Tables
|
|
01:59 | |
|
Transaction Rollbacks
|
|
01:44 | |
|
Demo: Transaction Rollbacks
|
|
02:11 | |
|
High-Availability Myths and Misconceptions |
|
00:17:48 | |
|
Introduction
|
|
00:52 | |
|
Database Snapshot Size
|
|
02:21 | |
|
Database Snapshot Efficiency
|
|
01:58 | |
|
Demo: Database Snapshot Memory Usage
|
|
04:36 | |
|
Database Mirroring Failure Detection
|
|
02:14 | |
|
Database Mirroring Failover Speed
|
|
01:23 | |
|
Failover Clustering is the Best High-Availability Technology
|
|
01:11 | |
|
Multiple Database Mirors
|
|
00:40 | |
|
Transactions After a Failover
|
|
01:03 | |
|
Replication is a Valid High-Availability Choice
|
|
01:30 |
