September 23, 2015 at 8:05 am
Hi All,
Can any one help me to get the script to find current queries /process that are running which causing the SQL server to use high memory.?on SQL server 2008/2008 r2.
Thanks in Advance
September 23, 2015 at 8:19 am
SQL Server will always use all the memory you make accessible to it.
Unless you specifically enforce releasing previously cached pages.
Normally SQL Server will keep cached execution plans, data sets in memory until they become irrelevant (say, changed stats change plans) or server needs to free some memory for new queries.
_____________
Code for TallyGenerator
November 9, 2015 at 8:06 am
Look for Adam Machanic's SP_WhoIsActive.
This gives you the queries running, as well as resources being used, and whether the query is blocked.
He has a specific one for Azure.
http://sqlblog.com/blogs/adam_machanic/archive/2015/04/10/sp-whoisactive-and-azure-sql-database.aspx
November 9, 2015 at 11:19 am
You can look at currently running queries as much as you like, but queries are not what use memory, and so you'll be wasting time looking at that.
SQL's memory usage is mainly the data cache and the plan cache, and both exist as optimisations.
Chapter 4 - https://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply