SQL SERVER – Q and A from Facebook Page

Social media has changed everything, there were times when we used to send emails but nowadays things have changed. We all have many different ways to connect with each other. Here are a few questions which I have received on the Facebook Page in the month of the march and I am sharing a few of them here.

Q: How to change collation of the our entire server? (link)
A: You will have to install your entire SQL Server to change collation of the server.

Q: How to recover system databases from suspect mode without having any backups? (link)
A: You need backup to recover a corrupt system database.

Q: How to retrieve last 5 inserted rows from a table? (link)
A: You can retrieve the data based on date field or identity field. If you do not have either, no luck. (blog)

Q: What does *= operator does for SQL Query? (link)
A: It is an old way of using left join, now it is deprecated so avoid using it.

Q: How to reduce CXPACKET and PAGEIOLATCH_XX waits in SQL Server database? (link)
A: CXPacket is the effect of parallelism, which may or may not be a problem. PageIOLatch indicates a problem with the slowness of the disk subsystem. This would very well cause CXPacket as well. (blog, blog)

 Well, these are the five questions which I have received on Facebook. If you have any question, let us connect on Facebook.

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

About these ads

SQL SERVER – SQL Server 2014 Developer Training Kit and Sample Databases

SQL Server 2014 RTM is around the corner (2014). There is plenty of the interest in the SQL Server 2014 in recent time. One of the most frequently asked questions is about learning resources of SQL Server 2014. Everybody wants to learn SQL Server 2014 but there is not enough learning material available currently in the outside world.

Microsoft has released two important resources around it.

SQL Server 2014 Developer Training Kit

The SQL Server 2014 Developer Training Kit includes technical content designed to help you learn how to develop SQL Server 2014 databases, BI Solutions, and gain insights into the new cloud capabilities to simplify cloud adoption for your SQL databases and help you unlock new hybrid scenarios.

SQL Server 2014 Developer Training Kit Sample Databases

This download contains several sample databases for the SQL Server 2014 Developer Training Kit which showcase many of the new and improved features of SQL Server 2014, including Clustered Columnstore, Online Operations, and Resource Governor IO.

Here are few of the key features in SQL Server 2014

  •  Resource Governor IO
  • Online Operations
  • Updatable Columnstore Index
  • In Memory OLTP Engine
  • Enhencements in SSDT and BI tools
  • Power BI Integration

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

SQLAuthority News – Sign up for SQL Authority Low Frequency Newsletter

One More Newsletter!

You might already be receiving our emails in your mailbox, so why should you sign up for SQL Authority Newsletter (Sign-up).

Well, a great question. Let me answer this first.

Why should you sign up?

First of all – it is not going to be a daily email from the blog which you are receiving. It is going to be a very informal conversation between us. I blog about various details related to technology, but there are few things, I just want to keep exclusively for newsletter. For example, if I want to discuss the book which I am reading or movie I have watched or database news which I have heard from industry or just something really cool which I do not feel appropriate for the blog, I am going to include them in my newsletter. I am not going to send your summary of the week or best of the monthly newsletter, if I am going to send your newsletter, it is going to contain something unique and useful.

What do I mean by Low Frequency Newsletter?

Well, there is no set frequency of this newsletter. I am not going to send this every Tuesday or Thursday. I am going to send it when I have something unique to say and newsletter is the most appropriate way to send email. You may receive this email once a month or maybe once every 2 weeks – there is no set frequency.

What will this newsletter contain primarily?

Again, there is no set agenda for the newsletter but I promise that it is going to be fun stuff and interesting. This blog is primarily for talking technology, but there are so many different elements of life I face every day and I hardly get time to share with everybody. I do quite a bit sharing on Facebook (if you have not liked the page, do it now), but again, there are few things, which just makes sense in the newsletter.

How do I sign up for the newsletter?

Click here to sign up for the newsletter. It will send you a confirmation email, please follow the link in the confirmation email to activate your subscription to the newsletter.

What if I want to unsubscribe to the newsletter?

In the newsletter, there is a link to unsubscribe it. No hard feelings – it is your mailbox and you decide what goes there.

Final Note

If you sign up for the newsletter before the end of this month, you will be entered to win Hexbug Hive (India) or  Hexbug Hive (USA). The winner will be announced in the newsletter only. If you have never played with Hexbug and Hexbug Hive, you may just want to sign up for the newsletter for the chances to win them. It is pretty fun game and people at every age likes it. See video over here.

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

SQL SERVER – SSIS Execution Control Using Precedence Constraints – Notes from the Field #021

[Notes from Pinal]: Lots of people think that SSIS is all about arranging various operations together in one logical flow. Well, the understanding is absolutely correct, but the implementation of the same is not as easy as it seems. Lots of people start with lots of enthusiasm, but when it is about building a control flow, which addresses all the necessary elements of the package execution they face issues with the order of the tasks. This is a very critical subject and it requires some expert advice.

