Unidad 06

UNIDAD ACADMICA N 06:
MONITOREO DE LA BASE DE DATOS
SQL Server acta como un servicio para proporcionar datos a las aplicaciones.
El ajuste del rendimiento tiene como objetivo la optimizacin de dicho servicio para la
aplicacin. La optimizacin debe concentrarse en reducir al mnimo el tiempo de
respuesta de cada consulta y aumentar lo mximo posible el rendimiento de todo el
servidor de bases de datos mediante la reduccin del trfico de red, la entrada y salida
(E/S) en disco, y el tiempo de CPU. Para conseguir este objetivo, debe comprender los
requisitos de la aplicacin, la estructura lgica y fsica de los datos, y el equilibrio entre
los usos conflictivos de la base de datos, por ejemplo, entre el proceso de transacciones
en lnea (OLTP, Online Transaction Processing) y la toma de decisiones.
Al finalizar el estudio de la presente unidad temtica el estudiante:
Describe las razones por las que es importante la supervisin de SQL

Server.
Desarrolla una metodologa de supervisin y optimizacin del

rendimiento.
Describe las herramientas disponibles para supervisar SQL Server.
Realiza tareas de supervisin y optimizacin habituales mediante los

contadores y herramientas apropiadas.
Ing. Metssy A.Ygnacio Garca
1. POR QUE SUPERVISAR EL RENDIMIENTO DE LA BASE DE DATOS?

SQL Server acta como un servicio para proporcionar datos a las
aplicaciones. El ajuste del rendimiento tiene como objetivo la optimizacin
de dicho servicio para la aplicacin. La optimizacin debe concentrarse en
reducir al mnimo el tiempo de respuesta de cada consulta y aumentar lo
mximo posible el rendimiento de todo el servidor de bases de datos
mediante la reduccin del trfico de red, la entrada y salida (E/S) en disco, y
el tiempo de CPU. Para conseguir este objetivo, debe comprender los
requisitos de la aplicacin, la estructura lgica y fsica de los datos, y el
equilibrio entre los usos conflictivos de la base de datos, por ejemplo, entre
el proceso de transacciones en lnea (OLTP, Online Transaction
Processing) y la toma de decisiones.
Los problemas de rendimiento deben considerarse durante el ciclo de
desarrollo, no slo en la fase final de implementacin del sistema. Muchas
de las soluciones que mejoran significativamente el rendimiento se obtienen
gracias a un diseo pormenorizado desde el comienzo del proyecto.
Por lo general, SQL Server administra automticamente los recursos de
hardware disponibles. Es evidente que otros problemas de rendimiento del
sistema, como la memoria y el hardware, afectan al rendimiento, aunque las
mejoras que pueden obtenerse en estas reas a menudo son incrementales
2. SUPERVISION Y OPTIMIZACION DEL RENDIMIENTO

La supervisin de un sistema puede ser una tarea compleja debido al gran
nmero de variables implicadas. Puede utilizar diferentes estrategias para
plantear la optimizacin del rendimiento del sistema en reas especficas.
Dichas estrategias deben integrarse de modo que sea posible localizar
cualquier causa y efecto relacionados. El desarrollo de este plan se inicia
con las fases de requisitos del usuario y diseo de aplicaciones. A partir de
este punto, el plan debe abarcar la identificacin de las mejoras de
rendimiento en las reas ms tradicionales.
a) Estrategias para la optimizacin del rendimiento

El objetivo de la optimizacin es mejorar el rendimiento. Hay muchos
factores que pueden afectar al rendimiento, por lo que necesitar
varias estrategias para optimizar una instancia de la base de datos.
Dichas estrategias deben mejorar el rendimiento en ambos extremos
de la aplicacin, el servidor y el usuario o cliente empresarial. Hay
dos indicadores del rendimiento:
Tiempo de respuesta. Mide el intervalo de tiempo necesario

para devolver la primera fila del conjunto de resultados.
Normalmente, se llama tiempo de respuesta al tiempo que

percibe el usuario hasta que recibe una confirmacin visual de
que una consulta se ha procesado.
Rendimiento. Mide el nmero total de consultas que el

servidor puede procesar en un intervalo de tiempo dado.
Deber supervisar ambas reas, ya que los problemas de conflictos

aumentan a medida que el nmero de usuarios se incrementa; esto
podra causar un aumento de los tiempos de respuesta del servidor y
una reduccin del rendimiento global. Si supervisa exclusivamente el
rendimiento del servidor no tendr informacin acerca del
rendimiento de la aplicacin para los usuarios. Y al contrario, si
supervisa exclusivamente la aplicacin averiguar cules son los
problemas, pero no podr resolverlos.
Optimizacin del tiempo de respuesta
Para optimizar las necesidades comerciales y los tiempos de
respuesta es necesario conocer la aplicacin, el entorno, los usuarios
y los datos. Para usar este enfoque debe disponer de informacin
acerca de las consultas que los usuarios emiten y realizar, a
continuacin, los ajustes necesarios en las consultas y la aplicacin.
Por lo general, el objetivo consiste en mejorar el rendimiento de
consultas especficas o de aplicaciones seleccionadas
Optimizacin del rendimiento
La optimizacin del rendimiento y el procesamiento del servidor
requiere conocimientos acerca de cmo SQL Server tiene acceso a
los datos, controla las actividades simultneas e interacta con el
sistema operativo. Esto podr ayudarle a crear un diseo lgico y
fsico ms eficaz para configurar el sistema, disear transacciones y
escribir consultas con el fin de optimizar el rendimiento
b) Seleccin de un mtodo para optimizar el rendimiento

Es posible plantear la optimizacin de la empresa y el servidor en
varios elementos, ya que factores como el diseo de la aplicacin, los
recursos del sistema y el sistema operativo pueden afectar al
rendimiento. Para mejorar el rendimiento puede:
Optimizar la aplicacin de cliente. Para ello debe:

Escribir consultas que limiten las bsquedas.
Crear ndices tiles.
Reducir el nmero de conflictos de bloqueo y evitar los

interbloqueos.
Usar procedimientos almacenados que reduzcan los
conflictos y aumenten la simultaneidad.
Descargar y procesar datos desde el servidor siempre
que sea apropiado.
Optimizar la base de datos. Esto puede mejorar el tiempo de

respuesta de las consultas. Para optimizar la base de datos,
perfeccione el diseo lgico y fsico.
Optimizar SQL Server. Para optimizar SQL Server puede

