How can I run a SQL Query to find out top 10 SPIDs

Question

I would like to run a SQL Query finding the top 10 SPIDs using memory.

I know that SQLSERVER.EXE is actually using high Memory. I tried going into SQLSERVER.EXE properties and found that these TIDs are using high CPU

But, not sure how to run a query on them to find it. I tried using this, but I think I need to know the SPID and SYSPROCESSES

This is giving an error

Msg 208, Level 16, State 1, Line 2
Invalid object name 'sysprocesses'.

Answer 1

It's not a meaningful question. Most of the memory is common and not used by a specific process. One spid may run

SELECT * FROM BigTable

and drag a 20 GB table into the buffer cache. But then the data will be in the cache for everyone to use, as long as there is no memory pressure. That is, something else is needed in the cache.

To confuse matters a little bit, a process might be running a query with a a plan with a high memory grant. And that memory is certainly private to the process. It is released when the query completes, but if there are many queries that requests high memory grants, they may be queued up.

But it is far from clear that this is your actual problem. All you are saying is that SQL Server is using high memory. Which is pretty normal. SQL Server is designed to grab as much memory it can and as much as the OS permits. That buffer cache helps things go faster.

sysprocesses is an old view, and these days we prefer to work with sys.dm_exec_sessions and sys.dm_exec_requests.

Answer 2

Hi @JKFrancis ,

Do you want to Find High CPU Query? If so, please see this link.
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/33c7521f-7ed4-4574-82dd-aeb2f32111c5/find-high-cpu-query?forum=sqldatabaseengine
Let me summarize, the two methods mentioned in the link are the same, one is through the UI interface of ssms, and the other is through the execution of sql scripts. Which method you use depends on you.
Here is a MS document about Troubleshoot high-CPU-usage issues in SQL Server : https://learn.microsoft.com/en-us/troubleshoot/sql/performance/troubleshoot-high-cpu-usage-issues
If I misunderstood what you mean, please let me know.

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

It is perfectly normal and expected behavior for SQL Server to use all RAM and CPU provided to it. This is not in any way an indication of a problem.

If your server is page swapping, this is likely due to not setting the max server memory correctly. Please see:
https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/server-memory-server-configuration-options?view=sql-server-ver15