SQL Server Optimizing Stored Procedure Performance [2014, ENG] torrent

Обучающие видео » Компьютерные видеоуроки и обучающие интерактивные DVD » Программирование (видеоуроки)

SQL Server Optimizing Stored Procedure Performance

Год выпуска: 2014
Производитель: Pluralsight
Сайт производителя: pluralsight.com
Автор: Kimberly Tripp
Продолжительность: 7:11
Тип раздаваемого материала: Видеоклипы
Язык: Английский
Описание: If you want to use SQL Server databases effectively, you will end up using stored procedures. Their aim is that you have optimized and compiled code that resides in a cache to improve workload performance for subsequent executions. However, you might find that it does not always work out that way. The performance of a stored procedure is heavily dependent on how the plan is chosen and cached. If the plan is not optimal for subsequent executions it could cause performance to suffer greatly. In the end, you might suffer from what is known as parameter-sniffing-problems where the optimal plan for a procedure varies based on the parameters supplied. This comprehensive course will teach you how stored procedure plan caching works and how to get SQL Server to produce the optimal plan for your stored procedure performance in a variety of circumstances. The course is applicable to SQL Server developers and anyone who is responsible for writing stored procedures that must repeatedly perform well. The demo database provided is compatible with SQL Server 2008 through SQL Server 2014. All course demos are shown on SQL Server 2014 with references to behaviors (where different) on all versions. Optimization strategies you will learn in the course apply to SQL Server 2005 onward, and some even back to SQL Server 2000.
[spoiler="Содержание"]
Introduction
Introduction
This Course
What Does Optimizing Stored Procedure Performance Mean?
Does This Sound Familiar?
What Does Optimizing Stored Procedure Performance NOT Mean?
Why This Course Is Relevant
Course Focus and Structure (1)
Course Focus and Structure (2)

Why Use Stored Procedures?
Overview
Different Ways to Execute SQL Statements
Some Statements Can Be Cached for Reuse (1)
Some Statements Can Be Cached for Reuse (2)
Version-Specific Plan Caching Whitepapers
Reducing Plan Cache Pollution
Understanding sp_executesql
Stored Procedures / sp_executesql and the Cache
Parameter Sniffing
Summary: Why Use Stored Procedures?

Creation, Compilation, and Invalidation Section 1
Overview
What Happens When You Create a Procedure?
Where Are Stored Procedures Stored? (1)
Where Are Stored Procedures Stored? (2)
Creating Stored Procedures
Stored Procedure Plans and Caching
Side Effect: Plan Cache Flush (1)
Side Effect: Plan Cache Flush (2)
Side Effect: Plan Cache Flush (3)
Side Effect: Plan Cache Flush (4)
DEMO: Generic Demo Intro
DEMO: Setup Credit Sample Database
DEMO: Setup Analysis Procedures for Credit
DEMO: Object-Level Invalidation or Eviction
DEMO: Database-Level Invalidation or Eviction
DEMO: Server-Level Invalidation or Eviction

Creation, Compilation, and Invalidation Section 2
Plan Invalidation
Plan Invalidation Due to Statistics Updates
DEMO: Plan Invalidation and Statistics - Part 1
Updates to Statistics may not Invalidate Bad Plans
DEMO: Plan Invalidation and Statistics - Part 2
Plan Invalidation / Recompilation Causes
Stored Procedure Caching + Compilation Concerns
When Should You Recompile?
Summary: Creation and Compilation

Optimization and Recompilation Section 1
Overview
Options for Recompilation
sp_recompile object_name
DEMO: Long Blocking Chains
CREATE ... WITH RECOMPILE
DEMO: CREATE ... WITH RECOMPILE
Conditional Logic
DEMO: Conditional Logic - Part 1
Modularization
DEMO: Conditional Logic - Part 2 - Modularization

Optimization and Recompilation Section 2
EXECUTE ... WITH RECOMPILE (1)
DEMO: EXECUTE ... WITH RECOMPILE For Testing
EXECUTE ... WITH RECOMPILE (2)
DEMO: Determining Plan Stability
Statement-Level Recompilation
DEMO: Recompilation With OPTION (RECOMPILE)
DEMO: Recompilation With OPTION (Optimize For Literal)
DEMO: Recompilation With OPTION (Optimize For UNKNOWN)
DEMO: Variables Simulate Optimize For UNKNOWN

Optimization and Recompilation Section 3
OPTION (RECOMPILE)
OPTIMIZE FOR ...
DEMO: Scenario - Setup
DEMO: Scenario - Scenario Explained
DEMO: Scenario - Conditional Logic Does NOT Work
DEMO: Scenario - EXEC With RECOMPILE
DEMO: Scenario - Conditional Logic Plus Modularization WORKS!
DEMO: Stabilizing Plans With Indexes - Setup and Scenario
DEMO: Stabilizing Plans With Indexes - Plan Stability
DEMO: Stabilizing Plans With Indexes - Voila!
Summary: Walkthrough Demo (1)
Summary: Walkthrough Demo (2)

Optimization and Recompilation Section 4
When Does a Procedure Get Optimized?
Server-Wide: OPTIMIZE FOR UNKNOWN
The Checkered Past of OPTION (RECOMPILE)
Dynamic String Execution
DEMO: Dynamic String Execution
Multi-Purpose Procedures
Building Strings Dynamically and Caching
DEMO: Multi-Purpose Procedures - Setup and Scenario
DEMO: Multi-Purpose Procedures - The Problem
DEMO: Multi-Purpose Procedures - Option Recompile too Much CPU
DEMO: Multi-Purpose Procedures - The Ultimate Multi-Purpose Proc
Patterns and Practices in Statement Recompilation
Summary: Stored Procedure Pitfalls/Performance

Other Concerns and Considerations
Overview
Demystifying Plan Caching for Bad Plans
Secondary Concerns and Considerations
Optimizing Statement and Procedure Performance
Just the Tip of the Iceberg
Course Summary
[/spoiler]
Файлы примеров: присутствуют
Формат видео: MP4
Видео: AVC, 1024x768, 4:3, 15fps, 743kbps
Аудио: AAC, 44.1kHz, 128kbps, stereo
[spoiler="Скриншоты"]

[/spoiler]