Sql-server – Low Procedure cache

I'm troubleshooting an issue with one of our SharePoint databases, essentially huge drops in PLE which I think are caused by increased I/O activity (I see increased reads, writes and lazy writes at the same time)

I can see from the question that you have SQL Server 2012 SP1. There was a Bug in SQL Server 2012 which forced PLE to plummet but that was fixed in SQL Server 2012 SP1 CU4. Now since ** SQL Server 2012 SP3** has been released I suggest you apply SP3 and see if the issue subsides.

I wanted the check the size of the buffer cache at the exact time of PLE drop,

You can't use perfmon counter Buffer Manager: Database Pages to check buffer size. As per BOL what it signifies is:

Indicates the number of pages in the buffer pool with database content.

So you can see it does not gives information about complete Buffer Pool. When you define SQL Server max server memory the value set in max server memory becomes the buffer pool size. As such from SQL Server 2012 onward the buffer pool has less significance. Previously it was both consumer and the provider of memory. From SQL Server 2012 onward its just the consumer. To see the contents of the buffer pool(to some extent) you have DMV sys.dm_os_buffer_descriptors.

Below query returns cached page count of each database.

SELECT COUNT(*)AS cached_pages_count
    ,CASE database_id 
        WHEN 32767 THEN 'ResourceDb' 
        ELSE db_name(database_id) 
        END AS database_name
FROM sys.dm_os_buffer_descriptors
GROUP BY DB_NAME(database_id) ,database_id
ORDER BY cached_pages_count DESC;  

If you want to get pages cached for each objects in particular database

SELECT COUNT(*)AS cached_pages_count 
    ,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 
ORDER BY cached_pages_count DESC;

It was tempdb contention. Adding more tempdb data files and enabling trace flag 1118 solved the issue.