question

narendba avatar image
narendba asked

high memory memory utilization of sqlserver 2008r2

Hi All, In our production environment we faced high memory utilization and we were not able to connect to sqlserver using ssms and with DAC also. We came to know query timedout errors in event viewer. Let us know in this type situation how to get which queries are problematic. Note: If we are not able to connect Sqlserver using ssms, then how to get the list connections and queries and how we can troubleshoot the issue. Regards, Narendra.
sql-server-2008-r2memorymemory-utilization
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Can I get a perfmon counters to get the details of memory utilization and need an explanation what information they provided to us? Is there any code to get exact details of how much RAM is used by SQLSERVER like "total RAM", "used by SQLSERVER", "free pages", "used data cache" and "used planned cache" etc...
0 Likes 0 ·

1 Answer

· Write an Answer
Grant Fritchey avatar image
Grant Fritchey answered
In order to do anything with the server, you'll need to make a connection. There's no other way to do anything within it. Once you have a connection, I'd suggest using the dynamic management objects (DMO) to identify which queries are actively using the most resources. You can look at sys.dm_exec_requests to get the queries currently running. You'll be able to see how many of what kind of resources they're using. You'll also be able to see if you have blocking occurring and which processes are causing the blocks. From there, you can drill down to understand what's happening within the queries. But, you have to get the connection first. There's no way to do anything within SQL Server without a connection to the server. I'd suggest having users log off or even shutting down an application/web server to reduce the load on the system while you get the server under control. For more details on other information you can gather from DMO, please see this book by [Tim Ford and Louis Davidson][1]. It's free to download the e-book. [1]: https://www.simple-talk.com/books/sql-books/performance-tuning-with-sql-server-dynamic-management-views/
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Yes, we have done the same. But we got the connection to sqlserver after 2hrs , so bussiness (prod server) down till 2hrs and it's got escalated. So is there any possibilities to get the details of quries or anything else to conclude the problem.
0 Likes 0 ·
Without a connection? No. You have to be able to get into the server in order to identify what's happening. there's just no other way to get it done.
0 Likes 0 ·
Can I get a perfmon counters to get the details of memory utilization and need an explanation what information they provided to us? Is there any code to get exact details of how much RAM is used by SQLSERVER like "total RAM", "used by SQLSERVER", "free pages", "used data cache" and "used planned cache" etc...
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.