[Go] How to host SQL Server tables memory and detect

Note that this feature is deprecated after SQL2005 because SQL2005 automatically manages which tables reside in memory.

Hosting SQL Server data table memory is a feature provided by SQL Server that is rarely involved in the development of a general small system. This document is compiled to show how to put all the data of a table in SQL Server into memory, implement the memory database, and improve the real-time performance.

1, DBCC pintable

Marks a table to is pinned, which means Microsoft SQL Server does not flush the pages for the table from memory.

Syntax

DBCC pintable ( database_id , table_id )

To determine the database ID and use the DB_ID function.

To determine the table ID and use the OBJECT_ID function.

Comments

DBCC pintable does not cause the table to be read into memory. When a page in a table is read into the cache by a normal Transact-SQL statement, the pages are marked as memory-resident pages. When SQL Server requires space to read into a new page, the memory-resident page is not emptied. SQL Server still records updates to the page and, if necessary, writes the updated page back to disk. However, before the DBCC unpintable statement is used to make the table reside, SQL Server keeps a copy of the available pages in the cache.

DBCC pintable is best used to keep small, frequently referenced tables in memory. A page of a small table is read into memory at once, and any future references to its data do not need to be read from disk.

Note DBCC Pintable can provide performance improvements, but be careful when you use them. If you host a large table, the table will start with a large portion of the cache, rather than leaving enough cache for the other tables in the system. If the table that resides is larger than the cache, the table fills the entire cache. A member of the sysadmin fixed server role must shut down and then restart SQL Server, and then make the table non-resident. Hosting too many tables and hosting tables that are larger than the cache will produce the same problem.

Example:

Declare @db_id int, @tbl_id int

Use database_name

Set @db_id = db_id (' database_name ')

Set @tbl_id = object_id (' Department ')

DBCC pintable (@db_id, @tbl_id)

The table department can be set to reside memory.

Declare @db_id int, @tbl_id int

Use database_name

Set @db_id = db_id (' database_name ')

Set @tbl_id = object_id (' Department ')

DBCC unpintable (@db_id, @tbl_id)

You can set the table department to reside memory.

You can use the following SQL directives to detect execution:

Select objectproperty (object_id (' Department '), ' tableispinned ')

If the returned result is 1: The table is already set to reside memory, and 0: Indicates that no memory is set to reside.

2, sp_tableoption

Sets option values for user-defined tables. sp_tableoption may used to turn on the text in row feature on tables with text, ntext , or image columns.

Syntax

sp_tableoption [@TableNamePattern =] 'table'
, [@OptionName =] 'option_name'
, [@OptionValue =] 'value'

where 'option_name' has the following usage:

pintable   --  When disabled (the default), it marks the table as no longer ram-resident. When enabled, marks the table as ram-resident. (The specified table can be memory-resident)

In addition, table lock on bulk load, insert row lock, text in row, and so on are optional values, because the table memory is not involved, and you can query SQL Server Books Online for specific usage.

Value has the following usage:

The option_name is enabled (true, on, or 1) or disabled (false, off , or 0)

Example:

EXEC sp_tableoption ' Department ', ' pintable ', ' true '

Department The data table to reside memory

EXEC sp_tableoption ' Department ', ' pintable ', ' false '

Cancel Data Table Department resident memory

You can use the following SQL directives to detect execution:

Select objectproperty (object_id (' Department '), ' tableispinned ')

If the returned result is 1: The table is already set to reside memory, and 0: Indicates that no memory is set to reside.

3. Conclusions

When you set the data table to reside in memory, you do not actually read the table into memory until the table is retrieved from. Therefore, you can use the following SQL instruction to further department the data table memory:

Select * from Department

In addition, you can use the following SQL command to easily display/detect all the tables in the database that are set to memory-resident:

SELECT * from information_schema. Tables

WHERE table_type = ' BASE TABLE '

and OBJECTPROPERTY (OBJECT_ID (table_name), ' tableispinned ') > 0

Why do I do Select objectproperty (object_id (' Department '), ' tableispinned ') always return 0 and never succeed?

After the query, it turned out that this feature was deprecated in version 2005.

http://msdn.microsoft.com/zh-cn/library/ms144262 (v=sql.90). aspx

These database engine features are not supported in SQL Server 2005 for earlier versions of SQL Server.

category	abolition of the function	Alternative Functions
Backup and restore	Name the pipe backup device.	Disk or tape device. For security reasons, SQL Server does not support backup of named pipes.
Command prompt utility	isql Practical tools	Use the sqlcmd utility. the sqlcmd and osql Utilities do not support passing a stored procedure return value to an exit value. Attention: For more information, see sqlcmd Utility.
Configuration options	sp_configure 's ' Allow updates ' option.	This option exists but does not support direct updates to system tables
Configuration options	sp_configure 's ' open objects ' option.	This option exists, but its functionality is disabled. In SQL Server 2005, the number of open database objects is dynamically managed, and the number is limited only by available memory. The ' open objects ' option is retained in sp_configure to ensure backward compatibility with existing scripts.
Configuration options	sp_configure ' set working set size ' option	This option exists, but its functionality is disabled.
Database creation	DISK INIT DISK RESIZE	Early behavior from SQL Server 6.x
Database creation	CREATE DATABASE for LOAD option	RESTORE operation to create a database
Dbcc	DBCC Dbrepair	You can delete a corrupted database by using drop databases. For more information, see DROP DATABASE (Transact-SQL).
Dbcc	DBCC Newalloc	DBCC Checkalloc For more information, see DBCC (Transact-SQL).
Dbcc	DBCC PINTABLE,DBCC unpintable	No.
Dbcc	DBCC Rowlock	Row-level locking is automatic.
Dbcc	DBCC Textall DBCC Textalloc	DBCC CHECKDB DBCC checktable
Extended stored Procedure Programming	When there is an impersonation context switch from the original login, use the Srv_pwd field in the Srv_pfield structure.	No.
Network protocol	The following protocols: NWLink ipx/spx, AppleTalk, Banyan vines, and multiprotocol.	Configure your application and the instance of the database engine to use one of the following supported protocols: TCP/IP sockets, Named Pipes, VIA, or shared memory. For more information, see Select a network protocol.
Regenerate Master	Rebuildm.exe	Use the Rebuilddatabase option in Setup.exe. For more information, see How to Install SQL Server 2005 from the command prompt.
Sample Database	Northwind and pubs	Use AdventureWorks; however, you can get Northwind and pubsby downloading or copying from an earlier version of SQL Server. For more information, see Samples and sample Databases.
Setup.exe	Remote Installation-The TargetComputer parameter is not supported.	Use remote connections to run SQL Server setup in user interface mode or from a command-line prompt.
Api	SQL-DMO-based WMI provider	Managed code: MICROSOFT.SQLSERVER.MANAGEMENT.SMO.WMI Unmanaged code: WMI Provider for Configuration Management
Api	SQL namespace API (Sql-ns)	No
Transact-SQL	*= and =* outer JOIN operators	Use the JOIN syntax from the FROM clause. For more information, see FROM (Transact-SQL).
Virtual tables	Syslocks	Sys.dm_tran_locks For more information, see sys.dm_tran_locks.