evaluar el diseo de almacenamiento o, en algunos casos,
ajustar las opciones de configuracin.
Optimizar la configuracin del hardware. El cambio de la

configuracin de hardware tambin puede mejorar el
rendimiento del sistema. Por ejemplo, puede agregar ms
memoria, procesadores o equipos; conseguir discos duros
ms rpidos; o incrementar el rendimiento de red.
Al planear el ajuste y la optimizacin de la base de datos, debe

considerar los elementos citados anteriormente como cuellos de
botella e identificar su impacto en el rendimiento del servidor y en
los tiempos de respuesta de los usuarios.
c) Desarrollo de una metodologa de optimizacin del

rendimiento
La metodologa de optimizacin del rendimiento debe considerarse
durante el ciclo de desarrollo de la aplicacin, no slo en la fase de
implementacin del sistema. Es necesario administrarla y
documentarla
cuidadosamente
para
evitar
investigaciones
innecesarias en direcciones que no lleven a ninguna conclusin.
Diseo para mejorar el rendimiento:
Algunos elementos que afectan al rendimiento son difciles de
optimizar una vez que la base de datos est en funcionamiento. La
preocupacin acerca del rendimiento de SQL Server debe ser una
parte integral del proceso de diseo de la aplicacin. Debe:
Analizar las expectativas y requisitos del usuario. Considerar si

la aplicacin tiene destinatarios de datos secundarios, por
ejemplo, clientes que telefonean a operadores de centralitas
que emplean aplicaciones de entrada de pedidos u ofertas de
productos.
Conocer los datos y cmo se seleccionan, cules son los

posibles valores, qu informacin representan y cmo se
utilizan.
Disear una base de datos que utilice apropiadamente la

normalizacin y la desnormalizacin, y aplicar diseos de
esquema relacionales, en estrella y de copo de nieve, siempre
que sea necesario.
Desarrollar y probar procedimientos almacenados.
Disear la estrategia de indizacin y optimizar las consultas.
Programar un mantenimiento y una monitorizacin continuada
Plan para mejorar el rendimiento:

Como parte del plan de optimizacin del rendimiento, desarrolle una
referencia basada en el entorno de prueba del diseo. El diseo y la
prueba son procesos continuos para garantizar que los cambios
realizados realmente mejorarn el rendimiento. Debe:
Definir los parmetros de funcionamiento del servidor para los

recursos, la carga y el rendimiento.
Establecer objetivos para el rendimiento y los tiempos de

respuesta.
Documentar todas las acciones y medir sus efectos.
Probar un entorno de produccin simulado.
Analizar las transacciones de cada base de datos.
Identificar los problemas de rendimiento.
Establecer una referencia de rendimiento.
d) Establecimiento de una referencia de rendimiento

No siempre es necesario que las tareas se realicen de la forma ms
rpida. Si una consulta o resultado se necesita en dos segundos,
SQL Server deber proporcionarla en dos segundos. Es posible que
el tiempo y los recursos necesarios para optimizar todava ms una
consulta no sean rentables.
Cinco factores clave influyen en el rendimiento de una base de datos:
Carga de trabajo. El volumen de actividad del servidor.
Rendimiento. Nmero total de consultas en un intervalo de

tiempo dado.
Recursos de sistema. Capacidad fsica del hardware del

equipo.
Optimizacin. Diseo de la aplicacin y la base de datos.
Conflictos. La competencia por los registros de datos.
El plan de rendimiento y las medidas de referencia deben considerar

estas reas. Cuando haya definido los parmetros de
funcionamiento, as como los recursos, las cargas y los objetivos
necesarios para el plan de rendimiento, deber supervisar el sistema.
Identifique y establezca la referencia de rendimiento a lo largo de un
perodo. Realice mediciones para determinar:
Las horas de actividad mxima y mnima de la base de datos.
Los tiempos de respuesta de los comandos de proceso por

lotes y consulta de produccin.
Los tiempos de ejecucin de los procesos de restauracin y

copia de seguridad de la base de datos.
Una vez establecida la referencia, podr comparar con ella el

rendimiento real del servidor en cada momento y as determinar las
reas que requieren investigacin. Las cifras que estn por encima o
por debajo de la referencia indican las reas en las que podra
realizarse una investigacin ms detallada
Figura 6.1: Establecimiento de una referencia de rendimiento
e) Deteccin de cuellos de botella en el rendimiento

Un cuello de botella es cualquier componente o actividad que limita el
rendimiento. Todos los sistemas tienen cuellos de botella, pero uno
de los objetivos de la supervisin de un servidor es localizar aqullos
que reducen el rendimiento por debajo de sus expectativas. Para ello,
debe comprender la naturaleza y el funcionamiento interno de las
operaciones.
Decida qu examinar:
Debe supervisar el uso de la memoria, el uso de la CPU, el
rendimiento de la entrada y salida de disco, las conexiones de
usuario y los bloqueos. Para determinar el origen de un cuello de
botella, observe las cuestiones relativas al sistema antes de examinar
las cuestiones referentes al cliente y a las consultas. Por ejemplo, los
cuellos de botella relacionados con el sistema y ocasionados por el
uso de los discos y la memoria pueden afectar al rendimiento general
de toda la aplicacin, incluidas las consultas individuales. La
resolucin de cuestiones como la hiperpaginacin en disco, antes de
examinar el rendimiento de una consulta en particular, resulta muy
til.
Conozca el intervalo de rendimiento aceptable:
Como ocurre con cualquier tcnica para solucionar problemas, el
conocimiento del grado de rendimiento aceptable ayuda a identificar
las reas problemticas. Las cifras pequeas pueden ser tan
significativas como las grandes. Una cifra menor o mayor de lo
esperado puede indicar un posible problema. En ocasiones, un
problema en un rea afecta desfavorablemente o disimula problemas
en otra. Por ejemplo:
Un componente puede evitar que la carga alcance a otro

componente.
La congestin de la red puede impedir que las solicitudes de

los clientes lleguen al servidor.
Los clientes pueden tener cuellos de botella que les impidan el

acceso al servidor.
Para descubrir los lmites reales, puede simular una carga de trabajo
en SQL Server mientras supervisa el sistema con las herramientas
descritas en este mdulo.
f) Tareas de supervisin habituales:

