Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
denis_the_thief
Aged Yak Warrior
596 Posts |
 Posted - 2013-10-31 : 11:27:46
|
| I was wondered if there is a query that we can see which values are in the cache (i.e. buffer pool) by key value?I know that we can run this: DBCC IND(DatabaseName, TableName, -1) GOThen we can pick a PagePID, and run this: DBCC TRACEON(3604) DBCC PAGE(DatabaseName, 1, PagePID, 3) WITH TABLERESULTS GOBut I was hoping this could be run in one query to list, to list all values of the Primary Key that are in the cache, showing:Table, PageID, PKColumn, ValueIs there a query that can do this? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2013-10-31 : 20:08:10
|
| Interesting problem, but I'm not sure I see what you could do with that information.It will probably be simpler to query sys.dm_os_buffer_descriptors, that will show you which pages are actually cached. You can then join that to sys.allocation_units and then to sys.partitions to link up to an object_id. Linking those to actual keys is another step I don't know how to do, except to use this:http://www.sqlskills.com/blogs/paul/sql-server-2008-new-undocumented-physical-row-locator-function/The main problem is that querying this info will affect the data that's in cache and probably moot whatever you're trying to determine. DBCC PAGE may do the same (according to my tests anyway), as it would buffer its results. |
 |
|
|
|
|
|
|
|
Can we list cached data by key?