jump to navigation

Identifying fragmentation level in SQL Server 2005 and 2008 March 22, 2009

Posted by Arshad Ali in Database Administration, DBA, SQL Server, SQL Server 2008, SQL Server New Version.
add a comment

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 March 22, 2009

Posted by Arshad Ali in Uncategorized.
add a comment
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 March 22, 2009

Posted by Arshad Ali in Database Administration, DBA, SQL Server, SQL Server 2008, SQL Server New Version.
add a comment
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 March 22, 2009

Posted by Arshad Ali in Database Administration, DBA, SQL Server, SQL Server 2008, SQL Server New Version.
add a comment

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 March 17, 2009

Posted by Arshad Ali in SQL Server Integration Services, SSIS, SSIS 2008.
add a comment

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 March 17, 2009

Posted by Arshad Ali in SQL Server Integration Services, SSIS, SSIS 2008.
add a comment
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 March 17, 2009

Posted by Arshad Ali in SQL Server Integration Services, SSIS, SSIS 2008.
add a comment
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 March 13, 2009

Posted by Arshad Ali in Uncategorized.
add a comment

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 March 13, 2009

Posted by Arshad Ali in SQL Server Integration Services, SSIS, SSIS 2008.
1 comment so far
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

Lookup Transformation in SSIS 2005 March 13, 2009

Posted by Arshad Ali in SQL Server Integration Services, SSIS, SSIS 2008.
add a comment
Lookup Transformation in SSIS 2005
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 an article covering Lookup trasnformation in SSIS 2005 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