Pin a list of tables into memory with DBCC PINTABLE.

Last issue did a comparison of two implementations of the Rot13 algorithm. As part of the experimental design the data that they worked with was pinned into memory with the DBCC PINTABLE command. Once the table was pinned and the contents forced into memory by a select that covered the whole table, all the table's pages were in memory. That allowed the two UDFs to be compared without I/O effecting the results.

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 you can override SQL Server's normal decision making process about which pages to keep in memory. You might do this on a medium size (less than 100,000 rows) table if it were being used to support a real-time process where extra delays in fetching the pages would be detrimental to the application's mission.

However, my usual advice on pinning tables is: DON'T DO IT.
Let SQL Server decide which pages belong in memory.

Having warned you I must admit that there are times when I've thought it best to pin a table into memory. I've usually done this when there was a disagreement about database structure that caused one party to argue for denormaization of the tables because it would be faster than a join. My response is: Lets keep the structure normalized and pin the (small to medium) table in memory so the join won't take much time at all. This usually wins the argument although I don't know if it ever improved performance. At least the database structure remained normalized.

I've also run into a circumstance where I wanted to pin a group of small code tables. These tables really didn't take up much space and they were used frequently so they would probably be in the cache always. In any case, it prompted the writing of this week's code. It's not a UDF, instead it's a combination of stored procedure and table.

The table, dba_Tbl_PinOnStartup, contains a list of tables that should be pinned by a stored procedure when the SQL Server instance starts. Here's the CREATE TABLE script:

CREATE TABLE dbo.dba_Tbl_PinOnStartup (
    Owner nvarchar(128) NOT NULL 
            CONSTRAINT DF_dba_Tbl_PinOnStartup_Owner 
                DEFAULT ('dbo'),
	TableName nvarchar (255)  NOT NULL ,
    ReadAllRecs BIT NOT NULL  
            CONSTRAINT DF_dba_Tbl_PinOnStartup_ReadAllRecs
                DEFAULT (1),
    Enabled BIT NOT NULL 
            CONSTRAINT DF_dba_Tbl_PinOnStartup_Enabled
                DEFAULT (1),
    Reason nvarchar(255) NOT NULL,
	CreateDT datetime NOT NULL 
            CONSTRAINT DF_dba_Tbl_PinOnStartup_CreateDT 
                DEFAULT (getdate()),
	CreateByUSERID varchar(128) NOT NULL 
            CONSTRAINT DF_dba_Tbl_PinOnStartup_CreateByUSERID
                DEFAULT (user_name()),
	CONSTRAINT PK_AppTablePinOnStartup 
             PRIMARY KEY  CLUSTERED 
	(
		TableName
	) WITH  FILLFACTOR = 90
)  
GO

The columns include Owner and TableName to identify the table, Enabled so that pinning can be toggled on and off without deleting the row, and Reason to force the entry of some rationale for pinning the table. Note that Reason is NOT NULL and that there are reasonable defaults for each row.

Now let's insert at least one row into the table. Since I don't know what tables are in your database, lets just use the dba_Tbl_PinOnStartup table:

INSERT INTO dbo.dba_Tbl_PinOnStartup (TableName, Reason)
     VALUES ('dba_Tbl_PinOnStartup', 'Demonstration')
GO

To get the tables listed in dba_Tbl_PinOnStartup to be pinned the DBCC PINTABLE command must be run. That's the job of this week's code, stored procedure dba_Tbl_PinListedTables. Here's the CREATE PROCEDURE script:

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE dbo.dba_Tbl_PinListedTables 

/* Pins tables into memory based on those listed in the 
* dba_Tbl_PinOnStartup table.
*
* DIRE WARNING: 
* Be careful about pinning tables into memory.  As DBCC will
* warn, pinning tables can fill SQL Server's data CACHE and 
* force you to stop and restart SQL Server.
*
* Example:
EXEC dbo.dba_Tbl_PinListedTables
*
* © Copyright 2004 Andrew Novick http://www.NovickSoftware.com
* You may use this procedure in any of your SQL Server databases
* including databases that you sell, so long as they contain 
* other unrelated database objects. You may not publish this 
* UDF either in print or electronically.
* Published in the T-SQL UDF of the Week Newsletter Vol 2 #45
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
****************************************************************/
AS 

