[closed] Dbcc Pintable

NavStudent

Hello
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.

DenSter

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.

kriki

[Topic moved from Navision forum to SQL General forum]

colin_leversuch-roberts

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];
go
--
-- 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
INNER JOIN
(
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)
UNION ALL
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_Brummel

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.

thaug

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.