[email protected] Yennifer da Ponte Santos Premier Field Engineer Microsoft Corporation Do you ever felt like this? Understanding SSIS architecture Troubleshooting DTSx Packages Performance Control flow Data flow Server location Data volume 3 Troubleshooting DTSx Packages - Other problems 64 bit concerns Data types in SSIS SSIS Memory and Performance Indicators Troubleshooting Methodologies 4 http://www.youtube.com/watch?v=ONqgaV U_XPk 6 Performance is mostly affected by external variables Bandwidth External servers connections Databases FTP servers Mail servers 7 Also affected by external factors Uses buffer architecture to manage the data pipeline DefaultMaxBufferSize DefaultMaxBufferRows Parallelism (packages, tasks, and transformations) MaxConcurrentExecutables (SSIS threads per package) Default = -1, which translates to the number of logical machine processors plus 2 8 At the target level 9 Partial 10 All operations 11 Apply parallelism in the slower component 12 SSIS server + Source database SSIS server + Target database Dedicated SSIS Server 13 Data volume impact in memory and CPU resource utilization Buffer-architecture Rows number Row types 14 Runtimes are DTSExec DTSHost DTSDebbuggers DTExecUI.exe 15 Data connectivity providers (OLEDB Providers, ODBC drivers) must match the bitness of the runtime Failure will result in obscure connectivity failures DTS_E_CANNOTACQUIRECONNECTIONFR OMCONNECTIONMANAGER Error: An OLE DB error has occurred Class Not Registered Excel providers (Jet/Ace) are only x86 32bit today 16 17 Run64BitRuntime is a setting on the solution at Design time. Determined Debug time bitness in BIDS SSIS Project Property Pages -> Configuration Properties -> Debugging Design time in BIDS will always be x86 32bit providers Debug time in BIDS can be x86 32-bit provider, or 64-bit provider depending on Run64BitRuntime 18 SSIS Uses its own types, similar to managed types Metadata and Data Types are defined in the package design at Design time, and checked again at time of validation and runtime They are hard coded into the .dtsx XML, so it is not so easy to change a data type in your system after the design is done 19 OLEDB Source / Destination asks the OLEDB provider what types are in the data sources IColumnsInfo::GetColumnInfo IColumnsRowset::GetColumnsRowset Has to map external type to common SSIS Types SQL int - DT_I4 SQL Nvarchar - DT_WSTR 20 21 22 23 24 25 The more tasks you run in parallel, the more likelihood peak memory consumption will be higher Sequences of tasks will take longer, but may reduce memory footprint 26 Watch SSIS with Perfmon Counters: Process: Private Bytes Watch Neighbors with Perfmon Counters Memory : Available MB Process: Private Bytes (for SQLServr.exe) SQL Server:Total Server Memory (if locked pages or AWE is set) 27 There are SSIS counters, but in all practice, they are per machine and not per package, so the usefulness is limited. \SQLServer:SSIS Pipeline 10.0\BLOB bytes read \SQLServer:SSIS Pipeline 10.0\BLOB bytes written \SQLServer:SSIS Pipeline 10.0\BLOB files in use \SQLServer:SSIS Pipeline 10.0\Buffer memory \SQLServer:SSIS Pipeline 10.0\Buffers in use \SQLServer:SSIS Pipeline 10.0\Buffers spooled \SQLServer:SSIS Pipeline 10.0\Flat buffer memory \SQLServer:SSIS Pipeline 10.0\Flat buffers in use \SQLServer:SSIS Pipeline 10.0\Private buffer memory \SQLServer:SSIS Pipeline 10.0\Private buffers in use \SQLServer:SSIS Pipeline 10.0\Rows read \SQLServer:SSIS Pipeline 10.0\Rows written \SQLServer:SSIS Pipeline 10.0\Flat buffers in use 28 Watch for Buffers Spooled Has an initial value of 0. When it goes above 0, it indicates that the engine has started memory swapping In this case to set Data Flow Task properties BLOBTempStoragePath and BufferTempStoragePath appropriately for maximal I/O bandwidth 29 Technique - Control Flow BIDS Debugging Color tells progress – Success InProgess Failed Set breakpoints on tasks, containers, and the package Progress Tab shows where Errors happen 30 Technique - Dataflow – DataViewers Peek inside the data to see if it gives expected results and values at various junctures in the Dataflow task 31 Technique - Dataflow - Capture the count of rows processed At Design time, numbers appear when debugging After deployment, you may need built in Row Counts to help measure data quality 32 Technique - Dataflow- Design Error Redirection ie. Red Arrows Configure error outputs on data flow components that support them Can direct either rows getting Errors or rows getting Data Truncation 33 Technique - Script BreakPoints in Script Task only – won’t work in Script Component Message Box in VB or C# code to popup interactive message boxes Code to log text when logging is enabled 34 Technique - Researching Errors Each SSIS error has 4 representation Cross Reference each one in your searches Description can have wildcards Errors are presented in a nested fashion, so low level is usually most helpful. Example: Hex code: 0xC0010003 Decimal code: -1073676285 Symbolic Name: DTS_E_VARIABLEREADONLY Description: Error trying to write to a read-only variable, "__" Reference BOL: Integration Services Error and Message Reference 35 Technique - Memory Dumps In 2008 we have 3 switches /DumpOnError /DumpOnWarning /Dump <code> Output dump files go to C:\Program Files\Microsoft SQL Server\100\Shared\ErrorDumps Files include .mdmp file and .tmp (text) Dtutil.exe /Dump for hangs of SSIS 36 Technique - Isolation Disable tasks not related to the failure For DataFlow task, put in dummy source/destination using Raw file to avoid externalities Use .udl files to test connectivity to RDBMS outside SSIS 37 38 Technique - Examine the providers and RDBMS BID Tracing – ETL tracing in Microsoft’s data providers ODBC Tracing – Checkbox to enabled ODBC trace External Proprietary Tracing – Oracle’s tracing. Microsoft’s DB2 OLEDB provider has SNA Trace SQL Profiler Tracing & DMV – Measure the batches coming into SQL to look for patterns of incoming requests and performance of queries 39 Questions and Answers © 2008 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.