habituales:
SQL Server proporciona herramientas que puede utilizar para
examinar diversos aspectos de su rendimiento. Estas herramientas
ofrecen distintos grados de detalle para ayudarle a aislar las reas
con problemas. Su objetivo debe ser determinar el factor que tiene un
mayor efecto en el rendimiento. A menudo, para aislar el cuello de
botella es necesario repetir varias veces este proceso de supervisin.
Supervisin del sistema:
Al evaluar el sistema, obsrvelo primero desde una perspectiva ms
general, para asegurarse de que dispone de hardware suficiente para
satisfacer los requisitos de las tareas que debe realizar. A
continuacin, valore los efectos de la configuracin del sistema
operativo y el diseo de la aplicacin. Para este tipo de supervisin,
puede utilizar:
Visor de sucesos de Windows 2000.
Monitor de sistema de Windows.
Supervisin especfica de SQL Server

A continuacin, supervise reas especficas de SQL Server. Observe
la actividad de SQL Server y la coherencia de los datos. Supervise la
cantidad de bloqueos y conflictos por los recursos, y el uso de
conexiones de usuarios. Para este tipo de supervisin, puede utilizar:
La ventana Actividad actual del Administrador corporativo de

SQL Server.
Procedimientos almacenados del sistema e instrucciones de

Transact-SQL.
Analizador de SQL.
Utilice instrucciones del comprobador de coherencia de la base de

datos (DBCC, Database Consistency Checker) para asegurar que las
estructuras internas de datos son correctas.
Rendimiento de consultas especficas
Por ltimo, examine consultas especficas para evaluar su
rendimiento. Entre los elementos que puede revisar se encuentran el
uso de ndices, el tiempo de CPU de una consulta y la E/S efectiva.
Para este grado detallado de supervisin puede usar el Analizador de
SQL, el Analizador de consultas SQL y el Asistente para optimizacin

de ndices.
3. HERRAMIENTAS PARA SUPERVISAR AL SGBDR:
a) Herramientas de supervisin
rendimiento de SQL Server
optimizacin
del
Microsoft SQL Server incluye un conjunto de herramientas para

supervisar los eventos de SQL Server y para optimizar el diseo de la
base de datos fsica. La eleccin de la herramienta depende del tipo
de supervisin u optimizacin que se realice y de los eventos
particulares que se supervisen.
A continuacin se describen las herramientas de supervisin y
optimizacin de SQL Server:
Herramienta
Descripcin
Analizador de
SQL Server
El Analizador de SQL Server realiza un

seguimiento de los eventos de procesos
del motor, como el inicio de un lote o una
transaccin, que permite supervisar la
actividad del servidor y de la base de
datos (por ejemplo, interbloqueos, errores
graves o actividad de inicio de sesin).
Puede capturar datos del Analizador de
SQL Server en un archivo o una tabla de
SQL Server para su anlisis posterior y
tambin reproducir paso a paso los
eventos capturados en SQL Server, para
ver qu sucedi exactamente.
Supervisar el
uso de
recursos
(Monitor de
sistema)
La funcin principal del Monitor de

sistema es hacer un seguimiento del uso
de los recursos, como el nmero de
solicitudes de pgina del administrador de
bfer activas, que permite supervisar el
rendimiento y actividad del servidor
mediante
objetos
y
contadores
predefinidos o contadores definidos por el
usuario para supervisar eventos. El
Monitor
de
sistema
(Monitor
de
rendimiento en Microsoft Windows NT 4.0)
recopila contadores y porcentajes en lugar
de datos acerca de los eventos (por
ejemplo, uso de la memoria, nmero de
transacciones
activas,
nmero
de
bloqueos bloqueados o actividad de la
CPU). Puede establecer umbrales en
contadores especficos para generar
alertas que notifiquen a los operadores.
El Monitor de sistema funciona en los
sistemas operativos Microsoft Windows
Server y Windows. Puede supervisar
(remota o localmente) una instancia de
SQL Server en Windows NT 4.0 o
posterior.
La diferencia clave entre el Analizador de
SQL Server y el Monitor de sistema es
que el Analizador de SQL Server
supervisa los eventos del motor de base
de datos, mientras que el Monitor de
sistema supervisa el uso de los recursos
asociado con los procesos del servidor.
El Monitor de actividad de SQL Server
Management Studio muestra informacin
grfica acerca de:
Monitor de
actividad (SQL
Server
Management
Studio)
Los procesos que se ejecutan en una

instancia de SQL Server.
Los procesos bloqueados.
Bloqueos.
La actividad de los usuarios.
Esto resulta til para vistas ad hoc de la
actividad actual.
Procedimientos
almacenados
de
Transact-SQL que crean, filtran y definen
trazas:
Introduccin a
Traza de SQL
sp_trace_create (Transact-SQL)
sp_trace_generateevent (Transact-SQL)
sp_trace_setevent (Transact-SQL)
sp_trace_setfilter (Transact-SQL)
sp_trace_setstatus (Transact-SQL)
Supervisar los
registros de
errores
El registro de sucesos de aplicacin de

Windows proporciona una imagen global
de los eventos que ocurren en todos los
sistemas operativos Windows Server y
Windows, as como de los eventos de
SQL Server, el Agente SQL Server y la
10
bsqueda de texto. Contiene informacin

acerca de los eventos de SQL Server que
no est disponible en ningn otro lugar.
Puede utilizar la informacin del registro
de errores para solucionar problemas
relacionados con SQL Server.
Los
siguientes
procedimientos
almacenados del sistema de SQL Server
suponen una alternativa muy eficaz para
realizar muchas tareas de supervisin:
sp_who (Transact-SQL)
Notifica informacin de instantneas
acerca de los usuarios y procesos
actuales de SQL Server, incluida la
informacin sobre la instruccin que se
ejecuta actualmente y si la instruccin
est bloqueada.
Procedimientos
almacenados
del motor de
base de datos
(Transact-SQL)
sp_lock (Transact-SQL)
Proporciona
informacin
de
instantnea acerca de bloqueos,
incluidos los identificadores de objeto y
de ndice, el tipo de bloqueo y el tipo o
recurso al que se aplica el bloqueo.
sp_spaceused (Transact-SQL)
Muestra una estimacin de la cantidad
actual de espacio en disco que utiliza
una tabla (o toda la base de datos).
sp_monitor (Transact-SQL)
Muestra estadsticas que incluyen el
uso de la CPU, el uso de E/S y el
tiempo de inactividad desde la ltima
vez que se ejecut sp_monitor.
DBCC
(Transact-SQL)
Las instrucciones DBCC (Comandos de

consola de base de datos) permiten
comprobar las estadsticas de rendimiento
y la coherencia lgica y fsica de una base
de datos.
Funciones
(Transact-SQL)
Las funciones integradas muestran

