Performance Management


The secret to good performance is often simplicity of design. Extract, transform, and load (ETL) packages are notorious for being convoluted and unnecessarily complicated. Often, as additional requirements crop up during design, it's common simply to add steps that can eventually lead to an overly complicated package that can be difficult to manage and maintain. Before you know it, your package reads from and writes to several staging tables and performs several different and independent transformations. Adhering to the throw-away-your-first-attempt design principle, make a point to review the end-to-end process and look for opportunities to streamline and simplify. It might be necessary to build and test the package more than once to fully understand the processes required to achieve the end goal. Most often, after careful review, you will find better and simpler methods to consolidate the data flow, transformations, and branching logic. Avoid over-design. Not only are too many moving parts difficult to maintain, but more steps often lead to using more buffer space and a slower overall process.

One of the most common trade-off decisions you will need to make is whether to make extensive use of various SSIS transformations or to use a single Transact-SQL statement. Within a single data flow, the difference in performance between these choices might be negligible. However, using tasks, loops, and containers to manage operations that can be performed in a single query will usually be considerably slower. Like using row-by-row cursor operations in a SQL stored procedure, loop containers that initiate a nested data flow can be dreadfully slow, especially when looping through a large number of rows. Consider that each iteration of a data-driven loop requires a separate data source connection, query execution, buffer allocation, and data flow destination to be written and finalized. If it's possible to perform these operations in a single query execution at the data source, SSIS will have far less work to do.

When you truly need to loop through a set of records, the trick is to get the rows of the result set into the workflow to enumerate over those rows and do meaningful work. For example, the meaningful work might be to execute a script task that sends e-mail messages to a large group of recipients. Applications for this integration, however, are limitless. Any time you need to execute a section of work flow per row of a result, this method provides a way.

Here are some examples:

  • Sending out e-mail notifications to clients when it's time to renew subscriptions

  • Executing subpackages that have been queued up through a management console that stores resulting queue requests in tables

  • Processing employee edge events such as birthdays, children turning 18, option expirations, and so on as described in human-resources databases

  • Customer relationship management (CRM) stale dates, reminding sales people to contact customers who haven't been contacted within a certain number of weeks

  • Processing sales logs to find potential fraud, bad checks, or stolen credit cards

The trick is to get the value of the columns of each row into a form that can be used inside the package. For Integration Services, the way to do that is to change the column values into variables. To do this, you need to take the recordset returned from the SQL task and somehow iterate over it. ForEachADOEnumerator was created for just that purpose.

Loops

Although creating a loop seems like a natural approach for many scenarios, it can be very slow and inefficient. Loops are much slower to process than SQL queries, yet they can be more flexible and capable. Even SQL cursor operations can prove to be faster yet might not afford the same opportunity to process business logic and interact with other components. As a rule, keep loops simple and keep the number of iterations as small as possible.

When developing packages, it's important to note that SQL Server Business Intelligence Development Studio (BIDS) runs as a client-side tool. Unlike a package running on the server, data must be pulled to the development machine to be processed. To understand effectively the effects of running a process in a loop, you must first isolate the inefficiencies of the development environment. After this, you can deal with the loop itself. After debugging the package, deploy and test it on the server.

To debug problems with a loop container, it is necessary to isolate the loop from the task running within the loop. To do this, design, test, and optimize the tasks as a single process. Next, create the loop and optimize it to run with nothing in the container or disable the contained tasks. Using this method, it might be possible to eliminate efficiency issues that would otherwise be difficult to track down. The most effective way to isolate a set of tasks from a loop container is to build a separate package and then use an execute package task within the loop.

Because a loop executes multiple instances of a control flow, deepening on the contained tasks, each execution can spawn separate threads. Data flow tasks in the loop can also create multiple connections and buffers, which might, in turn, slow the process significantly. Use SQL Server Profiler and Windows System Monitor to watch these counters under production conditions to get a better idea about the resources a looping process will consume. If you need to loop through records, remember that connecting to data sources in a loop will be very slow. Try reading data into an ActiveX Data Object (ADO) and then looping through the cached data.

Loops introduce more variable conditions than most other package design elements. Understanding the mechanics of the control flow and data flow engines will help, but without a complete understanding of all these elements, sometimes trial and error is the only effective design method. Don't be afraid to experiment; there are many ways to solve a problem.

Flat File Sources

