I have a 24core server with 256GB of memory, only running SQL Server 2008R2 Enterprise x64. Connecting locally (e.g. going on through remote desktop and then SSMS):
- You get connection timeouts.
- Activity Monitor runs for a little while, then has timeouts.
- While it is running, almost nothing is going on. Literally 0-4% CPU (in task manager I can see one or two spike every now and again for very short periods of time), 0-1 waiting tasks, database I/O mostly flatlined at 0 and occasionally 30-60MB/s for very short periods of time, 0-18 requests a second, and a fairly small list of open connections.
- The list of long running queries indicates literally a half dozen queries that look fairly simple... with monster times... the rest are very short-running things.
And yet whatever is going on on there is making it utterly dog slow. The memory ring buffers have a lot of type 2 events which indicate the OS is asking for more memory very regularly. The memory limit is set to 245GB so there is some reserved for the OS and it does have about 1GB free in task manager.
I'm kind of at a loss with where to start doing anything to it. Finding the application owner is a mission in itself ;-)
I thought I read an article at one stage where SQL Server could gobble memory that should be reserved for making connections, and there was an option to prevent this. Does that ring any bells?