estadsticas de instantneas acerca de la
actividad de SQL Server desde el inicio
del servidor; estas estadsticas se
almacenan en contadores de SQL Server
predefinidos.
Por
ejemplo,
@@CPU_BUSY contiene el tiempo que la
11
CPU ha estado ejecutando cdigo de SQL

Server, @@CONNECTIONS contiene el
nmero de conexiones o intentos de
conexiones
de
SQL
Server
y
@@PACKET_ERRORS
contiene
el
nmero de paquetes de red generados en
conexiones de SQL Server.
Marcas de traza
(Transact-SQL)
Las
marcas
de
traza
muestran
informacin acerca de una actividad
especfica en el servidor para diagnosticar
problemas o causas de bajo rendimiento
(por ejemplo, cadenas de interbloqueos).
Optimizar el
diseo fsico de
las bases de
datos
El Asistente para la optimizacin de motor

de base de datos analiza los efectos en el
rendimiento de las instrucciones TransactSQL ejecutadas en las bases de datos
que desea optimizar. El Asistente para la
optimizacin de motor de base de datos
proporciona
recomendaciones
para
agregar, quitar o modificar ndices, vistas
indizadas y particiones.
Instrucciones DBCC
Puede utilizar instrucciones DBCC para comprobar el rendimiento y
la actividad, as como la coherencia lgica y fsica de una base de
datos. La tabla siguiente contiene algunas instrucciones DBCC que
puede utilizar para supervisar el rendimiento.
Instruccin DBCC
Proporciona informacin acerca

de
SQLPERF
Estadsticas desde la ltima vez

que se inici el servidor. Puede
establecerlas
para
reunir
informacin acerca del uso del
espacio
del
registro
de
transacciones en todas las bases
de datos (LOGSPACE), la entrada
y salida de disco (IOSTATS), el uso
de memoria y cach (LRUSTATS),
o la actividad de red (NETSTATS).
12
OPENTRAN
La transaccin activa ms antigua,

as
como
las
transacciones
duplicadas
distribuida
y
no
distribuida ms antiguas, si las hay,
en la base de datos especificada.
Los resultados slo se muestran si
hay una transaccin activa o si la
base de datos contiene informacin
de duplicacin.
SHOW_STATISTICS
Selectividad de un ndice, lo que

supone la base para determinar si
un ndice es til para el optimizador
de consultas.
CHECKDB
Asignacin e integridad estructural

de todos los objetos de una base
de datos.
CHECKFILEGROUP
Asignacin e integridad estructural

de todas las tablas del grupo de
archivos.
CHECKTABLE
Integridad de las pginas de datos,

ndice o de datos de tipo text,
ntext o image de la base de datos
especificada.
Variables globales
Puede utilizar las variables globales siguientes para obtener
estadsticas especficas o informacin que pueda consultar.
Variable global
Objetivo
@@connections
Contiene el nmero de inicios de

sesin o intentos de inicio de sesin
desde la ltima vez que se inici SQL
Server.
@@error
Contiene el nmero de error de la

ltima instruccin de Transact-SQL
ejecutada.
@@spid
Contiene el identificador de proceso

de servidor del proceso de usuario
actual. Puede usar este valor para
13
identificar el proceso de usuario actual

en la salida de sp_who.
Contiene
el
identificador
procedimiento
almacenado
procedimiento actual.
@@procid
de
del
b) Herramientas de supervisin de Windows

Los sistemas operativos Windows y Windows Server
proporcionan adems estas herramientas de supervisin:
2003
Herramienta
Descripcin
Administrador de
tareas
Muestra una sinopsis de los procesos y

las aplicaciones que se ejecutan en el
sistema.
Agente de
supervisin de red
Supervisa el trfico de red.
Visor de sucesos de Windows 2000/2003

Utilice el Visor de sucesos de Windows 2000 para identificar sucesos
que puedan estar causando cuellos de botella en el rendimiento. Con
esta informacin podr determinar qu sucesos o reas del
rendimiento conviene examinar con ms detalle.
El Visor de sucesos de Windows 2000/2003 permite ver los registros
de sucesos descritos en la tabla siguiente.
Tipo
Registro
Windows
Descripcin
de
aplicacin
Contiene los sucesos que

registran las aplicaciones,
como SQL Server. Por
de
ejemplo, una aplicacin de
base de datos podra anotar
un error de archivo en el
registro de aplicacin.
Contiene
sucesos
que
Registro de sistema de Windows registran los componentes de
sistema de Windows. Por
14
ejemplo, un error al cargar un

controlador
u
otro
componente
del
sistema
durante el inicio del equipo se
anotar en el registro de
sistema.
Registro
Windows
de
seguridad
Registra los sucesos de

de seguridad de Windows, como
los intentos de iniciar una
sesin en el sistema.
Monitor de sistema de Windows con SQL Server

Al supervisar SQL Server y el sistema operativo Microsoft Windows
para investigar problemas relacionados con el rendimiento, hay tres
reas principales en las que debe concentrarse inicialmente:
Actividad del disco
Uso del procesador
Uso de la memoria
Puede resultar til supervisar el sistema operativo Windows y los

contadores de SQL Server al mismo tiempo para determinar las
posibles correlaciones entre el rendimiento de SQL Server y el de
Windows. Por ejemplo, la supervisin simultnea de los contadores
de E/S de disco de Windows y los contadores del Administrador de
bfer de SQL Server puede mostrar el comportamiento del sistema
en su totalidad.
La supervisin de un equipo en el que se ejecuta el Monitor de
sistema puede afectar un poco al rendimiento del equipo. Por tanto,
registre los datos del Monitor de sistema en otro disco o en otro
equipo para reducir as el efecto en el equipo que est supervisando,
o bien ejecute el Monitor de sistema desde un equipo remoto.
Supervise slo los contadores en los que est interesado. Si
supervisa demasiados contadores, la sobrecarga de uso de los
recursos se agrega al proceso de supervisin y afecta al rendimiento
del equipo que se est supervisando.
El Monitor de sistema permite obtener estadsticas sobre la actividad
y el rendimiento actuales de SQL Server. Con el Monitor de sistema,
puede:
Ver simultneamente datos de cualquier nmero de

