Find out Which Tables are Pinned by DBCC PINTABLE

This article shows how to find out which tables are pinned into memory. It complements the previous article, which featured dba_Tbl_PinListedTables. That stored procedure used DBCC PINTABLE to pin tables, listed in dba_Tbl_PinOnStartup, into SQL Server's data cache.

Once again I'll warn you that pinning tables into memory can fill up the data cache and force you to stop and restart SQL Server. So use pinning with caution.

This article's UDF shows which tables are pinned into memory it also includes information about the table size so you can have an idea of how much memory might be consumed in the data cache.

The usual reason for pinning tables into memory is to guarantee rapid access to the data in time critical situations. If your database is under memory pressure pinning tables lets you override SQL Server's normal decision making process about which pages to keep in memory.

If you're going to pin the table into memory, then you'll want to be able to find out how much memory might be consumed by your action. This article's UDF, udf_Tbl_PinnedTAB, does just that. It returns a table of information about pinned tables. Here's the CREATE FUNCTION script:

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE    FUNCTION dbo.udf_Tbl_PinnedTAB (

)   RETURNS TABLE
/*
* Returns a table of information about a table. Information is 
* from sysobjects, sysowner, sysindexes, extended properties,
* and OBJECTPROPERITES. Intended as input to a report writer.
*
* Example:
select * from udf_Tbl_PinnedTAB ()
****************************************************************/
AS RETURN 
SELECT TOP 100 PERCENT 
       U.[Name] as [Owner]
     , T.[Name] as [Name]
     , T.[Id] as [Id]
     , crdate [Create Date]
	 , OBJECTPROPERTY(T.[Id], 'TableIsPinned') as Pinned 
	 , SUM (CASE WHEN si.indid = 0 or si.indid = 1
	        THEN si.[rows] ELSE 0 END) as [Rows]
	 , CAST((SUM (CASE WHEN  si.indid = 0 or si.indid = 1
	        THEN si.[dpages] * 8192 
	        ELSE 0 END) / 1048576) as Numeric (18,2)) as [DataMB]
	FROM (SELECT [name], [id], uid, crdate, refdate, schema_ver
	          FROM sysobjects 
	          WHERE xtype = 'U'
	     ) T -- All User tables.
	
	    INNER JOIN sysusers U
	          ON T.uid = U.uid
	    LEFT OUTER JOIN sysindexes SI
	         on T.id = si.id

	WHERE 1=OBJECTPROPERTY(T.[Id], 'TableIsPinned')
	GROUP BY T.[id]
           , t.uid
           , T.[Name]
           , U.[Name]
           , T.crdate
	ORDER BY T.[Name] -- table name.
	       , U.[Name] -- owner
	
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Lets try the UDF out. Before I did this I reran usp_CreateExampleNumberString from Volume 2 #44 using the default parameter, which creates a million rows. I then pinned it into memory. Here's the script that I ran:

exec usp_CreateExampleNumberString default
GO

declare @dbid int, @tblid int
select @dbid = db_id(db_name())
     , @tblid = object_id('examplenumberstring')

dbcc pintable(@dbid, @tblid)
GO

ExampleLetterString was stilled pinned into memory from a previous experiment. So here's the output of executing udf_Tbl_PinnedTAB:

select Owner, Name, Rows, DataMB from dbo.udf_Tbl_PinnedTAB()
GO

(Results)
Owner    Name                  Rows        DataMB               
-------- --------------------  ----------- -------------------- 
dbo      ExampleLetterString   8192        .00
dbo      ExampleNumberString   1048576     66.00

ExampleNumberString might be consuming 66 megabytes of data cache. That could be a large percentage of the total RAM available to your server and I wouldn't do this except for a test, which is after all what we're doing.

One thing to note is that pinning a table survives starting and stopping SQL Server. So once a table is pinned it remains pinned until it is unpinned. But the data cache is cleaned out after the SQL Server instance restarts so the data pages aren't in memory. That is unless you use usp_Tbl_PinListedTables from the previous article, which makes a point of reading the entire table, which forces all the pages into data cache.

Another thing to note about the output of udf_Tbl_PinnedTAB is that the DataMB column measures the megabytes of memory that might be consumed by the table. If the pages haven't been read since the table was pinned then they're probably not in memory. However, since there's always a chance that the pages will be cached, the number to use for system management purposes is the amount of data in the table.

Do you have a great UDF that you'd like to share?  Or maybe you have a T-SQL problem that you think could be solved by a UDF but you don't know how? Send them to: UDF@NovickSoftware.com

and they might be published in this newsletter.  I try and respond to every request that I get. 

Thanks, 
Andrew Novick