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

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

Kill active database connections

— Create the sql to kill the active database connections
declare @execSql varchar(1000), @databaseName varchar(100)
— Set the database name for which to kill the connections
set @databaseName = ‘your db name’

set @execSql = ”
select  @execSql = @execSql + ‘kill ‘ + convert(char(10), spid) + ‘ ‘
from    master.dbo.sysprocesses
where   db_name(dbid) = @databaseName
and
DBID <> 0
and
spid <> @@spid
exec(@execSql

or the easy way is:

-alter database dbName set single_user with rollback immediate

and after you want to bring it back to normal status

-alter database dbName set multi_user with rollback immediate

July 6, 2009 Posted by | Scripts, SQL SERVER | , | Leave a comment

Tweet-SQL

Integrate your application in super quick time using Tweet-SQL. Tweet-SQL promises to allow fast and easy Twitter Integration through the use of CLR stored procedures on Microsoft SQL Server. A quick summary of Tweet-SQL…

http://sqlclrnews.blogspot.com/2008/09/easy-twitter-integration-with-tweet-sql_3653.html

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

Useful bit of code

create table #temp (spid int, status nvarchar(max), login nvarchar(max), hostname nvarchar(max), blkby nvarchar(max), dbname nvarchar(max), command nvarchar(max), cputime int, diskio int, lastbatch nvarchar(max), programname nvarchar(max), spid2 int, requestid int)

insert #temp exec sp_who2

select hostname, count(*)
from #temp
where hostname != ‘ .’ AND login = ‘your login’
group by hostname
order by hostname

drop table #temp

This way you can filter sp_who2.

enjoy 🙂

June 26, 2009 Posted by | Scripts, SQL SERVER | | 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

50 top high CPU queries

select top 50

st.objectid, st.dbid,

— object_name(st.objectid, st.dbid), — uncomment this line if you’re using SQL 2005 SP2

total_worker_time/execution_count AS AverageCPUTime,

CASE statement_end_offset

WHEN -1 THEN st.text

ELSE SUBSTRING(st.text,statement_start_offset/2,statement_end_offset/2)

END AS StatementText

from

sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

ORDER BY AverageCPUTime DESC

June 22, 2009 Posted by | Scripts, SQL SERVER | , , , , , , , | Leave a comment

The old argument between Programmer and DBA

Within my work experience, there is the “old argument” between DBA and programmer. The programmer does not always understands why the DBA would not give him

permissions for executions or DML operations in the DB (INSERT, UPDATE and DELETE) .

The DBA, as a data protector, tries to do his best in protecting the database, and the programmer from the other hand wants

an access to the db, as he wants to develop new stuf and writing some T_SQL with no help from the DBA. I have to say, that although the DBA does and really wants to help the

programmer, main part of his job is to protect the data and manage permissions.

I would say, that the best solution is to give the programmer a way to work in front of the DB, but on the other hand not to give the programmer a SYSADMIN role 🙂

June 22, 2009 Posted by | General | , , , , | Leave a comment

Update statistics All Tables Script

SET NOCOUNT ON
GO
DECLARE updatestats CURSOR FOR
SELECT table_name FROM information_schema.tables
where TABLE_TYPE = ‘BASE TABLE’
OPEN updatestats

DECLARE @tablename NVARCHAR(128)
DECLARE @Statement NVARCHAR(300)

FETCH NEXT FROM updatestats INTO @tablename
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT N’UPDATING STATISTICS ‘ + @tablename
SET @Statement = ‘UPDATE STATISTICS ‘  + @tablename –+ ‘  WITH FULLSCAN’
–print @Statement
EXEC sp_executesql @Statement
FETCH NEXT FROM updatestats INTO @tablename
END

CLOSE updatestats
DEALLOCATE updatestats
GO
SET NOCOUNT OFF
GO

June 22, 2009 Posted by | Administration, Scripts, SQL SERVER | , , | Leave a comment

Grant Permissions script

/*this script will give you execution statements for all objects under your DB. Simply replace the DbAdmin with the user you want to give permissions to and that is it.*/

set nocount on

select ‘grant select, insert, update, delete on ‘ + name + ‘ to DbAdmin’ + char(13) + ‘go’

from     sysobjects

where   type = ‘U’

and       name not like ‘dtp%’

select ‘grant select, insert, update, delete on ‘ + name + ‘ to DbAdmin’ + char(13) + ‘go’

from     sysobjects

where   type = ‘V’

select ‘grant exec on ‘ + name + ‘ to DbAdmin’ + char(13) + ‘go’

from     sysobjects

where   type = ‘P’

and       name not like ‘dt%’

select ‘grant exec on ‘ + name + ‘ to DbAdmin’ + char(13) + ‘go’

from     sysobjects

where   type = ‘FN’

and       name not like ‘dt%’

June 12, 2009 Posted by | Permissions | , , , | Leave a comment

runtime of Jobs scheduled to run multiple times day on SQL Server 2005

I have developed a script to review the runtime of Jobs scheduled to run multiple times day on SQL Server 2005.

the script has been developed on SQL server 2005 and gives the ability to find out from all active jobs the precentage of running time divided to job interval in the last 24 hours.

couple of remarks:

1) Does not work in SQL Server 2000 because sp_help_jobschedule returns less columns in 2000
2) Does not support multiple Job schedules such as onetimes
3) Only list jobs that are scheduled to run multiple times a day