equipos.
15
Ver y cambiar grficos para reflejar la actividad actual y

mostrar valores de contadores que se actualizan con la
frecuencia definida por el usuario.
Exportar datos desde grficos, registros, registros de

alertas e informes a aplicaciones de hoja de clculo o de
base de datos para manipularlos e imprimirlos.
Agregar alertas del sistema que muestran un evento en el

registro de alertas y que pueden notificarse mediante una
alerta de red.
Ejecutar un programa predefinido la primera vez, o todas

las veces, que el valor de un contador sea superior o
inferior a un valor definido por el usuario.
Crear archivos de registro que contengan datos relativos a

diversos objetos de equipos diferentes.
Anexar a un archivo secciones seleccionadas de otros

archivos de registro existentes para crear un archivo de
almacenamiento a largo plazo.
Ver informes de la actividad actual o crear informes a partir

de archivos de registro existentes.
Guardar la configuracin de grficos, alertas, registros o

informes individuales, o bien de toda el rea de trabajo,
para volverla a utilizar.
Elegir una herramienta de supervisin

La eleccin de la herramienta de supervisin depende del evento o
de la actividad que se va a supervisar.
Evento o
actividad
Analizador
de SQL
Server
Monitor de
sistema
Anlisis de
tendencias
Reproduccin de
los eventos
capturados
Supervisin ad
hoc
Generacin de
alertas
Monitor de
actividad
TransactSQL
Registros
de errores
16
Interfaz grfica
Uso en
aplicaciones
personalizadas
S1
S
S
4. TAREAS HABITUALES DE SUPERVISIN Y OPTIMIZACIN

Para supervisar una instancia de SQL Server es necesario realizar un
anlisis de algunas reas clave que se tratan en este mdulo. La
eliminacin de los cuellos de botella fsicos puede afectar al rendimiento de
forma inmediata y aislar todava ms los problemas de diseo en la base
de datos, las consultas de Transact-SQL o las aplicaciones de cliente.
a) Supervisin del uso de la memoria
SQL Server requiere memoria para necesidades de memoria esttica

(sobrecarga del ncleo, objetos abiertos y bloqueos) as como para la
cach de datos, tambin llamada cach del bfer).
Cmo utiliza SQL Server la memoria para la cach de datos
De manera predeterminada, SQL Server adquiere y libera memoria
para la cach de datos dinmicamente, segn los recursos del
sistema disponibles y la demanda simultnea de dichos recursos. Si
SQL Server precisa memoria adicional para la cach de datos,
1
Mediante procedimientos almacenados del sistema del Analizador de SQL Server.
17
consulta al sistema operativo para determinar si hay disponible

memoria fsica. En caso afirmativo, SQL Server la utiliza para la
cach de datos y conserva los datos ledos previamente.
SQL Server aumenta y reduce la cach de datos para mantener libre
una cantidad de memoria fsica comprendida entre 4 MB y 10 MB, en
funcin de la actividad del servidor, e impedir as la paginacin de
Windows 2000. Si se ha asignado o hay disponible una cantidad de
memoria insuficiente en SQL Server, el rendimiento disminuye, ya
que los datos se leen de forma continua en lugar de residir en la
cach de datos.
Si varias instancias de SQL Server se ejecutan en el mismo equipo,
cada una utilizar de forma independiente el algoritmo estndar de
administracin de memoria dinmica.
Supervisin del uso de los archivos de paginacin y la memoria
Los contadores siguientes indican la cantidad de bytes disponibles
actualmente para los procesos, el nmero de pginas relacionadas
con los fallos de pgina y la tasa de fallos de pgina atribuibles a
SQL Server.
Objeto:
Contador
Memoria:
Bytes
disponibles
Memoria:
Pginas/seg.
Proceso:
Fallos de
pgina/seg./In
stancia de
SQL Server
Descripcin
Pautas
Este contador siempre

debe mostrar una cifra
Supervisa el nmero de
mayor de 5000 KB. Un
bytes disponibles para
valor inferior indicara
que se ejecuten los
que la memoria fsica
procesos
global es insuficiente y
debe incrementarse.
pginas que el sistema
operativo
Windows
2000 leer o escribir
en el disco duro para
resolver los fallos de
pgina
Este contador nunca

debe ser mayor de cero
de forma regular. En
caso
contrario,
el
sistema
operativo
Windows 2000 est
utilizando el archivo de
paginacin para rellenar
solicitudes de memoria.
Supervisa los fallos de

pgina causados por
Windows
2000
al
recortar los tamaos de
los conjuntos de trabajo
Un nmero alto en este

contador
indica
un
exceso de paginacin e
hiperpaginacin
en
disco. Compruebe si
SQL Server u otro
18
de dichos procesos
proceso provoca el
exceso de paginacin.
b) Supervisin del uso de procesador y subprocesos
Para obtener un rendimiento ptimo del procesador debe haber un

equilibrio entre el rendimiento y los tiempos de respuesta.
Rendimiento del procesador
Al examinar el uso del procesador, tenga en cuenta el tipo de trabajo
que realizar la instancia de SQL Server. Si SQL Server debe realizar
un gran nmero de clculos, como consultas que requieran
agregados o consultas limitadas a la memoria que no requieran E/S
de disco, puede utilizarse todo el tiempo del procesador.
En los sistemas multiprocesador deber supervisar una instancia
independiente de este contador en cada procesador. Para determinar
el promedio de todos los procesadores, utilice el contador Sistema:
% de tiempo de procesador total.
Una tasa de procesador que se mantiene alta de forma continuada
puede indicar que necesita realizar una actualizacin de CPU o
agregar ms procesadores. Asimismo, una tasa de utilizacin de
CPU que se mantiene alta de forma continuada podra indicar un
ajuste o un diseo deficientes de la aplicacin
19
Subprocesos
Cada instancia de SQL Server constituye un proceso de sistema
operativo independiente. Las instancias de SQL Server emplean
subprocesos de Windows y, en ocasiones, intraprocesos, para
administrar estas tareas simultneas de forma eficaz.
Los procesos son instancias de una aplicacin, por ejemplo,

SQL Server, que pueden tener una o varias tareas.
Los subprocesos son mecanismos que procesan las tareas y

