14-1-2021
Facultad de Ingeniería y Ciencias Aplicadas
Ingeniería Informática
Bases de datos III
Ing. Jorge Gordillo
Séptimo Semestre
TEMA: Administración de memoria y disco en
SQL Server
Guaman Ilvis Jeniffer Ximena
UNIVERSIDAD CENTRAL DEL ECUADOR
FACULTAD DE INGENIERIA Y CIENCIAS APLICADAS
INGENIERIA INFORMATICA
1. Introduccion
1.1. SQL SERVER
SQL Server Management Studio: Es el sistema gestor para bases de datos
relacionales en infraestructura SQL, su última versión trabaja bajo el modo
cliente-servidor, es decir, toda la información se aloja del lado servidor, y el cliente
únicamente se encarga de acceder a ella, utiliza T-SQL (Transact-SQL) como
lenguaje para ejecutar sentencias DML.
1.2. Administración del espacio de disco
El espacio en disco es fundamental en las bases de datos porque sirve para
almacenar grandes cantidades de datos de forma permanente.
1.3. Administración de memoria
SQL Server adquiere y libera memoria de manera dinámica según sea preciso.
Normalmente, no es necesario que un administrador especifique la cantidad de
memoria que se debe asignar a SQL Server, aunque todavía existe esta opción
y es necesaria en algunos entornos.
2. Marco teórico
2.1. Gestión de la base de datos
Cuando hay que gestionar una base de datos, se toman en cuenta varios criterios.
Por ello se aborda la gestión del espacio utilizado por los archivos físicos que
constituyen la base de datos. Los puntos principales respecto a los de gestión
de los archivos son:
• el crecimiento dinámico o manual de los archivos,
• la adición de nuevos archivos y la reducción del tamaño de los archivos.
2.1.1. Aumentar el espacio de disco disponible para una base de datos
Los archivos de datos y los archivos diarios almacenan la información. Como la
base contiene normalmente cada vez más información, en un momento dado
estos archivos estarán llenos. En ese instante será necesario encontrar más
espacio.
Los diferentes métodos expuestos a continuación para aumentar el espacio de
almacenamiento para la base de datos son complementarios, ya que cada
método tiene sus ventajas y sus inconvenientes.
• Archivo con crecimiento dinámico
El momento de crear la base, es posible fijar ciertos criterios referentes al
tamaño máximo de los archivos (MAXSIZE) y a la tasa de crecimiento
(FILEGROWTH). Si estas opciones se omiten, el tamaño máximo será infinito
y la tasa de crecimiento será del 10 % para los diarios y de 1 MB para los
archivos de datos.
Si utiliza archivos de crecimiento dinámico, el servidor nunca se bloqueará por
el tamaño del archivo, excepto si se satura el disco o se alcanza el tamaño
máximo.
pg. 1
UNIVERSIDAD CENTRAL DEL ECUADOR
FACULTAD DE INGENIERIA Y CIENCIAS APLICADAS
INGENIERIA INFORMATICA
• Archivo con crecimiento manual
El crecimiento manual de los archivos permite controlar el momento en el que
el archivo va a crecer y, por lo tanto, el servidor va a asumir una carga de
trabajo adicional para adaptar el archivo si se trata de un archivo de datos.
• Adición de archivos
Para permitir a una base de datos obtener más espacio, es posible añadir
archivos. Esta solución presenta la doble ventaja de controlar el momento en
el que el servidor va a sufrir una sobrecarga de trabajo y al mismo tiempo
impedir la fragmentación física de los archivos, sobre todo si estos últimos se
almacenan en una partición NTFS.
• El diario de las transacciones
Está compuesto por uno o varios diarios. Con el objetivo de que el servidor
funcione correctamente, es indispensable que el diario no se sature nunca. El
diario está vacío cuando se realizan copias de seguridad y a veces en cada
punto de sincronización si la base se configura en modo de restauración
simple.
• Modificar un archivo en Transact SQL
La instrucción ALTER DATABASE permite efectuar todas las operaciones
relativas a la manipulación de los archivos de base de datos, tanto para los
archivos de datos como para los archivos del diario de transacciones.
ALTER DATABASE nombreBaseDeDatos
MODIFY FILE (especificaciónArchivo [;]
Para especificaciónArchivo los elementos de sintaxis son los siguientes:
(NAME = nombreLógico,
NEWNAME = nuevoNombreLógico,
FILENAME = ’rutaYNombreArchivo’
[,SIZE = tamaño [KB|MB|GB|TB]]
MAXSIZE = {tamañoMáximo[KB|MB|GB|TB]|UNLIMITED}]
[,FILEGROWTH = pasoIncremento [KB|MB|GB|TB|%]])
Modificar y añadir archivos desde SQL Server Management Studio
Mediante el cuadro de diálogo que indica las propiedades de la base de datos, es
posible gestionar las operaciones sobre el tamaño de los archivos y sobre su
propio tamaño.
pg. 2
UNIVERSIDAD CENTRAL DEL ECUADOR
FACULTAD DE INGENIERIA Y CIENCIAS APLICADAS
INGENIERIA INFORMATICA
Liberar el espacio en disco que usan los archivos de datos vacíos
Cuando las tablas se vacían de sus datos con los comandos DELETE o
TRUNCATE TABLE, las extensiones ocupadas por las tablas e índices se liberan.
Sin embargo, el tamaño de los archivos no se reduce. Para realizar esta operación
es importante asegurarse de que la totalidad del espacio libre se reagrupa al final
del archivo. Una vez que se realiza esta operación, es posible truncar el archivo
sin situarse nunca por debajo del tamaño inicial.
La aplicación de la reducción de los archivos se lleva a cabo utilizando dos
comandos DBCC.
1. SHRINKDATABASE
Esta instrucción permite compactar el conjunto de archivos que conforman
la base de datos (diarios y datos). Para los archivos de datos, todas las
extensiones utilizadas se almacenan de manera contigua en la parte inicial
del archivo. Para los archivos diarios, esta operación de compactación se
realiza en diferido y SQL Server intenta dar a los archivos diarios un tamaño
lo más cercano posible al tamaño alcanzado cuando los diarios se truncan.
DBCC SHRINKDATABASE {nombre_base_datos|id_base_datos|0}
[,porcentaje_determinado]
[,{NOTRUNCATE|TRUNCATEONLY}])
nombre_base_datos: Nombre de la base de datos sobre la que se va a
ejecutar el comando.
id_base_datos: Identificador de la base de datos. Este identificador puede
averiguarse mediante la ejecución de la función db_id() o bien consultando
la vista [Link] desde la base master.
pg. 3
UNIVERSIDAD CENTRAL DEL ECUADOR
FACULTAD DE INGENIERIA Y CIENCIAS APLICADAS
INGENIERIA INFORMATICA
porcentaje_determinado: Permite precisar el porcentaje de espacio libre
deseado en el archivo después de la compactación.
NOTRUNCATE: Permite no entregar al sistema operativo el espacio
obtenido después de la compactación. Por defecto, el espacio obtenido de
esta manera se libera.
TRUNCATEONLY: Permite liberar el espacio inutilizado en los archivos de
datos y compactar el archivo a la última extensión asignada. No se prevé
ninguna reorganización física de los datos: se desplazan las líneas de datos
con el objetivo de completar lo mejor posible las extensiones utilizadas por
el objeto. En este caso, el parámetro porcentaje_determinado se ignora.
2. SHRINKFILE
Esta instrucción, parecida a DBCC SHRINKDATABASE, permite realizar
las operaciones de compactación y reducción de archivo de datos por
archivo.
DBCC SHRINKFILE ([nombre_archivo|id_archivo]
{[[,tamaño_determinado]
[,{NOTRUNCATE|TRUNCATEONLY}]]|EMPTYFILE}
tamaño_determinado: Permite precisar el tamaño final deseado
expresado en megabytes en forma de número entero. Si no se especifica
ningún tamaño, el tamaño del archivo se reduce a su máximo.
EMPTYFILE: Este comando permite realizar la migración de todos los
datos contenidos en el archivo hacia otros archivos del mismo grupo.
Además, SQL Server ya no utiliza este archivo y, por tanto, es posible
eliminarlo de la base de datos usando un comando ALTER DATABASE.
NOTRUNCATE: Permite no entregar al sistema operativo el espacio libre
obtenido después de la compactación. Por defecto, el espacio obtenido de
esta manera se libera.
TRUNCATEONLY: Permite liberar el espacio inutilizado en los archivos de
datos y compactar el archivo a la última extensión asignada. No se prevé
ninguna reorganización física de los datos: se desplazan las líneas de datos
con el objetivo de completar lo mejor posible las extensiones utilizadas por
el objeto. En este caso, el parámetro tamaño_determinado se ignora.
pg. 4
UNIVERSIDAD CENTRAL DEL ECUADOR
FACULTAD DE INGENIERIA Y CIENCIAS APLICADAS
INGENIERIA INFORMATICA
Configuración de la base de datos
Es posible parametrizar numerosas opciones a nivel de base de datos. Esta
parametrización se efectúa con la instrucción ALTER DATABASE en Transact
SQL o desde la ventana de propiedades de la base de datos en SQL Server
Management Studio. Todos los parámetros listados a continuación se deben fijar
para cada base de datos. No es posible fijar las opciones de varias bases de datos
con un único comando, pero sí se pueden precisar varias opciones de una base
en un solo comando.
ALTER DATABASE nombreBaseDeDatos
SET opción [;]
Entre todas las opciones disponibles, se detallan las más interesantes:
Descripción
Opción
AUTO_SHRINK {ON|OFF} Si esta opción está activada, los archivos se reducen cuando disponen de más del
25 % de espacio libre.
READ_ONLY Permite poner la base de datos en modo de solo lectura.
READ_WRITE La base de datos se pone en modo de lectura/escritura.
SINGLE_USER Solo un usuario puede trabajar en la base de datos.
RESTRICTED_USER Solo se pueden conectar a la base de datos los usuarios miembros de los roles
db_owner, db_creator y sysadm.
MULTI_USER Es el modo de funcionamiento estándar de una base de datos, autorizando a varios
usuarios a trabajar al mismo tiempo.
AUTO_CREATE_ Cuando esta opció a opción está en O n está en ON, se calc N, se calculan de maner
STATISTICS { ON | OFF } e manera automática las est ca las estadísticas ausentes en el momento de la
optimización de la consulta.
AUTO_UPDATE_ Cuando esta opción está en ON, se calculan de manera automática las estadísticas
STATISTICS { ON | OFF} obsoletas.
pg. 5
UNIVERSIDAD CENTRAL DEL ECUADOR
FACULTAD DE INGENIERIA Y CIENCIAS APLICADAS
INGENIERIA INFORMATICA
Opciones actualmente gestionadas
Antes de fijar una nueva parametrización de la base de datos, es importante
averiguar la parametrización actual. El conocimiento de esta parametrización
también puede ayudar a la comprensión del funcionamiento de la base. Es posible
leer los valores de las diferentes opciones desde SQL Server Management Studio,
aunque el conocimiento de la parametrización de la base también se puede hacer
con scripts Transact SQL.
• Databasepropertyex: Para averiguar el valor de un parámetro.
• sp_helpdb: Este procedimiento permite averiguar el conjunto de bases de
datos que existen en el servidor. Los datos proporcionados son el nombre, el
tamaño, el propietario, el identificador, la fecha de creación y las opciones.
• sp_spaceused [nombre_objeto]: Permite averiguar el espacio de
almacenamiento utilizado por una base de datos, un diario o los objetos de la
base (tablas...).
pg. 6
UNIVERSIDAD CENTRAL DEL ECUADOR
FACULTAD DE INGENIERIA Y CIENCIAS APLICADAS
INGENIERIA INFORMATICA
Es posible, en SQL Server Management Studio, obtener un informe sobre la
utilización del espacio en disco por las diferentes tablas de la base de datos actual.
En este caso, solo los cuatro primeros informes tienen que ver con el uso de disco.
Ejemplo: El siguiente informe presenta el uso de disco para la base de datos
Base1.
pg. 7
UNIVERSIDAD CENTRAL DEL ECUADOR
FACULTAD DE INGENIERIA Y CIENCIAS APLICADAS
INGENIERIA INFORMATICA
pg. 8
UNIVERSIDAD CENTRAL DEL ECUADOR
FACULTAD DE INGENIERIA Y CIENCIAS APLICADAS
INGENIERIA INFORMATICA
Estructura de los índices
SQL Server ofrece dos tipos de índices:
1. Los índices ordenados
El índice ordenado se crea por defecto cuando se define una restricción de
clave primaria sobre una tabla.
La segunda posibilidad es definir un índice con la opción CLUSTERED.
2. Los índices no ordenados
El otro tipo de índices que es posible definir en SQL Server tiene que ver
con los índices llamados NONCLUSTERED. Es decir: la definición de estos
índices no reorganiza físicamente la tabla. De esta manera, es posible
definir varios índices de este tipo en una misma tabla.
3. Los índices de recubrimiento
El objetivo de estos índices es permitir al motor SQL Server recorrer
únicamente el índice, sin que sea necesario acceder a la tabla para
responder a las necesidades de datos de una consulta. En términos del
volumen de datos manipulados.
4. Indexar las columnas calculadas
Siempre con el objetivo de responder de manera rápida a los usuarios, es
posible definir columnas calculadas en una tabla. Sin embargo, para poder
definirlas, el cálculo deberá ser elemental, es decir, un cálculo realizado
sobre cada línea de datos, y no como resultado de una agrupación.
5. Indexar las vistas
Las vistas se utilizan frecuentemente en las consultas de extracción, ya que
permiten, entre otras cosas, simplificar la escritura de las consultas. Para
mejorar el rendimiento de las consultas que utilizan vistas, es posible definir
uno o varios índices sobre las vistas.
El punto de partida consiste en definir un índice ordenado (CLUSTERED)
único con el objetivo de materializar la vista. Seguidamente, se pueden
definir otros índices no ordenados sobre la vista.
6. Los índices filtrados
No son un nuevo tipo de índice, sino una solución ofrecida por SQL Server
para permitir definir los índices limitando el espacio ocupado en el disco
duro por los índices y, por tanto, el tiempo de actualización de los índices.
Durante la definición del índice filtrado, se añade una cláusula WHERE a la
definición del índice para que este no afecte a todos los registros de la tabla,
sino solo a los que cumplen los criterios de selección.
pg. 9
UNIVERSIDAD CENTRAL DEL ECUADOR
FACULTAD DE INGENIERIA Y CIENCIAS APLICADAS
INGENIERIA INFORMATICA
7. Los índices XML
La indexación de datos XML depende de la propia estructura de los datos.
Al tratar una consulta, la información XML se analiza en cada línea, lo que
puede dar lugar a tratamientos largos y costosos si el número de líneas es
importante o cuando la información en formato XML es abundante.
La partición de tablas y de índices
El objetivo de la partición es ofrecer un mejor rendimiento al trabajar con tablas
muy voluminosas en términos de datos y a las que acceden muchos usuarios.
La partición de una tabla permite dividir una tabla de grandes dimensiones en
varias tablas. una de estas subtablas es más pequeña que la tabla inicial y, por lo
tanto, más fácil de gestionar para SQL Server.
La tabla sobre la que se ha realizado una partición permite optimizar el
almacenamiento de la información sin que el número de tareas administrativas
adicionales sea elevado. Es más: los elementos tales como las restricciones de
integridad y los triggers se definen en la tabla sin tener en cuenta el espacio de
almacenamiento físico utilizado.
1. la función de partición
Permite dirigir los datos a un grupo de archivos o a otro. Para repartir los datos
entre las diferentes particiones, la función utiliza rangos de valores. Cada rango
está limitado por valores. En la función de partición solo se indican estos
valores límite.
CREATE PARTITION FUNCTION nombrefunción ( parámetro)
AS RANGE [ LEFT | RIGHT ] FOR VALUES ( [ valorLímite [ ,... ] ] )
Parámetro: Columna de cualquier tipo salvo timestamp, varchar(max),
nvarchar(max) y varbinary utilizada para calcular la clave de partición.
valorLímite: Valor que marca el límite de cada partición.
2. El esquema de partición
Permite establecer una tabla de correspondencia entre los valores devueltos
por la función de partición y la utilización de una partición u otra. Cada partición
está asignada a un grupo de archivos. Es posible, aunque no recomendable,
utilizar el mismo grupo de archivos para todas las particiones.
CREATE PARTITION SCHEME nombreEsquemaPartición
AS PARTITION nombreFunciónPartición
[ ALL ] TO ( { grupoDeArchivos | [ PRIMARY ] } [,_]
[;]
pg. 10
UNIVERSIDAD CENTRAL DEL ECUADOR
FACULTAD DE INGENIERIA Y CIENCIAS APLICADAS
INGENIERIA INFORMATICA
nombreEsquemaPartición: Identificador del esquema de partición.
nombreFunciónPartición: Nombre de la función de partición relativa al
esquema. Un esquema solo puede relacionarse con una única función. Por el
contrario, una misma función puede ser utilizada por varios esquemas.
grupoDeArchivos: Nombre del grupo o de los grupos de archivos utilizados
por las diferentes particiones.
3. Los índices con particiones
El proceso de partición es el mismo, es decir: se basa en un esquema y una
función de partición. Se recomienda que el índice que se quiere particionar
esté definido sobre una tabla que ya tenga particiones y se base en los mismos
esquemas y funciones de partición.
Si la columna de partición no forma parte de las columnas indexadas, entonces
se incluye en el índice como una columna que permite definir índices de
recubrimiento.
CREATE INDEX nombreÍndice
ON nombreTabla(col nombreTabla(columna1,...)
ON nombreEsquemaPartición (columnaDePartición);
Compresión de datos
SQL Server 2012 ofrece la posibilidad de activar la compresión a nivel de tablas e
índices. Si la compresión se puede definir sobre las tablas e índices existentes, no
se tomará en cuenta hasta después de la reconstrucción de la tabla (ALTER tabla
nombre abla REBUILD) o del índice en cuestión. Si la compresión de la tabla
implica la compresión del índice organizado (CLUSTERED), los índices no
organizados no se ven afectados y es necesario habilitar la compresión sobre
cada uno de ellos, uno a uno. En el caso de las tablas con particiones, la
compresión puede tener lugar partición a partición.
El objetivo de la compresión es reducir el espacio de disco que utilizan los datos
de la tabla. La compresión de los datos va a permitir almacenar más líneas de
información en el mismo bloque de 8 KB. La compresión no permite aumentar el
tamaño máximo de las líneas. De hecho, el mecanismo debe ser reversible.
En las tablas valorizadas, es posible conocer el impacto de la compresión de los
datos ejecutando el procedimiento almacenado
sp_estimate_data_compression_savings.
La compresión es una operación puntual. Por eso es preferible pasar por el
asistente que propone SQL Server Management Studio. A nivel de Transact SQL,
pg. 11
UNIVERSIDAD CENTRAL DEL ECUADOR
FACULTAD DE INGENIERIA Y CIENCIAS APLICADAS
INGENIERIA INFORMATICA
la compresión se realiza con las instrucciones CREATE TABLE/CREATE INDEX
y ALTER TABLE/ALTER INDEX.
Cifrado de datos
SQL Server permite encriptar los archivos de datos y los diarios de log. Este
encriptado es dinámico y se efectúa en el momento de cada escritura en disco.
Es igual para la operación de desencriptado de datos. Esta funcionalidad de
encriptado/desencriptado es transparente y se llama TDE o Transparent Data
Encryption.
Establecer esta operación de encriptado permite garantizar una opacidad más
grande de los archivos de datos y diarios para las diferentes herramientas de
sistema de análisis de los archivos o evitar una asociación/anulación de
asociación de base de datos no autorizada. Sin embargo, esta operación de
encriptado no tiene ninguna garantía adicional en lo que respecta a la
comunicación entre el proceso cliente y el servidor.
2.2. Administración de disco
El rendimiento de Microsoft SQL Server depende en gran medida del subsistema
de E/S (IOS). La latencia en el IOS puede provocar muchos problemas de
rendimiento.
En el Monitor de sistema, estos contadores monitorean la cantidad de E / S
generada por los componentes de SQL Server al examinar las siguientes áreas
de rendimiento:
o Escribir páginas en disco
o Leer páginas del disco
Para configurar los archivos de registro y datos de manera que el rendimiento sea
máximo, siga estas prácticas recomendadas:
1. Para evitar el conflicto de disco, no coloque los archivos de datos en la
misma unidad que contenga los archivos del sistema operativo.
2. Coloque los archivos de registro de transacciones en una unidad
separada de los archivos de datos. Esto proporciona el máximo
rendimiento ya que se reduce el conflicto de disco entre los datos y los
archivos de registro de transacciones.
3. Si es posible, coloque la base de datos en una unidad independiente,
preferiblemente en un sistema RAID 10 o RAID 5. En los entornos en
los que se produce un uso intensivo de las bases de datos tempdb
podrá obtener un mejor rendimiento colocando la base de datos
tempdb en una unidad independiente, permitiendo que SQL Server
pg. 12
UNIVERSIDAD CENTRAL DEL ECUADOR
FACULTAD DE INGENIERIA Y CIENCIAS APLICADAS
INGENIERIA INFORMATICA
realice operaciones de tempdb en paralelo con las operaciones de
base de datos.
A continuación, se muestra un diseño sugerido para reducir el conflicto de
entrada/salida de disco:
Las DMV (Dynamic Management Views) nos pueden ayudar a identificar cuellos
de botella con I/O son:
o sys.dm_os_wait_stats
o sys.dm_os_waiting_tasks
o sys.dm_io_virtual_file_stats
Vamos a ver las estadísticas generales de vista mediante la ejecución de la
consulta:
SELECT * FROM sys.dm_os_wait_stats dows
ORDER BY dows.wait_time_ms DESC;
El resultado de esta consulta será un total en ejecución de cualquier momento
en el que un subproceso esperó en algo:
pg. 13
UNIVERSIDAD CENTRAL DEL ECUADOR
FACULTAD DE INGENIERIA Y CIENCIAS APLICADAS
INGENIERIA INFORMATICA
Para ver las estadísticas de espera de I/O por base de datos/archivos, ejecute la
siguiente consulta:
SELECT * FROM sys.dm_io_virtual_file_stats(DB_ID('AdventureWorks2014'), NULL) divfs
ORDER BY divfs.io_stall DESC;
pg. 14
UNIVERSIDAD CENTRAL DEL ECUADOR
FACULTAD DE INGENIERIA Y CIENCIAS APLICADAS
INGENIERIA INFORMATICA
2.3. Gestión dinámica de la memoria
Tanto Oracle como SQL Server pueden adaptar dinámicamente el tamaño de los
componentes dentro de sus respectivas áreas de memoria para adecuarse a las
condiciones de ejecución de las distintas tareas. Con ello se reduce la
probabilidad de paginación. SQL Server libera toda la memoria no utilizada
cuando lo solicita el sistema operativo. Si el sistema operativo no utiliza toda la
memoria para sus propios procesos o para los de otras aplicaciones, SQL Server
asignará la memoria a sus buffers de cache cuando lo necesite. En SQL Server
se puede especificar la cantidad de memoria mínima reservada para la ejecución
de consultas. Este valor puede ir desde 512 Kb a 2 Gb para una sola consulta.
Uno de los principales objetivos de diseño de todo el software de base de datos
es minimizar la E/S de disco porque las operaciones de lectura y escritura del
disco realizan un uso muy intensivo de los recursos. SQL Server crea un grupo
de búferes en la memoria para contener las páginas leídas en la base de datos.
Gran parte del código de SQL Server está dedicado a minimizar el número de
lecturas y escrituras físicas entre el disco y el grupo de búferes. SQL Server
intenta encontrar un equilibrio entre dos objetivos:
1. Evitar que el grupo de búferes sea tan grande que todo el sistema se
quede con poca memoria.
2. Minimizar la E/S física a los archivos de base de datos al maximizar el
tamaño del grupo de búferes.
pg. 15
UNIVERSIDAD CENTRAL DEL ECUADOR
FACULTAD DE INGENIERIA Y CIENCIAS APLICADAS
INGENIERIA INFORMATICA
Efectos de las opciones min y max server memory
Las opciones de configuración min server memory y max server memory
establecen los límites superior e inferior de la cantidad de memoria que usa el
grupo de búferes y otras memorias caché del motor de base de datos de SQL
Server. Según aumenta la carga de trabajo del Motor de base de datos de SQL
Server, se sigue adquiriendo la memoria necesaria para permitir la carga de
trabajo.
SQL Server adquiere, como un proceso, más memoria de la especificada en la
opción max server memory. Los componentes tanto internos como externos
pueden asignar memoria fuera del grupo de búferes, lo cual consume memoria
adicional, pero la memoria asignada en el grupo de búferes todavía representa
normalmente la cantidad más grande de memoria que consume SQL Server.
La cantidad de memoria que adquiere el Motor de base de datos de SQL Server
es totalmente dependiente de la carga de trabajo colocada en la instancia. Una
instancia de SQL Server que no procesa muchas solicitudes nunca podrá
alcanzar el nivel de min server memory.
Herramientas para el seguimiento del rendimiento
Vistas de administración dinámica
Las tres DMV de uso común en SQL Server para el rendimiento de la memoria
son:
• sys.dm_os_sys_info
• sys.dm_os_sys_memory
• sys.dm_os_process_memory
Buscaremos información sobre la maquina:
SELECT dosi.physical_memory_kb,
dosi.virtual_memory_kb,
dosi.committed_kb,
dosi.committed_target_kb
FROM sys.dm_os_sys_info dosi;.
Esto hará retornar un conjunto de información útil sobre la máquina
• physical_memory_kb: cantidad total de memoria física en la máquina
• virtual_memory_kb: cantidad total de espacio de direcciones virtuales
disponibles para el proceso en modo de usuario
pg. 16
UNIVERSIDAD CENTRAL DEL ECUADOR
FACULTAD DE INGENIERIA Y CIENCIAS APLICADAS
INGENIERIA INFORMATICA
• committed_kb: memoria comprometida en kilobytes (KB), en el
administrador de memoria
• committed_target_kb: cantidad de memoria, en kilobytes (KB), que
puede consumir el administrador de memoria del SQL Server
Para ver la información actual de la memoria del sistema, utilice la siguiente
consulta:
SELECT dosm.total_physical_memory_kb,
dosm.available_physical_memory_kb,
dosm.system_memory_state_desc
FROM sys.dm_os_sys_memory dosm;
• total_physical_memory_kb: cantidad total de memoria física disponible
para el sistema operativo.
• available_physical_memory_kb: cantidad total de memoria física
disponible
• system_memory_state_desc: explicación del estado de la memoria
Muestra la memoria de proceso de SQL Server actual:
SELECT dopm.physical_memory_in_use_kb,
dopm.process_physical_memory_low,
dopm.process_virtual_memory_low
FROM sys.dm_os_process_memory dopm;
Esto hará retornar unas señales para hacernos saber si la memoria de proceso
física y virtual para SQL Server es baja:
• physical_memory_in_use_kb: indica el proceso de trabajo establecido
en KB
• process_physical_memory_low: indica que el proceso responde a una
notificación de memoria física baja
• process_virtual_memory_low: indica que se ha detectado una condición
de memoria virtual baja
pg. 17
UNIVERSIDAD CENTRAL DEL ECUADOR
FACULTAD DE INGENIERIA Y CIENCIAS APLICADAS
INGENIERIA INFORMATICA
Recopilador de datos
Algunos contadores para la supervisión del rendimiento y las herramientas de
supervisión de SQL Server que se pueden usar para realizar un seguimiento de
ellos:
• Memoria disponible en megabytes: Este es un excelente contador,
especialmente si buscamos durante mucho tiempo porque podemos
averiguar qué rangos hay para la memoria. El valor predeterminado del
rango de memoria es 100 MB.
• SQLServer: buffer Manager/buffer cache ratio: Este representa un
porcentaje de la frecuencia con la que SQL Server puede encontrar
páginas de datos en la memoria en lugar de recuperarlas del disco.
• SQLServer: buffer Manager/ Page life expectancy: este es el contador
de rendimiento más popular cuando se refiere a memoria en SQL Server.
Representa el número de segundos que una página se alojará en el grupo
de búferes sin las referencias.
• SQLServer: buffer Manager/Lazy writes/seg: este número muestra
cuántas páginas se vacían de la memoria fuera del proceso del punto de
comprobación o recuperación cuando hay sobrecarga de memoria. Este
valor siempre debe ser menor que 20, si es mayor, entonces
probablemente debería considerar la asignación de más memoria al SQL
Server.
Se puede ejecutar la consulta a continuación para ver cuánto se dedica a SQL
Server:
SELECT [Link], c.value_in_use
FROM [Link] c
WHERE [Link] LIKE '%server memory%';
Resultados es:
pg. 18
UNIVERSIDAD CENTRAL DEL ECUADOR
FACULTAD DE INGENIERIA Y CIENCIAS APLICADAS
INGENIERIA INFORMATICA
Grupo de búfer de SQL Server en acción
SQL Server recupera datos de dos áreas; memoria y disco.
Como las operaciones de disco son más caras en términos de E / S, lo que
significa que son mucho más lentas, SQL almacena y recupera páginas de datos
de un área conocida como Buffer Pool, donde las operaciones son mucho más
rápidas.
Para comprender cómo funciona el Buffer Pool y cómo beneficia nuestro
procesamiento de consultas, necesitamos verlo en acción. Afortunadamente, SQL
Server nos brinda varias vistas de administración y funcionalidades integradas
para ver exactamente cómo se usa el Buffer Pool y cómo, o lo que es más
importante, si nuestras consultas lo están utilizando de manera eficiente.
Para configurar las opciones de memoria de SQL Server:
Inicie sesión en SQL Server a través de SQL Server Management Studio con
privilegios de administrador.
Haga clic derecho en el nodo de la base de datos para configurar la memoria.
Asegúrese de elegir la instancia de base de datos correcta si está conectado a
más de una instancia. Vaya a la opción Memoria en la ventana Propiedades del
servidor.
Proporcione los valores en MB que desea asignar a SQL Server. Asegúrese de
considerar la cantidad de transacciones OLTP en el servidor antes de configurar.
3. Referencias Bibliográficas
• [Link]
sql-server-para-el-rendimiento-de-e-s-de-disco/
• [Link]
de-sql-server-para-el-rendimiento-de-la-memoria/
pg. 19