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 BUFFER ( [@dbid|'@dbname' ]
[,@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

You are not authorised to post comments.

Comments powered by CComment

DBASupport - Foro

Mirroring con estados disconnected
Hola, el día de hoy después de reiniciar mi servidor de destino a donde realizo el mirroring ya no pude replicar más, el error que muestra es que...

Se me duplica el mensaje en /etc/motd
  • Topic started by agamez
  • in General
  • 3 years ago.
  • 3 years ago.
  • 2 replies
Buen día, alguien tiene idea porque después de actualizar a fedora 28 se me esta duplicando la salida de mi /etc/motd?

Cómo puedo filtrar un mensaje en el log messages
Buenos días, estoy recibiendo en mi log de errores muchos mensajes de cron, ya que tengo varios crones configurados en mi server. Existe alguna...

Cómo validar la hora de ejecución de un proceso
Hola, estoy tratando de validar que un proceso sólo pueda ser ejecutado dentro de un periodo de tiempo, en este caso entre las 7:00 AM y las 7:00...

Estadisticas en la base de datos
Buenos días, estoy teniendo problemas de performance en mis queries y quisiera saber como puedo validar que mis tablas tienen las estadisticas...
URGE AYUDA
  • Topic started by
  • in General
  • 15 years ago.
  • 15 years ago.
  • 30 replies
HOLA ESPERO ME PUEDAN AYUDAR. TEN GO UNA TABLA CON una estructura similar a la siguiente: id_mov cargo subtotal total...

Duada de Memoria de SQL Server 2005
  • Topic started by Erzengel
  • in General
  • 11 years ago.
  • 11 years ago.
  • 30 replies
Buen Día Recurro nuevamente a su sabiduría. Quiero saber el porque tiene un tope SQL Serever de 2GB en Windows server 2003 de 32 bit y si alguien...

SqlDateTime overflow
  • Topic started by
  • in General
  • 15 years ago.
  • 15 years ago.
  • 11 replies
Alguno de ustedes ha tenido este problema: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.' Les...

problemas para iniciar el agente sql
  • Topic started by acgch
  • in General
  • 14 years ago.
  • 14 years ago.
  • 11 replies
Recientemente para asegurar el sql server 2005, cree una cuenta sql con derechos de sysadmin y borre el grupo BUILTIN\Administradores para evitar...

Problemas para levantar mirror
  • Topic started by acgch
  • in General
  • 14 years ago.
  • 14 years ago.
  • 16 replies
Hola amigos, tengan un buen día, les comento que estoy configurando mirror siguiendo los pasos a través del wizard del managment studio y también...
Mirroring con estados disconnected
Hola, el día de hoy después de reiniciar mi servidor de destino a donde realizo el mirroring ya no pude replicar más, el error que muestra es que...

Se me duplica el mensaje en /etc/motd
  • Topic started by agamez
  • in General
  • 3 years ago.
  • 3 years ago.
  • 2 replies
Buen día, alguien tiene idea porque después de actualizar a fedora 28 se me esta duplicando la salida de mi /etc/motd?

Cómo puedo filtrar un mensaje en el log messages
Buenos días, estoy recibiendo en mi log de errores muchos mensajes de cron, ya que tengo varios crones configurados en mi server. Existe alguna...

Cómo validar la hora de ejecución de un proceso
Hola, estoy tratando de validar que un proceso sólo pueda ser ejecutado dentro de un periodo de tiempo, en este caso entre las 7:00 AM y las 7:00...

Estadisticas en la base de datos
Buenos días, estoy teniendo problemas de performance en mis queries y quisiera saber como puedo validar que mis tablas tienen las estadisticas...
Deshabilitar todos los jobs
  • Topic started by agamez
  • in Jobs
  • 4 years ago.
  • 4 years ago.
  • 1 reply
Buenos días, alguien sabe como puedo deshabilitar rapidamente todos los jobs de SQL Server, gracias.

Mirroring con estados disconnected
Hola, el día de hoy después de reiniciar mi servidor de destino a donde realizo el mirroring ya no pude replicar más, el error que muestra es que...

Se me duplica el mensaje en /etc/motd
  • Topic started by agamez
  • in General
  • 3 years ago.
  • 3 years ago.
  • 2 replies
Buen día, alguien tiene idea porque después de actualizar a fedora 28 se me esta duplicando la salida de mi /etc/motd?

Cómo puedo filtrar un mensaje en el log messages
Buenos días, estoy recibiendo en mi log de errores muchos mensajes de cron, ya que tengo varios crones configurados en mi server. Existe alguna...

Cómo validar la hora de ejecución de un proceso
Hola, estoy tratando de validar que un proceso sólo pueda ser ejecutado dentro de un periodo de tiempo, en este caso entre las 7:00 AM y las 7:00...