Ramasankar Molleti Blog

How to debug SSIS package

You or your team or someone has written an SSIS package which is running flawlessly over few days/months/years. One fine day SSIS package is failing continuously and you need to troubleshoot and fix the issue.

Error message is not showing much information about why the package has been failed and until what step the package was successful or failed.

What will you do in this scenario?

Debug! Debug! Debug! Let’s see how this works.

You can debug this using breakpoints and data viewer in SSIS. Let’s explore this.

Data Viewer:

You can view the data while the ssis package is running in each step. This will help you to know what data is processing in each step in data flow task. You can see that in the below screenshot.

Dataviewer

After you execute the package then the result can be seen as below

Dataviewer2

Break Points:

You can also debug a package by setting the breakpoints on a package task/container as below

Breakpoint

breakpoint2

As you can see in the above screenshot that you can enable any of the above options to debug the ssis package. Also can see each option in the below table.

Break condition	Description
When the task or container receives theOnPreExecute event.	Called when a task is about to execute. This event is raised by a task or a container immediately before it runs.
When the task or container receives theOnPostExecute event.	Called immediately after the execution logic of the task finishes. This event is raised by a task or container immediately after it runs.
When the task or container receives theOnError event.	Called by a task or container when an error occurs.
When the task or container receives theOnWarning event.	Called when the task is in a state that does not justify an error, but does warrant a warning.
When the task or container receives theOnInformation event.	Called when the task is required to provide information.
When the task or container receives theOnTaskFailed event.	Called by the task host when it fails.
When the task or container receives theOnProgress event.	Called to update progress about task execution.
When the task or container receives theOnQueryCancel event.	Called at any time in task processing when you can cancel execution.
When the task or container receives theOnVariableValueChanged event.	Called by the Integration Services runtime when the value of a variable changes. The RaiseChangeEvent of the variable must be set to true to raise this event. Warning The variable associated with this breakpoint must be defined at the container scope. If the variable is defined at the package scope, the breakpoint does not get hit.
When the task or container receives theOnCustomEvent event.	Called by tasks to raise custom task-defined events.

Both breakpoints and data viewer options are very useful when you troubleshoot/debug/develop complex ssis packages.

Hope you like the post!

Happy Reading!

Cheers

Ramasankar Molleti

MSDN:LinkedIn:Twitter

Script to get SQL Server Error logs via Email

This is one of the handy script to get sql server error logs via email.

declare @Time_Start datetime;
declare @Time_End datetime;
set @Time_Start=getdate()-2;
set @Time_End=getdate();

create table #ErrorLog (logdate datetime
, processinfo varchar(255)
, Message varchar(max) )

insert #ErrorLog (logdate, processinfo, Message)
EXEC master.dbo.xp_readerrorlog 0, 1, null, null , @Time_Start, @Time_End, N'desc';

create table #SQL_Log_Errors (
[logdate] datetime,
[Message] varchar (500) )

insert into #SQL_Log_Errors
select LogDate, Message FROM #ErrorLog
where (Message like '%err%'
or Message like '%warn%'
or Message like '%kill%'
or Message like '%dead%'
or Message like '%cannot%'
or Message like '%could%'
or Message like '%fail%'
or Message like '%not%'
or Message like '%stop%'
or Message like '%terminate%'
or Message like '%bypass%'
or Message like '%roll%'
or Message like '%truncate%'
or Message like '%upgrade%'
or Message like '%victim%'
or Message like '%recover%'
or Message like '%critical%'
or Message like '%IO requests taking longer than%')
AND Message not like '%errorlog%'
AND Message not like '%dbcc%'
AND Message not like '%checkdb%'
order by logdate desc

drop table #ErrorLog

declare @cnt int
select @cnt=COUNT(1) from #SQL_Log_Errors
if (@cnt > 0)
begin

declare @strsubject varchar(100)
declare @oper_email nvarchar(100)

set @oper_email = (select email_address from msdb.dbo.sysoperators where name = 'DBA')
select @strsubject='There are errors in the SQL Error Log on ' + @@SERVERNAME

