How can I determine if a table is pinned in SQL Server memory or not?

This should be an easy task, but as far as I can tell, there is no really easy way to do this. In the following demo code, I will give an example of how to do this, although it is a lot of trouble.

–First, the following code pins the “authors” table in pubs:

DECLARE @db_id int, @tbl_id int
USE pubs
SET @db_id = DB_ID(‘pubs’)
SET @tbl_id = OBJECT_ID(‘pubs..authors’)
DBCC PINTABLE (@db_id, @tbl_id)
SELECT @tbl_id

–Next, a table is not really pinned until after it is retrieved from disk into the buffer, so you need to do this with the following command:

SELECT * FROM authors

–Now to see if the authors table is pinned or not, you can run the following code. If the table is pinned, you will get a message that it is, if it is not pinned, then you won’t get any return information about pinned tables.

IF OBJECTPROPERTY (object_id(‘authors’),’TableIsPinned’) = 1
PRINT ‘TableIsPinned’

–When you are done, you will want to unpin your table. To do so, run this example code:

DECLARE @db_id int, @tbl_id int
USE pubs
SET @db_id = DB_ID(‘pubs’)
SET @tbl_id = OBJECT_ID(‘pubs..authors’)
DBCC UNPINTABLE (@db_id, @tbl_id)

Of course, this example code only will check one table at a time. To check all your tables, run the following code:

SELECT * FROM INFORMATION_SCHEMA.Tables WHERE TABLE_TYPE = ‘BASE TABLE’ AND OBJECTPROPERTY(object_id(TABLE_NAME), ‘TableIsPinned’) > 0

Thanks to chappy for the above SELECT statement.




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |