sql-server - memoria - select sql server

Obligar a SQL Server a precachear toda la base de datos en la memoria

Question

Tenemos un sitio de cliente con una base de datos SQL 2012 de 50 Gb en un servidor con más de 100 Gb de RAM.

A medida que se utiliza la aplicación, el servidor SQL hace un gran trabajo almacenando en caché el db en la memoria, pero el aumento en el rendimiento del almacenamiento en caché ocurre la SEGUNDA vez que se ejecuta una consulta, no la primera.

Para intentar maximizar los hits de caché la primera vez que se ejecutan las consultas, escribimos un proceso que itera a través de cada índice de cada tabla dentro de todo el DB, ejecutando esto:

SELECT * INTO #Cache
FROM '' + @tablename + '' WITH (INDEX ('' + @indexname + ''))''

En un intento de forzar una lectura grande, fea y artificial para la mayor cantidad de datos posible. Tenemos programado que se ejecute cada 15 minutos, y hace un gran trabajo en general.

Sin tener en cuenta otros cuellos de botella, especificaciones de hardware, planes de consulta u optimización de consultas, ¿alguien tiene alguna idea mejor sobre cómo llevar a cabo esta misma tarea?

ACTUALIZAR
Gracias por las sugerencias. Se eliminó el "INTO #Cache". Probado y no hizo la diferencia al llenar el buffer.
Agregado: En lugar de Seleccionar *, estoy seleccionando SÓLO las teclas del Índice. Esto (obviamente) es más acertado y es mucho más rápido.
Agregado: Índices de restricción de lectura y caché también.

Aquí está el código actual: (espero que sea útil para otra persona)

CREATE VIEW _IndexView
as
-- Easy way to access sysobject and sysindex data
SELECT
so.name as tablename,
si.name as indexname,
CASE si.indid WHEN 1 THEN 1 ELSE 0 END as isClustered,
CASE WHEN (si.status & 2)<>0 then 1 else 0 end as isUnique,
dbo._GetIndexKeys(so.name, si.indid) as Keys,
CONVERT(bit,CASE WHEN EXISTS (SELECT * FROM sysconstraints sc WHERE object_name(sc.constid) = si.name) THEN 1 ELSE 0 END) as IsConstraintIndex
FROM sysobjects so
INNER JOIN sysindexes si ON so.id = si.id
WHERE (so.xtype = ''U'')--User Table
AND ((si.status & 64) = 0) --Not statistics index
AND ( (si.indid = 0) AND (so.name <> si.name) --not a default clustered index
OR
(si.indid > 0)
)
AND si.indid <> 255 --is not a system index placeholder
UNION
SELECT
so.name as tablename,
si.name as indexname,
CASE si.indid WHEN 1 THEN 1 ELSE 0 END as isClustered,
CASE WHEN (si.status & 2)<>0 then 1 else 0 end as isUnique,
dbo._GetIndexKeys(so.name, si.indid) as Keys,
CONVERT(bit,0) as IsConstraintIndex
FROM sysobjects so
INNER JOIN sysindexes si ON so.id = si.id
WHERE (so.xtype = ''V'')--View
AND ((si.status & 64) = 0) --Not statistics index
GO
CREATE PROCEDURE _CacheTableToSQLMemory
@tablename varchar(100)
AS
BEGIN
DECLARE @indexname varchar(100)
DECLARE @xtype varchar(10)
DECLARE @SQL varchar(MAX)
DECLARE @keys varchar(1000)
DECLARE @cur CURSOR
SET @cur = CURSOR FOR
SELECT v.IndexName, so.xtype, v.keys
FROM _IndexView v
INNER JOIN sysobjects so ON so.name = v.tablename
WHERE tablename = @tablename
PRINT ''Caching Table '' + @Tablename
OPEN @cur
FETCH NEXT FROM @cur INTO @indexname, @xtype, @keys
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT '' Index '' + @indexname
--BEGIN TRAN
IF @xtype = ''V''
SET @SQL = ''SELECT '' + @keys + '' FROM '' + @tablename + '' WITH (noexpand, INDEX ('' + @indexname + ''))'' --
ELSE
SET @SQL = ''SELECT '' + @keys + '' FROM '' + @tablename + '' WITH (INDEX ('' + @indexname + ''))'' --
EXEC(@SQL)
--ROLLBACK TRAN
FETCH NEXT FROM @cur INTO @indexname, @xtype, @keys
END
CLOSE @cur
DEALLOCATE @cur
END
GO

