MySQL – MySQL Query Optimization and Performance Tuning – Online Video Courses

In year 2013, I had focused on MySQL along with SQL Server in database products. As I learn more and more MySQL, I quickly realized that there is no proper course or structured information for MySQL Performance Tuning.  I had decided to build a course around common MySQL Performance Problems. I have decided to build the performance tuning course in two parts. 1) MySQL Indexing for Performance and 2) MySQL Query Optimization and Performance Tuning. I had released MySQL Indexing for Performance course earlier this year and right before the end of the year 2013 the second course MySQL Query Optimization and Performance Tuning has released as well.

MySQL Query Optimization and Performance Tuning

Performance is one of the most essential aspects of any application. Everyone wants their server to perform optimally and at the best efficiency. In this course we will understand the basics of query optimization and look at practical tips and tricks for performance tuning.

Here is the broad outline of the course MySQL Query Optimization and Performance Tuning.

Introduction

In this module we go over why MySQL Performance Tuning is different from other database products. We also explained with the help of few interesting analogies where exactly we should look into when there is a problem with MySQL Performance.

Optimizing Data Access

We cannot directly jump into performance tuning before taking care of a few of the best practices around MySQL. In this module we discuss few of the best practices which one must implement various techniques explained in this course.

In this module we will see the following topics in depth:

  • Understanding Data Needs
  • Demo: Setup
  • Demo: Best Practices for Data Access
  • Demo: Why SELECT Star (*) is a Bad Idea

Understanding MySQL Query Optimization

It is very important to understand the internals of the car if we are going to participate in a car race. Similarly it is important to understand what is under MySQL Query Engine if we are going to do query optimization with MySQL.

In this module we will see the following topics in depth:

  • Execution Path of a Query
  • Client Protocols
  • Query Cache
  • Parser
  • Preprocessor
  • Query Optimizer
  • Query Optimizer Responsibilities
  • Query Optimizer Limitations
  • Query Execution Engine and Storage
  • Returning Result to Client
  • Additional Notes on Query Optimizer
  • Maximizing Query Optimizer Performance
  • Understanding Query States
  • Demo: Show Full Processlist
  • Understanding Explain Command
  • Demo: Explain Command
  • Demo: Explain Extended Command

Performance Optimization by Practical Query Tuning

In the real world we get thousands of the different scenario where the performance of our query is not upto the mark. In this module we will discuss the most common query patterns which reduces the performance of any query. In this demo oriented session we will also understand how we can tune the performance of bad query with the help of query rewrite and by implementing performance best practices.

In this module we will see the following topics in depth:

  • Demo: Index Used for SELECT clause
  • Demo: One Complex Query vs Multiple Simple Queries
  • Demo: One Complex Query vs Multiple Simple Queries – Part 2
  • Demo: One Complex Query vs Multiple Simple Queries with Index
  • Demo: Table Order in Join Clause – INNER JOIN
  • Demo: Table Order in Join Clause – OUTER JOIN
  • Demo: Most Optimal Choice – Subquery vs Exists vs Joins
  • Demo: Most Optimal Choice – Subquery vs Exists vs Joins – Part 2
  • Demo: Tuning Aggregate Function
  • Demo: Optimizing Group By Clause
  • Demo: Optimizing Paging with LIMIT Clause
  • Demo: Impact on Performance of UNION and UNION ALL
  • Demo: Index and Not Equal to Operator

Best Practices

In this final module we sum up the course in the format of Best Practices. Though the length of this module is short, it contains some really good gem inside it.

You need a valid Pluralsight subscription to watch the courses. You can sign up for free trial as well.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

Developer Training – 6 Online Courses to Learn SQL Server, MySQL and Technology

Video courses are the next big thing and I am so happy that I have so far authored 6 different video courses with Pluralsight. Here is the list of the courses. I have listed all of my video courses over here.

Note: If you click on the courses and it does not open, you need to login to Pluralsight with a valid username and password or sign up for a FREE trial.

Please leave a comment with your favorite course in the comment section. Random 10 winners will get surprise gift via email. Bonus: If you list your favorite module from the course site.

SQL Server Performance: Introduction to Query Tuning

SQL Server performance tuning is an in-depth topic, and an art to master. A key component of overall application performance tuning is query tuning. Writing queries in an efficient manner, and making sure they execute in the most optimal way possible, is always a challenge. The basics revolve around the details of how SQL Server carries out query execution, so the optimizations explored in this course follow along the same lines.
Click to View Course

SQL Server Performance: Indexing Basics

Indexes are the most crucial objects of the database. They are the first stop for any DBA and Developer when it is about performance tuning. There is a good side as well evil side of the indexes. To master the art of performance tuning one has to understand the fundamentals of the indexes and the best practices associated with the same. This course is for every DBA and Developer who deals with performance tuning and wants to use indexes to improve the performance of the server.
Click to View Course

SQL Server Questions and Answers

This course is designed to help you better understand how to use SQL Server effectively. The course presents many of the common misconceptions about SQL Server, and then carefully debunks those misconceptions with clear explanations and short but compelling demos, showing you how SQL Server really works. This course is for anyone working with SQL Server databases who wants to improve her knowledge and understanding of this complex platform.
Click to View Course

MySQL Fundamentals

MySQL is a popular choice of database for use in web applications, and is a central component of the widely used LAMP open source web application software stack. This course covers the fundamentals of MySQL, including how to install MySQL as well as written basic data retrieval and data modification queries.
Click to View Course

Building a Successful Blog

Expressing yourself is the most common behavior of humans. Blogging has made easy to express yourself. Just like a letter or book has a structure and formula, blogging also has structure and formula. In this introductory course on blogging we will go over a few of the basics of blogging and show the way to get started with blogging immediately. If you already have a blog, this course will be even more relevant as this will discuss many of the common questions and issue you face in your blogging routine.
Click to View Course

Introduction to ColdFusion

ColdFusion is rapid web application development platform. In this course you will learn the basics of how to use ColdFusion platform and rapidly develop web sites. The course begins with learning basics of ColdFusion Markup Language and moves to common development language practices. From there we move to frequent database operations and advanced concepts of Forms, Sessions and Cookies. The last module sums up all the concepts covered in the course with sample application.
Click to View Course

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQLAuthority News – Advantages of Distance Learning

Distance education is extremely popular – almost overnight, it seems.  Almost everyone has taken an online course, or knows someone who has, or is considering joining an online school.  There are many advantages and disadvantages to attending an online school – but the same can be said of attending a physical school!  Let’s take a look at the top reasons to use distance education.

1) Flexibility.  Physical universities are usually willing to make some concessions to student – like night classes, study hours, and online networks.  However, nothing is going to beat the flexibility of distance education.  You can attend classes and take notes anytime, anywhere, wearing anything you’d like!

2) Affordability.  We don’t need to get into hard numbers to understand how an expensive university can be.  Students are taking on more and more debt just to get an education.  Many of these fees pay for room, board, and facilities.   Distance education cuts out all these costs, and makes attending school much more affordable for the average student.

3) Try before you buy.  Did you know that the average college student changes his or her major 10 times before they graduate?  You can imagine that this kind of indecision plays a huge part in WHEN you graduate – not being able to make up your mind can cost you big bucks if you have to stay in school for extra years!  Distance education allows you to take different classes from a wide range of disciplines.  Do you want to study forensic science or English literature?  Now you don’t have to pay for classes you can’t afford just to find out.

4) Pace yourself.  Some students struggle in a traditional classroom setting – classes can be taught too fast, too slow, or there are too many distractions.  Distance education allows mature students to set the pace themselves.  They can rewatch lectures they didn’t catch the first time, or go through classes quickly if they are already familiar with the material – cutting out the chance of burning out or getting bored.