Linchpin People are database coaches and wellness experts for a data driven world. In this 21th episode of the Notes from the Fields series database expert Tim Mitchell (partner at Linchpin People) shares very interesting conversation related to how to control the flow of the program using SSIS package execution.


In this post, I’m going to review the essentials of precedence constraints in SQL Server Integration Services, and will demonstrate how these can be used to precisely control the flow of the program during the SSIS package execution.

In a control flow for SQL Server Integration Services, the “lines” we commonly use to connect tasks to each other are actually smart controls.  These precedence constraints allow the ETL developer to customize the program flow from one task to another.  The most common use of precedence constraints is to simply connect two tasks with the default constraint, which is the success constraint.  In this usage, the downstream task will be executed if and only if the execution of the upstream task is successful.  As shown in the example below, the task DFT Write data to output table will be executed if the preceding task named SQL Truncate output table is successfully executed.  Similarly, the final task in the chain, named SQL Log row count to audit tbl, will be executed only if the preceding data flow task was successful.

The green lines connecting these tasks are the precedence constraints.  In the above example, we know that these are success precedence constraints by their color.

Using precedence constraints for success operations is not the only option we have.  Because these are smart controls, we can configure them in a variety of ways for maximum flexibility.  An example of this would be use a failure precedence constraint to code for the possibility of failure.  In this scenario, we could create one or more tasks that would be executed in the event of a failure within a package.  As shown below, we have a snippet of a package showing a failure path from the data flow by way of a failure precedence constraint.

In this example, we connect a precedence constraint from the data flow task to the task named SQL Remove data from partial load, but instead of using the default setting, we reconfigure this as a failure constraint (shown with the “Failure” label and red color) to redirect the execution flow to that task in the event that the data flow execution fails.  Downstream from there, we have yet another precedence constraint configured as a completion constraint (indicated by the label and the black color).  This constraint indicates that the task SQL Log failure event to event log will be executed whenever the upstream task is finished executing, irrespective of whether the result of that execution was success or failure.

To configure a precedence constraint to a setting other than the default value of success, simply double-click on the precedence constraint and set the appropriate setting, the options for which are shown below.

As shown, we can choose Success (the default setting), Failure, or Completion.

Note that there are a few other options as well.  Above the selection for the type of constraint, you’ll see that we can also choose the evaluation operation.  The default setting is Constraint, which means that the precedence constraint uses only the success/failure/completion results as described above.  However, we also have some other options.  We can use an SSIS expression along with, or perhaps instead of, the execution status evaluation to determine how to control the execution flow.

Additionally, we have the ability to control the behavior when multiple constraints are used.  By default, if multiple constraints are connected to a single downstream task, then all of those constraints must evaluate true for said downstream task to execute.  However, we can override that default behavior by setting this value for multiple constraints to Logical OR, indicating that the downstream task will be executed if any of the connected constraints evaluates true.

As shown in the example below, we have modified the original example, changing both the evaluation operation as well as the multiple constraint behavior.  On the constraint between the data flow task and the data deletion task, we add an SSIS expression to check the row count check to confirm whether any rows were loaded as part of the failed execution (since we wouldn’t need to run the delete operation if no rows were loaded).  Further, we’ve added a completion constraint between the data flow task and the task named SQL Log failure event to event log and set the multiple constraint behavior to Logical OR to make sure we log the failure whether or not the delete operation occurred.

We get visual reminders of these settings as well.  The function indicator (which looks like an italicized fx) appears on the precedence constraint on which we are using the expression.  For the Logical OR setting, the constraints will appear as dashed lines rather than solid lines.

Precedence constraints are much more than just lines drawn between two tasks.  In fact, these are powerful and flexible decision tools that allow the ETL developer a great deal of control over the flow of the SSIS execution.

If you want me to take a look at your server and its settings, or if your server is facing any issue we can Fix Your SQL Server.

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

SQL SERVER – Does Use of CTE Change the Order of Join in Inner Join

I just had an interesting conversation the day before when I was discussing about Join Order in one of my recent presentations.

The comment which triggered all the conversation was “If I want to change the order of how tables are joined in SQL Server, I prefer to use CTE instead of Join Orders”. 

During the conversation user was suggesting that he wanted his tables to be joined in certain ways in SQL Server but SQL Server Engine Optimizer was re-organizing everything to give the most optimal performance. His needs were to join tables in certain ways and did not care about the performance. To join tables as per his need he had to use a FORCE ORDER hint of the table. However, he recently learned about CTE and believed that CTE will help him to achieve his tasks without the help of FORCE ORDER hint.

He believed that as CTE syntax is constructed before the SELECT statement SQL Server will build a result set instead first and will use the same resultset to join the SELECT statement following the CTE. Well, the casual conversation converted to debate and it was getting very difficult as everybody started to express their opinion with very loud voice. Finally, we decided to run an example on my machine.

NOTE: The example uses INNER JOIN only and the conclusion applies to only INNER JOIN. In the case of OUTER JOIN there is an entire different story, we will cover that in future blog posts.

We created two examples and executed them one by one. Once we executed them, we compared their resultant - they were identical. Right after that we compared the order of the join for both the example.

