The DBA Blog by Tomer Lev

SQL information, scripts, tricks and more…

SSIS Logging and Performance

SSIS Logging and monitoring

  • SQL Server Integration Services includes log providers that you can use to implement logging in packages, containers, and tasks. With logging, you can capture run-time information about a package, helping you audit and troubleshoot a package every time it is run. For example, a log can capture the name of the operator who ran the package and the time the package began and finished.

When you add the log to a package, you choose the log provider and the location of the log. The log provider specifies the format for the log data: for example, a SQL Server database or text file.

MSDN link explanation you can find here:

http://msdn.microsoft.com/en-us/library/ms140246.aspx

  • Here you can find how to enable logging in the package (I have also explained Daniel how to do it):

http://msdn.microsoft.com/en-us/library/ms141212.aspx

  • Integration Services includes logging features that write log entries when run-time events occur and can also write custom messages.

Integration Services supports a diverse set of log providers, and gives you the ability to create custom log providers. The Integration Services log providers can write log entries to text files, SQL Server Profiler, SQL Server, Windows Event Log, or XML files.

Here you can find how to configure your SSIS package:

http://msdn.microsoft.com/en-us/library/ms138020.aspx

  • SQL Server Integration Services provide a set of performance counters. Among them the following few are helpful when you tune or debug your package:
  • Buffers in use
  • Flat buffers in use
  • Private buffers in use
  • Buffers spooled
  • Rows read
  • Rows written

“Buffers in use”, “Flat buffers in use” and “Private buffers in use” are useful to discover leaks. During package execution time, you will see these counters fluctuating. But once the package finishes execution, their values should return to the same value as what they were before the execution. Otherwise, buffers are leaked. In occasions like that, please contact Microsoft PSS.

“Buffers spooled” has an initial value of 0. When it goes above 0, it indicates that the engine has started memory swapping.

“Rows read” and “Rows written” show how many rows the entire Data Flow has processed. They give you an overall idea about the execution progress.

  • Here is a link for top 10 SSIS best practices:

http://sqlcat.com/top10lists/archive/2008/10/01/top-10-sql-server-integration-services-best-practices.aspx

  • Here is some code you can use in order to store the logging to Audit table:

CREATE TABLE AuditPackage (Id INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
PackageName VARCHAR(100) NOT NULL,
PackageGuid UNIQUEIDENTIFIER,
ExecutionGuid UNIQUEIDENTIFIER,
StartTime DATETIME,
EndTime DATETIME,
ElapsedTime INT,
Status VARCHAR(100));
GO

CREATE PROCEDURE dbo.sp_dts_addlogentry @event sysname,
@computer nvarchar(128),
@operator nvarchar(128),
@source nvarchar(1024),
@sourceid uniqueidentifier,
@executionid uniqueidentifier,
@starttime datetime,
@endtime datetime,
@datacode int,
@databytes image,
@message nvarchar(2048)
AS
BEGIN
INSERT INTO sysdtslog90 (event, computer, operator, source, sourceid, executionid, starttime, endtime, datacode, databytes, message)
VALUES (@event, @computer, @operator, @source, @sourceid, @executionid, @starttime, @endtime, @datacode, @databytes, @message);

INSERT INTO AuditPackage (PackageName, PackageGuid, ExecutionGuid, StartTime, ElapsedTime)
SELECT @source, @sourceid, @executionid, GETDATE(), 0
WHERE (@event = ‘PackageStart’);

UPDATE AuditPackage
SET EndTime = GETDATE(),
ElapsedTime = DATEDIFF(ms, StartTime, GETDATE()),
Status = ‘Complete’
WHERE (@event = ‘PackageEnd’
AND PackageGuid = @sourceid
AND ExecutionGuid = @executionid);

UPDATE AuditPackage
SET Status = ‘Error’
WHERE (@event = ‘OnError’
AND PackageGuid = @sourceid
AND ExecutionGuid = @executionid);
END

The SSIS logging gives you the ability to find bottlenecks in your SSIS package like:

  • How much time it takes to validate packages
  • Trace each step and find out how long it takes
  • Gives you a tool you can use to log valuable events of the package.

SSIS: Custom Logging Using Event Handlers

SQL Server Integration Services (SSIS) contains some really useful logging procedures but as with most things in SSIS, it is extensible. There are 2 methods of extending the logging capability of SSIS:

  • Build a custom log provider
  • Use event handlers

Look at this link in order to find out which events to capture and how to customize them:

http://blogs.conchango.com/jamiethomson/archive/2005/06/11/SSIS_3A00_-Custom-Logging-Using-Event-Handlers.aspx

Advertisements

July 8, 2009 Posted by | Administration, General, SQL SERVER, Tips | , , , , | 1 Comment

Defragmenting Indexes in SQL Server 2005

http://weblogs.sqlteam.com/tarad/archive/2008/09/03/Defragmenting-Indexes-in-SQL-Server-2005.aspx

I think this may come in handy…

June 26, 2009 Posted by | General, SQL SERVER, Tips | , , | Leave a comment

Craig Freedman’s SQL Server Blog

http://blogs.msdn.com/craigfr/

It seems like a very interesting blog. You might find it useful…

June 26, 2009 Posted by | General, Tips | , , | Leave a comment

Data Warehouse Query Performance

sql server 2008
Data Warehouse Query Performance:

http://technet.microsoft.com/en-us/magazine/2008.04.dwperformance.aspx

June 26, 2009 Posted by | Administration, General, SQL SERVER, Tips | , | Leave a comment

Clean Cache

Lets say you have a query and you need to check performance for this one.
Somtimes you will need to clean the cache – it is quite rare, but still there…
In order to clean the cache from any data, use the following:
dbcc dropcleanbuffers;
to clean any execution plan use:
dbcc freeproccache;
you need to take under considuration that these kind of operations influence on the all system. The SQL will need to read disk pages phisically and recompile all exeution plans – so be carefull when doing it on production servers…

June 26, 2009 Posted by | Administration, SQL SERVER, Tips | , | Leave a comment

SAVE XML EXCEUTION PLAN IN GRAPHIC WAY

Some DBA’s need to save the execution plan in graphic way. In order to do so you need to:

1.save your exceution query XML as filename.SQLPLAN

2. open the file with management studio, which open it in a graphic way instead of XML.

June 13, 2009 Posted by | SQL SERVER, Tips | , , , | Leave a comment