5) Lifelong learning.  Maybe you already have a degree, but would like to learn more about your field, or a related field, or maybe even about something completely unrelated – just because you are curious!  Distance education allows you to learn whatever you want ,whenever you want (and yes, wearing anything you’d like!).

6) Attend whatever college you want.  Because of the popularity of distance education, physical campuses are getting in on the game by offering online courses – often just uploaded versions of classes already taught at their campus.  Ever wanted to attend Harvard, but knew you couldn’t get in?  Take a class online!  Of course, you probably should not attempt to lie and say you have a Harvard degree, but Ivy League colleges are prestigious because they are the best in their field – take advantage of the best by taking an online course!

I am a big believer in continuing education, whether it is online courses, returning to school, or even take informal classes online.  Distance education can be a great way to accomplish these goals and become a lifelong learner. My friends at provides training through virtual classrooms for students who want to avoid travelling. Distance learning course allows IT aspirants to connect with trainers using the internet.  I encourage everyone to check it out!

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQLAuthority News – Koenig Solutions Invests into World’s Largest IT Training Center

Here is very interesting news from my friends at Koenig Solutions. They have opened a new IT training center which is indeed the state of the art facility. Here is the quick note of what I have observed about their new facility. This is excellent news for me as the leadership team of the Koenig and I go a long way back. It is fantastic to see your friends growing and becoming the largest IT Training Center.

Koenig Solutions houses the world’s largest IT training center in New Delhi. It comprises over 50 classrooms for imparting training as well as more than 26 hi-tech testing stations. There are also four other campuses throughout India, as well as a center in Dubai, to best suit all the students, no matter where they are.  Every campus boasts the same high standards, facilities, and amenities.  Their original facility in New Delhi remains the most popular classroom for “destination learning.”

This modern facility also boasts of an in-house coffee shop and travel desk to cater to all possible needs of our students, in addition to their educational requirements.  The facility doubles as a classroom and as a world-class hotel, with lodging for 25 students.  Every room features hot and cold running water, satellite television, and LCD screens.  There are also free laundry services, a free gymnasium, and free yoga classes.  At Koenig you can exercise your mind and your body!

Koenig’s Delhi Centre also garners huge credibility based on the fact that it is the largest Prometric and Vue testing center in India.  Having both certification tests available in one spot is extremely helpful because the two providers offer certifications in different technology companies.  For example, Microsoft uses only Prometric, while Cisco uses only VUE.  If you prefer one certification or another, or would like to try both, the Dehli Center is equipped to meet all your needs.

All these benefits make our Delhi Center irresistibly attractive to those who are looking for great quality training coupled with excellent infrastructure.  Additionally, fun day trips are within easy reach via this campus.  Visit Agra, location of the Taj Mahal, one your time off.  Combine business with pleasure, and learn while exploring amazing historical sites.

Other facilities:

Library: Access to a vast and rich study material for students who constantly like to expand their knowledge base.  Traditional text books are obviously available in the library, as well as online resources, so that you can better research any topic that catches your attention.

Prayer Room: Meditate and find peace in the prayer room whenever you feel like reconnecting with your Higher Self.  It has been repeatedly proven that after a day filled with study, exercise, and physical or mental exertion, the best way to both recover and recall the material is to spend some quiet time with yourself – not necessarily thinking about the subject, but letting your subconscious do the work for you.  Give the Prayer Room a shot.  You are guaranteed to emerge more relaxed.

Reflexology: Give those aching muscles some rest with a reflexology session and get back to study lessons feeling rejuvenated.  Most exercise programs recommend a full-body workout – it is pointless to exercise only the arms, or only the legs.  It is the same with learning.  Exercising the muscle that is your brain will leave the rest of your body “left out.”  We make sure to provide opportunities for our students to make the most of their training.

If you are traveling to Delhi – I suggest to stop by Koenig Solutions‘s IT center and check out their ultra modern facilities.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQLAuthority News – Excellent Learning Experience at SQLskills Immersion Events

