SQL Server Performance

Unpinning the pinned tables

Discussion in 'General DBA Questions' started by SARAVANANG, Jan 25, 2007.

  1. SARAVANANG New Member

    Hi,

    Can anyone tell me whether the DROPCLEANSTATUS will unpin the pinned tables?Due to tye pinned table swe are facing space issues.
    Plz respond as soon as possible

    Regards,
    SARAVANAN.G
  2. thomas New Member

    Are you using SQL Server 2005? You cannot pin tables in it. DBCC PINTABLE runs but does not do anything.

    In 2000 you need to run DBCC UNPINTABLE to unpin tables, I believe.
  3. SARAVANANG New Member

    Hi Thomas,

    Thanks for your reply.

    Actually in SQL server 2005 also we could able to pin the tables.
    Now only I got the script to check the pinned status of particular table.


    IF OBJECTPROPERTY (object_id('table_name' ),'TableIsPinned') = 1 PRINT 'table_name is pinned'.

    Once again I thank you for your concern.

    Regards,
    SARAVANAN.G
  4. ranjitjain New Member

    Hi,
    Even AFAIK DBCC PINTABLE and DBCC UNPINTABLE has been discontinued from SQL 2005.
  5. thomas New Member

    I tried this for a table called Role



    dbcc unpintable(6,922486365)

    SELECT OBJECTPROPERTY (object_id('Role' ),'TableIsPinned')



    and this returned 0, i.e. the pintable had no effect.

    Books Online states

    "This functionality was introduced for performance in SQL Server version 6.5. DBCC PINTABLE has highly unwanted side-effects. These include the potential to damage the buffer pool. DBCC PINTABLE is not required and has been removed to prevent additional problems. The syntax for this command still works but does not affect the server."

    Which I presume is correct.
  6. SARAVANANG New Member

    Yes Thomas.You are right.

    Regards,
    SARAVANAN.G
  7. SARAVANANG New Member

    Can you explain how this DBCC PINTABLE affects the inram space and letme know how to reduce the space took by that DBCC PINTABLE?
    Whether DBCC UNPINTABLE will be effective?
    I had used DBCC UNPINTABLE.But Some 'other' category memory was used instead of Inram memory.
    So to free those space should we restart the server?

    Regards,
    SARAVANAN.G
  8. thomas New Member

    dbcc pintable has no effect in SQL Server 2005. So that's it, you don't need to do anything. there is no other 'category' of memory other than RAM, as far as I know.

    Do you have a performance problem? If your server is in an uncertain state, and you can restart it, I would, at least then you know where you are starting from.
  9. Adriaan New Member

    Not sure why you continue asking? If you are using SQL Server 2005, the commands have no effect at all. If you include them in a script, they are ignored.
  10. MohammedU New Member

    As Thomos mentioned many times and BOL clearly stats...it has been removed... why are you asking the same question again and again...<br /><br />Is your sql server reporting table as PINNED?<br /><br />Read the topic "DBCC PINTABLE & DBCC UNPINTABLE" for more details about PINTABLE...from the following article...<br /><br /<a target="_blank" href=http://msdn2.microsoft.com/en-us/library/ms178015.aspx>http://msdn2.microsoft.com/en-us/library/ms178015.aspx</a><br /><br />I think it is better to lock this thread...<img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br /><br /><br />Mohammed U.
  11. satya Moderator

    Saravanan
    Actually in SQL server 2005 also we could able to pin the tables.
    Now only I got the script to check the pinned status of particular table.

    Can you show where you have got this information?

    Also what is your requirement, any issues with performance lately on the database?
    If so please be specific about your requirement.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  12. SARAVANANG New Member

    Hi Satya,

    I am so sorry.We are using SQL server 2000.Thats why I am again asking about the DBCC PINTABLE and DBCC UNPUINTABLE commands.So Can you answer my last question?

    Regards,
    SARAVANAN.G
  13. ghemant Moderator

    Hi,

    quote:Originally posted by SARAVANANG

    I had used DBCC UNPINTABLE.But Some 'other' category memory was used instead of Inram memory.

    Regards,
    SARAVANAN.G

    What is 'Other' category memory ?

    BOL Excerpts

    quote:DBCC PINTABLE does not cause the table to be read into memory. As the pages from the table are read into the buffer cache by normal Transact-SQL statements, they are marked as pinned pages. SQL Server does not flush pinned pages when it needs space to read in a new page. SQL Server still logs updates to the page and, if necessary, writes the updated page back to disk. SQL Server does, however, keep a copy of the page available in the buffer cache until the table is unpinned with the DBCC UNPINTABLE statement.

    To unpin you have to use DBCC UNPINTABLE command, and as mentioned in BOL it will not Immidiately Flush from data cache,but it is flushed as and when space is needed to read in a new page from disk. No you not need to restart the server.
    Refer sp_tableoption, and Memory Architecture in BOL for Memory.


    Hemantgiri S. Goswami
    MS SQL Server MVP
    -------------------------
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri S. Goswami

    http://hemantgirisgoswami.blogspot.com
  14. MohammedU New Member

  15. SARAVANANG New Member

    Hi all,
    Thank you verymuch for your replies.
    I would like to explain the 'other' category which I mentioned in my previous posts(as enquired by Hemanth)
    If we use DBCC MEMORYSTATUS,it will list a list buffer distribution and buffers.'other' is one among the buffer distribution.



    Regards,
    SARAVANAN.G
  16. ghemant Moderator

    Hi Saravanan,
    hope the explanation and links give you information you need.

    Regards


    Hemantgiri S. Goswami
    MS SQL Server MVP
    -------------------------
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri S. Goswami

    http://hemantgirisgoswami.blogspot.com
  17. SARAVANANG New Member

    Hi Hemanth,
    Thanks once again...

    Regards,
    SARAVANAN.G
  18. satya Moderator

    http://support.microsoft.com/kb/271624 explains the usage of DBCC MEMORYSTATUS.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  19. satya Moderator

    You have to be more clear here for the status of topic relevancy, please mention if you want to keep this to SQL 2000 or SQL 2005.

    quote:Originally posted by SARAVANANG

    Hi Satya,

    I am so sorry.We are using SQL server 2000.Thats why I am again asking about the DBCC PINTABLE and DBCC UNPUINTABLE commands.So Can you answer my last question?

    Regards,
    SARAVANAN.G


    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  20. SARAVANANG New Member

    Hi Satya,
    This topic is for SQL server 2000 only.I am sorry for not mentioning the SQL server edition clearly while starting the discussion.

    Regards,
    SARAVANAN.G
  21. satya Moderator

    Apprecaite your feedback and I hope you are clear on the information you have got above.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Share This Page