SQL SERVER – Cardinality Estimation and Performance – SQL in Sixty Seconds #072

Yesterday I wrote blog post based on my latest Pluralsight course on learning SQL Server 2014. I discussed newly introduced cardinality estimation in SQL Server 2014 and how it improves the performance of the query. The cardinality estimation logic is responsible for quality of query plans and majorly responsible for improving performance for any query. This logic was not updated for quite a while, but in the latest version of SQL Server 2104 this logic is re-designed. The new logic now incorporates various assumptions and algorithms of OLTP and warehousing workload.

I hope my earlier blog post clearly explained how new cardinality estimation logic improves performance. If not, I suggest you watch following quick video where I explain this concept in extremely simple words.

You can download the code used in this course from Simple Demo of New Cardinality Estimation Features of SQL Server 2014.

Action Item

Here are the blog posts I have previously written. You can read it over here:

You can subscribe to my YouTube Channel for frequent updates.

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

About these ads

SQL SERVER – Learn SQL Server 2014 Online in a Day – My Latest Pluralsight Course

Click here watch SQL Server 2014 Administration New Features

SQL Server 2014 was released earlier this year and it has been extremely popular in Microsoft world. Here is the announcement for everyone, who have been asking me to build a tutorial around SQL Server 2014. I have authored latest Pluralsight courses on the subject of SQL Server 2014. This course is 4 hours and 17 minutes long, but the best part is that this course contains all the latest features of SQL Server 2014. I have build this course with the assumption that DBA is familiar with earlier versions of SQL Server and wants to explore and learn new features of SQL Server 2014.

The Challenge I Faced

The biggest challenge I faced was how to come up with the outline for the course. The reason is that there are so many different features introduced in SQL Server 2014 that is will be difficult to cover each of the features in a single course. I wanted to cover the topics which are the most relevant and useful to developers, but in addition I also wanted to cover the topics which may be useful to develop if they know that they exists in the product. I finally decided to depend on blog readers and few of the SQL Experts. I reached out to selected 20 people via email and gave them a list of the topics which I should be covering in this course. They all work in different organizations and have a good understanding about the need of the DBA and Developers.

Based on their feedback, I was able to come up with a very good outline which is currently very popular with Pluralsight library. Lots of people have asked me how was I able to come up with a course content outline so accurately. The credit for the same goes to the developers and DBA, who have voted in the topics and have helped me to build a very solid outline for the course.

Outline of the Course

Here is a quick outline for the course:

  • Introduction
  • Backup Enhancements
  • Security Enhancements
  • Columnstore Enhancements
  • Online Data Operations Enhancements
  • Enhancements with Microsoft Azure
  • SSD Buffer Pool Extensions
  • Resource Governor IO
  • Miscellaneous Features
    • Online Index Rebuilding
    • Live Plans for Long Running Queries
    • Transaction Durability
    • Cardinality Estimation
    • In Memory OLTP Optimization

Well, I had a great fun working on the topics which I have mentioned in the outline. I am very confident that once you start with the course, you will indeed understand how each of the topics builds and presented. I have made sure that each of the topic has a vivid and clear story to begin with. I first explain the story and right after that I explain the concept.

Who Should Attend This Course

Everyone who has basic knowledge of SQL Server and wants to update themselves with SQL Server 2014. They should attend this course. One thing I have made sure that this course is easy to understand and I have decided complex subject into multiple parts. This way the learning is progressive and anyone with a poor knowledge of the subject can have enough time to understand the presented concept.

Screenshot of the Course

Here are few of the screenshot of the courses.

How to Watch Video Course

This course is available at Pluralsight, and you will need a valid login to Pluralsight. If you do not have Pluralsight login, you can quickly sign up for the FREE Trial.

Click here watch SQL Server 2014 Administration New Features

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

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.


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)

SQL SERVER – Finding Object Dependencies in SSMS – SQL in Sixty Seconds #071

While we are doing development, we create and drop objects. We build new things and we need to understand the relationships between database objects when we are doing various activities in SQL Server. Well, it is indeed very hard to know all, the relationship between various objects in SQL Server. However, with the help of SQL Server 2014 Management Studio, you can for sure do the same task very easily.

