THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Andy Leonard

Andy Leonard is an author and engineer who enjoys building and automating data integration solutions.

Parsing SSIS Catalog Messages for Lookup Performance, v2

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!

Published Wednesday, March 08, 2017 2:25 PM by andyleonard

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

News

My Latest Book:

Community Awards



Friend of Red Gate

Contact Me

Archives

Privacy Statement