As many of you know, I attended the Immersion Event at SQLskills learning center in early Feb. I consider myself a SQL professional, but there is no age limit for being a student. One can learn forever, there is always something new to pick up – particularly when you go to a SQLskills event. They are going to teach you something fantastic, something you didn’t know before. It is quite possible to know a lot about a concept, but when we learn about the right ways and wrong ways to do things, and see all the mistakes we made without knowing it, we will learn how to do our jobs better.

The quality of learning at a SQLskills event is amazing. The instructors for Immersion 1 were Paul and Kimberly, and for Immersion 2 there was Paul, Kimberly, Jonathan and Joseph. They take turns teaching, and at every interval there is a new instructor with a new style and new material. It is both stimulating and relaxing, because you are constantly learning, constantly challenged, and there is a new instructor always coming up next. You never get bored. It was also highly interactive with the audience as well.

At both Immersions, questions are encouraged, and the instructors are not shy about answering any and all questions. They encourage feedback and engage with the students. There is always plenty of time allowed for questions and answers, so the sessions are not rushed. The instructor comes at breakfast and stays the whole day, even through lunch. They make sure everyone has had a chance to ask questions and get answers, and they don’t leave the room until the job is done.

The attention to detail is amazing. Each session is right on time, nothing is delayed, nothing is late, there are frequent breaks and meals are right on time. You are totally immersed in learning. They even pay special attention to dietary restrictions. For example, I am a vegetarian and they always made sure I was provided with the correct meals and that those around me with other dietary requirements were also served probably. I was especially a big fan of the desserts, which were served every day at 4 o’clock. They were served closer to dinner time because lunches were so filling no one had room for desert! There were also plenty of drinks, munchies, and coffee available at all times, so that if your stomach growling was getting in the way of your learning, you didn’t have to worry, there was a snack available.

Immersion Event Completion Certification

The best part was that the instructors never tried to hide their information. Sometimes at training events the trainer is reluctant to share slides or give handouts. However, at SQLskills, every slide, demo and diagram were delivered to the students. Before you leave you are sent an email with all the details. I had a great time, those 15 days were tremendous. I will never forget them. If you love SQL you need to attend an event at least once – you can’t afford NOT to.

Additionally, if you are a Pluralsight subscriber, you can also watch the SQL courses authored by SQLskills trainers.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – 3 Online SQL Courses at Pluralsight and Free Learning Resources

Usain Bolt is an inspiration for all. He broke his own record multiple times because he wanted to do better! Read more about him on wikipedia. He is great and indeed fastest man on the planet.

Usain Bolt - World’s Fastest Man

Usain Bolt – World’s Fastest Man

“Can you teach me SQL Server Performance Tuning?”

This is one of the most popular questions which I receive all the time. The answer is YES.

I would love to do performance tuning training for anyone, anywhere.  It is my favorite thing to do, and it is my favorite thing to train others in.  If possible, I would love to do training 24 hours a day, 7 days a week, 365 days a year.  To me, it doesn’t feel like a job.

Of course, as much as I would love to do performance tuning 24/7/365, obviously I am just one human being and can only be in one place at one time.  It is also very difficult to train more than one person at a time, and it is difficult to train two or more people at a time, especially when the two people are at different levels.  I am also limited by geography.  I live in India, and adjust to my own time zone.  Trying to teach a live course from India to someone whose time zone is 12 or more hours off of mine is very difficult.  If I am trying to teach at 2 am, I am sure I am not at my best!

There was only one solution to scale – Online Trainings. I have built 3 different courses on SQL Server Performance Tuning with Pluralsight. Now I have no problem – I am 100% scalable and available 24/7 and 365. You can make me say the same things again and again till you find it right. I am in your mobile, PC as well as on XBOX. This is why I am such a big fan of online courses.  I have recorded many performance tuning classes and you can easily access them online, at your own time.  And don’t think that just because these aren’t live classes you won’t be able to get any feedback from me.  I encourage all my viewers to go ahead and ask me questions by e-mail, Twitter, Facebook, or whatever way you can get a hold of me.

Here are details of three of my courses with Pluralsight. I suggest you go over the description of the course. As an author of the course, I have few FREE codes for watching the free courses. Please leave a comment with your valid email address, I will send a few of them to random winners.

SQL Server Performance: Introduction to Query Tuning 

SQL Server performance tuning is an art to master – for developers and DBAs alike. This course takes a systematic approach to planning, analyzing, debugging and troubleshooting common query-related performance problems. This includes an introduction to understanding execution plans inside SQL Server.

In this almost four hour course we cover following important concepts.

  • Introduction 10:22
  • Execution Plan Basics 45:59
  • Essential Indexing Techniques 20:19
  • Query Design for Performance 50:16
  • Performance Tuning Tools 01:15:14
  • Tips and Tricks 25:53
  • Checklist: Performance Tuning 07:13

The duration of each module is mentioned besides the name of the module.

SQL Server Performance: Indexing Basics

This course teaches you how to master the art of performance tuning SQL Server by better understanding indexes.

In this almost two hour course we cover following important concepts.

  • Introduction 02:03
  • Fundamentals of Indexing 22:21
  • Practical Indexing Implementation Techniques 37:25
  • Index Maintenance 16:33
  • Introduction to ColumnstoreIndex 08:06
  • Indexing Practical Performance Tips and Tricks 24:56
  • Checklist : Index and Performance 07:29

The duration of each module is mentioned besides the name of the module.

SQL Server Questions and Answers

This course is designed to help you better understand how to use SQL Server effectively. The course presents many of the common misconceptions about SQL Server, and then carefully debunks those misconceptions with clear explanations and short but compelling demos, showing you how SQL Server really works.

In this almost 2 hours and 15 minutes course we cover following important concepts.

  • Introduction 00:54
  • Retrieving IDENTITY value using @@IDENTITY 08:38
  • Concepts Related to Identity Values 04:15
  • Difference between WHERE and HAVING 05:52
  • Order in WHERE clause 07:29
  • Concepts Around Temporary Tables and Table Variables 09:03
  • Are stored procedures pre-compiled? 05:09
  • UNIQUE INDEX and NULLs problem 06:40
  • DELETE VS TRUNCATE 06:07
  • Locks and Duration of Transactions 15:11
  • Nested Transaction and Rollback 09:16
  • Understanding Date/Time Datatypes 07:40
  • Differences between VARCHAR and NVARCHAR datatypes 06:38
  • Precedence of DENY and GRANT security permissions 05:29
  • Identify Blocking Process 06:37
  • NULLS usage with Dynamic SQL 08:03
  • Appendix Tips and Tricks with Tools 20:44

The duration of each module is mentioned besides the name of the module.

SQL in Sixty Seconds

You will have to login and to get subscribed to the courses to view them. Here are my free video learning resources SQL in Sixty Seconds. These are 60 second video which I have built on various subjects related to SQL Server. Do let me know what you think about them?

Here are three of my latest videos:

You can watch and learn at your own pace.  Then you can easily ask me any questions you have.  E-mail is easiest, but for really tough questions I’m willing to talk on Skype, Gtalk, or even Facebook chat.  Please do watch and then talk with me, I am always available on the internet!

Here is the video of the world’s fastest man.Usain St. Leo Bolt inspires us that we all do better than best. We can go the next level of our own record. We all can improve if we have a will and dedication.  Watch the video from 5:00 mark.

http://youtu.be/2O7K-8G2nwU?t=5m3s

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Beginning of SQL Server Architecture – Terminology – Guest Post