You have to go the object of which you want to see properties of and right click over it.

Now click over the option “View Dependencies”. It will bring up a screen listing various dependencies.

I hope this is clear enough. If not, I strongly suggest you watch following quick video where I explain this concept in extremely simple words.

Action Item

Here are the blog posts I have previously written on SSMS. You can read it over here:

You can subscribe to my YouTube Channel for frequent updates.

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

SQL SERVER – Difference Between Login and User – SQL in Sixty Seconds #070

“What is the difference between Login and User in SQL Server?”

This is a very common question I often receive. Yesterday when I was browsing Facebook, I once again noticed this question once again asked in SQLBangalore group. My very good friends – Vinod Kumar and Balmukund Lakhani had already answered the question there. However, I every time, I read this question, I realize that not everyone knows the basics of these two concepts. If I have to explain the difference between them, it may take a long time, but I will try to explain it at a very basic level.

Basic Explanation:

SQL Server Login is for Authentication and SQL Server User is for Authorization. Authentication can decide if we have permissions to access the server or not and Authorization decides what are different operations we can do in a database. Login are created at the SQL Server instance level and User is created at SQL Server database level. We can have multiple user from different database connected to a single login to a server.

I hope this is clear enough. If not, I strongly suggest you watch following quick video where I explain this concept in extremely simple words.

Action Item

Here are the blog posts I have previously written on the subject of security. You can read it over here:

You can subscribe to my YouTube Channel for frequent updates.

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

SQL SERVER – Change Fill Factor – SQL in Sixty Seconds #069

“I want to change fill factor to another value – how do I do that?”

The other day I received this question in via Facebook. Fill factor is the value that determines the percentage of space on each leaf-level page to be filled with data. In an SQL Server, the smallest unit is a page, which is made by  Page with size 8K. Every page can store one or more rows based on the size of the row. The default value of the Fill Factor is 100, which is same as value 0. The default Fill Factor (100 or 0) will allow the SQL Server to fill the leaf-level pages of an index with the maximum numbers of the rows it can fit. There will be no or very little empty space left on the page, when the fill factor is 100.

I decided to build a quick video which explains how to change fill factor with the help of T-SQL as well as SSMS.

The code which I have used in this code is over here:

EXEC sys.sp_configure N'fill factor (%)', N'80'

Action Item

Here are the blog posts I have previously written on the subject of Fillfactor. You can read it over here:

You can subscribe to my YouTube Channel for frequent updates.

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

SQL SERVER – Attach or Detach Database – SQL in Sixty Seconds #068

When we have to move a database from one server to another server or when we have to move a database from one file to another file, we commonly use Database Attach or Detach process. I have been doing this for quite a while as well. Recently, when I was visiting an organization I found that in this organization lots of developers are still using an older version of the code to attach the database. I quickly pointed that out to them the new method to attach the database, however it was really interesting to find out that they really did not know that sp_attach_db is now a deprecated method to attach the database. This really made me to do today’s SQL in Sixty Seconds. I demonstrate in this SQL in Sixty Seconds how to attach or detach the database using a new method of attaching database.

The code which I have used in this code is over here:

-- Detach Database
USE [master]
EXEC MASTER.dbo.sp_detach_db @dbname = N'AdventureWorks2014_new'
-- Deprecated Way to Attach Database
USE [master]
EXEC MASTER.dbo.sp_attach_db 'AdventureWorks2014_new',
-- Correct Way to Attach Database
USE [master]
CREATE DATABASE [AdventureWorks2014_new] ON
( FILENAME = 'E:\AdventureWorks2012_Data_new.mdf'),
FILENAME = 'E:\AdventureWorks2012_log_new.ldf')

Here is the question back to you – Do you still use old methods to attach database? If yes, I suggest that you start using the new method onwards.

SQL in Sixty Seconds Video

I have attempted to explain the same subject in simple words over in following video.

Action Item

Here are the blog posts I have previously written on the subject of SA password. You can read it over here:

You can subscribe to my YouTube Channel for frequent updates.

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