November 3, 2011 at 9:00 am
Our tempdb is stealing memory on the server and at various times of the day takes up more memory than any other database. I'd like to know what is telling it to consume so much memory. Is there a way to determine it? When I run the following SQL, it is currently taking up 3.2 GB on a 12GB server.
SELECT LEFT(CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE db_name(database_id)
END, 20) AS Database_Name,
count(*)AS Buffered_Page_Count,
count(*) * 8 / (1024) as Buffer_Pool_MB
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY Buffered_Page_Count DESC
I just don't know what the 3.2GB is consumed with.
November 3, 2011 at 9:10 am
I'm not an expert in this (yet), but have you looked at the recent expensive queries?
Thanks,
Jared
Jared
CE - Microsoft
November 3, 2011 at 9:14 am
amrinea (11/3/2011)
Our tempdb is stealing memory on the server and at various times of the day takes up more memory than any other database. I'd like to know what is telling it to consume so much memory. Is there a way to determine it? When I run the following SQL, it is currently taking up 3.2 GB on a 12GB server.SELECT LEFT(CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE db_name(database_id)
END, 20) AS Database_Name,
count(*)AS Buffered_Page_Count,
count(*) * 8 / (1024) as Buffer_Pool_MB
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY Buffered_Page_Count DESC
I just don't know what the 3.2GB is consumed with.
dont confuse memory with space on disk.... have a look here: http://msdn.microsoft.com/en-us/library/ms176029.aspx
November 3, 2011 at 9:57 am
November 3, 2011 at 10:04 am
Correct. I'm referring to Buffer Pool Memory, not physical disk space.
November 3, 2011 at 10:09 am
you can query sys.dm_db_session_space_usage to see which session utilized tempdb heavily. The session(s) using huge IO will be the one using large amount of memory.
also check for open transactions.
November 3, 2011 at 10:27 am
Based on the first link, I'm put together this query:
SELECT R1.session_id,
R1.internal_objects_alloc_page_count
+ R2.task_internal_objects_alloc_page_count AS session_internal_objects_alloc_page_count,
R1.internal_objects_dealloc_page_count
+ R2.task_internal_objects_dealloc_page_count AS session_internal_objects_dealloc_page_count
FROM sys.dm_db_session_space_usage AS R1
INNER JOIN (SELECT session_id,
SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count,
SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count
FROM sys.dm_db_task_space_usage
GROUP BY session_id) AS R2 ON R1.session_id = R2.session_id;
Which when I ran it I saw a couple of offenders, and then they were gone. Does this query properly show me what is consuming buffer pool space? I'm not concerned about the disk size of tempdb, which is currently around 17GB. It is high for what it should normally be in our environment, but I'm especially concerned that tempdb is consuming so much memory and keeping the other databases on the box from utilizing it. This is resulting in poor performance.
November 3, 2011 at 11:50 am
Sounds like you (client apps) might be using a lot of server side or API cursors. They are performance killers... change your code to use client side cursors instead.
The probability of survival is inversely proportional to the angle of arrival.
November 3, 2011 at 12:00 pm
Easier said than done. The majority of everything running on this database server are purchased products, not home grown that we could tweak if necessary.
November 3, 2011 at 12:08 pm
amrinea (11/3/2011)
Easier said than done. The majority of everything running on this database server are purchased products, not home grown that we could tweak if necessary.
Do you mean running "against" the server? If you have applications on there, that is a problem. How much memory do you have on that machine total (maybe you mentioned it in your first post, but I want to make sure). Are you allowing SQL Server to prioritize?
Jared
Jared
CE - Microsoft
November 3, 2011 at 12:13 pm
The server is a database server and only a database server. Nothing else is running on the box. It has a total of 12GB of memory in it.
Like I said before, the issue is that tempdb is hogging much of the memory in the buffer pool, not that the machine itself is hurting for memory. I want to know what is using it so that I can determine which database we need to isolate and move to it's own server so it is not affecting the performance of our other databases.
November 3, 2011 at 12:22 pm
Again, I would look at recent expensive queries to try to track down the culprit. Have you looked at wait stats? Also, (not being an expert here) why are we saying that 3gb out of 12 for tempdb is to much? It really doesn't seem like that much, but I don't know how much data you have or how often it is being accessed.
Jared
Jared
CE - Microsoft
November 3, 2011 at 12:30 pm
I guess the best way to put it is that 3GB doesn't feel right. 25% of a servers memory allocated to tempdb seems out of the ordinary and is definitely not how it should be running. It indicates to me that something is hungry to do some processing and it's starving our primary databases of much needed memory. In fact, it is currently the biggest consumer of memory on the server. I just checked it's currently at 3.8 GB. The next closest one is at 2.3GB of memory. We initially started to dive into this because of random performance issues, and the most unusual thing we could find was tempdb consuming the type of memory that it is.
November 3, 2011 at 12:36 pm
I really don't find it that unusual, depending on the queries of course. tempdb can be accessed by all queries on all databases if needed, so of course it will be utilized more than other dbs if temp tables and such are used often. If you are having performance issues, I suggest to start by looking at waits first and not assuming that this is large memory usage, unless someone tells you otherwise on here. Paul Randall has mentioned that some Ad-hoc queries have been seen using 6-8gb of memory. For 1 query.
Jared
Jared
CE - Microsoft
November 3, 2011 at 12:38 pm
Also, if you had 32gb of memory it would not seem so bad? I'm not sure that % to total is the way to look at this. My assumption is it would use 3gb if you had 8, 16, 32, or 64 gb of ram.
Jared
Jared
CE - Microsoft
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply