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

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'
GO
RECONFIGURE WITH OVERRIDE
GO

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]
GO
EXEC MASTER.dbo.sp_detach_db @dbname = N'AdventureWorks2014_new'
GO
-- Deprecated Way to Attach Database
USE [master]
GO
EXEC MASTER.dbo.sp_attach_db 'AdventureWorks2014_new',
'E:\AdventureWorks2012_Data_new.mdf',
'E:\AdventureWorks2012_log_new.ldf'
GO
-- Correct Way to Attach Database
USE [master]
GO
CREATE DATABASE [AdventureWorks2014_new] ON
( FILENAME = 'E:\AdventureWorks2012_Data_new.mdf'),
(
FILENAME = 'E:\AdventureWorks2012_log_new.ldf')
FOR ATTACH
GO

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)

SQL SERVER – Contest Winner – What Next on SQL in Sixty Seconds – Poll Result

A few days ago, I have asked a question on this blog. The question was – What would you like to see in the next episodes of SQL in Sixty Seconds. The poll is still active and posted over here: SQL SERVER – Poll – What would you love to see in SQL in Sixty Seconds? The contest was to suggest the next item of SQL in Sixty Seconds and vote for the your choice of subject. There have been plenty of votes to this contest, however, there were only 4 comments to this blog post. Hence, selecting a winner was very simple.

Result of Poll

It is very clear from result, most of the people would like to watch Performance Tuning subjects. I will continue to build video on this subject in future.

Contest Winner

Now is the time for the winner of the contest, who left comments on the blog. The winner is Raelyard. Here is the comment which he has left on the blog.

raelyard please reach out to me via email and I will send you the gift card.

Current Contest

Here is the contest which is currently running on this blog. You can take part in the contest and can win a Drone.

SQL in Sixty Seconds

Here are few of the episodes of SQL in Sixty Seconds, which you can watch. We will have more episodes of SQL in Sixty Seconds from next week which are focused on performance.

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

SQL SERVER – Copy Database – SQL in Sixty Seconds #067

There are multiple reasons why a user may want to make a copy of the database. Sometimes a user wants to copy the database to the same server and sometime wants to copy the database on a different server. The important point is that DBA and Developer may want copies of their database for various purposes. I copy my database for backup purpose. However, when we hear coping database – the very first thought which comes to our mind is – Backup and Restore or Attach and Detach. Both of these processes have their own advantage and disadvantages. The matter of the fact, those methods is much efficient and recommended methods.

However, if you just want to copy your database as it is and do not want to go for advanced feature. You can just use the copy feature of the SQL Server. Here are the settings, which you can use to copy the database.

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)