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="1001"> <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