SQL Server Performance Tuning Tips and Tricks

SQL Server Performance Tuning 

For more than 2 yrs of experience people this Performance Tuning mandatory concept and they must have good knowledge on this.

Situations to go:

a) To create a package with optimization.

b) There is a package which is running such a long time.

In the above situation we need to identify the “bottle necks” and resolve it.

This bottle necks can be at many levels

  1. Package level.
  2. Source level.
  3. Destination level.
  4. Transformation level.
  5. Data flow task level.
  6. System level.

Identifying bottle necks

By using “progress tab” information or by using “log providers” we identify the bottle necks (because they display step by step execution).

Package level tuning tips

Implement check points to have better restart ability of components in the package.

a) Disable event handlers: Event handler decrease package performance so, unnecessary event handlers should be removed or disabled.

b) Maximum concurrent executables: Increasing the one no.of executable will increase the parallelism of package and concurrently execute in less time.

c) Maximum error count: Default ‘1’ means it fails for single error in the package. If you increase the error count it doesn’t fail the package until it reaches the count.

Data flow task level tips

1) Delay validation: (it’s true/ false)

True means the validations of component is delayed until the execution of other component finished.

Delay validation=true


Description: Until execute SQL task1” execution finishes. “Execute SQL task2”. Validation not started.

BLOB temp storage path: Specify this at the time of working with Binary large objects such as images, media files etc..

Default buffer max. rows and size: Increase or decrease according to the volume of data loading. i.e., for more volume increase rows and buffer size, for less volume, decrease rows and buffer size.

Engine threads: Default it takes ‘10’, if we increase more threads it runs more parallel and uses more processes to finish the data flow operations.

Run in optimized mode: If it’s true the dataflow avoids unnecessary transformations, conservations etc operations usage.

Source level tuning tips

a) In case of flat file

  1. Try to take the flat file local to the system.
  2. Use the property “fast parse=true”. So that the columns user faster, local neutral parsing routines and avoid unnecessary conversions.

b) If the source is table or view

  1. Create indexes on the source table so that it set gives the data faster.
  2. Instead of taking a table, take an SQL query or SQL command as data access mode to get required columns and rows of data.

Destination level tuning tips

a) In case of flat file

  1. Try to take the file local to the system
  2. In case of relational destination (table or view)
  3. Use data access mode as SQL command to load any required rows and columns.

b) Use data access mode as fast load to load the data much faster.

c) The table contains constraints, indexes, triggers, and then loading will be slow. So we need to disable or drop them, later once the loading is finished recreate or enable them.

To implement this there are many ways:


Another way to implement the above package functionality is by event handling by

On pre execute of data flow → Disable/drop      functionality

On post execute of data flow → Enable /recreate

Transformation level tuning tips

We need to identify either the transformation is synchronous or asynchronous.

Asynchronous transformation always taken time to run than synchronous.

Synchronous transformation:

Here the rows are processed as it is getting from the source.

Eg: derived column, data conversion…. Etc.

This transformation occupies less memory and processes

Asynchronous transformation:

In this case until the required rows the system collects, it doesn’t perform any operation.

It requires more memory and process many rows. This transformation either blocks partially or fully. So these transformations can also be called as “partially blocking or full blocking transformations”.

Eg:  sort, aggregate, merge..etc.

In this transformation, until they receive all rows they will not perform any operation.

  • Use ‘multicast’ transformation to read source data only one time and to perform different operations on the source in a single package.
  • Use ‘lookup’ transformation to get the exact values.
  • Use ‘SQL query options’ for look up to write customized query and to get required data.
  • Use ‘Bulk insert task’ to load the data from file to table if the table already existed and there are no transformations required.

Enroll for Instructor Led Live Online MSBI Training

0 Responses on SQL Server Performance Tuning Tips and Tricks"

Leave a Message

Your email address will not be published. Required fields are marked *

Copy Rights Reserved © Mindmajix.com All rights reserved. Disclaimer.