Performance Considerations When Choosing Transformations in SSIS

The use of SQL Server Integration Services (SSIS) can help to save a lot of time when creating ETL solutions.  The graphical nature of the tool makes it very intuitive and easy to understand.  Often, a person can look at a SSIS ETL flow and understand at a glance what the intended purpose is.

I have been working with SSIS for several years now and I have realized that the easy to use graphical nature of the tool is very advantageous but it recently occurred to me that there is a disadvantage that I had not considered.  I have also been the recipient of formal training courses on the tool as well as completed formal exams on the subject.

When you select a transformation to use from the tool box, you select the transformation and drag it over to your data flow.  This could give the illusion that all transformations are created equally because they all come from the same tool box.  What is not immediately apparent is that there are significant differences between the performance behaviors of the transformations based on a few different properties of the objects.  

The properties I will discuss are the synchronous/asynchronous property and the transformation type property ie:  non-blocking, semi-blocking, fully-blocking.  The properties that an object possesses are a significant factor in the performance you can expect from the object.

Synchronous/Asynchronous

A synchronous transformation is the more highly performing type because the transformation uses the same buffer for the output as is used for the input.  The incoming rows are processed and passed on one row at a time and this is possible because the transformation does not need any additional information from other buffers.  MSDN uses the Data Conversion transformation as a perfect example of this.  Each row that is used as input from the buffer is converted and then outputted one row at a time.  Each conversion is completely separate from the rest of the row set so the process does not have to wait on anything else and can complete much faster.

Example: The Data Conversion transformation is synchronous.

An asynchronous transformation is the just the opposite of that.  Instead of relying on the same input buffer as the output buffer, the process may have to acquire multiple buffers before it can complete the necessary processing.  There may be some additional waiting involved.  The output buffer is not independent of the input buffer.   A perfect example of this is the Merge transformation.  Before the output buffer can be complete it has to first look at many rows from each of the input buffers and then completes the merge operation and return the row set in a sorted order.

Example: The Merge Conversion transformation is asynchronous.

It is interesting to note that all destination adapters are synchronous and all source adapters are asynchronous because they need 2 output buffers.  One for the success and one for any failed outputs.

Three Transformation Types

In this next section, Ill briefly discuss the three transformation type of non-blocking, semi-blocking, fully-blocking.

Non-Blocking

This transformation type is the best performing type.  They process row by row with no dependency on other rows.  The number of rows that are in the input buffer are equal to the rows that are in the output buffer.  The process is synchronous and therefore they are made available to the downstream output with no waiting on the status of other buffers.  This makes them very lightweight and efficient.  Row transformation are a good example of a non-blocking transformation.

Semi-Blocking

Semi-blocking transformation have dependence on other rows.  Rows are added to the input buffer and held for a while until the process is able to release the rows to the output buffer.  A good example of this is the Merge transformation.  Rows are added the input buffer and held until a match is found and then the rows are released to the output buffer in a sorted output.  The time taken before a match is found is what causes these types of transformation to perform worse than non-blocking transformations.

Fully-Blocking

A full-blocking transformation is the least performing transformation.  They require all rows to be received from the input buffer and held until they can be subjected to transformation.  The nature of the output is dependent on collecting all the rows first.  A perfect example of a fully-blocking transformation is an Aggregate transformation.  If you are going to SUM a row set it only makes sense that you collect the entire row set first before you can SUM it.

It is important to consider these properties when deciding which transformations to utilize.  A complete understanding of the difference in performance helps to make the best choice.

Example: Transformation Type Categories (Satya Katari)

Thanks for reading,

SQL Canuck

Using LOCALHOST to connect to a named SQL Server 2012 Instance

Apart from working on database servers that are part of a wide area network for most of my day to day duties as a Senior Data Warehouse Architect, I like to keep  local SQL Server instance(s) on my work PC.  This allows me to spend some time working through training demos that I come across or that are part of my self-paced learning objectives to prepare for passing certification exams.

I am about a week away from writing the first of 2 certification exams for the MCSA SQL Server 2012 certification.  In preparation for that, I am working through some material from Microsoft Press.  This material comes with excellent demos and these demos need to access to a named instance of SQL Server 2012.  I installed the developer edition of SQL Server 2012 on my PC and created an appropriate named instance.

