Logging during Cleansing Stored Procedure

Hi Peter,

As as support specialist, I'd like to see more detail in the execution log myself!  It would make my job a lot easier in some cases.  I'm definitely upvoting this idea.  

I will say that there are a number of native Windows/SQL Server and 3rd party monitoring and logging tools that you can use to accomplish this goal right now.  Working with you or your client's DBA is very helpful for this sort of thing.

As an aside, lookups often perform more slowly if auto indexing is turned off, if they are using certain legacy algorithms, or if they are TOP lookups with a sorting that requires organizing a lot of data.  If you are getting poor performance on simple, normal lookups, do let us know!

Best regards,

Doug Wynkoop

+1 and preferably to an external (auxiliary) database:

https://support.timextender.com/hc/en-us/community/posts/115017825683-Row-Count-execution-time-logging-to-external-database

We are doing performance improvements by re-examining our lookups but it's hard to pinpoint. So far we have to fall back on the execution plans generated by SQL Server Management Studio and see which query has the biggest impact in the plan.

Great idea and it could easily be added to the Data Cleansing stored procs to log some details for each step within.

Great idea! We are also experiencing very slow lookups, this seems to be because TX wants to update raw records (causes physical writes: very slow)

I don't know the status of this, but using the https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store would help you to find exactly those steps.

Each step in the cleansing process is a separate query and it wil show here: (as you can see I had one lookup which updated all 8million rows)

For performance reasons, I would have hoped they didn't update the basic RAW table but inserted the "new" lookup values in a side table and joined them afterwards. Updating just one column in a large table with many columns isn't the best strategy (since it effectively rewrites the entire table).