declare @tableHTML nvarchar(max);
set @tableHTML =
N'<H1>SQL Error Log Errors - ' + @@SERVERNAME + '</H1>' +
N'
<table border="1">' +
N'
<tr>
<th>Log Date</th>
' +
N'
<th>Message</th>
</tr>
' +
CAST ( ( SELECT td = [logdate], '',
td = [Message]
FROM #SQL_Log_Errors
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>
' ;

EXEC msdb.dbo.sp_send_dbmail
@from_address='',
@recipients= @oper_email,
@subject = @strsubject,
@body = @tableHTML,
@body_format = 'HTML',
@profile_name='YourProfileName'

end

drop table #SQL_Log_Errors

go

Cheers

Ramasankar Molleti

MSDN:LinkedIn:Twitter

Cumulative Update #4 for SQL Server 2012 SP3

Microsoft announced the 4th cumulative update release for SQL Server 2012 SP3 is now available for download at the Microsoft Support site.

For original post you may refer here

Cheers

Ramasankar Molleti

MSDN:LinkedIn:Twitter

New SQL Server 2016 Online Alter column

Over the few months i have been playing with SQL Server 2016 new features. Microsoft SQL Server 2016 introduced new alter column with ONLINE option. Let’s explore this.

Create a table and insert data

CREATE TABLE dbo.test_online (column_1 INT ) ;
GO
INSERT INTO dbo.test_online(column_1) values(10)
GO

Find what page has been allocated for the above operation. I have used un documented function fn_PhysLocCracker to get the information

SELECT fnph.page_id FROM dbo.test_online
 OUTER APPLY sys.fn_PhysLocCracker(%%PhysLoc%%) AS fnph;

Perform alter column without online option

ALTER TABLE dbo.test_online
ALTER COLUMN column_1 DECIMAL (5, 2)
--WITH (ONLINE = ON);
GO

Verify what page has been allocated for the above alter column

You can see in the above screenshot that same page has been allocated for alter command.
Now, let’s Perform alter column with online option. Repeat the above steps

ALTER TABLE dbo.test_online 
 ALTER COLUMN column_1 DECIMAL (5, 2) 
 WITH (ONLINE = ON); 
GO

Now verify the allocated page for the above alter command

You can see in the above screenshot that the new page has been allocated for alter column statement without changing the old page id. This means leaving the old page available during the operation.

Conclusion: Online operation on alter column is really useful feature for DBA’s to perform the alter table with minimal downtime during the operation especially with table with large data.

Hope you like the post!

Happy Learning!

Cheers

Ramasankar Molleti

MSDN:LinkedIn:Twitter

The SQL Server Basic Installer: Just Install it in three clicks

Microsoft introduced the new Basic Installer experience for SQL Server 2016 Express, SQL Server 2016 Developer, and SQL Server 2016 Evaluation Editions. Just three clicks you can get a default installation of these editions. You may check full details here

Cheers

Ramasankar Molleti

MSDN:LinkedIn:Twitter

SQL 2014 Service Pack 2 is now Available !!!

Microsoft released SQL Server 2014 SP2 is available for download. These are the improvements introduced in SQL Server 2014 SP2

For the detailed list of improvements you may check here

Cheers

Ramasankar Molleti

MSDN:LinkedIn:Twitter

How to move Tables/objects between Filegroups

In this post, I will be explaining how to move tables/objects between different FileGroups.

Create a sample database

USE master
GO
CREATE DATABASE MoveFG
GO

Create Two file groups. In this example i have created two filegroups MoveFG_DATA_1 and MoveFG_DATA_2

ALTER DATABASE MoveFG ADD FILEGROUP MoveFG_DATA_1
GO
ALTER DATABASE MoveFG ADD FILEGROUP MoveFG_DATA_2
GO

Create data files in different filegroups. In this example, i have created four data files, two for each filegroup

ALTER DATABASE MoveFG
ADD FILE
( NAME = MoveFG11,
FILENAME = 'C:\Ram\MoveFGDB\MoveFG_11.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 1MB)
TO FILEGROUP MoveFG_DATA_1
GO

ALTER DATABASE MoveFG
ADD FILE
( NAME = MoveFG20,
FILENAME = 'C:\Ram\MoveFGDB\MoveFG_20.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 1MB)
TO FILEGROUP MoveFG_DATA_2
GO

ALTER DATABASE MoveFG
ADD FILE
( NAME = MoveFG21,
FILENAME = 'C:\Ram\MoveFGDB\MoveFG_21.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 1MB)
TO FILEGROUP MoveFG_DATA_2
GO

Now, create a table on filegroup MoveFG_DATA_1 and later move the filegroup to another group name MoveFG_DATA_2

USE MoveFG
GO

CREATE TABLE TAB1
(
TAB1_ID INT IDENTITY(1,1),
TAB1_NAME VARCHAR(100),
CONSTRAINT PK_TAB1 PRIMARY KEY(TAB1_ID)
) ON MoveFG_DATA_1 -- Filegroup we created.
GO

Insert few records into the table TAB1

SET NOCOUNT OFF
INSERT INTO TAB1(TAB1_NAME)
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
GO 10000

Verify which file group the data resides. You can use sp_help to get the details as shown in below.

As you can see above screenshot the table is on First file group MoveFG_DATA_1. Let us move the table TAB1 to another filegroup name MoveFG_DATA_2. To accomplish this we need to alter table with drop constraint with move option to move to another file group as shown below

ALTER TABLE TAB1 DROP CONSTRAINT PK_TAB1 WITH (MOVE TO MoveFG_DATA_2)
GO
ALTER TABLE TAB1 ADD CONSTRAINT PK_TAB1 PRIMARY KEY(TAB1_ID)
GO

Now, Check which file group the table TAB1 resides

There you go, The table has been moved to secondary file group MoveFG_DATA2. You can also use below query to get the details of tables/objects on different filegroups. I have taken this from Pinal’s Blog

SELECT obj.[name], obj.[type], i.[name], i.[index_id], fg.[name] FROM sys.indexes i
INNER JOIN sys.filegroups fg
ON i.data_space_id = fg.data_space_id
INNER JOIN sys.all_objects obj
ON i.[object_id] = obj.[object_id] WHERE i.data_space_id = fg.data_space_id
AND obj.type = 'U' -- User Created Tables
GO

Be careful running this code on a production system, You may want to consider running this code during a maintenance window so the users are not impacted.

Hope you like the post. Stay tuned for next tip.

Ramasankar Molleti

MSDN:LinkedIn:Twitter

New SQL Server 2016 Live Query Statistics

SQL Server 2016 introduce a new feature Live query statistics which allows us to view the real-time insights into query without needing to wait for the query to complete.

The live query plan displays the overall query progress and operator-level run-time execution statistics such as the number of rows produced, elapsed time, operator progress, etc.

Let’s see how it works.

I have opened the SSMS and write a sample query from Adventureworks databasse.

select a.* from HumanResources.Employee a cross apply HumanResources.Employee b

To view the live query execution plan, on the tools menu click the Live Query Statistics icon. Execute the query. The live query plan displays the execution plan as below

You can also highlight the query and right click and select “Include Live Query Statistics”

Another way of selecting the live query plan is to use activity monitor.

When you specify Include Live Query Statistics information it automatically enables statistics profile infrastructure for the current query session.

There are two other ways to enable the statistics infrastructure which can be used to view the live query statistics from other sessions (such as from Activity Monitor).

Execute SET STATISTICS XML ON; or SET STATISTICS PROFILE ON; in the target session.

Enable the query_post_execution_showplan extended event. This is a server wide setting that enable live query statistics on all sessions.

Note: This feature is available beginning with SQL Server 2016 Management Studio, however it can work with SQL Server 2014.

That’s all about Live Query Statistics

Cheers

Hope you like the post!

Ramasankar Molleti

MSDN:LinkedIn:Twitter

New SQL Server 2016 SCOPED CONFIGURATION

Microsoft SQL Server 2016 introduced new way of configuring some of the instance level setting to database level which gives you the ability to easily make several database level configuration changes such as

Setting MAXDOP for an individual database
Set the query optimizer cardinality estimation model independent of the database to compatibility level.
Enable or disable parameter sniffing for an individual database
Enable or disable query optimization hotfixes for an individual database
Clear the plan cache for an individual database without using DBCC Command

Let’s us explore each option:

MAXDOP:

Set the MAXDOP parameter to an arbitrary value (0,1,2, …) to control the maximum degree of parallelism for the queries in the database. It is recommended to switch to db-scoped configuration to set the MAXDOP instead of using sp_configure at the server level, especially for Azure SQL DB where sp_configure is not available. You can set the different MAXDOP settings for primary and secondary. For Example, you can set the MAXDOP value to 1 on a primary and on the secondary where used for reporting can be set to 4 as below.

-- Set MAXDOP for Primary database
 ALTER DATABASE SCOPED CONFIGURATION 
 SET MAXDOP = 1;
 GO

 -- Set MAXDOP for Secondary database(s)
 ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY 
 SET MAXDOP = 4;
 GO

Legacy Cardinality Estimation:

Set the option “LEGACY_CARDINALITY_ESTIMATION” Enables you to set the query optimizer cardinality estimation model to the SQL Server 2012 and earlier version independent of the compatibility level of the database. This is equivalent to Trace Flag 9481. To set this at the instance level, see Trace Flags (Transact-SQL). To accomplish this at the query level, add the QUERYTRACEON query hint.

You can use the below sql statements to enable the legacy cardinality estimation for primary and secondary based on your requirement.

-- Enable legacy Cardinality Estimation for Primary database
 ALTER DATABASE SCOPED CONFIGURATION 
 SET LEGACY_CARDINALITY_ESTIMATION = ON;
 GO
 -- Enable legacy Cardinality Estimation for Secondary database
 ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY 
 SET LEGACY_CARDINALITY_ESTIMATION = ON;
 GO

 -- Set legacy Cardinality Estimation for the Secondary database(s) 
--to the same value as the Primary database
 ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY 
 SET LEGACY_CARDINALITY_ESTIMATION = PRIMARY;
 GO

Enable/Disable Parameter Sniffing:

Enables or disables parameter sniffing. This is equivalent to Trace Flag 4136. o set this at the instance level, see Trace Flags (Transact-SQL). To set this at the query level, see the OPTIMIZE FOR UNKNOWN query hint.

You can use the below sql statements to disable parameter siniffing.

 -- Disable parameter sniffing for Primary database
 ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF;
 GO

 -- Disable parameter sniffing for Secondary database
 ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = OFF;
 GO

 -- Set parameter sniffing for the Secondary database(s) to 
 ---the same value as the Primary database

 ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY 
 SET PARAMETER_SNIFFING = PRIMARY;
 GO

Query Optimizer Hotfixes:

Enable or disable “QUERY_OPTIMIZER_HOTFIXES” at the database level, to take advantage of the latest query optimizer hotfixes, regardless of the compatibility level of the database. This is equivalent to Trace Flag 4199

Sample T-SQL to enable query optimizer hotfixes

-- Enable query optimizer fixes for Primary database
ALTER DATABASE SCOPED CONFIGURATION
SET QUERY_OPTIMIZER_HOTFIXES = ON;
GO

-- Enable query optimizer fixes for Secondary database
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY
SET QUERY_OPTIMIZER_HOTFIXES = ON;
GO

Clear Procedure Cache:

“Clear Procedure Cache” option allows to clear procedure cache at the database level without impacting other databases.

Sample T-SQL Script to clear the procedure cache

 -- Clear plan cache for current database (only possible for Primary database)
 ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

That’s all about new sql server 2016 scoped configuration option.

Reference: https://msdn.microsoft.com/en-us/library/mt629158.aspx

Cheers

Hope you like the post!

Ramasankar Molleti

LinkedIn: LinkedIn Profile

Twitter: Twitter

“If you could give a DBA just one piece of advice, what would it be?”

“If you could give a DBA just one piece of advice, what would it be?”

For those of you hunger to learn and explore your skills you may read the book DBA Jump start

This book contains advice from most renowned successful Database Professionals.

Cheers

Hope you like the post!

Ramasankar Molleti

LinkedIn: LinkedIn Profile

Twitter: Twitter