For many years, I have kept various instances of SQL Server local to my PC so I could play in a secure environment.  I got in the habit of connecting to these instances by using the LOCALHOST hostname.  This allows me to access the network services that run on the host (my own PC) by using the loopback network interface.  I could type my computer name each time but this is just easier to type LOCALHOST or even the loopback IP 127.0.0.1 .

After I created my newest named instance, I noticed that I was having connection issues when I tried to connect using the LOCALHOST as my server name.  The same was true when I would try use the loopback IP address.  I would try to connect to SQL Server SSMS and if I used LOCALHOST or 127.0.0.1 as the server name it wouldn’t not connect and give me the following error.

Example: Connect to Server error message

After some investigation, I realized that because I was trying to connect to a named instance as opposed to a default instance there were a few steps I would need to take to continue to use LOCALHOST as my server name. 

If I wanted the hostname LOCALHOST to be associated with my newly installed named instance I would need to create an alias.  This can be done in the SQL Server Configuration Manager that is provided when you install SQL Server 2012.  This can found at Start>All Programs>Microsoft SQL Server 2012>Configuration Tools.  

Once the configuration manager is opened you can go to the node that is called SQL Native Client 11.0 Configuration (32bit).  You can right click and select New Alias and enter ‘LOCALHOST’ under Alias Name and your SERVERNAME/InstanceName under Server.  You should do the same on SQL Native Client 11.0 Configuration if your PC runs a 64 bit architecture.

Example: SQL Server Configuration Manager

Example: Add a new alias

Now you will find that you can connect to the database engine by using LOCALHOST as server name and because your alias points to a named instance you don’t even need to specify the \InstanceName as part of your server name when you connect to SSMS or SSIS etc.

It is important to also note that you can connect to a named instance using a loopback address as long as you also specify your instance name ie:  127.0.0.1\InstanceName and in this case an alias isn’t necessary.

The method I have described above is not a lot of work and if you are going to be doing a lot of work on a local named instance then being able to connect by using LOCALHOST instead of SERVERNAME\InstanceName is a convenient time saver

Thanks for reading,

SQL Canuck

Out of Date Statistics

This week a BI specialist came up to me and asked me to inspect a query that was not performing very well.  He knew that I have attended a week long Microsoft course called ‘SQL Server 2012: Performance Tuning- Design, Internals and Architecture’ so I have had the opportunity to help him out on a number of occasions for many performance based questions. 

He sent the query over and I ran it and inspected the actual query plan by pressing the ‘Include Actual Execution Plan’ button in SQL Server 2012 Management Studio.

(Example: Include Actual Execution Plan button)

The query was not very long or complicated so I was surprised when it took several minutes to return data.  When I inspected the query execution plan I didn’t notice anything that looked like it should be causing a long execution time.  Quite the opposite was true actually.

When I broke the query into chunks and ran each chunk individually the performance was fine until I introduced a specific table.  We will call it TableA.  I investigated the meta-data of TableA and discovered that it was a large table but it had appropriate indexes on it including a clustered b-tree index on a surrogate integer column. 

When I examined the query plan for that TableA, it indicated that it was using an Clustered Index Seek on with a Key Look up.  I was surprised to see this because this really is the ideal situation and is the best choice for highly selective queries.  It means that the optimizer has located an appropriate index.  The Clustered index seek means that the engine will not have to scan all the rows in the data pages to find what it is looking for.  It can traverse the b-tree index and in just a couple reads get right to the index key it is looking for.  It can then do a quick key look up and go right to the needed data.

(Example: Clustered Index Seek operator)

(Example: Key Lookup operator)

So this struck me as strange. If everything was so ideal then why was it taking so long?  If you guessed ‘Statistics’ then you are correct.  The first thing that came to mind was that the statistics may have been out of date for that index. 

I suggested that the developer identify the statistics associated with that index and see when it was last updated. 

(Example: Statistics node associated with the Products table)

(Example: Property page of a statistic object shows the last time they were updated.)

If the statistics were out of date then they should be updated by running a similar command to the following:

 
USE AdventureWorks2012;
GO
UPDATE STATISTICS Production.Product(Products)
    WITH FULLSCAN, NORECOMPUTE;
GO

'The following example updates the Products statistics in the Product table, forces a full scan of all rows in the Product table, and turns off automatic statistics for the Products statistics.'

(https://msdn.microsoft.com/en-us/library/ms187348.aspx)

The developer came back to me later that day and said he had updated the statistics and now the query was completing in just a few seconds.

Thanks for reading,

SQL Canuck