A couple years ago I wrote Administering SSIS: Parsing SSIS Catalog Messages for Lookups. I’ve updated the script to return all Lookup Transformation performance messages captured by the SSIS Catalog with Basic logging enabled (the SSIS Catalog’s default Logging Level). Some smart data integration people recommend setting the SSIS Catalog Logging Level to Verbose and querying catalog schema views to retrieve row counts and performance metrics. I’m not a fan of running a Verbose logging level by default due to the overhead. Granted, the overhead is relatively light but it’s not nothing. Sometimes I need all the cycles!
I prefer to passively gather performance metrics – even if it’s a little convoluted and more work (for me). I don’t often need these metrics immediately available, so I execute queries similar to the query below and store the performance metrics in a table. I can (and do) build dashboards to track SSIS performance (perhaps I should blog about them…) using this passively-collected performance metrics.
Without further ado, the updated query:
Use SSISDB
go
declare @LookupIdString varchar(100) = '% rows in the cache. The processing time was %'
declare @LookupNameStartSearchString varchar(100) = '%:Information: The %'
declare @LookupNameStartLen int = Len(@LookupNameStartSearchString) - 2
declare @LookupNameEndSearchString varchar(100) = '% processed %'
declare @LookupNameEndLen int = Len(@LookupNameEndSearchString) - 2
declare @ProcessingTimeString varchar(100) = 'The processing time was '
declare @ProcessingTimeSearchString varchar(100) = '%' + @ProcessingTimeString + '%'
declare @CacheUsedString varchar(100) = 'The cache used '
declare @CacheUsedSearchString varchar(100) = '%' + @CacheUsedString + '%'
Select
SubString(om.[message]
, (PatIndex(@LookupNameStartSearchString, om.[message]) + @LookupNameStartLen)
, (PatIndex(@LookupNameEndSearchString, om.[message]) - (PatIndex(@LookupNameStartSearchString, om.[message]) + @LookupNameStartLen))
) As LookUpName
, Convert(bigint, Substring(om.[message]
, (PatIndex(@LookupNameEndSearchString, om.[message]) + @LookupNameEndLen)
, ((CharIndex(' ', om.[message], PatIndex(@LookupNameEndSearchString, om.[message]) + @LookupNameEndLen))
-
(PatIndex(@LookupNameEndSearchString, om.[message]) + @LookupNameEndLen)
)
)
) As LookupRowsCount
, Convert(decimal(16, 3), Substring(om.[message]
, (PatIndex(@ProcessingTimeSearchString, om.[message]) + Len(@ProcessingTimeString) + 1)
, ((CharIndex(' ', om.[message], PatIndex(@ProcessingTimeSearchString, om.[message]) + Len(@ProcessingTimeString) + 1))
-
(PatIndex(@ProcessingTimeSearchString, om.[message])+ Len(@ProcessingTimeString) + 1)
)
)
) As LookupProcessingSeconds
, Convert(bigint, Substring(om.[message]
, (Patindex(@CacheUsedSearchString, om.[message]) + Len(@CacheUsedString) + 1)
, ((Charindex(' ', om.[message], Patindex(@CacheUsedSearchString, om.[message]) + Len(@CacheUsedString) + 1))
-
(Patindex(@CacheUsedSearchString, om.[message]) + Len(@CacheUsedString) + 1))
)
) As LookupBytesUsed
, Convert(decimal(16, 3), (Convert(bigint, Substring(om.[message]
, (PatIndex(@LookupNameEndSearchString, om.[message]) + @LookupNameEndLen)
, ((CharIndex(' ', om.[message], PatIndex(@LookupNameEndSearchString, om.[message]) + @LookupNameEndLen))
-
(PatIndex(@LookupNameEndSearchString, om.[message]) + @LookupNameEndLen))
)
)
/
(Convert(decimal(16, 3), Substring(om.[message]
, (PatIndex(@ProcessingTimeSearchString, om.[message]) + Len(@ProcessingTimeString) + 1)
, ((CharIndex(' ', om.[message], PatIndex(@ProcessingTimeSearchString, om.[message]) + Len(@ProcessingTimeString) + 1))
-
(PatIndex(@ProcessingTimeSearchString, om.[message])+ Len(@ProcessingTimeString) + 1 ))
)
)
)
)
) As LookupRowsThroughput
, ex.start_time As ExecutionStartTime
, ex.folder_name As FolderName
, ex.project_name As ProjectName
, ex.package_name As PackageName
--Into rpt.LookupMetrics -- drop table rpt.LookupMetrics
From [catalog].operation_messages om
Join [catalog].executions ex
On ex.execution_id = om.operation_id
Where om.message_source_type = 60 -- Data Flow Task
And om.[message] Like @LookupIdString
-- data type-checking
And IsNumeric(Substring(om.[message], (PatIndex(@LookupNameEndSearchString, om.[message]) + @LookupNameEndLen ), ((CharIndex(' ', om.[message], PatIndex(@LookupNameEndSearchString, om.[message]) + @LookupNameEndLen)) - (PatIndex(@LookupNameEndSearchString, om.[message]) + @LookupNameEndLen)))) = 1 -- Check LookupRowsCount
And IsNumeric(Substring(om.[message], (PatIndex(@ProcessingTimeSearchString, om.[message]) + Len(@ProcessingTimeString) + 1 ), ((CharIndex(' ', om.[message], PatIndex(@ProcessingTimeSearchString, om.[message]) + Len(@ProcessingTimeString) + 1)) - (PatIndex(@ProcessingTimeSearchString, om.[message])+ Len(@ProcessingTimeString) + 1 )))) = 1 -- Check LookupProcessingSeconds
And IsNumeric(Substring(om.[message], (Patindex(@CacheUsedSearchString, om.[message]) + Len(@CacheUsedString) + 1 ), ((Charindex(' ', om.[message], Patindex(@CacheUsedSearchString, om.[message]) + Len(@CacheUsedString) + 1)) - (Patindex(@CacheUsedSearchString, om.[message]) + Len(@CacheUsedString) + 1)))) = 1 -- Check LookupBytesUsed
-- catch 0-valued denominator rows
And Convert(bigint, Substring(om.[message], (PatIndex(@LookupNameEndSearchString, om.[message]) + @LookupNameEndLen ), ((CharIndex(' ', om.[message], PatIndex(@LookupNameEndSearchString, om.[message]) + @LookupNameEndLen)) - (PatIndex(@LookupNameEndSearchString, om.[message]) + @LookupNameEndLen)))) <> 0 -- Check LookupRowsCount
Order By operation_id DESC
:{>
Related Training:
IESSIS1: Immersion Event on Learning SQL Server Integration Services – April 2017, Chicago
From Zero to Biml – 19-22 Jun 2017, London
IESSIS2: Immersion Event on Advanced Integration Services – Oct 2017, Chicago
Learn More:
SSIS Design Patterns and Biml: A Day of Intelligent Data Integration – Boston SQL Saturday precon, 24 Feb 2017
Save Time and Improve SSIS Quality with Biml
An Example of Data Integration Lifecycle Management with SSIS, Part 4
The Recordings for SSIS Academy: Using the SSIS Catalog are Available
SSIS Catalog Compare v2.0 Launch Event 7 Mar 2017!