Answer 1

En primer lugar, hay una configuración llamada "memoria de servidor mínimo" que parece tentadora. Ignoralo. Desde MSDN:

La cantidad de memoria adquirida por el motor de base de datos depende por completo de la carga de trabajo que se coloca en la instancia. Es posible que una instancia de SQL Server que no está procesando muchas solicitudes nunca llegue a la memoria mínima del servidor.

Esto nos dice que establecer una memoria mínima más grande no forzará o alentará ningún precaching. Puede tener otras razones para configurar esto , pero el llenado previo del grupo de búferes no es uno de ellos.

Entonces, ¿qué puedes hacer para precargar los datos? Es fácil. Simplemente configure un trabajo de agente para hacer una select * de cada tabla. Puede programarlo para que se inicie automáticamente cuando el agente Sql se inicie. En otras palabras, lo que ya está haciendo es bastante similar a la forma estándar de manejar esto.

Sin embargo, necesito sugerir tres cambios:

1. No intente usar una tabla temporal. Solo selecciona de la tabla. No necesita hacer nada con los resultados para que Sql Server cargue su grupo de búferes: todo lo que necesita hacer es seleccionar. Una tabla temporal podría obligar al servidor sql a copiar los datos del grupo de búferes después de la carga ... terminaría (brevemente) almacenando cosas dos veces .
2. No ejecute esto cada 15 minutos. Simplemente ejecútalo una vez al inicio y luego déjalo en paz. Una vez asignado, se requiere mucho para que Sql Server libere la memoria. Simplemente no es necesario volver a ejecutar esto una y otra vez.
3. No intente insinuar un índice. Las pistas son solo eso: pistas. Sql Server es libre de ignorar esas sugerencias, y lo hará para las consultas que no tienen un uso claro para el índice. La mejor forma de asegurarse de que el índice esté precargado es crear una consulta que obviamente use ese índice. Una sugerencia específica aquí es ordenar los resultados en el mismo orden que el índice. Esto a menudo ayudará a Sql Server a usar ese índice, porque luego puede "recorrer el índice" para producir los resultados.

Answer 2

Esta no es una respuesta, pero para complementar la respuesta de Joel Coehoorn, puede ver los datos de la tabla en la memoria caché usando esta declaración. Use esto para determinar si todas las páginas permanecen en el caché como era de esperar:

USE DBMaint
GO
SELECT COUNT(1) AS cached_pages_count, SUM(s.used_page_count)/COUNT(1) AS total_page_count,
name AS BaseTableName, IndexName,
IndexTypeDesc
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT s_obj.name, s_obj.index_id,
s_obj.allocation_unit_id, s_obj.OBJECT_ID,
i.name IndexName, i.type_desc IndexTypeDesc
FROM
(
SELECT OBJECT_NAME(OBJECT_ID) AS name,
index_id ,allocation_unit_id, OBJECT_ID
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT OBJECT_NAME(OBJECT_ID) AS name,
index_id, allocation_unit_id, OBJECT_ID
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.partition_id
AND au.type = 2
) AS s_obj
LEFT JOIN sys.indexes i ON i.index_id = s_obj.index_id
AND i.OBJECT_ID = s_obj.OBJECT_ID ) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
INNER JOIN sys.dm_db_partition_stats s ON s.index_id = obj.index_id AND s.object_id = obj.object_ID
WHERE database_id = DB_ID()
GROUP BY name, obj.index_id, IndexName, IndexTypeDesc
ORDER BY obj.name;
GO