SQL Server: Optimizing Ad Hoc Statement Performance-0daytown

SQL Server: Optimizing Ad Hoc Statement Performance

WMV | VC-1 252kbps | English | 1024×768 | 15fps | 7h 15mins | WMA2 stereo 128kbps | 968 MB

Genre: Video Training

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!

│ sqlserver-optimizing-adhoc-statement-performance.zip
│
├───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