Mongodb – Mongo takes too much memory when insert data

The splitVector commands are slow because your system is slow, it's not the cause of the slowness itself - it just happens to be running frequently and hence is showing up a lot in your logs - it is a symptom not a cause (unless you are running many, many mongos processes and hitting an edge case). People often make the same mistake by misdiagnosing slow serverStatus commands (run every 60 seconds by MMS monitoring) as a root cause.

Usually the slowness is because your system is oversubscribed and hitting a bottleneck somewhere, but you have not provided enough information to make a guess as to what your issue is. I would recommend looking for other evidence in the logs as well as the usual suspects (IO, CPU, Memory) to narrow down the actual root cause here.

I don't think DBCC MEMORYSTATUS (or the other queries) are being that revealing at the moment (don't know what the others think); so I am going to ask you to run just one more:

dbcc traceon(3654, -1)
go
waitfor delay '00:05:00'
go
select top 20 memory_object_address, source_file, sum(size_in_bytes) as total_bytes
from sys.dm_os_memory_allocations
group by memory_object_address, source_file
order by total_bytes desc
go
dbcc traceoff(3654, -1)
go

You can see I've built in a delay there to allow it time to collect some information. Depending on what that reveals, I think you are faced with a choice, continue to investigate or speculate:

1. continue to drill further into the memory leak using more advanced debugging techniques, eg using sysinternals tool Process Explorer; find process sqlservr.exe and look for evidence of memory leak, or:
2. use our instincts and what we know about your SQL Server and make and educated guess this is most likely to do with the MySQL linked server. Next time you restart your service, temporarily disable this bit of functionality, eg drop the linked server and/or disable reports which depend upon it. Monitor for your issue. If the issue does not occur, you have a good candidate for your culprit. I think there are newer drivers available (5.3.4?) or you could consider refactoring this part of the application to an SSIS job which creates a local cache once a night for example. If the problem does not occur you at least rule it out.

As this is a live server I think your debugging options are a bit limited so it might be time to try some options, see how the server responds.

Just in reference to using DBCC MEMORYSTATUS, I have had some success with this technique in the past, for example identifying run-away full-text queries or use of OPENXML without the compulsory sp_xml_removedocument.