Home > SSIS > Update Statistics using SSIS

Update Statistics using SSIS


Statistics are the most important attributes of a table or indexed view that helps SQL Query optimizer to create an efficient query plan.
Updating statistics of a table will keep the query performance better by preparing better query plan. There are lots of proven experiments that how query optimizer design a best query plan if the statistics are up-to-date on web.
There are certain pros and cons in this statistic updates. People often debate about the tradeoff between query performance and query recompile time if the statistics are update frequently. It also uses Tempdb.  Update statistics frequently is entirely depends on the application or necessity.
It is always good to keep the server with the best performanceJ.
In this article, I will explain you that how to update statistics of table(s) in database(s). Of course, you can select multiple databases and tables from a SQL Instance using this task in SSIS.
Implementation steps
Step 1: create a package and add Update Statistics task from tool box
Step 2: Edit the task and create new database connection by clicking on new button and configure database connection and click OK
Step 3: select database(s) and also check the option to ignore database when it is offline
Step 4: Select object type for statistics update. I have chosen table in this example.
Step 5: Select object listed in box. In this picture, it is showing tables from the selected database
Step 6: Choose the statistic update option from the select option. Learn more about each option in MSDN link given at the end of this article. In our example I have chosen column statistics update option
Step 7: choose scan type for the statistics from this options. In this example I am taking 50% sample data for the scan. It ranges from 0 to 100. It will use TempDB for sorting the data
Step 8: click view button to view T-SQL for the setting of this task and click close button to go back to task configuration
Step 9: finally, click ok and complete the process and execute the package
To learn more about Update statistics read http://msdn.microsoft.com/en-us/library/ms187348.aspx
Thanks for reading this article. I welcome your comments. Please spend some time for comments here.
Advertisements
Categories: SSIS Tags:
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: