8/9/2019 SQL Server - Indices
1/47
Sql Server
Indices
8/9/2019 SQL Server - Indices
2/47
Ejemplo de un libro
cmo se busca la info?
SQL server acta igual
tiene que ir buscando pagina por pagina
al crear una tabla insertar atos
no hay ninguna organizacin
la info se inserta en primero en llegar
para encontrar un registro Sql Server debe leer todos los registros de la tabla
recorrido de la tabla
ralentiza mucho el servidor de BD
8/9/2019 SQL Server - Indices
3/47
8/9/2019 SQL Server - Indices
4/47
Tipos de ndices
indices agrupados
indices no agrupados
vemos cmo aceleran el acceso a datos
8/9/2019 SQL Server - Indices
5/47
Arquitectura de ndices
Sql almacena datos en disco duro pginas de 8 KB
dentro de los ficheros de datos
por defecto no hay organizacin
para imponer un orden crear un ndice
tendremos entonces
pginas de datos
contienen info que los usuarios insertan
paginas de indices almacenan una lista de todos los valores en una
columna indizada (valores claves)
junto un puntero a la ubicacin del registro quecontiene ese valor en la tabla indizada
8/9/2019 SQL Server - Indices
6/47
Ejemplo de ndice
Juan 400620
el primer registro con un valor Juan en el campo
nombre
estn en la extensin 40 (grupo de 8 paginas)
pgina 06
registro 20
8/9/2019 SQL Server - Indices
7/47
cundo usar ndices agrupados o no?
debemos saber cmo Sql almacena y accede a los
datos
8/9/2019 SQL Server - Indices
8/47
NO hay ndice
se denomina montn
ejemplo ciudad con calles cortadas y debemos
consultar el mapa siempre
cuando una extensin se llena , se abre otra
extensiones no estan juntas fisicamente
acceso lento
Sql tiene que ir buscando para reunir todos los datosque estan en varias extensiones o pginas
8/9/2019 SQL Server - Indices
9/47
para encontrar algo Sql
busca en tabla sysindexes
existe siempre
almacena info de indices si no hay indice creado
la columna indid (index identifier) es 0
tiene que buscar en la columna FirstIAM
indica a Sql donde se encuentra exactamente la
pagina deMapa de asignacin de indicex IAM Index Allocation Map
igual que un callejero
sin el IAM, SQL debe recorrer toda la tabla
8/9/2019 SQL Server - Indices
10/47
incluso con IAM
acceso lento
si no hay cortesO
K pero debe consultar varias veces IAM
encontrar la sigueinte extensin de la tabla
8/9/2019 SQL Server - Indices
11/47
Ver recorrido
Crea una nueva consulta
AbresManagement Studio
Bases de datos Tablas tu tabla Indices
boton derecho en el ndice que haya
PK_tal y tal
si lo eliminas
ahora busca algo sin ndices
menu archivo nuevo nueva consulta con conexinactual
8/9/2019 SQL Server - Indices
12/47
USE Base de datos
SELECT * FROM tabla a buscar
en el menu consulta Mostrar plan de ejecucinestimado muestra lo que hace SQL Server para encontrar los
datos
vamos al final de panel de Resutlados y colocas elcursor en Table Scan indica costo de recurrido
tiempo CPU en recorrido (milisegundos)
8/9/2019 SQL Server - Indices
13/47
los recorridos de tabla pueden ralentizar el
sistema, pero no siempre.
rpido si la tabla es pequea (una extensin)
aqu tarda ms el indice
pq tiene que leer primero las paginas de indice y luego las
pginas de tabla
mejor solo recorrer la tabla
8/9/2019 SQL Server - Indices
14/47
tablas pequeas
montn
tablas grandes indices
8/9/2019 SQL Server - Indices
15/47
Indices agrupados
reorganizan fisicamente los datos que los
usuarios insertan en la tabla
organizacin como diccionario
juan antes que maria
estructura de arbol descendente
arbol binario
parte superior de arbol pagina raiz
informa sobre ubicacin del resto de paginas
asi hasta el final
8/9/2019 SQL Server - Indices
16/47
hacemos consulta select
sql consulta tabla sysindexes
tabla con un indice agrupado tiene un valor 1 en columna
indid busca datos
buscajuan
si no est en la pagina tiene que ir a por otra extensin
cada pagina con indice agrupado tiene un puntero a
indice anterior y posterior
No necesitas IAM que usan los montones
acelera acceso a datos
no hay que ir consultando mapas
8/9/2019 SQL Server - Indices
17/47
si pides un intervalo de datos los datos estan organizados fisicamente
cuando sql tiene el primero no necesita volver a consultar paginas raiz
indice agrupado excelente para encontrar intervalos dedatos
o tablas con selectividad baja selectividad numero de valors duplicados en una columna
baja
hay muchos duplicados apellidos
alta
numeros de telefono
8/9/2019 SQL Server - Indices
18/47
Modificar datos en indice agrupado
insert update delete
sql debe almacenar datos
en un monton
los datos se insertan al final de la tabla final de la pagina de datos
si no caben, busca donde puede
en indice agrupado
no puede
pq tienen que ir organizados fisicamente
se deja un poco de espacio al final de cada pagina de indiceagrupado
factor de relleno
para que pueda insertar datos nuevos
8/9/2019 SQL Server - Indices
19/47
si tienes que insertar un nuevo cliente
entre otros
busca la pagina de datos que le corresponda
y con el factor de relleno libre
lo coloca ah
8/9/2019 SQL Server - Indices
20/47
factor de relleno se especifica al crear el indiceagrupado
se peude cambiar cuando quieras
factor alto
deja menos espacio
factor 70
70 % relleno de datos
libre 30%
factor bajo
deja mas espacio
8/9/2019 SQL Server - Indices
21/47
si pones factor 100%
no hay sitio libre
toma la mitad de la pagina y lo pasa a otra vacia
ya hay sitio
divide una pagina
tb actualiza encabezados de pagina
cambia los punteros de apuntamiento
8/9/2019 SQL Server - Indices
22/47
solo podemos tener un ndice agrupado por
tabla
debemos elegir bien
8/9/2019 SQL Server - Indices
23/47
Indices no agrupados
tb estructura de arbol binario
pagina raiz
niveles intermedios
nivel de hoja
igual que el agrupado
8/9/2019 SQL Server - Indices
24/47
Diferencias
nivel de hoja no tiene datos, sino punteros a
los datos almacenados en pagina de datos
no reorganiza fisicamente los datos
es un indice alfabetico de un libro, no un
diccionario
8/9/2019 SQL Server - Indices
25/47
si buscamos un intervalo
debes consultar varias veces el indice
pq los datos estn en paginas distintas
8/9/2019 SQL Server - Indices
26/47
Acceso a datos con un ndice no
agrupado
busca datos
sql consulta la tabla sysindexes
busca un registro que tenga un nombre de la tabla en la
columna indid
lo encuentra
mira la columna root para ver la pgina en donde est
busca ya los datos
pero tal vezjuan no est al principio, tal vez al final
tiene que buscar en la hoja (como un libro)
es un puntero
8/9/2019 SQL Server - Indices
27/47
si buscas intervalo
debes consultar otra vez el indice
debemos unsar indices no agrupados en columnas
en donde rara vez se buscan intervalos de datos o
columnas con selectividad alta
8/9/2019 SQL Server - Indices
28/47
Modificar datos con no agrupados
no hay mucho trabajo
coloca los datos en cualquier sitio donde
tenga espacio problema
si hay agrupado y no al mismo tiempo
8/9/2019 SQL Server - Indices
29/47
Agrupados
solo 1 por tabla
reordenan fisicamente los datos de la tabla
se usan en columnas en donde se buscanintervalos de datos con frecuencia
se usan en columnas con selectividad baja
8/9/2019 SQL Server - Indices
30/47
No agrupados
hasta 249 por tabla
crea una lista separada de valores clave con
punteros a la ubicacin de los datos en laspaginas de datos
se usan en columnas en donde se buscan
valores individuales
se usan en columnas con selectividad alta
8/9/2019 SQL Server - Indices
31/47
Crear ndices
es muy sencillo
dos formas
Management Studio
Asistente para optimizacin del motor de base de
datos
8/9/2019 SQL Server - Indices
32/47
Management Studio
Bases de datos tu base Tablas Tabla elegida Indice - boton derecho nuevo indice
PK_nombretabla_nombrecolumna_otracolumna
tipo de indice agrupado
activo casilla verificacin unico
Agregar
activas las columnas clientesid y fecha (seleccionas lascolumnas que se agregan al indice)
Aceptar
8/9/2019 SQL Server - Indices
33/47
pagina opciones
factor relleno 70%
Aceptar
Para hacerlo no agrupado
igual
dificil es saber qu se va a indizar
8/9/2019 SQL Server - Indices
34/47
Crear indices con Asistente para
optimizacin del motor BD
herramienta Sql Profiler
funcion es hacer seguimineto de Sql Server
ventaja en indizacin
Sql Profiler supervisa todo lo que sucede en servidor
MSSQLServer
insert
update
delete
select
8/9/2019 SQL Server - Indices
35/47
Sql Profiler puede supervisar lo que hacen
usuarios
puede calcular que columnas se pueden indizar
para realizar esas acciones ms rapido
8/9/2019 SQL Server - Indices
36/47
Sql Profiler
guarda todos los eventos supervisados en un
fichero de disco
carga de trabajo
sin este fichero, el Asistente de optimizacin NO puede
funcionar
8/9/2019 SQL Server - Indices
37/47
Crear carga de trabajo
ejecutar una traza
proceso de supervisin
permite capturar trafico del usuario a lo largo del
periodo activo de la jornada
8/9/2019 SQL Server - Indices
38/47
Inicio Programas Sql Server 2005
herramientas de rendimiento
menu archivo nueva traza nos conectamos a sql server
Propiedades de traza
nombre : traza de indice
Activas guardar fichero
Guardar comoindex.trc
ejecutar para iniciar la traza
8/9/2019 SQL Server - Indices
39/47
Sql Management Studio
menu archivo nuevo nueva consulta con
conexin actual
8/9/2019 SQL Server - Indices
40/47
USE base de datos
SELECT * FROM tabla que quieras
Ejecuta la consulta
8/9/2019 SQL Server - Indices
41/47
hacemos otra si quieres para que haya ms
trafico
8/9/2019 SQL Server - Indices
42/47
vamos a Sql Profiler y detener la traza
clic en boton rojo sobre ventana de traza
se ven las consultas en la traza?
tal vez tb hay info de los servicios del sistema
tal vez tb algo de Agente Sql Server
8/9/2019 SQL Server - Indices
43/47
Abrimos el Asistente para optimizacin de
motor de BD
inicio programas Sql Server
se crea una nueva sesin
8/9/2019 SQL Server - Indices
44/47
Panel derecho
aparece el nombre de carga de trabajo
ponemos un nombre en nombre de sesin
base de datos Session
en la seccin carga de trabajo
pones el archivo index.trc
Selecciona la base de datos para analisis
en la cuadricula de bases de datos y tabla
seleccionas la base de datos nuestra
en tablas seleccionadas clic en flecha y vemos las tablasque hemos seleccionado
8/9/2019 SQL Server - Indices
45/47
vamos a ficha Opciones de optimizacin
mejor no crear particiones
en opciones avanzadasno tocas nada..
8/9/2019 SQL Server - Indices
46/47
En ficha general
iniciar analisis
completo
lista de recomendaciones
si la BD est optimizada no nos da una lista valida de
optimizaciones
Podemos cambiar a la ficha informes
en seleccionar informe informes de optimizacion y estadisticas de BD
8/9/2019 SQL Server - Indices
47/47
es ms lento pero muy interesante
lo ms importante es saber dnde crear un indice
y que tipo de indice a crear
podemos comprobarlo varias veces a ver
tb una vez crado los indices
debemos mantenerlos con regularidad paraasegurarnos que funcionan adecuadamente
Top Related