se utilizan para programar el tiempo en los procesadores.
Para lograr la mxima utilizacin de los procesadores, el sistema

operativo cambia entre los subprocesos cuando un subproceso
queda inactivo mientras espera a que una operacin se complete,
como una lectura o escritura en el disco duro. El cambio entre
subprocesos se denomina cambio de contexto. Adems, cada
instancia de SQL Server mantiene un grupo de subprocesos para las
conexiones del usuario. Los subprocesos de este grupo se
denominan subprocesos de trabajo.
Los cuellos de botella pueden identificarse cuando Procesador: %
de tiempo de procesador se aproxima de forma regular al 100 por
ciento y Sistema: Longitud de cola de procesador muestra varios
procesos de la aplicacin a la espera de ser procesados, o cuando el
valor de Sistema: Cambios de contexto/Seg. es alto. Si
Procesador: % de tiempo de procesador se aproxima al 100 por
ciento y Sistema: Cambios de contexto/Seg. se aproxima a 8.000,
considere la posibilidad de utilizar procesadores ms rpidos,
procesadores adicionales o de comenzar a utilizar intraprocesos.
Un solo subproceso puede contener varios intraprocesos. Para
habilitar el uso de intraprocesos dentro de un subproceso en SQL
Server en lugar de utilizar varios subprocesos para tareas de SQL,
cambie el valor de lightweight pooling a 1. Esto se denomina
programacin en modo de intraproceso.
Al utilizar la programacin en modo de intraproceso, SQL Server:
Asigna los intraprocesos del sistema operativo Windows 2000

desde un grupo de intraprocesos a las tareas del usuario, en
lugar de asignar los subprocesos del sistema operativo
Windows 2000 a un grupo de subprocesos.
Realiza todos los cambios entre intraprocesos, en lugar de

permitir que el sistema operativo Windows 2000 se ocupe de
esta tarea.
20
Cuando SQL Server cambia entre intraprocesos dentro de un

subproceso, Windows 2000 ahorra recursos, ya que no necesita
cambiar entre modos. La sobrecarga asociada con la habilitacin de
la programacin en el modo de intraproceso suele ser mayor que
cuando se permite que Windows 2000 realice el cambio de contexto.
Basndose en la referencia, debe asegurarse de que todos los
cambios se prueban y tienen un efecto favorable.
La tabla siguiente enumera las descripciones y pautas para los
contadores tiles de los objetos Sistema y Procesador.
Objeto:
Contador
Procesador: %
de tiempo de
procesador
Sistema:
Cambios de
contexto/Seg.
Sistema:
Longitud de
cola de
procesador
Descripcin
Pautas
Supervisa el porcentaje
de tiempo que el
procesador emplea en
procesar subprocesos
no inactivos
El valor de este
contador debe ser
inferior al 90 por ciento.
Si el valor es superior,
reduzca la carga de
trabajo, aumente la
eficacia de la carga de
trabajo o la capacidad
del procesador.
veces por segundo que
el procesador realiza
cambios entre
subprocesos
En equipos con
multiprocesador, si el
valor de este contador
llega a 8000 y el valor
del contador
Procesador: % de
tiempo de procesador
est por encima del
90%, considere la
posibilidad de habilitar
la programacin en
modo de intraproceso
de SQL Server.
subprocesos a la
espera de utilizar el
tiempo de procesador
Este contador nunca

debe superar de forma
regular el valor de 2. Si
ste es el caso,
reduzca la carga de
trabajo, incremente la
eficacia de la carga de
trabajo o aumente la
capacidad o el nmero
de procesadores de un
sistema
21
multiprocesador.
Procesador:
%Tiempo
privilegiado
de tiempo que el
procesador emplea del
tiempo privilegiado en
la ejecucin de los
comandos del ncleo
del sistema operativo
Windows 2000, como el
procesamiento de
solicitudes de E/S de
SQL Server
Si se emplea un
porcentaje significativo
de tiempo de
procesador en ejecutar
comandos del ncleo
del sistema y el valor
de los contadores de
disco duro es alto,
considere la posibilidad
de mejorar el
rendimiento del
subsistema de E/S del
disco duro.
Procesador:
%Tiempo del
usuario
de tiempo que el
procesador emplea en
ejecutar procesos del
usuario, como el propio
SQL Server
Puede indicar que otros

procesos o aplicaciones
estn ejecutando e
impidiendo operaciones
de SQL Server.
c) Supervisin de la entrada y salida en el disco duro
SQL Server usa llamadas de entrada y salida (E/S) de Windows para

leer y escribir en el disco, y administra cundo y cmo se realiza la
entrada y salida en el disco pero delega en Windows la realizacin de
las operaciones de E/S subyacentes. El subsistema de E/S incluye el
bus del sistema, tarjetas controladoras de disco, discos, unidades de
22
cinta, unidades de CD-ROM y muchos otros dispositivos de E/S.

Habitualmente, los discos representan el mayor cuello de botella de
un sistema.
Supervisin de la E/S de disco fsico
La supervisin de la entrada y salida en el disco duro le ayudar a
determinar si la escritura y lectura de pginas supera la capacidad
del subsistema de disco duro. Un subsistema de disco duro ocupado
tambin puede indicar un exceso de entrada y salida de archivos de
paginacin provocada por una cantidad de memoria insuficiente. La
tabla siguiente describe los contadores de rendimiento de objeto que
puede usar para supervisar el rendimiento del subsistema del disco
duro.
Objeto: Contador
Descripcin
Pautas
Disco fsico:
%Tiempo de disco
Supervisa el
porcentaje de
tiempo que el disco
duro emplea en
atender las
solicitudes de
lectura y escritura
El valor de este
contador debe ser
inferior al 90 por
ciento, de forma
regular.
Disco fsico:
Promedio de
longitud de cola de
disco
Supervisa el
promedio de
solicitudes de
lectura y escritura
que se ponen en
cola
El valor de este
contador no debe
ser superior al doble
del nmero de
discos del cilindro.
Supervisa la tasa de
operaciones de
lectura
El valor de este
contador debe ser
inferior de forma
regular a la
capacidad del
subsistema de disco
duro.
Supervisa la tasa de
operaciones de
escritura
El valor de este
contador debe ser
inferior de forma
regular a la
capacidad del
subsistema de disco
duro.
Disco fsico:
Lecturas de
disco/seg.
Disco fsico:
Escritura de
disco/seg.
23
Si estos contadores de disco duro indican que est sobrecargando la

