Quick Handy SQL Script to get the instance count

select sc.nvcName "Service Name", [Status]=CASE
WHEN i.nState IN(1) THEN 'Ready To Run'
WHEN i.nState IN(2) THEN 'Active'
WHEN i.nState IN(4) THEN 'Suspended (Resumable)'
WHEN i.nState IN(8) THEN 'Dehydrated'
WHEN i.nState IN(16) THEN 'Completed With Discarded Messages'
WHEN i.nState IN(32) THEN 'Suspended (Not Resumable)'
WHEN i.nState IN(64) THEN 'In Breakpoint'
END, count(i.uidInstanceID) "MsgCount" from [Instances] AS i with(nolock)
JOIN [ServiceClasses] AS sc on sc.uidServiceClassID = i.uidClassIDwhere i.uidServiceID in
(select distinct uidServiceID from [Services] with(nolock))
OR i.uidServiceID not in
(select distinct uidServiceID from [Services] with(nolock))
group by sc.uidServiceClassID, sc.nvcName, i.nState
This might not be optimized AT ALL !!