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.
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]. It's free to download the e-book. :