Here’s a rather complicated SQL query to list the top 10 largest mailbox indices on your NearPoint server:

SELECT TOP 10 MB.MailboxID, SG.StorageGroupName, SG.MailboxStoreName, MB.MailboxName, LV.SharePath+IL.IndexRelativePath as IndexLocation, IndexStatistics
FROM storageGroup SG (NOLOCK), Mailbox MB (NOLOCK), IndexLocation IL (NOLOCK), NPStorageLogicalVolume LV (NOLOCK)
WHERE MB.StorageGroupID = SG.StorageGroupID AND MB.MailboxID = IL.MailboxID
AND LV.LogicalVolumeID=IL.LogicalVolumeID
ORDER BY IndexStatistics.value('(/Root/CurrentChunkSize)[1]', 'int') DESC

To find the top 10 largest mailbox indices on a particular index volume, include the WHERE condition AND LV.SharePath LIKE '%INDEX1%' where INDEX1 is the share name of one of your index volumes:

SELECT TOP 10 MB.MailboxID, SG.StorageGroupName, SG.MailboxStoreName, MB.MailboxName, LV.SharePath+IL.IndexRelativePath as IndexLocation, IndexStatistics
FROM storageGroup SG (NOLOCK), Mailbox MB (NOLOCK), IndexLocation IL (NOLOCK), NPStorageLogicalVolume LV (NOLOCK)
WHERE MB.StorageGroupID = SG.StorageGroupID AND MB.MailboxID = IL.MailboxID
AND LV.LogicalVolumeID=IL.LogicalVolumeID
AND LV.SharePath LIKE '%INDEX1%'
ORDER BY IndexStatistics.value('(/Root/CurrentChunkSize)[1]', 'int') DESC

Note that the mailbox index chunk size in contained the XML data stored in the IndexStastics column in the MimosaContext..IndexLocation table, e.g.,

<Root>
  <IndexState>0</IndexState>
  <CurrentChunkSize>786</CurrentChunkSize>
  <Asset AssetId=&quot;1001&quot;>
    <IndexStateUpdateTS>2011-12-16 18:19:50</IndexStateUpdateTS>
    <StartMessageItemId>1002</StartMessageItemId>
    <EndMessageItemId>34725997</EndMessageItemId>
    <StartRecordTS>2011-12-16 18:20:02</StartRecordTS>
    <EndRecordTS>2012-05-17 08:04:21</EndRecordTS>
    <StartReceivedDate>2011-03-08 16:51:39</StartReceivedDate>
    <EndReceivedDate>2012-05-17 07:28:59</EndReceivedDate>
  </Asset>
</Root>

To sort by the current chunk size, you have to use the XQUERY notation highlighted below:

SELECT TOP 10 MB.MailboxID, SG.StorageGroupName, SG.MailboxStoreName, MB.MailboxName, LV.SharePath+IL.IndexRelativePath as IndexLocation, IndexStatistics
FROM storageGroup SG (NOLOCK), Mailbox MB (NOLOCK), IndexLocation IL (NOLOCK), NPStorageLogicalVolume LV (NOLOCK)
WHERE MB.StorageGroupID = SG.StorageGroupID AND MB.MailboxID = IL.MailboxID
AND LV.LogicalVolumeID=IL.LogicalVolumeID
AND LV.SharePath LIKE '%INDEX1%'
ORDER BY IndexStatistics.value('(/Root/CurrentChunkSize)[1]', 'int') DESC

For more information about working with XML in SQL, check out the following article on SQL Server Performance:

Practical Introduction to XML Manipulation Using XQuery in SQL Server