SQL Authority News – Play by Play with Pinal Dave – A Birthday Gift

Today is my birthday.

Personal Note

When I was young, I was always looking forward to my birthday as on this day, I used to get gifts from everybody.

Now when I am getting old on each of my birthday, I have almost same feeling but the direction is different. Now on each of my birthday, I feel like giving gifts to everybody. I have received lots of support, love and respect from everybody; and now I must return it back.Well, on this birthday, I have very unique gifts for everybody – my latest course on SQL Server.

How I Tune Performance

I often get questions where I am asked how do I work on a normal day. I am often asked that how do I work when I have performance tuning project is assigned to me. Lots of people have expressed their desire that they want me to explain and demonstrate my own method of solving performance problem when I am facing real world problem. It is a pretty difficult task as in the real world, nothing goes as planned and usually planned demonstrations have no place there.

The real world, demands real solutions and in a timely fashion. If a consultant goes to industry and does not demonstrate his/her capabilities in very first few minutes, it does not matter how much fame he/she is, the door is shown to them eventually. It is true and in my early career, I have faced it quite commonly. I have learned the trick to be honest from the start and request absolutely transparent communication from the organization where I am to consult.

Play by Play

Play by Play is a very unique setup. It is not planned and it is a step by step course. It is like a reality show – a very real encounter to the problem and real problem solving approach. I had a great time doing this course. Geoffrey Grosenbach (VP of Pluralsight) sits down with me to see what a SQL Server Admin does in the real world. This Play-by-Play focuses on SQL Server performance tuning and I go over optimizing queries and fine-tuning the server.

The table of content of this course is very simple.

Introduction

In the introduction I explained my basic strategies when I am approached by a customer for performance tuning.

Basic Information Gathering

In this module I explain how I do gather various information for performance tuning project. It is very crucial to demonstrate to customers for consultant his capability of solving problem. I attempt to resolve a small problem which gives a big positive impact on performance, consultant have to gather proper information from the start. I demonstrate in this module, how one can collect all the important performance tuning metrics.

Removing Performance Bottleneck

In this module, I build upon the previous module’s statistics collected. I analysis various performance tuning measures and immediately start implementing various tweaks on the performance, which will start improving the performance of my server. This is a very effective method and it gives immediate return of efforts.

Index Optimization

Indexes are considered as a silver bullet for performance tuning. However, it is not true always there are plenty of examples where indexes even performs worst after implemented. The key is to understand a few of the basic properties of the index and implement the right things at the right time. In this module, I describe in detail how to do index optimizations and what are right and wrong with Index. If you are a DBA or developer, and if your application is running slow – this is must attend module for you. I have some really interesting stories to tell as well.

Optimize Query with Rewrite

Every problem has more than one solution, in this module we will see another very famous, but hard to master skills for performance tuning – Query Rewrite. There are few do’s and don’ts for any query rewrites. I take a very simple example and demonstrate how query rewrite can improve the performance of the query at many folds. I also share some real world funny stories in this module.

This course is hosted at Pluralsight. You will need a valid login for Pluralsight to watch  Play by Play: Pinal Dave course. You can also sign up for FREE Trial of Pluralsight to watch this course.

As today is my birthday – I will give 10 people (randomly) who will express their desire to learn this course, a free code. Please leave your comment and I will send you free code to watch this course for free.

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

About these ads

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)

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)