SQL Server - Indices

8/9/2019 SQL Server - Indices

1/47

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


3/47


4/47

Tipos de ndices

indices agrupados

indices no agrupados

vemos cmo aceleran el acceso a datos


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


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


7/47

cundo usar ndices agrupados o no?

debemos saber cmo Sql almacena y accede a los

datos


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


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


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


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


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)


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


14/47

tablas pequeas

montn

tablas grandes 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


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


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


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


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


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


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


22/47

solo podemos tener un ndice agrupado por

tabla

debemos elegir bien


23/47

Indices no agrupados

tb estructura de arbol binario

pagina raiz

niveles intermedios

nivel de hoja

igual que el agrupado


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


25/47

si buscamos un intervalo

debes consultar varias veces el indice

pq los datos estn en paginas distintas


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


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


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


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


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


31/47

Crear ndices

es muy sencillo

dos formas

Management Studio

Asistente para optimizacin del motor de base de

datos


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


33/47

pagina opciones

factor relleno 70%

Aceptar

Para hacerlo no agrupado

igual

dificil es saber qu se va a indizar


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


35/47

Sql Profiler puede supervisar lo que hacen

usuarios

puede calcular que columnas se pueden indizar

para realizar esas acciones ms rapido


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


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


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


39/47

Sql Management Studio

menu archivo nuevo nueva consulta con

conexin actual


40/47

USE base de datos

SELECT * FROM tabla que quieras

Ejecuta la consulta


41/47

hacemos otra si quieres para que haya ms

trafico


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


43/47

Abrimos el Asistente para optimizacin de

motor de BD

inicio programas Sql Server

se crea una nueva sesin


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


45/47

vamos a ficha Opciones de optimizacin

mejor no crear particiones

en opciones avanzadasno tocas nada..


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


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

SQL Server - Indices

Documents

Transcript of SQL Server - Indices