capacidad del subsistema de disco duro, considere la posibilidad de:
Ajustar la aplicacin o la base de datos para reducir las

operaciones de E/S en el disco duro (como cobertura y mejora
de ndices, o normalizacin).
Aumentar la capacidad de E/S de disco duro del hardware

mediante el uso de un disco duro ms rpido.
Transferir algunos archivos a un disco duro o servidor

adicional.
Agregar una matriz de discos duros.
Aumentar la cantidad de memoria, lo que podra contribuir a

aliviar la hiperpaginacin en el disco.
d) Supervisin de bloqueos
SQL Server bloquea los recursos mediante distintos modos de
bloqueo que determinan el tipo de acceso que las transacciones
actuales tendrn a los recursos. El bloqueo de filas aumenta la
simultaneidad, pero tambin la sobrecarga, ya que ser necesario
mantener ms bloqueos en caso de que se bloquee un gran nmero
de filas. El bloqueo de tablas es caro en lo que respecta a la
simultaneidad pero produce una sobrecarga menor, ya que reduce el
nmero de bloqueos que mantener.
Interbloqueos
Los interbloqueos pueden producirse cuando dos o ms procesos
esperan simultneamente a que finalicen los bloqueos que ha
establecido cada uno. Ninguno de los procesos liberar el bloqueo
que mantiene hasta que pueda obtener el recurso que permanece
bloqueado por el otro proceso. Cuando se haya identificado un
interbloqueo, para finalizarlo, SQL Server elegir automticamente el
subproceso (la vctima del interbloqueo) que puede romper el
interbloqueo sin que se produzcan tiempos de espera. Puede utilizar
la opcin prioridad para definir el intervalo de tiempo durante el que
SQL Server esperar entre la deteccin de un bloqueo y la
declaracin de que se trata de un interbloqueo.
Bloqueos de cierre
La mayor parte de los problemas de bloqueo se producen debido a
que un nico proceso mantiene bloqueos durante un perodo
prolongado, lo que causa una cadena de procesos bloqueados a la
espera de que otros procesos liberen sus bloqueos. SQL Server no
24
identifica los bloqueos de cierre y los resuelve automticamente, lo

que deja el recurso bloqueado. Debe supervisar la existencia de
bloqueos de cierre para poder eliminarlos manualmente. Mediante la
creacin de una configuracin de tiempo de espera de bloqueo en la
aplicacin podr impedir que se produzcan bloqueos de cierre. Esto
permitir a la aplicacin identificar la existencia de bloqueos de cierre
y deshacer la transaccin, en lugar de esperar indefinidamente o
volver a emitir la instruccin bloqueada.
Administracin de bloqueos
El Monitor de sistema de Windows supervisa la actividad global de
bloqueo del sistema mediante el Contador de objetos de bloqueo de
SQL Server. Para trazar y registrar la actividad de bloqueo de forma
detallada, use el Analizador de SQL. El objeto Bloqueos del
Administrador corporativo de SQL Server proporciona informacin
acerca de los bloqueos de SQL Server en tipos de recursos
individuales. Adems, puede supervisar la actividad de SQL Server
mediante los procedimientos almacenados de sistema sp_who y
sp_lock.
El procedimiento almacenado de sistema sp_who proporciona

informacin de instantneas acerca de los procesos y usuarios
actuales de SQL Server, incluida la instruccin que est
ejecutndose e independientemente de que la instruccin est
bloqueada.
El procedimiento almacenado de sistema sp_lock proporciona

informacin de instantneas acerca de los bloqueos, incluido
el identificador de objeto y de ndice, el tipo de bloqueo y el
tipo de recurso al que se aplica el bloqueo.
Es posible que el uso de sp_lock para presentar informacin de

bloqueo no resulte prctico si se aplican y liberan varios bloqueos
ms rpido de lo que sp_lock puede mostrarlos.
Tras usar una de estas herramientas para identificar el tipo de
bloqueo y el identificador de proceso, o el objeto que lo causa, puede
usar la ventana Actividad actual del SQL Server Management Studio
para administrar el bloqueo.
e) Supervisin de consultas de bajo rendimiento

Para solucionar un problema de rendimiento quizs le tiente la idea
de optimizar nicamente el rendimiento del servidor en el sistema,
por ejemplo, el tamao de la memoria, ubicacin, nmero y tipo de
procesadores. Sin embargo, normalmente no es posible solucionar
los problemas relacionados con consultas de bajo rendimiento de
esta forma. Para ello debe analizar la aplicacin, las consultas y las
actualizaciones que la aplicacin emite a la base de datos, y la forma
25
en que las consultas y las actualizaciones interactan con el

esquema de base de datos.
Identificacin del rendimiento de las consultas
El Analizador de SQL puede supervisar las consultas en funcin del
tiempo transcurrido con el fin de identificar las consultas de bajo
rendimiento del sistema. Para trazar las consultas de peor
rendimiento, cree una traza que capture sucesos relacionados con
las clases de suceso TSQL y Procedimiento almacenado, en
concreto, RPC:Completed (Completo) y SQL: BatchCompleted
(Proceso por lotes completado)
Causas del bajo rendimiento en las consultas
Las consultas y actualizaciones cuya duracin se prolonga de forma
imprevista pueden deberse a:
Comunicaciones de red lentas.
Consultas de Transact-SQL que transfieren una gran cantidad

de datos entre el cliente y el servidor.
Memoria insuficiente para SQL Server.
Ausencia de estadsticas tiles.
Estadsticas obsoletas o ausencia de ndices tiles.
Ausencia de creacin de bandas de datos tiles.
Bloqueos de cierre o interbloqueos causados por

transacciones de larga duracin emitidos por otros usuarios.
Utilizacin de aplicaciones de proceso de transacciones y de

ayuda a la toma de decisiones en el mismo equipo.
26
1. Utilizar el monitor de sistema de Windows para monitorear la

actividad del disco, el uso del procesador y el uso de la memoria
2. Utilizar el monitor de actividades de SQL Server para monitorear las
actividades de los usuarios de las bases de datos
En este captulo se abarcaron temas acerca de la importancia de la

