February 16, 2011 at 3:32 pm
Is there a posibility to know if a table stays in memory and when it was loaded?
Are big tables fully loaded into memory?
Thanks for help, Jan
February 17, 2011 at 4:50 am
Whether or not data stays in memory is largely a function of the size of your memory and how volatile your cache is. Tables won't stay in memory unless they're regularly being referenced, and there's enough room to store them there. The main determination for what is being referenced in memory and what's being referenced from the disk are the performance counters that monitor memory and disk I/O.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 17, 2011 at 8:40 am
If you search SQL 2005 dbcc memusage (an old dbcc function) you can find some functions that will give you an idea if your table is in cache. They are not perfect however and your mileage may vary.
February 17, 2011 at 8:42 am
You can find cache size for the objects with this query:
SELECT db_name(database_id) dbname,page_type,
SUM(CASE WHEN ([is_modified] = 1) THEN 1 ELSE 0 END) AS DirtyPageCount,
SUM(CASE WHEN ([is_modified] = 1) THEN 0 ELSE 1 END) AS CleanPageCount ,
count(*)AS cached_pages_count ,count(1) * 8/1024 cache_size_mb
,name ,index_id
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT object_name(object_id) AS name
,index_id ,allocation_unit_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
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.partition_id
AND au.type = 2
) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = db_id()
GROUP BY name, index_id ,db_name(database_id),page_type
ORDER BY cached_pages_count DESC;
February 17, 2011 at 8:48 am
magasvs (2/17/2011)
You can find cache size for the objects with this query:
Nice! Did you write this or did you find it somewhere? I started writing something like this last night after I read this post but didn't get around to finishing it. I'll be checking this out more later. Thanks!
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 17, 2011 at 9:12 am
Nice script. I hadn't thought of that option when I first saw this post. I was venturing down the path of a hexeditor to examine memory and wanted to test it first.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 17, 2011 at 9:25 am
I had only thought of this approach due to some tempdb analysis that I was doing and I found something similar on a blog post from SQL Soldier. I can't find that blog post now but there was a similar script in the comments using sys.dm_os_buffer_descriptors which made me think that we could get a total count of pages in memory for an object.
Regardless, very handy information.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 17, 2011 at 9:28 am
Agreed - handy information indeed. If you google for dbcc memusage, I was able to find a few more scripts similar to what you created.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 18, 2011 at 8:18 am
Interesting query for objects sizes!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 18, 2011 at 11:47 am
David Benoit (2/17/2011)
magasvs (2/17/2011)
You can find cache size for the objects with this query:Nice! Did you write this or did you find it somewhere? I started writing something like this last night after I read this post but didn't get around to finishing it. I'll be checking this out more later. Thanks!
I found it somewhere and kept it in my scripts library, but unfortunately don't remember where I found it. Next time I'll add source to the script's comments.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply