Troubleshooting Data warehouse arithmetic overflows


In the last weeks, in the wage of R2 RC release, I decided it was time to review all the MPs we developed to prepare an action plan for improvement. I have great expectations for R2.

So I started to revamp our development Lab adding missing MPs and updating obsolete ones. Soon I started to get error 31553 and my state reports started to mess up.

Error 31553 is pretty generic, my own was referring to an arithmetic overflow:

Event Type:            Error
Event Source:          Health Service Modules
Event Category:        Data Warehouse 
Event ID:              31553
Date:                  3/6/2009
Time:                  6:51:41 PM
User:                  N/A
Computer:              SCOM-MS1
Description:
 
Data was written to the Data Warehouse staging area but processing failed on one of the subsequent operations.
Exception 'SqlException': Sql execution failed. Error 8115, Level 16, State 2, Procedure ManagedEntityChange, Line 157, Message: Arithmetic overflow error converting expression to data type int. 
One or more workflows were affected by this.
Workflow name: Microsoft.SystemCenter.DataWarehouse.Synchronization.ManagedEntity

I knew the DW has a staging process so that new data is staged in tiny and fast tables and then inserted in the proper ones, this way the insertion completes quickly and the a background process takes care of the proper consolidation. In this case the table is dbo.ManagedEntityStage. Opening the table with SQL Server Studio returned too much rows for a transit table in a very limited lab environment, but the data seemed ok. The data is queuing up that’s for sure, so something must have gone wrong in the process. I drilled down further in the staging process, the main stored procedure here is dbo.StateProcessStaging so I started to step through the SQL code to check where an arithmetic could occur, while I was in this process I typed this simple query: select * from dbo.ManagedEntityStage. Tada a certain number of rows were returned but the select stopped with an arithmetic overflows (I forgot one of my golden rules, don’t trust GUIs).

So the issue is with one or more rows in ManagedEntityStage. Time to review the table schema:

image

As it turned out the table has a computed column (InsertReadyInd), the formula is as follows

image

So here they’re multiplying the ManagedEntityTypeRowID (i.e. the unique identifier inside the DW for object classes) with the host id of the managed entity. These two are integers. SQL as an implicit type conversion for computed columns, this type conversion takes the least restrictive type of the terms involved in the formula as the field type (http://msdn.microsoft.com/en-us/library/ms163363.aspx). In our case InsertReadyInd is an integer for SQL sake. But, int * int casted to an int can lead to an arithmetic overflow, and this was exactly my case. You must consider that in our dev Lab we constantly add and remove MPs, every time an MP is added the ManagedEntityTypeRowId increments, even if that MP has been added in the past (this won’t be the case if we just updated the MPs). So probably this is not a common scenario, btw the same issue is true for other staging tables. Nevertheless it cannot be excluded that this same issue could hit a huge production management group.

Obviously, since this is a lab environment, I didn’t open a PSS incident, but rather simply I fixed the computed field. ** do not do this in production if not directed by PSS **

So I changed  the formula for InsertReadyInd to

(abs(isnull([ManagedEntityTypeRowId],(0))*CAST(isnull([TopLevelHostManagedEntityRowId],(0)) AS FLOAT)))

This did the magic and now my stale state data is flowing into the DW.

- Daniele

This posting is provided "AS IS" with no warranties, and confers no rights.

Technorati Tags: ,,
About these ads
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 348 other followers

%d bloggers like this: