800+ open connections being caused by SharePoint 2013 crawl?

Question

All are "sleeping". Everything seems to be working fine but need to figure out why there are so many open connections. Any ideas?

CREATE PROCEDURE dbo.proc_MSS_GetNextCrawlBatch @ComponentID int, @CrawlID int, @BatchSize int, @MaxTransactionsPerHost int, @BatchID bigint OUTPUT AS DECLARE @ITEMTYPE_INVALIDLINK tinyint SET @ITEMTYPE_INVALIDLINK = 0 DECLARE @ITEMTYPE_STARTADDRESS tinyint SET @ITEMTYPE_STARTADDRESS = 1 DECLARE @ITEMTYPE_LINK tinyint SET @ITEMTYPE_LINK = 2 DECLARE @ITEMTYPE_LINK_CHANGELOG tinyint SET @ITEMTYPE_LINK_CHANGELOG = 4 DECLARE @ITEMTYPE_LINK_EXCLUDED tinyint SET @ITEMTYPE_LINK_EXCLUDED = 5 DECLARE @ITEMTYPE_LASTLINK tinyint SET @ITEMTYPE_LASTLINK = 7 DECLARE @ITEMTYPE_LINK_DISCOVERED tinyint SET @ITEMTYPE_LINK_DISCOVERED = 8 DECLARE @STATUS_OK int SET @STATUS_OK = 1 DECLARE @STATUS_NOTINITIALIZED int SET @STATUS_NOTINITIALIZED = 0 DECLARE @STATUS_DISABLED int SET @STATUS_DISABLED = 3 DECLARE @STATUS_REMOUNT int SET @STATUS_REMOUNT = 4 DECLARE @STATUS_INACTIVE int SET @STATUS_INACTIVE= 5 DECLARE @STATUS_DISABLED_FOR_REMOVE int SET @STATUS_DISABLED_FOR_REMOVE = 6 DECLARE @MINI_HIGH_PRIORITY_CRAWL_ID int SET @MINI_HIGH_PRIORITY_CRAWL_ID = 1 DECLARE @MINI_LOW_PRIORITY_CRAWL_ID int SET @MINI_LOW_PRIORITY_CRAWL_ID = 2 SET NOCOUNT ON --SET XACT_ABORT ON - no need to set XACT_ABORT ON because the transaction is now driven by C++ code DECLARE @Status int SELECT @Status = Status FROM MSSCrawlComponentsLocal WITH(HOLDLOCK) WHERE ComponentID = @ComponentID IF @@ROWCOUNT = 0 OR @Status = @STATUS_DISABLED BEGIN
RAISERROR (N'The component ID %d is disabled', 16, -- Severity. 1, -- State. @ComponentID) RETURN 0 END --this exclusive lock prevents 2 crawl components to load transactions from the queue concurently INSERT INTO MSSBatchHistory WITH(TABLOCKX)(CrawlID) VALUES (@CrawlID) SELECT @BatchID = @@IDENTITY DECLARE @RowsSelected int SET @RowsSelected = 0 DECLARE @HostsInCrawl int SELECT @HostsInCrawl = COUNT() FROM MSSCrawlHostsLog WITH(NOLOCK) WHERE CrawlID = @CrawlID IF @HostsInCrawl > 1 BEGIN DECLARE @DocsInQueue int = 0 SELECT @DocsInQueue = COUNT() FROM MSSCrawlQueue WITH(NOLOCK) WHERE CrawlID = @CrawlID IF @DocsInQueue < 1000000 OR @CrawlID IN(@MINI_HIGH_PRIORITY_CRAWL_ID, @MINI_LOW_PRIORITY_CRAWL_ID)
BEGIN CREATE TABLE #HostsInQueue (HostID int, DocCount int)
INSERT INTO #HostsInQueue (HostID, DocCount) SELECT TOP 10 HostID, COUNT(*) as C FROM TVF_MSSCrawlQueue_CrawlID_Nolock (@CrawlID) WHERE BatchID != 0 AND ComponentID = @ComponentID GROUP BY HostID ORDER BY C DESC
DELETE #HostsInQueue WHERE DocCount < @MaxTransactionsPerHost
UPDATE _CrawlQueue SET BatchID = @BatchID, ComponentID = @ComponentID FROM ( SELECT TOP (@BatchSize) SeqID FROM TVF_MSSCrawlQueue_CrawlID_BatchID(@CrawlID, 0) _CrawlQueue_Tmp
OUTER APPLY TVF_MSSCrawlQueue_DocID_ComponentID(_CrawlQueue_Tmp.DocID, @ComponentID) AS _CrawlQueue_DocID --CROSS APPLY TVF_MSSCrawlURL_DocID (_CrawlQueue_Tmp.DocID) _CrawlURL
WHERE _CrawlQueue_Tmp.HostID NOT IN (SELECT HostID FROM #HostsInQueue) AND _CrawlQueue_DocID.DocID IS NULL --AND _CrawlURL.DocID = xxxx ORDER BY SeqID ) AS _CrawlQueue_Top CROSS APPLY TVF_MSSCrawlQueue_SeqID(_CrawlQueue_Top.SeqID) AS _CrawlQueue
OPTION(FORCE ORDER, MAXDOP 1) SELECT @RowsSelected = @@ROWCOUNT END END IF @RowsSelected < @BatchSize BEGIN
UPDATE _CrawlQueue SET BatchID = @BatchID, ComponentID = @ComponentID FROM ( SELECT TOP (@BatchSize - @RowsSelected) SeqID FROM TVF_MSSCrawlQueue_CrawlID_BatchID(@CrawlID, 0) _CrawlQueue_Tmp
OUTER APPLY TVF_MSSCrawlQueue_DocID_ComponentID(_CrawlQueue_Tmp.DocID, @ComponentID) AS _CrawlQueue_DocID --CROSS APPLY TVF_MSSCrawlURL_DocID (_CrawlQueue_Tmp.DocID) _CrawlURL WHERE _CrawlQueue_DocID.DocID IS NULL --AND _CrawlURL.DocID = xxxx ORDER BY SeqID ) AS _CrawlQueue_Top CROSS APPLY TVF_MSSCrawlQueue_SeqID(_CrawlQueue_Top.SeqID) AS _CrawlQueue OPTION(FORCE ORDER, MAXDOP 1) END SELECT _CrawlQueue.SeqID, _CrawlQueue.SourceDocID, _CrawlQueue.DocID, _CrawlQueue.StartAddressID, _CrawlQueue.HostDepth, _CrawlQueue.EnumerationDepth, _CrawlQueue.TransactionFlags | _CrawlURL.TransactionFlags, _CrawlQueue.TransactionType, _CrawlQueue.Scope, _CrawlQueue.CachedBlob, _CrawlURL.AccessURL, _CrawlURL.CompactURL, _CrawlURL.EndPathFlag, _CrawlURL.MD5, _CrawlURL.LastModifiedTime, _CrawlURL.FolderDelCount, _CrawlURL.ChangeLogCookie, _CrawlURL.ChangeLogCookieType, _CrawlURL.RetryCount, _CrawlURL.HostID, _CrawlURL.ParentHostID, _CrawlURL.SecurityID, _CrawlURL.PHFlags, _CrawlURL.DelayRetryCount, _CrawlURL.ChangeLogCookieEnd, _CrawlURL.LogLevel, _CrawlURL.IndexType, _CrawlURL.SDHash, _CrawlURL.PartitionID, _CrawlQueue.MiniCrawlID, DATEDIFF(millisecond, _CrawlQueue.TimeInsertedInLinksTable, _CrawlQueue.TimeInsertedInQueue), DATEDIFF(millisecond, _CrawlQueue.TimeInsertedInQueue, GetUTCDate() ), _CrawlURL.ContentSourceID, _CrawlURL.RefreshBucket, _CrawlURL.RefreshPromoteCount, _CrawlURL.SPOIndexTypeName FROM TVF_MSSCrawlQueue_CrawlID_BatchID (@CrawlID, @BatchID) _CrawlQueue CROSS APPLY TVF_MSSCrawlURL_DocID(_CrawlQueue.DocID) _CrawlURL ORDER BY SeqID OPTION(FORCE ORDER) RETURN 1

Answer 1

• Have you try to reduce the value @DocsInQueue < 1000000 ?
• Check the status of the mssdmn.exe process and see if it reach some kind of boundaries, cpu, memory, threads.
• Check the tables [MSSCrawlURLLog] and [MSSCrawlURL] and count the items every single url is a potential connection.
• Use wireshark Network tool and check if the connections are closed at network level use the filter tcp.flags.fin eq 1 or tcp.flags.reset eq 1, if the connection is not close then a timeout should pop out in the uls log.
• Recycling bits: This blog will explain in deep how the crawling occurs in the starting

These points will help you to pin down why are so many connections. In some cases the crawler gets stuck in a document and rest of connections don't close properly until Search Service is stopped and restarted.

• Check that you can open the documents url via IE using crawler account and none firewall , Load balanced, nor proxy server stops the communication.