I did dbcc pintable for a particular table. Is there are a way to find out whether it is really on cache as the manual says for dbcc pintable.
Something like IsTableCache(table_name) would be helpful. Or is there any system tables that needs to read to get this info.
I turned on "set statistics io on" - which explains whether the pages are logical or physical read. I am not able to find out this info if the sql call is made from application. I am using SQL Server 2000.
Caution Although DBCC PINTABLE can provide performance improvements, it must be used with care. If a large table is pinned, it can start using a large portion of the buffer cache and not leave enough cache to service the other tables in the system adequately. If a table larger than the buffer cache is pinned, it can fill the entire buffer cache. A member of the sysadmin fixed server role must shut down SQL Server, restart SQL Server, and then unpin the table. Pinning too many tables can cause the same problems as pinning a table larger than the buffer cache.
And I can't find anything...and it would have to be at the page level...