CODEWITHSUNDEEP
sql-serverperformance
Younes
Younes

Reputation: 395

SQL Server Buffer cache content

I'm having an issue on my instance page life expectancy would drop to a few seconds.
I know this is due bad code, and I'm trying to find out which object pages are in the buffer cache. That would lead me to what causes the problem. Kind of v$bh equivalent in Oracle.

Edit

I came across this great article which talk about the issue I got with pagelife expectancy. And how to find the buffer content.

Thanks,

Upvotes: 2

Views: 3273

Answers (2)

Remus Rusanu
Remus Rusanu

Reputation: 294467

sys.dm_os_buffer_descriptors has the data about buffer pool content. You will also be interested in sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats. Between these you will be able to identify hot objects. sys.dm_exec_query_stats will show you queries that are touching a lot of pages (high logical_reads).

Upvotes: 0

brian
brian

Reputation: 3695

This link should provide you with the queries to use for this: http://www.mssqltips.com/sqlservertip/2393/determine-sql-server-memory-use-by-database-and-object/

Note: You didnt' say which version of SQL Server you are using, but this will only work on 2005 and up boxes

Upvotes: 3

Related Questions