Saturday, March 30, 2013

Deprecated Feature - PIN and UNPIN a Table From Memory

How to PIN a Table into Memory?
The result will be that pinned Table pages will not be flushed out from memory, whatever comes in, will stay in memory. If the table is used frequently, the result is improved performance.

use AdventureWorks
GO
DECLARE @db_id int, @tbl_id int
SET @db_id = DB_ID('AdventureWorks')
SET @tbl_id = OBJECT_ID('AdventureWorks.Person.Address')
DBCC PINTABLE (@db_id, @tbl_id)


How to UNPIN a Table from Memory?
Very frequently used tables should be pinned. Performance improvement should be monitored. If no gain/improvement, pinning should be discontinued or else it will overhead to the memory

use AdventureWorks
GO
DECLARE @db_id int, @tbl_id int
SET @db_id = DB_ID('AdventureWorks')
SET @tbl_id = OBJECT_ID('AdventureWorks.Person.Address')
DBCC UNPINTABLE (@db_id, @tbl_id)




NOTE: This feature has been deprecated from 2005 onward, DBCC PINTABLE does nothing anymore.
Query Performance Tuning, SQL Server Query Performance Tuning, sql server query performance tuning tips, DBCC PINTABLE, SQL Server 2005 "Pin" data in Memory, Is there a way to force a table into memory, Keeping data available in the SQL Server data cache with PINTABLE, SQL Server Pin Table into Memory, sql server pin table in memory, dbcc buffer, dbcc dropcleanbuffers, SQL Server, Pinning Tables, DBCC PINTABLE UNPINTABLE, Store a table in a memory, Useful SQL Server DBCC Commands,

0 comments:

Post a Comment