A partir de la versión 7.0 el cache de datos y de procedures de SQL Server se encuentran en un solo pool de memoria, en el se almacenan tanto datos como planes de ejecución. Conoce la manera de manipularla.
SQL Server utiliza este cache para dos propósitos, liberar los buffers o almacenar planes de ejecución. Un buffer es una página en la memoria que es del mismo tamaño que una página de datos o de índices y es utilizada para almacenar una página de datos de la base de datos. El buffer (buffer pool) es administrado por un proceso llamada lazywriter, el cuál utiliza un algoritmo para liberar las páginas de este buffer de tal manera que tenga buffers libres para las siguientes páginas de datos.El lazywriter también es el encargado de escribir a disco las páginas sucias.
SQL Server también almacena en el cache planes de ejecución, estos planes de ejecución también pueden referirse a planes de ejecución de ad-hoc queries. Un ad-hoc query es básicamente cualquier consulta que no es un store procedure como sp_executesql, sentencias ejecutadas por medio de un ODBC/OLE, SQLPrepare/SQLExecute o IcommandPrepare.
Cuando el plan de ejecución es compilado, el plan es salvado en el cache con un costo, este es 0 si una sentencia ad-hoc y si no lo es, el costo es el dado al generar el plan. Un costo de 0 indica que el plan puede ser eliminado del cache inmediatamente.
Los planes de ejecución de las sentecias Ad-hoc se incrementan en uno, cada vez que son reutilizados, el máximo número que pueden tener es el costo de la compilación.
La forma de poder interactuar con el cache por medio se sentencias es la siguiente.
Determinando que hay en el cache.
Se puede consultar la tabla syscacheobjects, en donde se encuentran todos los objetos que están en el cache en ese momento.
|
bucketid
|
INTEGER
|
Bucket ID. Value indicates a range from 0 through (directory size - 1). Directory size is the size of the hash table.
|
|
cacheobjtype
|
NVARCHAR(34)
|
Object Type in cache:
Compiled Plan Executable Plan Parse Tree Cursor Parse Tree Extended Stored Procedure |
|
objtype
|
NVARCHAR(16)
|
Object Type:
Stored Procedure Prepared statement Ad hoc query ReplProc (replication procedure) Trigger View Default User table System table Check Rule |
|
objid
|
INTEGER
|
Object ID. Internally generated for ad-hoc queries or prepared statements.
|
|
dbid
|
SMALLINT
|
Database ID in which the object was compiled.
|
|
dbidexec
|
SMALLINT
|
Internal use.
|
|
uid
|
SMALLINT
|
The creator ID of the plan for ad hoc query plans and prepared plans. A -2 indicates the batch submitted does not depend on implicit name resolution and can be shared among different users.
|
|
refcounts
|
INTEGER
|
Number of other cached objects referencing this object. 1 is the base number.
|
|
usecounts
|
INTEGER
|
Number of times this cached objects has been used since cached.
|
|
pagesused
|
INTEGER
|
Number of memory pages used by this object.
|
|
lasttime
|
BIGINT
|
Internal use.
|
|
maxexectime
|
BIGINT
|
Internal use.
|
|
avgexectime
|
BIGINT
|
Internal use.
|
|
lastreads
|
BIGINT
|
Internal use.
|
|
lastwrites
|
BIGINT
|
Internal use.
|
|
setopts
|
INTEGER
|
SET option settings modified for the object. Options include:
ANSI_PADDING FORCEPLAN CONCAT_NULL_YIELDS_NULL ANSI_WARNINGS ANSI_NULLS QUOTED_IDENTIFIER ANSI_NULL_DFLT_ON ANSI_NULL_DFLT_OFF |
|
langid
|
SMALLINT
|
The language ID the connection that created the cache object.
|
|
dateformat
|
SMALLINT
|
Date format of the connection that created the cache object.
|
|
status
|
INTEGER
|
Indicates whether the cache object is a cursor plan.
|
|
sqlbytes
|
INTEGER
|
Length of name or batch submitted.
|
|
sql
|
NVARCHAR(256)
|
Procedure name or characters of the batch submitted.
|
Determinando el número de páginas de cache que están siendo utilizadas por un proceso
Se puede consultar la tabla sysprocesses
You can query the sysprocesses system table to determine the number of pages a process currently has allocated in the cache.
|
spid
|
SMALLINT
|
SQL Server Process ID
|
|
kpid
|
SMALLINT
|
Windows NT Thread ID
|
|
blocked
|
SMALLINT
|
SPID of blocking process
|
|
waittype
|
BINARY(2)
|
Reserved.
|
|
waittime
|
INTEGER
|
Current wait time, in milliseconds.
If 0 then process is not currently waiting. |
|
lastwaittype
|
NCHAR(32)
|
The last or current wait type.
|
|
waitresource
|
NCHAR(256)
|
Lock resource.
|
|
dbid
|
SMALLINT
|
The database ID that the process is currently running in.
|
|
uid
|
SMALLINT
|
The ID of the user executing this process.
|
|
cpu
|
INTEGER
|
The cumulative CPU usage for this process.
|
|
physical_io
|
BIGINT
|
The cumulative Disk I/O for this process.
|
|
memusage
|
INTEGER
|
Number of pages in the procedure cache that are currently allocated to this process.
A negative number indicates that the process is taking memory away from another process. |
|
login_time
|
DATETIME
|
The time the client process logged onto the server.
If the process is a system process the time is the time that SQL Server was last started. |
|
last_batch
|
DATETIME
|
The last time the client process executed a remote stored procedure call or an EXECUTE statement.
If the process is a system process the time is the time that SQL Server was last started. |
|
ecid
|
SMALLINT
|
Execution context ID used to identify all subthreads.
|
|
open_tran
|
SMALLINT
|
The number of open transactions the process holds.
|
|
status
|
NCHAR(30)
|
The current status of the process.
|
|
sid
|
BINARY(86)
|
A GUID (globally uniqueidentifier) for the user.
|
|
hostname
|
NCHAR(128)
|
The name of the workstation running the process.
|
|
program_name
|
NCHAR(128)
|
The name of the application running the process.
|
|
hostprocess
|
NCHAR(8)
|
The workstation SPID number.
|
|
cmd
|
NCHAR(16)
|
The command currently being executed.
|
|
nt_domain
|
NCHAR(128)
|
The Windows Domain name for the client if the process is using Windows Authentication or a trusted connection.
|
|
nt_username
|
NCHAR(128)
|
The Windows user name running the process if the process is using Windows Authentication or a trusted connection.
|
|
net_address
|
NCHAR(12)
|
The assigned unique identifier number for each of the client's network cards.
|
|
net_library
|
NCHAR(12)
|
The client's network library.
|
|
loginame
|
NCHAR(128)
|
The client's login name.
|
|
context_info
|
BINARY(128)
|
Internal Use.
|
Como mantener una tabla en el cache
DBCC PINTABLE ( database_id , table_id )
Liberar de memoria una tabla
DBCC UNPINTABLE ( database_id , table_id )
Forzar que todas las páginas sucias se escriban a disco
CHECKPOINT
Liberar todas las páginas de datos de la memoria
DBCC DROPCLEANBUFFERS
Imprimir los buffers headers y las paginas del buffer cache
--Debe de encenderse la bandera 3604
DBCC TRACEON (3604)
DBCC TRACEON (3604)
DBCC BUFFER ( [@dbid|'@dbname' ]
[,@objid|obname ] [,@numofbuffers]
[,@printopt] )
[,@objid|obname ] [,@numofbuffers]
[,@printopt] )
Desplegar el procedure cache
DBCC PROCCACHE
Remover todos los planes de ejecución del cache
Se puede usar el commando DBCC FREEPROCCACHE o DBCC FLUSHPROCINDB para liberar sus planes de ejecución del cache y provocar que todos los store procedures se recompilen la siguiente ocasion que sean ejecutados
DBCC FLUSHPROCINDB (@dbid)
DBCC FREEPROCCACHE
Mostrar el uso de la memoria cache
DBCC MEMORYSTATUS