supervisin y los factores que afectan al rendimiento. Tambin como
desarrollar un plan para optimizar el rendimiento de SQL Server.
La supervisin de un sistema puede ser una tarea compleja. Por ello,
puede utilizar diferentes estrategias para plantear la optimizacin del
rendimiento del sistema.
[1]
Delaney, K. Inside Microsoft SQL Server 2005: the storage engine. Microsoft
Press, 2007
[2] STANEK, William. Ms. SQL Server 2005 Manual del Administrador. 1ra Edicin,
McGraw Hill. 2006.
[3] Silberschatz, A., Korth, H.F., Sudarshan, S., Fundamentos de Bases de Datos, 5
edicin, Madrid, 2006.
[4] Matsukawa Maeda, Sergio. Conozca y utilice SQL Server 2005, Lima, 2005
[5]
Date, C. J. An introduction to Database Systems. 8 edition. Pearson Addison

Wesley. 2004.
Bibliografa electrnica:
Administracin de Archivos de bases de datos
27
http://www.mygnet.net/manuales/sqlserver//administracion_de_archivos_de_base_d
e_datos.1114
Tutorial de SQL Server 2005
http://technet.microsoft.com/en-us/library/ms169620(SQL.90).aspx
Tutorial de Administracin de Base de Datos
http://sistemas.itlp.edu.mx/tutoriales/admonbasedat/index.htm
En la siguiente unidad temtica detallaremos aspectos relacionados a la automatizacin de

tareas administrativas
28
ADMINISTRACION DE BASES DE DATOS
UNIDAD ACADMICA N 6
NOMBRE:__________________________________________________________________
APELLIDOS:________________________________________FECHA; ____/_____/______
CIUDAD:_______________________________SEMESTRE:_________________________
1. Mencione algunas razones que sustenten la necesidad de supervisar la base de datos

_______________________________________________________________________
_______________________________________________________________________
_______________________________________________________________________
_______________________________________________________________________
2. Qu herramientas pueden utilizarse para el monitoreo de base de datos?

_______________________________________________________________________
_______________________________________________________________________
_______________________________________________________________________
_______________________________________________________________________
3. Qu aspectos son necesarios monitorear para asegurar un buen rendimiento de la

base de datos?
_______________________________________________________________________
_______________________________________________________________________
_______________________________________________________________________
_______________________________________________________________________
4. Qu debe tomarse en cuenta para obtener un ptimo rendimiento del procesador?

_______________________________________________________________________
_______________________________________________________________________
_______________________________________________________________________
_______________________________________________________________________
5. Qu debe tomarse en cuenta para obtener un ptimo rendimiento de la memoria?
_______________________________________________________________________
_______________________________________________________________________
_______________________________________________________________________
_______________________________________________________________________
29

Unidad 06

Cargado por

Copyright:

Formatos disponibles

Unidad 06

Cargado por

Información del documento

Título original

Derechos de autor

Formatos disponibles

Compartir este documento

Compartir o incrustar documentos

Opciones para compartir

¿Le pareció útil este documento?

¿Este contenido es inapropiado?

Copyright:

Formatos disponibles

Unidad 06

Cargado por

Copyright:

Formatos disponibles

UNIDAD ACADMICA N 06:

MONITOREO DE LA BASE DE DATOS

Al finalizar el estudio de la presente unidad temtica el estudiante:

Describe las razones por las que es importante la supervisin de SQL

Desarrolla una metodologa de supervisin y optimizacin del

Describe las herramientas disponibles para supervisar SQL Server.

Realiza tareas de supervisin y optimizacin habituales mediante los

Ing. Metssy A.Ygnacio Garca

1. POR QUE SUPERVISAR EL RENDIMIENTO DE LA BASE DE DATOS?

2. SUPERVISION Y OPTIMIZACION DEL RENDIMIENTO

a) Estrategias para la optimizacin del rendimiento

Tiempo de respuesta. Mide el intervalo de tiempo necesario

Ing. Metssy A.Ygnacio Garca

Normalmente, se llama tiempo de respuesta al tiempo que

Rendimiento. Mide el nmero total de consultas que el

Deber supervisar ambas reas, ya que los problemas de conflictos

b) Seleccin de un mtodo para optimizar el rendimiento

Optimizar la aplicacin de cliente. Para ello debe:

Ing. Metssy A.Ygnacio Garca

Reducir el nmero de conflictos de bloqueo y evitar los

Optimizar la base de datos. Esto puede mejorar el tiempo de

Optimizar SQL Server. Para optimizar SQL Server puede

Optimizar la configuracin del hardware. El cambio de la

Al planear el ajuste y la optimizacin de la base de datos, debe

c) Desarrollo de una metodologa de optimizacin del

Analizar las expectativas y requisitos del usuario. Considerar si

Ing. Metssy A.Ygnacio Garca

Conocer los datos y cmo se seleccionan, cules son los

Disear una base de datos que utilice apropiadamente la

Desarrollar y probar procedimientos almacenados.

Disear la estrategia de indizacin y optimizar las consultas.

Programar un mantenimiento y una monitorizacin continuada

Plan para mejorar el rendimiento:

Definir los parmetros de funcionamiento del servidor para los

Establecer objetivos para el rendimiento y los tiempos de

Documentar todas las acciones y medir sus efectos.

Probar un entorno de produccin simulado.

Analizar las transacciones de cada base de datos.

Identificar los problemas de rendimiento.

Establecer una referencia de rendimiento.

d) Establecimiento de una referencia de rendimiento

Ing. Metssy A.Ygnacio Garca

Cinco factores clave influyen en el rendimiento de una base de datos:

Carga de trabajo. El volumen de actividad del servidor.

Rendimiento. Nmero total de consultas en un intervalo de

Recursos de sistema. Capacidad fsica del hardware del

Optimizacin. Diseo de la aplicacin y la base de datos.

Conflictos. La competencia por los registros de datos.

El plan de rendimiento y las medidas de referencia deben considerar

Las horas de actividad mxima y mnima de la base de datos.

Los tiempos de respuesta de los comandos de proceso por

Los tiempos de ejecucin de los procesos de restauracin y

Una vez establecida la referencia, podr comparar con ella el

Figura 6.1: Establecimiento de una referencia de rendimiento

Ing. Metssy A.Ygnacio Garca

e) Deteccin de cuellos de botella en el rendimiento

Un componente puede evitar que la carga alcance a otro

La congestin de la red puede impedir que las solicitudes de

Los clientes pueden tener cuellos de botella que les impidan el