While I was investigating the data warehouse grooming internals, I found a neat debugging feature that can be turned on to check the internals of DW stored procedures. In the dbo.StandardDataSet table, for every dataset, there’s a DebugLevel column. By default its value is 0 or no debugging info at all. The higher the value the more is logged accordingly with the stored procedure logic. Every stored procedure defines what needs to be logged and at what debug level. The debugging info are stored in the dbo.DebugMessage table.
Beware of some caveats:
- debugging as always has a cost, it is not designed to be always on. Be careful on production systems.
- the DebugMessage table needs to be manually groomed, once you get the debugging info you need
- there’s a bug in the EventProcessStaging stored procedure, if you turn on debugging for events the event insertion process will be blocked. The EventProcessStaging stored procedure tries to insert NULL messages into dbo.DebugMessage, there’s a constrain that prevents it. So to debug the Event dataset you need to remove the constrain from the DebugMessage table or patch the EventProcessStaging stored procedure. Obviously neither of the two is supported in a production environment. But if you need it badly I’d suggest to remove the constrain.
This posting is provided "AS IS" with no warranties, and confers no rights.