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
- Package level.
- Source level.
- Destination level.
- Transformation level.
- Data flow task level.
- 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.
Subscribe to our youtube channel to get new updates..!
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.
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
Try to take the flat file local to the system.
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
Create indexes on the source table so that it set gives the data faster.
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
- Try to take the file local to the system
- In case of relational destination (table or view)
- 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.
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
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.
List of Related Microsoft Certification Courses:
|SSAS||SQL Server DBA|
|Team Foundation Server||BizTalk Server Administrator|