SQL Server Performance

Pinned Tables

Discussion in 'Performance Tuning for DBAs' started by Chappy, Nov 29, 2002.

  1. Chappy New Member

    Is there a way to see what tables, if any, are pinned ?

    Thanks
  2. bradmcgehee New Member

    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.

    --First, the following code pins a 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 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, you need to rewrite your code to check for every table in the database, checking the OBJECTPROPERTY of each table. Who wants to volunteer to write this code?


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  3. Chappy New Member

    Its about time I contributed something back to this forum, however minor <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br />After messing about with various ways, this seems a nice and simple way to list all tables which are pinned. Im not sure if querying sysobjects like this is recommended, or will work in future versions.<br /><br />-- show all pinned user tables<br />select name from sysobjects where xtype = 'U' and OBJECTPROPERTY (object_id(name), 'TableIsPinned') &gt; 0<br /><br />
  4. bradmcgehee New Member

    Cool! I wish I had thought of that.


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  5. Chappy New Member

    For completeness sake, I just learnt how to write this code so its guaranteed not to break on future versions...

    select * from INFORMATION_SCHEMA.Tables where TABLE_TYPE = 'BASE TABLE' and OBJECTPROPERTY(object_id(TABLE_NAME), 'TableIsPinned') > 0
  6. satya Moderator

    That's gr8 on part of Chappy for contributing and its always recommended to query against INFORMATION SCHEMA views than directly to system tables.

    Satya SKJ
  7. x002548 New Member

    ...and to take a little further:

    Set NoCount On

    Declare @Name sysname

    DECLARE CSR1 CURSOR READ_ONLY FOR
    Select name from sysobjects (NoLock) where type = 'U'

    OPEN CSR1

    FETCH NEXT FROM CSR1
    INTO@Name

    WHILE @@FETCH_STATUS = 0

    BEGIN
    select * from INFORMATION_SCHEMA.Tables
    where TABLE_TYPE = 'BASE TABLE'
    and OBJECTPROPERTY(object_id(@Name), 'TableIsPinned') > 0

    FETCH NEXT FROM CSR1
    INTO@Name

    END

    CLOSE CSR1
    DEALLOCATE CSR1

    happy hunting,

    Brett

Share This Page