Loading data from text files can be fast because data is stored in an uncomplicated format. Processing the data can be slow because values must be converted from text to specific data types. FastParse, a feature of the Flat File Source component, can be used to improve processing performance significantly if values such as dates and currencies are stored in common formats. If this is the case, you can enable the FastParse property for those columns that the parsing routine understands. The property is buried a bit in the designer user interface.

After a flat file connection manager has been created, an associated Flat File Data Source adapter can be configured to support this capability. To do this, right-click the adapter and select Show Advanced Editor. In the Advanced Editor For Flat File Source dialog box, you would choose the Input and Output Properties tab. On the left side, a tree view list is displayed under Inputs And Outputs. Expand the Flat File Source Output node and view the Output Columns node. An item is displayed for each of the columns returned by the data source. For each column, you can set properties, using a properties sheet grid displayed on the right side of this tab page in the dialog box window.

image from book

For the columns that are supported, change the FastParse property to True. SQL Server Books Online contains an extensive list of data types and format string values that are supported. It doesn't perform locale-specific parsing, nor does it recognize special characters in currency data, and it can't convert hexadecimal or scientific notation of numeric values.

Filters and Variables

Filtering data at the source is one of the simplest and most effective ways to optimize package execution. In addition to including only necessary columns in the query explicitly, the query should also exclude unwanted rows. Always filter your data in the Source Adapter rather than filtering it with a Conditional Split transformation component. If your requirements call for transforming multiple pipelines of unique records from a single data source into conditional branches, filter the data at the source first to satisfy all of the branching requirements rather than tossing out unqualified records in a Conditional Split transformation. Suppose a source table contains 500,000 rows, and your package is designed to transform 200,000 of these rows into three separate destinations, using three sets of unique criteria. Allowing 300,000 rows to flow through the package only to be blocked by a Conditional Split transformation would be wasteful. It would make more sense to use a SQL query in the Data Source Adapter with the same filtering criteria as the combined three branches of the Conditional Split transformation. In this example, the WHERE clause of the query might contain three corresponding condition statements separated by using OR logic. It might actually be faster to have three separate Data Source adapters reading from the same table, each using a different SQL query, than to use the Conditional Split transformation at all. If a filtering condition relies on string parsing or pattern matching, Conditional Split would likely be the faster method as opposed to filtering on simple indexed values, where SQL should be more effective. The only way to know for sure is to try both techniques.

Variables provide a way to set values dynamically and control processes in packages, containers, tasks, and event handlers. Variables can also be used by precedence constraints to control the direction of the flow of data to different tasks.

Each container has its own Variables collection. When a new variable is created, it is within the scope of its parent container. Because the package container is at the top of the container hierarchy, variables with package scope function like global variables and are visible to all containers within the package. The collection of variables for the container can also be accessed by the children of the container through the Variables collection.

Variable scope can affect thread dependency and the memory required to manage object variables and arrays. For this reason, and for manageability, variables should be scoped to the containers in which they are used. Remember that you cannot re-scope a variable once it has been created. To change the scope, the variable must be deleted and then re-created. Because the visibility of a variable is scoped from the top down, variables declared at the package level are visible to all the containers in the package. Therefore, the Variables collection in a container includes all the variables that belong to its parent in addition to its own variables. Conversely, the variables contained in a task are limited in scope and visibility and are visible only to the task.

If a package runs other packages, the variables defined in the scope of the calling package are available to the called package. The only exception occurs when a same-named variable exists in the called package. When this collision occurs, the variable value in the called package overrides the value from the calling package. Variables defined in the scope of the called package are never available back to the calling package.

Data Destination Management

As a rule, a package should run as close to the data as possible. A package running on any computer other than the data source or destination is a data client and will move data across a network connection to be processed. This doesn't necessarily mean that all of your packages should be run on the corporate production database servers, but the server hosting SSIS packages should at least have fast network access. A separate subnet from the user community for interserver traffic is typically a good design principle.

The OLE DB provider for SQL Server has a FastLoad option that can optimize large insert operations, much like a bulk-load type of insert. To enable this feature, set the AccessMode advanced property of the connection manager to OpenRowset Using Fastload.

Applying some universal database optimization techniques can help you get the most out of your interaction with SQL Server. Consider the following.

Locking Modes

The default locking mode for inserts takes into account other users who might be working with table data. Because concurrent database access is often not an important factor when transforming data, applying a nondefault table lock when records are inserted can speed things up because the database engine denies concurrent access to the entire table during the insert operation and doesn't have to manage slower row- or page-level locks on the table.

Drop or Disable Indexes

Inserting records into a table that has no indexes or into one in which its existing indexes are disabled can be considerably faster than tables with indexes. A common optimization technique is to drop or disable existing indexes and then to either re-create them or rebuild them. Disabled indexes must be rebuilt to bring them back online. Keep in mind that this applies only to nonclustered indexes. Disabling a clustered index takes the table offline. Index management can be performed in the package by using an Execute SQL task or by calling a maintenance stored procedure on the database server. Although the overall processing time (including index rebuilds) can be longer, using this technique might allow you to complete the package processing faster or to perform other operations while the indexes are being regenerated by the database server.

Use Explicit Transactions

You usually think of transaction processing in a relational database environment as necessary overhead that can threaten performance, but under some conditions, transactions can actually speed things up. When records are inserted (and updated or deleted), new data is written to the database transaction log and then rolled forward into the actual database files in a background process called a checkpoint. When multiple data operations are performed, each operation is wrapped in an implicit transaction and must be rolled forward individually. When using a single explicit transaction for multiple data operations, all of the data is written to the transaction log before rolling the rows forward into the database files. By applying these operations in one big explicit transaction, the roll-forward process can complete all at once and in the background after your SSIS package has completed its work.

Performance-Tuning Exercises

The following exercises show how to use optimization options in an SSIS package. The image from book Chap11.sln solution contains five packages that show you different configurations and design options to increment speed when you execute an SSIS package.

The following chart can be used to record the test results for the exercises that follow. If you prefer not to write in your book, a Microsoft Office Excel document named Chapter 11 Exercise.xls is provided in the Chap11 project folder to record the execution for each package, according to the following instructions:

Open table as spreadsheet

Package

Test Criteria

Execution Time

Package 0

Test baseline with default buffer settings and OLE DB destination

 

Package 1

Default buffer settings and SQL Server destination

 

Package 2

SQL Server destination with elevated buffer settings

 

Package 3

Single data flow task with Multicast, Sort, and Union All transformations

 

Package 4

Two data flow tasks

 

Create a Destination Table
  1. Open Microsoft SQL Server Management Studio and connect to the local database server. Copy the text from this file onto the clipboard, paste it into the SSIS Query window, and use the Execute button on the toolbar.

  2. Open a New Query and execute the next script to create the [OLE DB Destination - Test] table. You can find this script in C:\Documents and Settings\<username>\My Documents\Microsoft Press\is2005sbs\Chap11\Data\ image from book Create OLE DB Destination - Test table.txt.

     USE is2005sbs GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE dbo.[OLE DB Destination - Test](    rowguid uniqueidentifier NULL,    Name nvarchar(50) NULL,    CurrencyCode nvarchar(3) NULL,    CurrencyRateID int NULL,    CurrencyRateDate datetime NULL,    FromCurrencyCode nvarchar(3) NULL,    AverageRate float NULL,    ToCurrencyCode nvarchar(3) NULL,    EndOfDayRate float NULL,    ModifiedDate datetime NULL,    Expr1 datetime NULL,    Expr2 uniqueidentifier NULL ) ON PRIMARY 

Working with Buffer Properties

The first package, image from book Package 0.dtsx, under the image from book Chap11.sln solution shows a simple Data Flow transformation with an SSIS default setting for DefaultBufferMaxRows (10,000 rows) and DefaultBufferSize (10,485,760 bytes) options in a data flow.

Test Execution Performance
  1. Use Windows Explorer to navigate to the Chapter 11exercise solution folder located at C:\Documents and Settings\<username>\My Documents\Microsoft Press\is2005sbs \Chap11 and open the image from book Chap11.sln solution.

    image from book

  2. Double-click image from book Package 0.dtsx.

  3. Right-click Data Flow Task - Copy Test table, click Properties, and then check the values for DefaultBufferMaxRows and DefaultBufferSize in the properties pane.

    image from book

  4. Right-click image from book Package 0.dtsx and click Execute Package. At the end of the execution, check the Progress tab and see the very last line: Finished-.

    It gives you the processing time in seconds (for example, 27.640 seconds). Record this value. Note that the execution time in your development environment will vary based on several factors.

    image from book

  5. On the Debug menu, click Stop Debugging.