SQL Server Architecture is a very deep subject. Covering it in a single post is an almost impossible task. However, this subject is very popular topic among beginners and advanced users.  I have requested my friend Anil Kumar who is expert in SQL Domain to help me write  a simple post about Beginning SQL Server Architecture. As stated earlier this subject is very deep subject and in this first article series he has covered basic terminologies. In future article he will explore the subject further down. Anil Kumar Yadav is Trainer, SQL Domain, Koenig Solutions. Koenig is a premier IT training firm that provides several IT certifications, such as Oracle 11g, Server+, RHCA, SQL Server Training, Prince2 Foundation etc.


In this Article we will discuss about MS SQL Server architecture.

The major components of SQL Server are:

  1. Relational Engine
  2. Storage Engine
  3. SQL OS

Now we will discuss and understand each one of them.

1) Relational Engine: Also called as the query processor, Relational Engine includes the components of SQL Server that determine what your query exactly needs to do and the best way to do it. It manages the execution of queries as it requests data from the storage engine and processes the results returned.

Different Tasks of Relational Engine:

  1. Query Processing
  2. Memory Management
  3. Thread and Task Management
  4. Buffer Management
  5. Distributed Query Processing

2) Storage Engine: Storage Engine is responsible for storage and retrieval of the data on to the storage system (Disk, SAN etc.). to understand more, let’s focus on the concepts.

When we talk about any database in SQL server, there are 2 types of files that are created at the disk level – Data file and Log file. Data file physically stores the data in data pages. Log files that are also known as write ahead logs, are used for storing transactions performed on the database.

Let’s understand data file and log file in more details:

Data FileData File stores data in the form of Data Page (8KB) and these data pages are logically organized in extents.

Extents: Extents are logical units in the database. They are a combination of 8 data pages i.e. 64 KB forms an extent. Extents can be of two types, Mixed and Uniform. Mixed extents hold different types of pages like index, system, data etc (multiple objects). On the other hand, Uniform extents are dedicated to only one type (object).

Pages: As we should know what type of data pages can be stored in SQL Server, below mentioned are some of them:

  • Data Page: It holds the data entered by the user but not the data which is of type text, ntext, nvarchar(max), varchar(max), varbinary(max), image and xml data.
  • Index: It stores the index entries.
  • Text/Image: It stores LOB ( Large Object data) like text, ntext, varchar(max), nvarchar(max),  varbinary(max), image and xml data.
  • GAM & SGAM (Global Allocation Map & Shared Global Allocation Map): They are used for saving information related to the allocation of extents.
  • PFS (Page Free Space): Information related to page allocation and unused space available on pages.
  • IAM (Index Allocation Map): Information pertaining to extents that are used by a table or index per allocation unit.
  • BCM (Bulk Changed Map): Keeps information about the extents changed in a Bulk Operation.
  • DCM (Differential Change Map): This is the information of extents that have modified since the last BACKUP DATABASE statement as per allocation unit.

Log File: It also known as write ahead log. It stores modification to the database (DML and DDL).

  • Sufficient information is logged to be able to:
    • Roll back transactions if requested
    • Recover the database in case of failure
    • Write Ahead Logging is used to create log entries
      • Transaction logs are written in chronological order in a circular way
      • Truncation policy for logs is based on the recovery model

SQL OS: This lies between the host machine (Windows OS) and SQL Server. All the activities performed on database engine are taken care of by SQL OS. It is a highly configurable operating system with powerful API (application programming interface), enabling automatic locality and advanced parallelism. SQL OS provides various operating system services, such as memory management deals with buffer pool, log buffer and deadlock detection using the blocking and locking structure. Other services include exception handling, hosting for external components like Common Language Runtime, CLR etc.

I guess this brief article gives you an idea about the various terminologies used related to SQL Server Architecture. In future articles we will explore them further.

Guest Author 

The author of the article is Anil Kumar Yadav is Trainer, SQL Domain, Koenig Solutions. Koenig is a premier IT training firm that provides several IT certifications, such as Oracle 11g, Server+, RHCA, SQL Server Training, Prince2 Foundation etc.

Reference: Pinal Dave (http://blog.sqlauthority.com)