SQL Server Integration Services SSIS Best Practices
Refer the Source Video:SSIS Performance Tuning
FULL RECOVERY - lot of logging done
DW project - generally Simple Recovery Mode (minimal logging)
Simple Recovery it loads lot of rows in sec.
Control Flow:
Create DB
{notes:Create DB ( Execute SQL Task:SQL Statement )}
{notes: ALTER DATABASE [DBname] SET RECOVERY FULL
Create Table
Data Flow:
Flat File Source ( 6,000,000 rows)
OLE DB Destination
notes:
CREATE DATABASE (Transact-SQL)
Create DB ( Execute SQL Task:SQL Statement )
USE [master]
GO
IF DB_ID(DBNAME) IS NOT NULL
BEGIN
ALTER DATABASE [DBNAME] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE DBNAME;
END
GO
CREATE DATABASE [DBNAME]
CONTAINMENT = NONE
ON PRIMARY
(NAME = N'DBNAME', FILENAME = N'C:\ProgramFiles\Microsoft SQL Server\MS SQL11.MSSQLSERVER\MSSQL\DATA\DBNAME.mdf', SIZE =10 MB, MAXSIZE = UNLIMITED, FILEGROWTH = 1 MB)
LOG ON
( NAME = N'DBNAME_log', FILENAME = N' C:\ProgramFiles\Microsoft SQL Server\MS SQL11.MSSQLSERVER\MSSQL\DATA\DBNAME.ldf', SIZE = 2 MB, MAXSIZE = 2000GB, FILEGROWTH = 10%)
GO
GO
ALTER DATABASE [DBNAME] SET RECOVERY FULL
-----------------------------------------------------------------------------------------------------------
Source: SSIS Performance Tuning Simple Load Part 2 Database Growth SSIS
In the CREATE DATABASE statement if the FILEGROWTH is increased say 20MB or 100MB , performance improves.
-----------------------------------------------------------------------------------------------------------
Source:SSIS Performance Tuning Simple Load Part 3 Initial Database Size
In previous video using the "DBGrowth" which refer to "FILEGROWTH" the performance improved.The table that being loaded is below the "FILEGROWTH" so it will not impart on performance.
By changing the "Initial Database Size" which refers to "SIZE" , it will change the performance.
In previous video using the "DBGrowth" which refer to "FILEGROWTH" the performance improved.The table that being loaded is below the "FILEGROWTH" so it will not impart on performance.
By changing the "Initial Database Size" which refers to "SIZE" , it will change the performance.
Source:SSIS Performance Tuning Simple Load Part 4 BI vs Enterprise Edition
In Enterprise Edition its faster.
-----------------------------------------------------------------------------------------------------------
Source:SSIS Performance Tuning Simple Load Part 5 Commit Size
Data Flow:
OLE DB Destination
-----------
OLE DB Destination Editor:
Configure the properties used to insert data into a relational database using an OLE DB provider.
[Connection Manager
Mappings
Error Output]
Specify an OLE DB Connection manager, a data source, or a data source view, and select the data access mode.If using the SQL Command access mode, specify the SQL Command either by typing
the query or by using Query Builder. For Fast - load access, set the table update options.
OLE DB Connection manager:
oledb-LoadDatabase
Data access mode:
Table or View - fast load
Name of the table or the view:
[dbo].[Tablename..]
[]Keep Identity [*]Table lock
[]Keep nulls []Check Constraints
Rows. per batch:
Maximum insert commit size: 0 ( changed from a higher number to 0) ( it means 1 transaction)[1 transaction: it will commit all rows, in 1 time{Logsize,TempDB size will be bigger]
[Caution: Table having "Clustered Index" won't work.][Only for heap table(having no indexes)]
View Existing Data
----
~Source:Making Fast Load really fast on clustered indexed tables with SSIS
Moreover, as using MICS parameter leads to a dramatic performance degradation, I think that the OleDb destination adapter should issue a warning if both ORDER and MICS parameter are set so to make the programmer think twice before using them together.
~Source:Batch Sizes, Fast Load, Commit Size And The OLE DB Destination
Rows per batch:
This setting affects the performance and commit semantics of the SQL engine, but doesn’t affect things on the SSIS side. Lowering this value does not affect the number of times SSIS commits data to the server.
Maximum insert commit size:
Enlisting the Data Flow task in a transaction also has the same effect as setting FastLoadMaxInsertCommitSize = 0. All rows will be committed or rolled back at the end of the Data Flow Task.
Beware
Setting the Maximum insert commit size property to a low value will cause a lot of overhead on the destination as the rows will be committed much more often.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SSIS Performance Tuning Simple Load Part 6 Packet Size SSIS 2012 Video Example
Ole Db -right click - All - see "Packet Size:0" - by default it means 4kb. Change it to 32KB which is maximum, if the value disappears then its on the "Connection" , choose the "project.param" , choose connection
string and specify "Packet Size"
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Source: SSIS Performance Tuning Simple Load Part 7 Row vs Page Compression SSIS 2012 Video Example
Connect to Server
Server type: Database Engine
Choose the Database->Table(right click "Storage" - "Manage Compression" - change Compression type "Page"
Next screen "Data Compression Wizard" , Run immediately ,
Task Manager - Look at "Performance", "Write Speed" is less , "Read speed" as usual.
Conclusion:Compression "Slows down" the load into "heap" table.
Observation:"Row" Compression is bit faster , "Write Speed" is little faster , "Read speed" little better
But any how "Row" Compression is slower.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Source: SSIS Performance Tuning Simple Load Part 8 Balanced Data Distributor SSIS 2012 Video Example
Balanced Data Distributor , does parallel processing.It works like "Conditional Split" , but its more efficient.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Source:SSIS ole db vs SQL Server Destination Simple Performance Test
OLEDB destination is faster and its recommened but in this example SQL Server Destination is faster.
----------------------------------------------------------------------------------------------------------------------------------
Source:SSIS Performance Tuning Simple Load Part 5 Commit Size
Data Flow:
OLE DB Destination
-----------
OLE DB Destination Editor:
Configure the properties used to insert data into a relational database using an OLE DB provider.
[Connection Manager
Mappings
Error Output]
Specify an OLE DB Connection manager, a data source, or a data source view, and select the data access mode.If using the SQL Command access mode, specify the SQL Command either by typing
the query or by using Query Builder. For Fast - load access, set the table update options.
OLE DB Connection manager:
oledb-LoadDatabase
Data access mode:
Table or View - fast load
Name of the table or the view:
[dbo].[Tablename..]
[]Keep Identity [*]Table lock
[]Keep nulls []Check Constraints
Rows. per batch:
Maximum insert commit size: 0 ( changed from a higher number to 0) ( it means 1 transaction)[1 transaction: it will commit all rows, in 1 time{Logsize,TempDB size will be bigger]
[Caution: Table having "Clustered Index" won't work.][Only for heap table(having no indexes)]
View Existing Data
----
~Source:Making Fast Load really fast on clustered indexed tables with SSIS
Moreover, as using MICS parameter leads to a dramatic performance degradation, I think that the OleDb destination adapter should issue a warning if both ORDER and MICS parameter are set so to make the programmer think twice before using them together.
~Source:Batch Sizes, Fast Load, Commit Size And The OLE DB Destination
Rows per batch:
This setting affects the performance and commit semantics of the SQL engine, but doesn’t affect things on the SSIS side. Lowering this value does not affect the number of times SSIS commits data to the server.
Maximum insert commit size:
Enlisting the Data Flow task in a transaction also has the same effect as setting FastLoadMaxInsertCommitSize = 0. All rows will be committed or rolled back at the end of the Data Flow Task.
Beware
Setting the Maximum insert commit size property to a low value will cause a lot of overhead on the destination as the rows will be committed much more often.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SSIS Performance Tuning Simple Load Part 6 Packet Size SSIS 2012 Video Example
Ole Db -right click - All - see "Packet Size:0" - by default it means 4kb. Change it to 32KB which is maximum, if the value disappears then its on the "Connection" , choose the "project.param" , choose connection
string and specify "Packet Size"
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Source: SSIS Performance Tuning Simple Load Part 7 Row vs Page Compression SSIS 2012 Video Example
Connect to Server
Server type: Database Engine
Choose the Database->Table(right click "Storage" - "Manage Compression" - change Compression type "Page"
Next screen "Data Compression Wizard" , Run immediately ,
Task Manager - Look at "Performance", "Write Speed" is less , "Read speed" as usual.
Conclusion:Compression "Slows down" the load into "heap" table.
Observation:"Row" Compression is bit faster , "Write Speed" is little faster , "Read speed" little better
But any how "Row" Compression is slower.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Source: SSIS Performance Tuning Simple Load Part 8 Balanced Data Distributor SSIS 2012 Video Example
Balanced Data Distributor , does parallel processing.It works like "Conditional Split" , but its more efficient.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Source:SSIS ole db vs SQL Server Destination Simple Performance Test
OLEDB destination is faster and its recommened but in this example SQL Server Destination is faster.
----------------------------------------------------------------------------------------------------------------------------------
No comments:
Post a Comment