Example 1: Without CTE

USE AdventureWorks2012
GO
SELECT c.CustomerID, p.BusinessEntityID
FROM Person.Person p
INNER JOIN Person.EmailAddress ea ON ea.BusinessEntityID = p.BusinessEntityID
INNER JOIN Person.PersonPhone ph ON ph.BusinessEntityID = p.BusinessEntityID
INNER JOIN Sales.PersonCreditCard pc ON pc.BusinessEntityID = p.BusinessEntityID
INNER JOIN Sales.Customer c ON c.CustomerID = p.BusinessEntityID;

Execution Plan:

Example 2: With CTE

USE AdventureWorks2012
GO
WITH MyCTE AS
(SELECT c.CustomerID
FROM Sales.PersonCreditCard pc
INNER JOIN Sales.Customer c ON c.CustomerID = pc.BusinessEntityID)
SELECT cte.CustomerID, p.BusinessEntityID
FROM Person.Person p
INNER JOIN Person.EmailAddress ea ON ea.BusinessEntityID = p.BusinessEntityID
INNER JOIN MyCTE cte ON cte.CustomerID = p.BusinessEntityID
INNER JOIN Person.PersonPhone ph ON ph.BusinessEntityID = p.BusinessEntityID;

Execution Plan:

When we compare both the execution plan, it is very clear that there is no change in the order of the table orders. Both the statements have a very similar execution plan and tables were joined in exactly the same order. The matter of the fact in the case of CTE, it was interesting to see that SQL Server started to join very first one table from CTE and another from SELECT statements and continued to build the entire execution plan.

The reason for this is very simple – CTE is not temporary table kind of object or feature. It is just an expression to represent your SELECT statement in such a way that it increases your readability and usability. They do not execute before the regular SELECT statement or build a result set before hand. It actually executes just like regular SELECT statement all together.

In summary: CTE does not impact Table Join Order when all Joins in the query are INNER JOIN. 

Here is one of the my favorite videos on this subject:

http://www.youtube.com/watch?v=Z7VuYBXX2dg

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

SQL SERVER – Turning On Graphical Execution Plan After Enabling ShowPlan Text

This may be surprising to many, but I have seen quite a few times so far so decided to blog over here.

Here is the sequence of the action:

  1. The developer turns on Graphical Execution plan for any query with CTRL+M
  2. Now turns on the execution plan in the text format with SET SHOWPLAN_TEXT ON command
  3. After this, the developer does his/her task to analysis the execution plan
  4. Now turns on the execution plan in the XML format with SET SHOWPLAN_XML ON command

Now when a developer has to turn back Graphical Execution Plan, he/she gets confused. We know that we can turn on TEXT and XML with the help of SET commands, but how to turn on graphical execution plans from T-SQL. Well, the matter of facts is there is no way to turn the graphical execution plan from T-SQL.

The only way to get the Graphical Execution plan back in SQL Server Management Studio (SSMS) is to turn off TEXT and XML plan and it will automatically turn on the graphical execution plan.

For example, in above situation when we have turned off TEXT and XML plan, set them off as described in the following steps

  1. Turn off XML execution plan with SET SHOWPLAN_XML OFF command
  2. Turn off TEXT execution plan with SET SHOWPLAN_TEXT OFF command

It will automatically turn on the graphical execution plan. Remember, if you have turned off either of XML or TEXT, you will need to turn off that one only. However, if you have turned on both of them, you will have to turn off both of them to get back your graphical execution plan.

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

Professional Development – Dr W. Edwards Deming’s 14 Principles on Total Quality Management

I was just reading Dr. W. Edwards Demings 14 principles of Total Quality Management. It is indeed very impressive and interesting. I have tried to collect a few of the important resources related to the same over here.

Dr. Demings’s 14 principles

  1. Create a constant purpose toward improvement
  2. Adopt the new philosophy
  3. Cease dependence on mass inspection
  4. Use a single supplier for any one item
  5. Improve every process
  6. Create training on the job
  7. Adopt and institute leadership aimed at helping people do a better job
  8. Drive out fear
  9. Break down barriers between departments
  10. Get rid of unclear slogans
  11. Eliminate arbitrary numerical targets
  12. Permit pride of workmanship
  13. Implement education and self-improvement
  14. Make transformation everyone’s job

Here are few other interesting resources related to Dr. W Edwards Demings

  • Wikipedia page (Link)
  • Original White Paper with 14 Key Principles (Link)
  • Original Website of Demings Institute (Link)
  • SlideShare PPT (Link)

Here is the Official YouTube channel of Demings Institute. They have excellent videos and I strongly encourage everyone to view them.  Additionally, here is the famous Deming’s Red Bead Experiment Video.

Before I end the post I would like to include the video of Daniel Pink who has authored books Drive, which is very motivational book and often time I felt it resonated with the same message as Dr. Demings. This video is from TED presentation of Dan where he discussed The Puzzle of Motivation.

http://www.youtube.com/watch?v=rrkrvAUbU9Y

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