[closed] Dbcc Pintable

NavStudentNavStudent Member Posts: 399
edited 2007-09-11 in SQL General
i was reading the sql server resouce.pdf and read that you can use the command DBCC PINTABLE to make certain tables to stay in memory.
It recommends small hot tables that are used a lot.

I would like to know the experience people have had with using this command on navision tables.

One of our customer uses the customer card a lot to search for customers as they call. The tables has 150 K records. How much space will this table take if it stays in memory?
The server 64 bit sql with 16 gig of memory.
my 2 cents


  • DenSterDenSter Member Posts: 8,304
    Open the database information form (file, database, information) and click the 'Tables' button. There's a column called 'Record Size', which tells you how big each record is in that table. Check this value for the customer table, multiply by the number of records in the table, and that should be the memory you need to store the whole table. I don't know though if it would be extended by the space it needs to store all the indexes.
  • krikikriki Member, Moderator Posts: 9,079
    [Topic moved from Navision forum to SQL General forum]
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • pinning tables is generally considered bad practice - if your table is "hot" it will be in cache anyway so makes pinning unnecessary. A pinned table will usually have it's used indexes in cache too.
    Pinning tables can degrade performance by interferring with how the cache work. Check the value for page life expectancy to see if you have data cache memory pressures, the value of this counter should be high and not fluctate to very low values .. with a low page life then maybe you might gain by pinning but at the expense of other data so i'd figure most likely you'd do more harm than good.
    Use this query to see which tables/indexes are in cache - index 1 is the clustered index, e.g. table. Change the use statement to your database name. ( this is only for sql 2005 ) you can adjust the top to show more or less objects.
    As with all code use with care:-

    use [database];
    -- run in database to be monitored
    -- returns 20 largest table/index objects in data cache
    SELECT top 20 obj.[name]as "Table Name" ,obj.index_id ,si.name,convert(numeric(10,2),(count(*)*8)/1024.0) AS "cached size (mb)"
    FROM sys.dm_os_buffer_descriptors AS bd
    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)
    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 = 2
    ) AS obj
    ON bd.allocation_unit_id = obj.allocation_unit_id
    join sys.indexes si on si.index_id = obj.index_id and si.[object_id] = object_id(obj.name)
    WHERE bd.database_id = db_id()
    GROUP BY obj.name, obj.index_id,si.name
    ORDER BY "cached size (mb)" DESC
  • Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    The SQL Server resource kit contains lot of old and/or outdated information so be carefull. It is based on 3.7 and the knowledge we had back then.

    AFAK the pintable was used to cache the objects for a certain customer but that is just a hear say.

    I agree with colin to not use it. There are better ways to ensure good performance.

    Thanks for the query.
  • thaugthaug Member Posts: 106
    I agree to not use it, and in fact, it doesn't do anything on SQL 2005, so why bother? From BOL:
    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.
    There is no data, only bool!
Sign In or Register to comment.