Sql-server – How to know which application is consuming the ram

If you don't have IIS 7 and the provider, you can use WMI. The attached script works for most of your requirements, except CPU usage. Save the below script as get-webserverapppoolstats.ps1 (or whatever you want).

You can run the script then with:

./Get-WebServerAppPoolStats.ps1 'Server1', 'Server2', 'Server3' -IntegratedAuthentication OR Get-Content servers.txt | ./Get-WebServerAppPoolStats.ps1 -IntegratedAuthentication

param (
    $webserver = $null,
    $username,
    $password,
    [switch]$IntegratedAuthentication)

BEGIN
{
    $path = $MyInvocation.MyCommand.Path

    if ($webserver -ne $null)
    {
        if ($IntegratedAuthentication)
        {
            $webserver | &$path -IntegratedAuthentication
        }
        else
        {
            $webserver | &$path -username $username -password $password
        }
    }
    $OFS = ', '
    $Fields = 'CommandLine', 'Name', 'CreationDate', 'ProcessID', 'WorkingSetSize', 'ThreadCount', 'PageFileUsage', 'PageFaults' 

    $query = @"
    Select $fields
    From Win32_Process
    Where name = 'w3wp.exe'
"@

    $AppPool =  @{Name='Application Pool';Expression={($_.commandline).split(' ')[-1]}}
    $Process = @{Name='Process';Expression={$_.name}}
    $RunningSince = @{Name='Running since';Expression={[System.Management.ManagementDateTimeconverter]::ToDateTime($_.creationdate)}}
    $Memory = @{Name='Memory Used';Expression={'{0:#,#}' -f $_.WorkingSetSize}}
    $Threads = @{Name='Thread Count';Expression={$_.threadcount}}
    $PageFile = @{Name='Page File Size';Expression={'{0:#,#}' -f $_.pagefileusage}}
    $PageFaults = @{Name='Page Faults';Expression={'{0:#,#}' -f $_.pagefaults}} 
}

PROCESS
{
    $server = $_ 

    if ($server -ne $null)
    {
        if ($IntegratedAuthentication)
        {   
            $result = Get-WmiObject -Query $query -ComputerName $server
        }
        else
        {
            $securepassword = ConvertTo-SecureString $password -AsPlainText -Force
            $cred = New-Object System.Management.Automation.PSCredential -ArgumentList $username, $securepassword

            $result = Get-WmiObject -Query $query -ComputerName $server -Credential $cred 

        }
        $Server = @{Name='Server';Expression={$server}}
        $result | Select-Object $Server, $AppPool, $Process, $RunningSince, $Memory, $Threads, $PageFile, $pageFaults
    }
}

JP, I believe your comment that this is an automated build / unit test server that is running less than stable code says it all. My guess is that this server needs to be rebooted frequently because of the unstable code running on it probably has memory leaks that are giving you the symptoms you are seeing. Memory leaks are not going to get listed on any of the process trees of running processes as they are memory that is grabbed by processes that no longer exist, and are still allocated, even though the process associated with them are long gone.

Get a frequent maintenance window to allow you to automagically reboot this machine, perhaps nightly, a couple times a week, or weekly depending upon the speed at which this behavior starts to exhibit performance issues.

We have Oracle Application Servers servers on Windoze a while back that needed to be rebooted twice daily. We kicked out the users at lunch time, and in the middle of the night every day. That was a few years back, with supposedly stable productions code from Oracle.