2013
Pluralsight
Kimberly L. Tripp
7h 15m
English
This course is about how different ad hoc statement execution methods affect caching, plan reuse, memory and ultimately performance. Knowing when to use each method is important and understanding how SQL Server works will demystify certain behaviors you may have seen but previously have been unable to explain. SQL Server can support any workload, any design, and any data requests but knowing exactly which one is the most beneficial to use can give you better long-term scalability, availability, and performance. Using the wrong method can cause more memory to be wasted and even result in parameter sniffing problems (where subsequent statements perform poorly because of the plan that’s been cached). This course will show you how each statement execution method works, how it’s cached, whether or not it wastes cache, and finally how to test and rewrite the statement to take better advantage of caching. Along the way we will also cover a variety of other necessary features and tools: estimates, statistics, and heuristics; how to analyze query plans; some indexing strategies to improve performance; and plan guides. This course is an absolute must for everyone that works with SQL Server and it’s also an introduction to concepts that will be built upon in future courses. This course is applicable to all SQL Server versions from SQL Server 2005 onward, and for SQL Server developers as well as anyone responsible for writing data access statements to SQL Server tables. You can have any level of experience to gain from this course but those of you who have experienced what seemed odd behavior/performance with your ad hoc statements will probably benefit the most!
01. Introduction
01. Introduction and Background.wmv
02. This Course.wmv
03. What Does Optimizing Ad Hoc Statement Performance NOT Mean .wmv
04. What Does Optimizing Ad Hoc Statement Performance Mean .wmv
05. Why is This Course Relevant .wmv
06. Course Focus and Structure (1).wmv
07. Course Focus and Structure (2).wmv
02. Statement Execution Methods
01. Introduction.wmv
02. Different Ways to Execute SQL Statements.wmv
03. Understanding Ad Hoc Statements.wmv
04. Understanding sp_executesql.wmv
05. Understanding Dynamic String Execution.wmv
06. Dynamic String Execution and SQL Injection.wmv
07. Demo Credit Sample Database Setup for This Course.wmv
08. Demo Setting Up For Analyzing Cache.wmv
09. Demo Part 1 - Ad Hoc Safe Statements.wmv
10. Demo Part 2 - Ad Hoc Unsafe Statements.wmv
11. Demo Part 3 - Ad Hoc Safe and Unsafe with Variables.wmv
12. Demo Part 4 - sp_executesql with Safe Statement.wmv
13. Demo Part 5 - sp_executesql with Unsafe Statement.wmv
14. Demo Part 6 - Dynamic String Execution with Safe Statement.wmv
15. Demo Part 7 - Dynamic String Execution with Unsafe Statement.wmv
16. Summary Statement Execution Methods.wmv
03. Estimates and Selectivity
01. Introduction.wmv
02. Statement Execution Simplified.wmv
03. Cost-Based Optimization.wmv
04. Understanding Selectivity.wmv
05. Demo Setup and First Look at Statistics.wmv
06. Demo Updates and Estimates.wmv
07. Demo Ad Hoc Statements and Variables.wmv
08. Demo When No Statistics Exist then Heuristics are Used.wmv
09. Demo Summary Estimates and Selectivity.wmv
10. How Do You See Statistics .wmv
11. What Do Statistics Tell Us About Our Data (1).wmv
12. What Do Statistics Tell Us About Our Data (2).wmv
13. What Do Statistics Tell Us About Our Data (3).wmv
14. What Do Statistics Tell Us About Our Data (4).wmv
15. Demo Reading the Histogram.wmv
16. When and How Does SQL Server Use Statistics .wmv
17. Summary Estimates and Selectivity.wmv
04. Statement Caching
01. Introduction.wmv
02. What Affects Ad Hoc Statement Behavior .wmv
03. Default Ad Hoc Statement Behavior.wmv
04. Ad Hoc Statement Textual Matching.wmv
05. Ad Hoc Statements Safe vs. Unsafe.wmv
06. Ad Hoc Statement Caching.wmv
07. Demo Ad Hoc Statements and the Plan Cache.wmv
08. Verifying Plans in Cache NOW.wmv
09. Analyzing the Plan Cache.wmv
10. Demo query_hash and query_plan_hash.wmv
11. Changing Ad Hoc Statement Behavior (1).wmv
12. Changing Ad Hoc Statement Behavior (2).wmv
13. Multiple Plans (Tipping Covering).wmv
14. Demo Part 1 - Making a Statement Safe with Covering.wmv
15. Demo Part 2 - The Right Way to Force Statements.wmv
16. Summary Statement Caching.wmv
05. Plan Cache Pollution
01. Plan Cache Pollution.wmv
02. Ad Hoc Plan Cache Pollution Defined.wmv
03. Plan Cache Stores.wmv
04. Verifying State of Plan Cache.wmv
05. Demo Analyzing for Plan Cache Pollution - Setup.wmv
06. Demo Analyzing for Plan Cache Pollution.wmv
07. Balancing Plan Cache Pollution, CPU, and PSP (1).wmv
08. Demo Part 1a - Optimize for Adhoc Workloads.wmv
09. Demo Part 1b - Covering to Make a Query Safe.wmv
10. Balancing Plan Cache Pollution, CPU, and PSP (2).wmv
11. Balancing Plan Cache Pollution, CPU, and PSP (3).wmv
12. Balancing Plan Cache Pollution, CPU, and PSP (4).wmv
13. Demo Part 2a - Forcing a Stable Statement With sp_executesql.wmv
14. Demo Part 2b - Forcing a Stable Statement with a Plan Guide.wmv
15. Demo Part 2c - Optimizing an Expensive Statement.wmv
16. Balancing Plan Cache Pollution, CPU, and PSP (5).wmv
17. Demo Part 3 - Clearing Single-Use Plan Cache Pollution.wmv
18. Alternatives to Ad Hoc Statements.wmv
19. Demo Summary of All Script Executions.wmv
20. Summary Plan Cache Pollution.wmv
06. Summary
01. Statement Execution Summary.wmv
02. Statement Execution, Estimates, and Caching (1).wmv
03. Statement Execution, Estimates, and Caching (2).wmv
04. Statement Execution Methods, Caching, and Concerns.wmv
05. Bringing It All Together.wmv
06. Statement Execution Solutions (1).wmv
07. Statement Execution Solutions (2).wmv
08. Statement Execution Solutions (3).wmv
09. Summary Statement Execution.wmv
10. Just the Tip of the Iceberg.wmv
11. Where to Go Next and Final Summary.wmv
http://pluralsight.com/
Download File Size:811.09 MB
|
