پیشنهاد فرادرس

آموزش بهینه سازی و افزایش کارایی Stored Procedure در SQL Server

دسته بندی ها: پایگاه داده ، آموزش SQL Server ، آموزش های پلورال سایت (Pluralsight)

آموزش-بهینه-سازی-و-افزایش-کارایی-stored-procedure-در-sq

استفاده از Stored Procedure در پروژه های بانک اطلاعاتی تاثیر زیادی در افزایش سرعت و کیفیت برنامه شما دارد.

در این آموزش تضویری با روش بهینه سازی و افزایش کارایی Stored Procedure ها در SQL Server آشنا می شوید.

این دوره آموزشی محصول Pluralsight می باشد.

سرفصل های دوره آموزشی:

  • بهینه سازی عملکرد Stored Procedure چیست؟
  • چرا از Stored Procedure استفاده می کنیم؟
  • روش های مختلف برای اجرای SQL
  • ذخیره سازی Whitepapers
  • Stored Procedure کجا ذخیره می شود؟
  • ایجاد Stored Procedure
  • انواع روش های ذخیره ساز
  • بهینه سازی و گردآوری مجدد
  • تنظیمات برای گردآوری مجدد
  • منطق شرطی
  • تعیین برنامه پایداری
  • متغیرهای شبیه سازی را برای UNKNOWN بهینه کنید
  • برنامه های تثبیت با شاخص - راه اندازی و سناریو
  • برنامه های تثبیت با شاخص - پایداری برنامه
  • برنامه های تثبیت با شاخص - نام نویسی!
  • اجرای عبارات پویا
  • ساختمان رشته ها به صورت پویا و ذخیره سازی
  • روش چند منظوره - نصب و راه اندازی
  • روش چند منظوره - گزینه کامپایل مجدد CPU

عنوان دوره آموزشی: SQL Server: Optimizing Stored Procedure Performance سطح: متوسط مدت زمان: 7 ساعت و 11 دقیقه نویسنده: Kimberly L. Trippلیست سرفصل های دوره آموزشی:

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. 

Introduction 01:51 This Course 02:35 What Does Optimizing Stored Procedure Performance Mean? 02:49 Does This Sound Familiar? 02:11 What Does Optimizing Stored Procedure Performance NOT Mean? 03:46 Why This Course Is Relevant 03:11 Course Focus and Structure (1) 02:09 Course Focus and Structure (2) 02:44 Why Use Stored Procedures? 00:27:04 Overview 02:04 Different Ways to Execute SQL Statements 02:25 Some Statements Can Be Cached for Reuse (1) 01:45 Some Statements Can Be Cached for Reuse (2) 03:43 Version-Specific Plan Caching Whitepapers 01:26 Reducing Plan Cache Pollution 04:32 Understanding sp_executesql 02:51 Stored Procedures / sp_executesql and the Cache 02:39 Parameter Sniffing 02:01 Summary: Why Use Stored Procedures? 03:34 Creation, Compilation, and Invalidation Section 1 01:08:12 Overview 02:15 What Happens When You Create a Procedure? 03:22 Where Are Stored Procedures Stored? (1) 01:43 Where Are Stored Procedures Stored? (2) 05:20 Creating Stored Procedures 07:35 Stored Procedure Plans and Caching 16:11 Side Effect: Plan Cache Flush (1) 03:43 Side Effect: Plan Cache Flush (2) 06:55 Side Effect: Plan Cache Flush (3) 01:53 Side Effect: Plan Cache Flush (4) 01:38 DEMO: Generic Demo Intro 02:47 DEMO: Setup Credit Sample Database 05:00 DEMO: Setup Analysis Procedures for Credit 02:35 DEMO: Object-Level Invalidation or Eviction 01:20 DEMO: Database-Level Invalidation or Eviction 03:09 DEMO: Server-Level Invalidation or Eviction 02:37 Creation, Compilation, and Invalidation Section 2 00:53:29 Plan Invalidation 05:09 Plan Invalidation Due to Statistics Updates 04:16 DEMO: Plan Invalidation and Statistics - Part 1 14:23 Updates to Statistics may not Invalidate Bad Plans 03:01 DEMO: Plan Invalidation and Statistics - Part 2 13:30 Plan Invalidation / Recompilation Causes 02:31 Stored Procedure Caching + Compilation Concerns 04:06 When Should You Recompile? 03:45 Summary: Creation and Compilation 02:45 Optimization and Recompilation Section 1 00:55:44 Overview 04:24 Options for Recompilation 04:11 sp_recompile object_name 06:52 DEMO: Long Blocking Chains 11:13 CREATE ... WITH RECOMPILE 03:21 DEMO: CREATE ... WITH RECOMPILE 05:22 Conditional Logic 02:49 DEMO: Conditional Logic - Part 1 08:43 Modularization 03:17 DEMO: Conditional Logic - Part 2 - Modularization 05:27 Optimization and Recompilation Section 2 00:51:43 EXECUTE ... WITH RECOMPILE (1) 03:38 DEMO: EXECUTE ... WITH RECOMPILE For Testing 10:18 EXECUTE ... WITH RECOMPILE (2) 03:39 DEMO: Determining Plan Stability 07:44 Statement-Level Recompilation 05:33 DEMO: Recompilation With OPTION (RECOMPILE) 04:15 DEMO: Recompilation With OPTION (Optimize For Literal) 04:23 DEMO: Recompilation With OPTION (Optimize For UNKNOWN) 09:17 DEMO: Variables Simulate Optimize For UNKNOWN 02:51 Optimization and Recompilation Section 3 01:08:24 OPTION (RECOMPILE) 03:56 OPTIMIZE FOR ... 04:41 DEMO: Scenario - Setup 04:15 DEMO: Scenario - Scenario Explained 05:26 DEMO: Scenario - Conditional Logic Does NOT Work 05:46 DEMO: Scenario - EXEC With RECOMPILE 07:20 DEMO: Scenario - Conditional Logic Plus Modularization WORKS! 06:32 DEMO: Stabilizing Plans With Indexes - Setup and Scenario 04:36 DEMO: Stabilizing Plans With Indexes - Plan Stability 10:23 DEMO: Stabilizing Plans With Indexes - Voila! 10:17 Summary: Walkthrough Demo (1) 02:20 Summary: Walkthrough Demo (2) 02:46 Optimization and Recompilation Section 4 01:02:20 When Does a Procedure Get Optimized? 03:39 Server-Wide: OPTIMIZE FOR UNKNOWN 03:08 The Checkered Past of OPTION (RECOMPILE) 03:37 Dynamic String Execution 03:09 DEMO: Dynamic String Execution 08:04 Multi-Purpose Procedures 02:41 Building Strings Dynamically and Caching 03:44 DEMO: Multi-Purpose Procedures - Setup and Scenario 05:21 DEMO: Multi-Purpose Procedures - The Problem 05:08 DEMO: Multi-Purpose Procedures - Option Recompile too Much CPU 03:15 DEMO: Multi-Purpose Procedures - The Ultimate Multi-Purpose Proc 16:47 Patterns and Practices in Statement Recompilation 02:59 Summary: Stored Procedure Pitfalls/Performance 00:43 Other Concerns and Considerations 00:23:17 Overview 03:46 Demystifying Plan Caching for Bad Plans 03:04 Secondary Concerns and Considerations 07:57 Optimizing Statement and Procedure Performance 02:35 Just the Tip of the Iceberg 04:19 Course Summary 01:33

حجم فایل: 1.52GB

Pluralsight SQL Server Optimizing Stored Procedure Performance

پیشنهاد فرادرس