Identifying fragmentation level in SQL Server 2005 and 2008

Identifying fragmentation level in SQL Server 2005 and 2008

While indexes can speed up execution of queries several fold as they can make the querying process faster, there is overhead associated with them. They consume additional disk space and require additional time to update themselves whenever data is updated, deleted or appended in a table. Also when you perform any data modification operations (INSERT, UPDATE, or DELETE statements) index fragmentation may occur and the information in the index can get scattered in the database. Fragmented index data can cause SQL Server to perform unnecessary data reads and switching across different pages, so query performance against a heavily fragmented table can be very poor.
Refer this link to learn more details about fragmentation and different queries to determine the level of fragmentation.

http://www.mssqltips.com/tip.asp?tip=1708

MERGE SQL Statement in SQL Server 2008

MERGE SQL Statement in SQL Server 2008

In a typical data warehousing application, quite often during the ETL cycle you need to perform INSERT, UPDATE and DELETE operations on a TARGET table by matching the records from the SOURCE table. For example, a products dimension table has information about the products; you need to sync-up this table with the latest information about the products from the source table. You would need to write separate INSERT, UPDATE and DELETE statements to refresh the target table with an updated product list or do lookups. Though it seems to be straight forward at first glance, but it becomes cumbersome when you have do it very often or on multiple tables, even the performance degrades significantly with this approach. With the new MERGE SQL statement in SQL Server 2008 you can perform all these operations in one pass.

Refer this link to learn more details about it.

http://www.mssqltips.com/tip.asp?tip=1704

Debugging T-SQL in SQL Server 2008 SSMS

Debugging T-SQL in SQL Server 2008 SSMS

If you recall your days working with SQL Server 2000, you would remember debugging a routine (Stored Procedure, UDF and trigger) in Query Analyzer, as a debugger tool was available with it. Starting with SQL Server 2005, Query Analyzer and Enterprise Manager had been clubbed together as SQL Server Management Studio (SSMS). Though this single interface has simplified working with SQL Server, one major drawback was, it does not allow you to debug a routine from there. For that purpose you needed Visual Studio (Enterprise and Professional) edition installed, on your development machine, which allowed you to debug a routine. The requirement to install Visual Studio is something that database developers and DBAs would be reluctant to do as it requires additional funds for a Visual Studio license and puts additional pressure on the physical box after installation. Thankfully Microsoft SQL Server team decided to provide this feature in SQL Server 2008 SSMS.

Refer this link to learn more details about it.

http://www.mssqltips.com/tip.asp?tip=1695

Migration Strategies for SQL Server 2008

Migration Strategies for SQL Server 2008

SQL Server 2008 delivers a powerful set of capabilities to solve the growing needs of managing data in the enterprise, on desktops, and on mobile devices, it also builds on the strong momentum in the business intelligence market by providing a scalable infrastructure that enables information technology to drive business intelligence throughout the organization and deliver intelligence where users want it. SQL Server 2008 also delivers improved performance in many areas, including data warehousing, reporting, and analytics. So if you make the decision to upgrade to 2008, there are a number of tools that make the process easier, but you still need to understand what things you should consider.

Refer this link to learn more details about it.

http://www.sqlservercentral.com/articles/Upgrade/65872/

VSTA support for Script Task and Script Component in SSIS 2008

VSTA support for Script Task and Script Component in SSIS 2008

Unlike SQL Server 2005, SQL Server 2008 provides VSTA (Visual Studio Tools for Applications) environment for writing Script Task and Script Component instead of VSA (Visual Studio for Applications) environment. VSTA includes all the standard features of the Visual Studio environment, such as the color-coded Visual Studio editor, IntelliSense, and Object Browser and debugging features like breakpoints, watch/auto/locals windows and many more.

Refer this link to learn more details about it.

http://www.sql-server-performance.com/articles/biz/SSIS_New_Features_in_SQL_Server_2008_Part5_p1.aspx

Pipeline Performance Improvements in SSIS 2008

Pipeline Performance Improvements in SSIS 2008

In SQL Server 2008 SSIS, the data flow task has been redesigned to do dynamic scheduling and can now execute multiple components in parallel, even if they belong to the same execution tree. In other words, several threads can work together to do the work that a single thread is forced to do by itself in SQL Server 2005 SSIS. This can give you several-fold speedup in ETL performance.

Refer this link to learn more details about it.