You can also find the post here:

http://www.sqlservercentral.com/scripts/Administration/63245/

The ones who does not have an account at sqlservercentral.com, here is the script:

USE [msdb]
GO
/*******************************************************
created by: Tomer Lev / Israel / SQL Server DBA
date: 28/05/2008
out put:
Run Date – job running date
Job Name
Average running time/Interval
Min running time/Interval
Max running time/Interval
stdev running time/Interval
*****************************************************/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create procedure [dbo].[JobsTimeIntervalCheckingTime]
AS

create table #tempschedule
(
id int identity(1,1) ,
schedule_id int,
schedule_name sysname,
enabled int,
freq_type int,
freq_interval int,
freq_subday_type int,
freq_subday_interval int,
freq_relative_interval int,
freq_recurrence_factor int,
active_start_date int,
active_end_date int,
active_start_time int,
active_end_time int,
date_created datetime,
schedule_description nvarchar(4000),
next_run_date int,
next_run_time int,
schedule_uid uniqueidentifier,
jobcoubnt int
)

create table #helpschedule
(
jobname sysname,
freq_type int,
freq_subday_type int,
freq_subday_interval int
)

declare @jobnametemp sysname
declare @schedule_id int

DECLARE CursorQuery CURSOR FOR
SELECT [name] FROM sysjobs WHERE [enabled] = 1

OPEN CursorQuery
FETCH NEXT FROM CursorQuery
INTO @jobnametemp
PRINT ‘Record Status’ + CAST(@@FETCH_STATUS as varchar)
WHILE @@FETCH_STATUS = 0
BEGIN
insert #tempschedule exec msdb..sp_help_jobschedule @job_name=@jobnametemp
insert #helpschedule select @jobnametemp,freq_type,freq_subday_type,freq_subday_interval from #tempschedule
where id = @@identity
FETCH NEXT FROM CursorQuery
INTO @jobnametemp
END
CLOSE CursorQuery
DEALLOCATE CursorQuery

drop table #tempschedule

select
[Run Date],
[Job Name],
avg([RunningTime/Interval])*100 as [Avg RunningTime/Interval],
min([RunningTime/Interval])*100 as [Min RunningTime/Interval],
max([RunningTime/Interval])*100 as [Max RunningTime/Interval],
stdev([RunningTime/Interval])*100 as [stdev RunningTime/Interval],
count(*) as [count]
from(
select
[Run Date],
[Job Name],
(seconds + minutes * 60 + hours * 3600) [total seconds runnning time],
([frequency_sub_type] * [freq_subday_interval]) as [interval Time in seconds],
cast((seconds + minutes * 60 + hours * 3600) as float) / cast(([frequency_sub_type] * [freq_subday_interval]) as float) [RunningTime/Interval]
from(
SELECT CAST(CONVERT(datetime,CAST(run_date AS char(8)),101) AS char(11)) [Run Date],
SUBSTRING(T2.name,1,40) [Job Name],
T1.[run_duration],
len(T1.[run_duration]) [len],
case len(T1.[run_duration])
when 4 then right(T1.[run_duration],2)
when 5 then right(T1.[run_duration],2)
when 6 then right(T1.[run_duration],2)
else T1.[run_duration] end [seconds],
case len(T1.[run_duration])
when 1 then 0.00001
when 2 then 0.00001
when 3 then 0.00001
when 4 then left(T1.[run_duration],2)
when 5 then substring(cast(T1.[run_duration] as varchar),2,2)
when 6 then substring(cast(T1.[run_duration] as varchar),3,2)
else T1.[run_duration] end [minutes],
case len(T1.[run_duration])
when 1 then 0.00001
when 2 then 0.00001
when 3 then 0.00001
when 4 then 0.00001
when 5 then substring(cast(T1.[run_duration] as varchar),1,1)
when 6 then substring(cast(T1.[run_duration] as varchar),1,2)
else T1.[run_duration] end [hours],
case(h.freq_type)
when 1 then ‘Once’
when 4 then ‘Daily’
when 8 then ‘Weekly’
when 16 then ‘Monthly ‘
when 32 then ‘Monthly, relative to the freq_interval’
when 64 then ‘Run when SQLServerAgent service starts’ end [frequency_type],
case(h.freq_subday_type)
when 4 then 60
when 1 then 1
when 8 then 3600 end [frequency_sub_type], — min, sec, hours
h.freq_subday_interval — time interval
FROM msdb..sysjobhistory T1
JOIN msdb..sysjobs T2
ON T1.job_id = T2.job_id
join #helpschedule h
on T2.name = h.jobname
where T2.enabled = 1 and T1.run_status not in (2,3,4) and T1.step_id = 0
AND run_date >= CONVERT(char(8), (select dateadd (day,-1,getdate())), 112))q
where [frequency_sub_type] * [freq_subday_interval] !=0
)
q2
group by [Run Date],[Job Name]

drop table #helpschedule

go

exec JobsTimeIntervalCheckingTime

Enjoy,
Tomer Lev

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