Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Question
Friday, September 22, 2017 9:19 AM
Hello Forum
Is there a way to grab the following information from Task Manager on a Server, for all SQL Server Processes: -
Name
Status
CPU (%)
Memory (%)
Note the "Name" needs to include the SQL Server Instance name.
I would like to grab these values into Variables so that they can be logged into a SQL Server Database.
Many Thanks
Please click "Mark As Answer" if my post helped. Tony C.
All replies (16)
Friday, September 22, 2017 9:38 AM
help get-process -full
Learn PowerShell: https://mva.microsoft.com/en-us/training-courses/getting-started-with-microsoft-powershell-8276<o:p></o:p>
\(ツ)_/
Monday, October 9, 2017 2:56 PM
Hello
Using Get-Process and Get-Process SQL* does return some useful information, however it does not return the Full Process Name (e.g. SQL Server (DATABASE01), SQL Server Agent (DATABASE01)) It returns multiple lines of sqlservr, SQLAGENT etc instead.
Is there a way to get the Full Process Name as well?
Many Thanks
Please click "Mark As Answer" if my post helped. Tony C.
Monday, October 9, 2017 8:48 PM
Get-Process | select name, ProcessName
\(ツ)_/
Tuesday, October 10, 2017 8:10 AM
Get-Process is NOT supplying what I'm looking for, it supplies the Process Name but not the INSTANCE name. Get-Service does supply the full name but not the Process ID, is there a way of retrieving what Process ID a Service is running under?
Please click "Mark As Answer" if my post helped. Tony C.
Tuesday, October 10, 2017 8:13 AM
What is an "instance" name? Processes do not have that property. An "Instance" is specific to SQLServer. Use SQL SMO to find instances.
\(ツ)_/
Tuesday, October 10, 2017 8:15 AM
You can also imply the instance form the service name:
get-service mssql*
\(ツ)_/
Tuesday, October 10, 2017 8:47 AM
I think Get-WmiObject -Class Win32_Service may be of use; if I manage to get process id, name and process stats I'll post the resolution here.
Please click "Mark As Answer" if my post helped. Tony C.
Tuesday, October 10, 2017 8:54 AM
You asked for the "instance" name. That will not get you anything more than Get-Service.
We don't need to know how to use Win32_Service. This is basic to PowerShell/WMI. Perhaps you should take some time to learn PS.
Learn PowerShell: https://mva.microsoft.com/en-us/training-courses/getting-started-with-microsoft-powershell-8276
Guessing is not a very professional approach to any techn ology.
\(ツ)_/
Tuesday, October 10, 2017 1:21 PM
Get-WmiObject -Class Win32_Service will return the name, state and process Id (if a Service is running). Get-Process returns the Process ID and the various stats. All I need to do is to collate both sets of information together.
Please click "Mark As Answer" if my post helped. Tony C.
Monday, October 16, 2017 9:57 AM
Hi,
Based on my research, you could have a try with the following scripts. Hope it is helpful to you:
$services = Get-WmiObject -Class Win32_Service -Filter {State = 'Running'}
foreach ($service in $services)
{
Get-Process -Id $service.ProcessId | Select-Object @{n='Name';e={$service.Name}},@{n='State';e={$service.State}},@{n='ProcessId';e={$service.ProcessId}},VM
}
If you need further help, please feel free to let us know.
Best Regards,
Albert Ling
Please remember to mark the replies as an answers if they help and unmark them if they provide no help.
If you have feedback for TechNet Subscriber Support, contact [email protected].
Tuesday, October 17, 2017 8:32 AM
Here's my solution:
$ComputerName = 'srv-sql-001'
$services = Get-WmiObject -Class Win32_Service -ComputerName $ComputerName -Filter {Name LIKE 'MSSQL$%'}
$TotalRAM = (Get-WmiObject -Class Win32_ComputerSystem -ComputerName $ComputerName).TotalPhysicalMemory
foreach ($service in $services)
{
$Process = Get-Process -Id $service.ProcessId -ComputerName $ComputerName
$ProcessData = Get-WmiObject Win32_PerfFormattedData_PerfProc_Process -Filter "IDProcess = $($service.ProcessId)" -ComputerName $ComputerName
[pscustomobject]@{
Name = $Service.Caption
State = $Service.State
'CPU(%)' = "{0:D2}" -f [int]$ProcessData.PercentProcessorTime
'Memory(%)' = "{0:D2}" -f [int]($ProcessData.WorkingSetPrivate / $TotalRAM * 100)
}
}
If you only run it locally on the SQL server, just remove the ComputerName parameters on the WMI queries.
MicaH
Tuesday, October 17, 2017 8:47 AM
Except in a virtual memory OS you memory calculation is meaningless. A processes working set can exceed total physical memory very easily. For SQLServer this is almost guaranteed for many processes. remember the SQS is not just one process. It is one service but many other processes.
SQLServer also is set to allocate all of available memory to itself and then gives back to the OS as needed unless you option it otherwise.
\(ツ)_/
Thursday, October 19, 2017 9:07 AM
Hi,
Just checking in to see if the information provided was helpful.
Please let us know if you would like further assistance.
Best Regards,
Albert Ling
Please remember to mark the replies as an answers if they help and unmark them if they provide no help.
If you have feedback for TechNet Subscriber Support, contact [email protected].
Tuesday, October 24, 2017 2:09 AM
Hi,
I am checking how the issue is going, if you still have any questions, please feel free to contact us.
If you resolved it using our solution, please "mark it as answer" to help other community members find the helpful reply quickly.
If you resolve it using your own solution, please share your experience and solution here. It will be very beneficial for other community members who have similar questions.
If no, please reply and tell us the current situation in order to provide further help.
Appreciate for your feedback.
Best Regards,
Albert Ling
Please remember to mark the replies as an answers if they help and unmark them if they provide no help.
If you have feedback for TechNet Subscriber Support, contact [email protected].
Wednesday, October 25, 2017 11:47 AM
Hello Everyone
I'm getting information fed into my database now, but there are some interesting notes on here; I'll review the notes with the process I've devised and provide some feedback.
In the meantime thank you all for your responses.
Please click "Mark As Answer" if my post helped. Tony C.
Thursday, October 26, 2017 8:25 AM
Hi,
Awaiting your feedback.
Please let us know if you would like further assistance.
Best Regards,
Albert Ling
Please remember to mark the replies as an answers if they help and unmark them if they provide no help.
If you have feedback for TechNet Subscriber Support, contact [email protected].