http://www.sql-server-performance.com/articles/biz/SSIS_New_Features_in_SQL_Server_2008_Part4_p1.aspx

Data Profiling task in SSIS 2008

Data Profiling task in SSIS 2008

In SQL Server 2008, SSIS introduces the Data Profiling task in its toolbox, which provides data profiling functionality inside the process of extracting, transforming, and loading data. By using the Data Profiling task, you can analyze the source data more effectively, understand the source data better, and prevent data quality problems before they are introduced into the data warehouse.

Refer this link for more details.

http://www.sql-server-performance.com/articles/biz/SSIS_New_Features_in_SQL_Server_2008_Part3_p1.aspx

Lookup Transformations in SSIS 2008

Lookup Transformations in SSIS 2008

Lookup transformation in SSIS 2008 has been improved to allow explicit control over the lookup data, a new breed of connection manager viz. Cache Connection Manager has been introduced to store cache to file and share cached lookup data among different components and packages and finally the more intuitive UI for designing Lookup transformation.

Refer this link for more details.

http://www.sql-server-performance.com/articles/biz/SSIS_New_Features_in_SQL_Server_2008_Part2_p1.aspx

Lookup Transformation in SSIS 2005 and 2008

Lookup Transformation in SSIS 2005 and 2008

The Lookup transformation performs lookups by joining data in input columns with columns in a reference dataset/table. If there is no matching entry in the reference dataset, no join occurs and no values are returned from the reference dataset. This is an error, and the transformation fails, unless it is configured to ignore errors or redirect error rows to the error output. If there are multiple matches in the reference table, the lookup returns only the first match based on the lookup query.

I have written articles covering Lookup Tranformation in SSIS 2005 and Lookup Transformation in SSIS 2008 in details including different caching mechanism and detail usage example.

Refer this link for more details.

http://www.sql-server-performance.com/articles/biz/SSIS_New_Features_in_SQL_Server_2008_Part1_p1.aspx

SSIS Parallel Processing

SSIS Parallel Processing

Parallel execution improves the performance on the computers that have multiple physical or logical processors. To support parallel execution of different tasks in the package, SSIS uses two properties: MaxConcurrentExecutables and EngineThreads.

In my next article on SSIS Parallel processing I will cover how you can utilize the parallel processing capabilities of SSIS. In the later part of this article, I will provide some tips for SSIS Performance optimization and finally I will talk of what you need to take care of when executing your SSIS Package on 64-bit computers.

Refer this link for more details.

http://www.sql-server-performance.com/articles/biz/SSIS_An_Inside_View_Part_4_p1.aspx

SSIS Buffer Management

SSIS Buffer Management
Data flow engine requires buffer to store incoming data from source, do the necessary transformation in-memory if any, and upload it in the destination. The creation, allocation and management of buffer are done by SSIS Buffer Manager.

I have written an article covering all aspects of SSIS buffer management, including how buffers are allocated and de-allocated for the transformation, different kind of buffer related performance counters etc.

Refer this link for more details.
http://www.sql-server-performance.com/articles/biz/SSIS_An_Inside_View_Part_3_p1.aspx

SSIS Transformation and Execution Tree

SSIS Transformation and Execution Tree

There are two main concepts related to SSIS internals which need to be understood before we deep dive in optimizing SSIS packages.
Transformation - There are different kinds of tranformation in SSIS which overall impacts the performance of SSIS.
Execution Tree - At run time, the data flow engine breaks down Data Flow task operations into execution trees. Execution trees are enormously valuable in understanding buffer usage.

I have written an article covering different kinds of transformation and how data flow task operations are divided into execution trees.

Refer this link for more details.

http://www.sql-server-performance.com/articles/biz/SSIS_An_Inside_View_Part_2_p1.aspx

SSIS Architecture

SSIS Architecture
SSIS is a component of SQL Server 2005/2008 and is successor of DTS (Data Transformation Services) which had been in SQL Server 7.0/2000. Though from end-user perspective DTS and SSIS looks similar to each to some extent, it is not the case in actual. SSIS has been completely written from the scratch (it’s a new enterprise ETL product altogether) and hence it overcomes the several limitations of DTS.

I have written an article covering SSIS architecure and how it is different from DTS. Understanding these things will let you understand internal of SSIS or how SSIS actually works.

Refer this link for more details.
http://www.sql-server-performance.com/articles/biz/SSIS_An_Inside_View_Part_1_p1.aspx