Working with a SQL Server Destination

Using an OLE DB data adapter is generally the best option for a package that will run on a separate server than the database (either now or later when the solution is scaled out). However, this flexibility comes at a cost. The SQL Server adapter is optimized to run on the same server as the destination database with improved performance. A SQL Server destination might be the best option if you want to write the result of a data flow to a SQL Server database.

Use a SQL Server Connection Manager
  1. Double-click image from book Package 1.dtsx.

  2. On the Control Flow tab, double-click Data Flow Task - Copy Test table in image from book Package 1.dtsx.

    The destination is now a SQL Server Destination component. Values for Rows and Buffer-Size are the same as in package 0 (SSIS by default).

  3. Execute the package and check its execution time as in the previous package (for example, 21.100 seconds). Record this value and note the difference from the previous trial.

    Note 

    When developing the exercise, this package ran about 25 percent faster than the previous package. Your results might vary, depending on variables in your system configuration.

  4. On the Debug menu, click Stop Debugging.

Test DefaultBufferMaxRows and DefaultBufferSize
  1. Double-click image from book Package 2.dtsx.

  2. On the Control Flow tab, double-click Data Flow Task - Copy Test table in image from book Package 2.dtsx.

  3. In the Properties pane, change DefaultBufferMaxRows to 100000 and DefaultBufferSize to 30485760 (30 MB). Type only the numbers into the property fields (without commas).

    Note 

    The OLE DB Source component allocates a number of rows close to 100,000 into a buffer, for example, 99,942 rows. It is based on the metadata that SSIS collects about your source data at design time.

  4. Execute the package and check execution time (for example, 18.526 seconds). Record this value. Note that the Log Events window is open on the left side of your screen.

    Note 

    If the Log Events window isn't visible by default, you can open it from the View menu by clicking Other Windows and then Log Events.

  5. Check the Message column in that Log Events window. It gives you information about the package execution.

  6. On the Debug menu, click Stop Debugging.

Design Considerations

The next two packages show you two different designs performing the same functionality but with different execution results.

Note 

Verify that for image from book Package 3.dtsx and image from book Package 4.dtsx:

BufferMaxRows = 1,000 and BufferSize = 1,048,576. This change in these properties forces SSIS to auto-optimize buffer size.

Test a Package with Blocking Transformations
  1. Double-click image from book Package 3.dtsx in Solution Explorer to open the package designer.

  2. In the Control Flow tab, double-click Data Flow Task - Copy Test table in image from book Package 3.dtsx.

    image from book

    At first glance, it seems to be a parallel package.

    Note 

    Remember that a Sort transformation is a blocking transformation, and Union All is a partially blocking transformation.

  3. Execute the package.

    This might take several minutes to run. While it is executing, switch to the Data Flow tab and watch the row counters.

    image from book

    At the beginning, it runs in parallel. The left-side and the right-side output from the Multicast run at the same time. The data flow is blocked until the last record is read from the source because of the Sort transformation.

    When both Sort transformations finish reading from the source, only the left side of the data flow runs.

    image from book

    Note the Message column in the Log Events window. SSIS creates new buffers to execute this data flow. After the left-side pipeline finishes writing to the destination, the right side starts loading data to the destination.

    image from book

    It is apparent that this design is not very efficient.

  4. On the Debug menu, click Stop Debugging. Observe the control flow design.

    image from book

    Now you have two data flow components almost identical to each other, one for the left Sort and another one for the right Sort of the data flow in package 3. You would probably think that the processing time for the package would have a direct correlation to the value of data flowing through the package. However, this design runs faster than the previous one. It is a parallel design and a parallel execution.

  5. Click the Control Flow tab, and then execute image from book Package 4.dtsx.

  6. Note that now the package is executing in parallel. Check both data flows, and you can see how data flows at about the same time in both of the data flow tasks.

  7. Monitor the row counts and make note of the time when the package execution completes.

  8. On the Debug menu, click Stop Debugging.

    image from book

    You can check the processing time on the Progress tab and compare it against the previous one in package 3. During the test when designing the exercise, it was at least half of the execution time.




Microsoft SQL Server 2005 Integration Services Step by Step
MicrosoftВ® SQL Server(TM) 2005 Integration Services Step by Step
ISBN: 0735624054
EAN: 2147483647
Year: 2007
Pages: 152

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net