2014
pluralsight
Kimberly L. Tripp
7h 11m
English
When using stored procedures, their performance is dependent on creating the optimal plan for the execution environment and making sure that it remains cached. This comprehensive course will show you all of that and more, and is applicable to SQL Server developers and anyone responsible for ensuring performance of stored procedures, from SQL Server 2005 onward.
1. Introduction
01. Introduction
02. This Course
03. What Does Optimizing Stored Procedure Performance Mean?
04. Does This Sound Familiar?
05. What Does Optimizing Stored Procedure Performance NOT Mean?
06. Why This Course Is Relevant
07. Course Focus and Structure (1)
08. Course Focus and Structure (2)
2. Why Use Stored Procedures?
01. Overview
02. Different Ways to Execute SQL Statements
03. Some Statements Can Be Cached for Reuse (1)
04. Some Statements Can Be Cached for Reuse (2)
05. Version-Specific Plan Caching Whitepapers
06. Reducing Plan Cache Pollution
07. Understanding sp_executesql
08. Stored Procedures / sp_executesql and the Cache
09. Parameter Sniffing
10. Summary: Why Use Stored Procedures?
3. Creation, Compilation, and Invalidation Section 1
01. Overview
02. What Happens When You Create a Procedure?
03. Where Are Stored Procedures Stored? (1)
04. Where Are Stored Procedures Stored? (2)
05. Creating Stored Procedures
06. Stored Procedure Plans and Caching
07. Side Effect: Plan Cache Flush (1)
08. Side Effect: Plan Cache Flush (2)
09. Side Effect: Plan Cache Flush (3)
10. Side Effect: Plan Cache Flush (4)
11. DEMO: Generic Demo Intro
12. DEMO: Setup Credit Sample Database
13. DEMO: Setup Analysis Procedures for Credit
14. DEMO: Object-Level Invalidation or Eviction
15. DEMO: Database-Level Invalidation or Eviction
16. DEMO: Server-Level Invalidation or Eviction
4. Creation, Compilation, and Invalidation Section 2
01. Plan Invalidation
02. Plan Invalidation Due to Statistics Updates
03. DEMO: Plan Invalidation and Statistics - Part 1
04. Updates to Statistics may not Invalidate Bad Plans
05. DEMO: Plan Invalidation and Statistics - Part 2
06. Plan Invalidation / Recompilation Causes
07. Stored Procedure Caching + Compilation Concerns
08. When Should You Recompile?
09. Summary: Creation and Compilation
5. Optimization and Recompilation Section 1
01. Overview
02. Options for Recompilation
03. sp_recompile object_name
04. DEMO: Long Blocking Chains
05. CREATE ... WITH RECOMPILE
06. DEMO: CREATE ... WITH RECOMPILE
07. Conditional Logic
08. DEMO: Conditional Logic - Part 1
09. Modularization
10. DEMO: Conditional Logic - Part 2 - Modularization
6. Optimization and Recompilation Section 2
01. EXECUTE WITH RECOMPILE (1)
02. DEMO: EXECUTE WITH RECOMPILE For Testing
03. EXECUTE WITH RECOMPILE (2)
04. DEMO: Determining Plan Stability
05. Statement-Level Recompilation
06. DEMO: Recompilation With OPTION (RECOMPILE)
07. DEMO: Recompilation With OPTION (Optimize For Literal)
08. DEMO: Recompilation With OPTION (Optimize For UNKNOWN)
09. DEMO: Variables Simulate Optimize For UNKNOWN
7. Optimization and Recompilation Section 3
01. OPTION (RECOMPILE)
02. OPTIMIZE FOR ...
03. DEMO: Scenario - Setup
04. DEMO: Scenario - Scenario Explained
05. DEMO: Scenario - Conditional Logic Does NOT Work
06. DEMO: Scenario - EXEC With RECOMPILE
07. DEMO: Scenario - Conditional Logic Plus Modularization WORKS!
08. DEMO: Stabilizing Plans With Indexes - Setup and Scenario
09. DEMO: Stabilizing Plans With Indexes - Plan Stability
10. DEMO: Stabilizing Plans With Indexes - Voila!
11. Summary: Walkthrough Demo (1)
12. Summary: Walkthrough Demo (2)
8. Optimization and Recompilation Section 4
01. When Does a Procedure Get Optimized?
02. Server-Wide: OPTIMIZE FOR UNKNOWN
03. The Checkered Past of OPTION (RECOMPILE)
04. Dynamic String Execution
05. DEMO: Dynamic String Execution
06. Multi-Purpose Procedures
07. Building Strings Dynamically and Caching
08. DEMO: Multi-Purpose Procedures - Setup and Scenario
09. DEMO: Multi-Purpose Procedures - The Problem
10. DEMO: Multi-Purpose Procedures - Option Recompile too Much CPU
11. DEMO: Multi-Purpose Procedures - The Ultimate Multi-Purpose Proc
12. Patterns and Practices in Statement Recompilation
13. Summary: Stored Procedure Pitfalls/Performance
9. Other Concerns and Considerations
01. Overview
02. Demystifying Plan Caching for Bad Plans
03. Secondary Concerns and Considerations
04. Optimizing Statement and Procedure Performance
05. Just the Tip of the Iceberg
06. Course Summary
Download File Size:1.72 GB
|
