July 16, 2011 at 9:35 am
I was looking into memory using this thread
http://www.sqlskills.com/BLOGS/PAUL/category/Fragmentation.aspx
I ran my statistics and noticed there was lots of old tables in here going back to 2 years ago..one table named xxx_2009 so that helped.
We have shutdown our SQL Server but the tables still remain in the buffer memory. I was under the impression when you shut down it be cleared.
I can used DBCC DROPCLEANBUFFERS to clear it but was wondering if shoutdown SQL Server should have cleared it. Happens on 2005/2008 servers.
Also I was curious why such old tables still in buffer..and wondering if I should just clear these tables in buffer memory as they will never be used and other pages can be stored here.
Many Heaps in here too probrably from select * so thinking to clear these too.
--Testing
So i started with test server and dropped the tables from memory using DBCC DROPCLEANBUFFERS
command..
Ran the script below.. - no tables in here.
SELECT TOP 2000
obj.[name],
i.[name],
i.[type_desc],
count(*)AS Buffered_Page_Count ,
count(*) * 8192 / (1024 * 1024) as Buffer_MB
-- ,obj.name ,obj.index_id, i.[name]
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT object_name(object_id) AS name
,index_id ,allocation_unit_id, object_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT object_name(object_id) AS name
,index_id, allocation_unit_id, object_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND au.type = 2
) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
LEFT JOIN sys.indexes i on i.object_id = obj.object_id AND i.index_id = obj.index_id
WHERE database_id = db_id()
GROUP BY obj.name, obj.index_id , i.[name],i.[type_desc]
ORDER BY Buffered_Page_Count DESC
Great.
So then I in sql studio and just clicked on the databases expand the tables and then run above script and all the tables appear in the buffer.
Hmmm..I thought the tables only appeared in here when you access the data not just by doing expand.
Not all of my tables show up in the list.
Im on 64 bit and SQL 2008 and did notice the lock in pages not set, max memory set to 10 GIG.
Question1:
When you view just a databases expand tables all of this goes in to memory and stays here?
Question2: Do i only see half the tables as the max memory isn't been used?
Anyway what are your thoughts?
July 19, 2011 at 6:03 pm
Hi
Maybe a process is using the table. Generally if you boot the server your tables are not going to pop into buffer unless it's being used.
For instance there might also not be enough space to load all your tables and their pages.
I'm not sure about the effect of the GUI on your tables. Maybe is search for columns & indexes but even that should check against metadata and system tables not your table itself. But If you queries the table to see what inside it it will be in buffer.
You can see what type of page is in bufer? IAM, DATAPAGE etc. That will also help to see what is accesing it and how.
Other tools SQL prompt etc. maybe also have an effect on metadata loaded for tables.
Cheers
Jannie
July 19, 2011 at 7:05 pm
If you clean buffers and just do open database and see the list of tables ...upon doing this they go to buffer memory and stay there that seems strange to me but that is what happens. So i guess don't open up the tables if you don't want to use all your memory.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply