I was using the following code to retrieve suspended (not resumable) messages in WMI using the System.Management namespace:
string classNamespace = "root\\MicrosoftBizTalkServer";
string query = "SELECT * FROM MSBTS_MessageInstance WHERE ServiceInstanceStatus = 32";
EnumerationOptions enumOptions = new EnumerationOptions();
enumOptions.ReturnImmediately = false;
enumOptions.EnumerateDeep = false;
ManagementObjectSearcher searchObject = new ManagementObjectSearcher(classNamespace, query ,enumOptions);
ManagementObjectCollection col = searchObject.Get();
(I was actually using asynchronous calls, but this synchronous example is simpler to describe)
At the time of calling this code, there were roughly 600 messages in the suspend queue (about 10 non resumable). This WMI call produced requests to the database similar to that below:
exec MBOM_LookupMessageReferences @nvcHost = NULL, @nServiceClass = 127, @uidServiceType = NULL, @uidInstanceId = NULL, @uidMessageId = NULL, @snStatus = 63, @nReferenceType = 15, @dtFrom = 'Sep 6 3004 4:53:28:890PM', @dtUntil = 'Sep 6 1804 4:53:28:890PM', @nMaxMatches = 200
exec MBOM_LookupMessageReferences @nvcHost = NULL, @nServiceClass = 127, @uidServiceType = NULL, @uidInstanceId = NULL, @uidMessageId = NULL, @snStatus = 63, @nReferenceType = 15, @dtFrom = 'Sep 6 2004 3:58:08:733PM', @dtUntil = 'Sep 6 2004 3:58:08:733PM', @nMaxMatches = 0
exec MBOM_LookupMessageReferences @nvcHost = NULL, @nServiceClass = 127, @uidServiceType = NULL, @uidInstanceId = NULL, @uidMessageId = NULL, @snStatus = 63, @nReferenceType = 15, @dtFrom = 'Sep 6 3004 4:53:28:890PM', @dtUntil = 'Sep 6 2004 3:58:08:737PM', @nMaxMatches = 200
exec MBOM_LookupMessageReferences @nvcHost = NULL, @nServiceClass = 127, @uidServiceType = NULL, @uidInstanceId = NULL, @uidMessageId = NULL, @snStatus = 63, @nReferenceType = 15, @dtFrom = 'Sep 6 2004 3:58:15:107PM', @dtUntil = 'Sep 6 2004 3:58:15:107PM', @nMaxMatches = 0
exec MBOM_LookupMessageReferences @nvcHost = NULL, @nServiceClass = 127, @uidServiceType = NULL, @uidInstanceId = NULL, @uidMessageId = NULL, @snStatus = 63, @nReferenceType = 15, @dtFrom = 'Sep 6 3004 4:53:28:890PM', @dtUntil = 'Sep 6 2004 3:58:15:110PM', @nMaxMatches = 200
The WMI call causes the stored procedure above to be executed due to the built in batch size of 200, denoted as nMaxMatches. I have discovered that this batch size cannot be altered.
Therefore, if you are ever attempting to do something similar to the above your performance will suffer if you have a considerable amount of messages to deal with, as would be the case in my scenario. For example, if you had 2000 messages and one was non resumable the stored procedure would be executed 100 times. It would also get called the same amount of times even if you had 2000 non resumable messages.
Not particularly useful I am sure you would agree, especially as these executions can take a noticeable time to complete. The only other solution would be to implement my own calls directly to the database rather than through WMI, which I am obviously not overly keen on doing.