در این آموزش تصویری با مباحث مربوط به بهینه سازی و افزایش کارایی در SQL Server آشنا می شوید.

این دوره آموزشی محصول موسسه Udemy است.

سرفصل های این دوره:

  • مقدمه
  • ابزارها
  • SET Statistics IO و SET Statistics Time On
  • جستجوی برنامه ها با اکسپلورر
  • اضافه کردن AdventureWorks
  • پارامترهای ساده
  • پارامتر Sniffing Demonstration
  • Plan Guides چیست؟
  • Buffer Pool چیست؟
  • Hints و فیلترها
  • استفاده از شاخص Hints برای مقایسه شاخص Effectiveness
  • Filtered Index
  • امتحان بهترین کدهای دنیای برنامه نویسی
  • Schema Qualified Objects
  • توقف مرتب سازی پایگاه داده
  • Trace Flags
  • فعال کردن به روز رسانی خودکار Async
  • نتیجه گیری

عنوان دوره: Udemy Advanced SQL Server Performance Tuning

سطح: متوسط

نویسنده: Mike West

مدت دوره: 1.5 ساعت


Udemy Advanced SQL Server Performance Tuning

Mike West
1.5 hours
Intermediate Level

Please note: This is a continuation from my first course titled, "How to Become a World Class SQL Server Performance Tuner." I highly recommend you take that course first.

"I've been working as SQLServer dba for +8 years, but I'm still learning every single day... So, thanks a lot for your courses, videos and tutorials, they really help me."


"He uses clear cut examples to illustrate real world scenarios. Mike is one of the few SQL Server instructors that seems very focused on performance issues and I'm really glad to have stumbled onto his courses."


This course focuses on making SQL Server perform more optimally.

In my first course we laid the groundwork by tuning OUR instances with a top down approach.

WE will continue on that path with this course.

Even though the course has the word “Advanced" in it, there's NOTHING COMPLICATED about taking what's in the course and applying it in the real word.

It's “advanced" in that I'll take more time explaining how to implement these recommendations.

The course is still very much a "buttons and Knobs" course. This simply means WE won't dive too deeply into the minutia. We are still going to focus on items that are easily implemented. Some features will have a level of contention associated with them in the SQL Server community. It will be up to you decide to implement or not.

Additionally, some of the concepts may be new to even more senior level resources so I want to make sure I'll cover the why of the topic as well as the how.

This course will give YOU deeper insight into what it takes to become a skilled PERFORMANCE TUNER.

Once thought of as an art, performance tuning is nothing more than a series of processes that seek to accomplish TWO basic goals.

The FIRST one is to increase the response time for a given transaction or set of transactions.

The SECOND one is reduce resource consumption. On the surface this sounds simple but nothing could be further from the truth.

Take the next step on YOUR performance tuning journey and SIGN UP now.

What are the requirements?
You will need to have SQL Server 2012 or 2014 installed. It can be an express version.
I'll be providing all the scripts and schema for the course. There is no other cost other than the price of the course. Everything else is included or free.
If you're not familiar with SQL Server fundamentals then this course may not be for you. I do appreciate and understand your enthusiasm and promise this course will be here when you're ready.
What am I going to get from this course?
By the end of this course you will have a systematic process approach and all the necessary tools needed to begin your journey as a SQL Server performance tuner.
In this course we will dive a little into performance tuning. We will look caching, hints, filters, trace flags and best coding practices.
In the course we will use statistics and query plans to analyze the effectiveness of our indexes.
What is the target audience?
If you have basic understanding of SQL Server and want to learn how to tune and optimize a SQL Server then this course is for you.
This course is not a beginner’s course. Most of the students will be mid-level to senior SQL Server resources.
You'll need to be familiar with transact SQL and have a strong grasp navigating SQL Server Management Studio.
My first is not required but it will help build a foundation for some of these more complicated topics.

Section 1: Introduction
Lecture 1
What's In This Course?

Lecture 2
Content Warning. Please Don't Skip. Thanks.

Lecture 3
Instructor Introduction.

Lecture 4
Section 1 Summary
Section 2: Tools. These Two Tools Really Help Us Get A Deeper Grain To Our Tuning
Lecture 5
SET Statistics IO and SET Statistics Time On. What Do They Mean?

Lecture 6
Query Plans With Plan Explorer. The Second Best Free Tool On The Market.
Lecture 7
Adding AdventureWorks. It's Free and Easy. We Will Use It In Our Demos.
Lecture 8
Download Content Here
Lecture 9
Summary 2 Summary
Quiz 1
Section 2 Quiz
10 questions
Section 3: Caching Issues
Lecture 10
What is the Buffer Pool?
Lecture 11
High Level Structure of Procedure Cache
Lecture 12
Procedure Cache Architecture
Lecture 13
Simple Parameterization
Lecture 14
Parameter Sniffing Demonstration
Lecture 15
Fixing Parameter Sniffing
Lecture 16
What Are Plan Guides?
Lecture 17
Plan Guides: Demonstration
Lecture 18
Section 3 Summary
Quiz 2
Section 2 Quiz
10 questions
Section 4: Hints and Filters
Lecture 19
Using Index Hints For Comparing Index Effectiveness
Lecture 20
Filtered Indexes
Lecture 21
Filtered Indexes With Sparse Columns
Lecture 22
Filtered Index With Massive Reduction In Logical Reads
Lecture 23
Section 4 Summary
Quiz 3
Section 4 Quiz
10 questions
Section 5: Trace Flags. If You Feel Safe Using Them - And I Do, Then Start Here.
Lecture 24
Trace Flag: -T4199
Lecture 25
Trace Flag: -T1117
Lecture 26
Trace Flag: -T3226
Lecture 27
Section 5 Summary
Quiz 4
Section 5 Quiz
10 questions
Section 6: Real World Coding Best Practices Put To The Test
Lecture 28
Does Using Fewer Columns Really Matter?
Lecture 29
Schema Qualified Objects
Lecture 30
Does SET NOCOUNT ON Really Help?
Lecture 31
Batching Up Deletes And Updates Is A Must
Lecture 32
Stop Sorting In The Database - It's Killing Your Queries
Lecture 33
Fixing Implicit Conversions
Lecture 34
Taking The Load Off The Overburdened tempdb Database
Lecture 35
Section 6 Summary
Quiz 5
Section 6 Quiz
10 questions
Section 7: Miscellaneous
Lecture 36
Enable Auto Update Stats Async
Lecture 37
Section 7 Summary
Section 8: Conclusion
Lecture 38
Thank you! Let's Wrap Up This Course.