SSRS ExecutionLog For Performance

SSRS Executing log table contains  important metrics for performance.It allows to see the reports execution results which contains information aboutthe user who executed reports,the amount of time it has taken  for data retrieval,time taken to process,time taken to render,no of bytes,number of rows  ,time the report started execution,time the report completed etc .These metrics can be further analysed ,suppose we can use the no of rows and bytes to check which report is giving large number of records ,also the execution time for data retrievel and further we can use other performance tools to look into the details of  query if there is any other issues such as blocking .

SSIS Connection Managers

There are many connecton managers ,the most popular connection managers are ADO.NET,OLEDB
so 'when to choose what' is important.when you need speed, use oledb else use Ado.Net .It Provides greater flexibility  where you can execute different scripts with parameters  but marshaling can be overhead which gives ado.net that extra flexibility

Another important   connection manager which is less known is Multi Flat-file connection manager,it allows to load multiple files.It brings major performance benefits as everything is executed within a data flow

Upcoming SSIS Books

Two new SSIS  books are coming from well known authors in early 2012

Professional Microsoft SQL Server 2012 Integration Services

SSIS Design Patterns

SSIS Non Relational Source Optimisation

When dealing with non relational  sources  a lot factors can play important role increasing the performance of the flow.As we know ssis moves the data in buffers in the data flow,so  the buffer size play an important role in  the overall  speed of load.When we connect to non relational    sources such as text file ,the source component allocates  default data type string of  length 50 as shown below

which might consume a lot of buffer size.You can optimise by selecting a sample of rows and analysing the data types and length.This can be done on connection manager editor  advanced  properties  and suggest types  tab and accept the suggest column types as below

and after  accepting suggest column types it will optimise to minimum length. based on the column length as
shown below

This is an important  step as it impacts buffer size heavily

SSIS DataFlow Destination Optimisation

Use FastLoad  at Destination ,try to use the batch size depending upon the buffer size .If you use Fast load and Buffer size of 0 it will push all the rows in single transaction,it may create some back pressure on the flow .

Avoid using sqlserver destination if your destination is on different server.

Make sure to turn on trace flag 610 for minimal logging.

SSIS DataFlow Performance Guide

Avoid Use of Blocking Transformations such as Sort .If you have a Relational Source  sort it in the source adapter query .
Avoid use Oledb Command for updates on a large dataset,try to use set based updates by using staging table and sql task.
If you use SCD component try to do the updates in a setbased way  by removing oledb command and using a staging table.
Convert DateTime Conversions at source itself
Increase Network PacketSize at Source Adapter default is  4k maximum is 32k
Select only the required columns from source
Use LockHint   NoLock  in the source query to avoid locking over head when there is no trasactional activity going at source at the time of load
Efficiently use lookup Full Cache where possible.avoid using full cache for small number of records
When using lookups try to ignore failure and handle all the lookups failures down in a derived column transform.Because if you error handle in lookup and unionall ,the union all copies the buffer bucket which creates overhead