DECLARE @Owner nvarchar(128)
      , @TableName nvarchar(255)
      , @FullTableName nvarchar(512) -- with owner
      , @ReadAllRecs BIT 
      , @Enabled BIT
      , @DbName nvarchar(776) -- This database Name 
      , @DBID int -- Database ID for pinning the table
      , @ObjectID Int
      , @sql varchar(2000)
      , @Msg nvarchar(4000)
      , @TableCount int

DECLARE PinCursor CURSOR FAST_FORWARD FOR 
    SELECT Owner, TableName, ReadAllRecs, Enabled
        FROM dba_Tbl_PinOnStartup

SELECT @DbName = db_name()
SELECT @DBID = db_id(@DbName) -- dbid of this database
     , @TableCount = 0 -- Init so it's not null

OPEN  PinCursor
FETCH PinCursor INTO @Owner, @TableName
                   , @ReadAllRecs, @Enabled

WHILE @@Fetch_status = 0  BEGIN

    IF 1=@Enabled BEGIN
        SELECT  @objectid = Object_ID(@Owner + '.' + @TableName)
        
        DBCC PINTABLE (@dbid, @objectid)
    
        if @ReadAllRecs = 1 begin
            select @sql = N'declare @rows int ' 
                           + N'select @Rows = count(*) from ' 
                           + @Owner + '.' + @TableName
            EXEC (@sql)
        END
 
        SELECT @Msg = 'Pinned table ' + @DbName + '.'
                      + @Owner + '.' + @TableName
        PRINT @Msg
        EXEC master.dbo.xp_logevent 50001, @Msg , 'INFORMATIONAL'

        SELECT @TableCount = @TableCount + 1        

    END -- If Enabled

    FETCH PinCursor INTO @Owner, @TableName
                       , @ReadAllRecs, @Enabled
END -- of the WHILE LOOP

SELECT @Msg = 'Pinned ' + CONVERT(VARCHAR(10), @TableCount)
              + ' tables in database ' + @DbName
PRINT @Msg
EXEC master.dbo.xp_logevent 50002, @Msg , 'INFORMATIONAL'

-- Clean up the cursor
CLOSE PinCursor
DEALLOCATE PinCursor

RETURN 0



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Let's run the procedure just for yucks:

EXEC dbo.dba_Tbl_PinListedTables
GO

(Results)
Warning: Pinning tables should be carefully considered. If a pinned table is larger, or grows larger, than the available data cache, the server may need to be restarted and the table unpinned.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Pinned table SQLDBADEV.dbo.dba_Tbl_PinOnStartup
Pinned 1 tables in database SQLDBADEV

As you can see, you get the usual dire warning about using DBCC PINTABLE. That's reasonable, and then that's followed by the message about which tables were pinned.

The procedure has sent the messages to both the output of the stored procedure using PRINT and to SQL Server's message log using the extended stored procedure xp_logevent. This will cause it to show up in the text log file that SQL Server keeps for messages.

There are two ways to get a stored procedure to run when the SQL Server instance starts. The first way is to use sp_procoption system stored procedure. The second way is to use a SQL Agent job.

sp_procoption only works on stored procedures in master owned by dbo. I could have moved the table there but I prefer not to because I sometimes encounter resistance to modifying master. It would also be necessary to change the table dba_Tbl_PinOnStartup and the procedure dba_Tbl_PinListedTables to add a the ability to pin tables in specific databases.

Creating a SQL Job to execute the stored procedure is easy to do with Enterprise Manager and I'll leave that up to you. Just be sure that you set the correct database in the step and that you add a schedule to start "Whenever SQL Agent starts".

Next issue will examine how to tell which tables are pinned.

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