Tipos de Tablas

Las tablas dentro del motor de base de datos SQL Server pueden ser de dos tipos, “heap” o “cluster”. Ambos tipos de tablas almacenan la información de manera diferente, por lo que es importante conocer cada a detalle cada uno de estos tipos de tablas para poder tomar mejores decisiones al momento de diseñar una base de datos.

A las tablas de tipo “Heap”, se les denomina así debido a que los registros se almacenan en forma de montón, es decir sin ningún orden en particular. Los registros se ingresaran en el primer espacio disponible dentro de las páginas pertenecientes a la tabla. Dicho esto se puede tener como conclusión dos cosas, la primera es que este tipo de tablas son muy rápidas para ingresos de información debido a que el motor de base de datos no hará otra cosa más que buscar el primer espacio libre dentro de las páginas de la tabla e ingresará el nuevo registro; la segunda es que son muy malas para las lecturas intensivas, quisiera resaltar la palabra intensiva porque si la tabla tiene pocos registros, como una tabla maestra, entonces este no es realmente un problema, pero si la tabla es bastante transaccional y tiene un numero de registros significativo, entonces si hay un problema porque las lecturas en disco serán bastante aleatorias cuando se busque un registro debido a que la información como ya mencionamos no se guarda en ningún orden en particular.

Para probar los puntos expuestos en el párrafo anterior, se planteara el siguiente ejemplo: una tabla de tipo heap que solo pueda almacenar dos registros por pagina de datos, esto se lograra debido a que esta tabla solo contara con dos columnas, una de tipo de dato “int” que como ya se sabe ocupa cuatro bytes y otra columna de tipo char(3000) la cual ocupara tres mil bytes, entonces si multiplicamos el espacio que ocupara cada registro por dos, nos dará 6016 bytes, y como hemos visto una página solo puede almacenar hasta 8 KB de información, es decir en una página no podrán entrar más de dos registros de esta tabla.

USE tempdb
GO
-- Eliminamos la tabla si ya existe
IF (OBJECT_ID('TablaHeap') IS NOT NULL)
DROP TABLE TablaHeap
-- Creamos la tabla heap
CREATE TABLE TablaHeap
(
Codigo INT
,Valor CHAR(3000)
)
GO
-- Ingresamos 6 registros, es decir 3 paginas
INSERT INTO TablaHeap
VALUES (1,REPLICATE('A',3000)),
(
2,REPLICATE('A',3000)),
(
3,REPLICATE('A',3000)),
(
4,REPLICATE('A',3000)),
(
5,REPLICATE('A',3000)),
(
6,REPLICATE('A',3000))

GO
— El siguiente comando permite visualizar las paginas asignadas a la tabla
DBCC IND (‘tempdb’, ‘TablaHeap’, 1);
GO
— Este es el resultado, ha sido recortado por temas de espacio pero estas
— son las columnas q nos interesan. Basicamente hay 4 paginas la primera
— es de tipo “IAM Page” (PageType = 10), permite rastrear el espacio
— disponible en las demas paginas de datos (PageType = 1). Entonces como
— podemos ver tenemos 3 paginas de datos como lo predijimos
/*
PageFID PagePID     IAMPID      ObjectID    IndexID     PageType
——- ———– ———– ———– ———– ——–
1       121         NULL        261575970   0           10
1       120         121         261575970   0           1
1       126         121         261575970   0           1
1       127         121         261575970   0           1
*/
— Elimino un registro
DELETE TablaHeap
WHERE Codigo = 3
GO
— Inserto un nuevo registro, al eliminar el anterior, este deberia ingresar
— en la misma pagina del registro anterior sin necesidad de crear otra nueva
— pagina.
INSERT INTO TablaHeap
VALUES (9,REPLICATE(‘A’,3000))
GO
— Ahora comprobaremos que efectivamente siguen solo las 3 paginas de datos
DBCC IND (‘tempdb’, ‘TablaHeap’, 1);
GO
/*
PageFID PagePID     IAMPID      ObjectID    IndexID     PageType
——- ———– ———– ———– ———– ——–
1       121         NULL        261575970   0           10
1       120         121         261575970   0           1
1       126         121         261575970   0           1
1       127         121         261575970   0           1
*/
— Es mas para terminar de comprobarlo haremos un select a los datos de la
— tabla y veremos que el registro 9 aparece en el tercer lugar de la consulta
SELECT Codigo, Valor FROM TablaHeap
GO
/*
Codigo      Valor
———– ——————–
1           AAAAAAAAAAAAAAAAA…
2           AAAAAAAAAAAAAAAAA…
9           AAAAAAAAAAAAAAAAA…
4           AAAAAAAAAAAAAAAAA…
5           AAAAAAAAAAAAAAAAA…
6           AAAAAAAAAAAAAAAAA…
*/

Ahora veremos un poco de lo que son las tablas de tipo “cluster”, este tipo de tablas tiene la particularidad que esta compuestas de un índice de tipo “cluster”, el cual en realidad es el que contiene toda la data de la tabla, es decir no hay una estructura aparte que contenga toda esta información, como lo es una tabla “heap”. Entonces al estar toda la información contenida en un índice, esta siempre estará ordenada por la llave del índice. Estas tablas son muy optimas para las consultas, especialmente si se hacen por la llave del índice, debido a que la información ya se encuentra ordenada, la búsqueda será mucho más rápida y eficiente. En cuanto al ingreso de datos, estas tablas pueden tener algunos problemas, dependiendo de cómo este constituida la clave del índice “cluster” y el tamaño del registro de la tabla, estos dos factores pueden causar problemas a largo plazo debido a que puede estar altamente fragmentada, o puede tener muchos “forwarding pointers”, los cuales finalmente causan que la tabla sea mucho más grande de lo que realmente es, y también causa problemas de fragmentación, los cuales a la larga afectan a las consultas.

A continuación mostrare un ejemplo del uso de esta tabla “cluster”, para esto se creara una tabla, a la que posteriormente se le creara un índice “cluster”. Luego se ingresaran 6 registros, los cuales ocuparan tres páginas de datos debido a que según el tamaño del registro definido para esta tabla solo permitirá hasta 2 registros por página. Seguidamente se procederá a borrar uno de esos registros y se ingresara otro. Se podrá observar que el comportamiento para el almacenamiento de esta tabla difiere con la tabla “heap”, debido a que como el código del nuevo registro será mayor al de los demás, SQL Server asignara una nueva página para este nuevo registro, esta página se encontrara al final de índice.

USE tempdb
GO
-- Eliminamos la tabla si ya existe
IF (OBJECT_ID('TablaClustered') IS NOT NULL)
DROP TABLE TablaClustered
-- Creamos la tabla
CREATE TABLE TablaClustered
(
Codigo INT
,Valor CHAR(3000)
)
GO
-- Ahora la convierto en cluster
CREATE CLUSTERED INDEX CLIX_TablaClustered ON TablaClustered (Codigo)
GO
-- Ingresamos 6 registros, es decir 2 paginas
INSERT INTO TablaClustered
VALUES (1,REPLICATE('A',3000)),
(
2,REPLICATE('A',3000)),
(
3,REPLICATE('A',3000)),
(
4,REPLICATE('A',3000)),
(
5,REPLICATE('A',3000)),
(
6,REPLICATE('A',3000))

GO
-- El siguiente comando permite visualizar las paginas asignadas a la tabla
DBCC IND ('tempdb', 'TablaClustered', 1);
GO
-- Este es el resultado, ha sido recortado por temas de espacio pero estas
-- son las columnas q nos interesan. Basicamente hay 5 paginas la primera
-- es de tipo "IAM Page" (PageType = 10), permite rastrear el espacio
-- disponible en las demas paginas de datos. Adicionalmente tenemos 1 pagina
-- de indice (PageType = 2). Finalmente podemos ver las 3 paginas de datos
-- (PageType = 1).
/*
PageFID PagePID     IAMPID      ObjectID    IndexID     PageType
------- ----------- ----------- ----------- ----------- --------
1       151         NULL        309576141   1           10
1       148         151         309576141   1           1
1       173         151         309576141   1           2
1       174         151         309576141   1           1
1       175         151         309576141   1           1
*/
-- Elimino un registro
DELETE TablaClustered
WHERE Codigo = 3
GO
-- Inserto un nuevo registro, este deberia ingresar al final de la tabla debido
-- al orden que tiene la tabla sobre la columna "Codigo", la cual definimos como
-- llave de nuestro indice cluster
INSERT INTO TablaClustered
VALUES (9,REPLICATE('A',3000))
GO
-- Ahora comprobaremos que efectivamente se ha agregado una nueva pagina de datos
DBCC IND ('tempdb', 'TablaClustered', 1);
GO
/*
PageFID PagePID     IAMPID      ObjectID    IndexID     PageType
------- ----------- ----------- ----------- ----------- --------
1       151         NULL        309576141   1           10
1       148         151         309576141   1           1
1       173         151         309576141   1           2
1       174         151         309576141   1           1
1       175         151         309576141   1           1
1       90          151         309576141   1           1
*/
-- Finalmente para terminar de comprobarlo haremos un select a los datos de la
-- tabla y veremos que los codigos aparecen en orden ascendente en la consulta
SELECT Codigo, Valor FROM TablaClustered
GO
/*
Codigo      Valor
----------- --------------------
1           AAAAAAAAAAAAAAAAA...
2           AAAAAAAAAAAAAAAAA...
4           AAAAAAAAAAAAAAAAA...
5           AAAAAAAAAAAAAAAAA...
6           AAAAAAAAAAAAAAAAA...
9           AAAAAAAAAAAAAAAAA...
*/

Finalmente podemos sacar como conclusión que ambos tipos de tablas tienen sus ventajas y desventajas, ya es tarea de nosotros poder decidir donde debemos aplicar cada tipo, según sea el caso. Una cosa que es importante mencionar es que ambos tipos de tabla pueden tener índices “no cluster” los cuales ayudan a agilizar la búsqueda de los datos. Bueno eso fue todo lo que respecta a este tema, mas adelante hare otro post exclusivamente sobre índices y su funcionamiento.

Anuncios

Solución alternativa a SQL Profiler

Una de las herramientas más importantes para el monitoreo de base de datos SQL Server es el SQL Profiler, el cual permite visualizar en tiempo real la actividad en la base de datos. Esta herramienta es ampliamente utilizada por los desarrolladores de aplicaciones y los administradores de base de datos. Pero alguna vez se han preguntado cuál es el impacto en performance que tiene esta herramienta sobre el servidor de base de datos. Bueno a continuación intentaremos graficar un poco el problema y ver cuál es la alternativa al SQL Profiler sin impactar tan significativamente al servidor de base de datos.

El SQL Profiler es una herramienta de cliente para servidores de base de datos Microsoft SQL Server. Esta herramienta permite conectarse a un servidor de base de datos y capturar en tiempo real las sentencias SQL ejecutadas a la base de datos. Esta herramienta se puede configurar para que guarde esta información de tres modos diferentes:

  • El primero no tiene ningún respaldo en disco, solo se visualiza la información en la consola de la herramienta. A esta se le denomina “Normal Trace”, y está configurado por defecto.
  • El segundo permite grabar la información en el disco duro, en un archivo de traza, el cual puede ser leído posteriormente por el mismo SQL Profiler o por la función “ fn_trace_gettable”.
  • El tercero implica grabación directa en una “Trace Table” la cual es una tabla alojada en una base de datos SQL Server, no necesariamente la misma desde donde se hace la traza.

Además el SQL Profiler permite escoger los eventos que se desea monitorear, actualmente se cuenta con una gran cantidad de eventos que permiten monitorear varias cosas como: deadlocks, auditorias, logins fallidos, ejecución de procedimientos almacenados, ejecución de sentencias SQL adhoc, etc. Otra de las ventajas que nos da esta herramienta es la selección de las columnas que deseamos monitorear, cada evento puede guardar información en diferentes columnas tales como, “TextData”, “DatabaseId”, “ObjectName”,”Reads”, “Writes”, “CPU”, etc. Es decir con esta herramienta podemos filtrar nuestro monitoreo tanto de forma vertical como horizontal. Finalmente para completar la trilogía de opciones de filtro que nos ofrece el SQL Profiler, cabe mencionar que podemos filtrar por valores específicos de alguna de las columnas seleccionadas, es decir, podemos indicar que solo deseamos obtener la información de la base de datos “X”, y además que la duración sea mayor a diez segundos, etc. Juntando todos estos filtros podemos tener de manera más personalizada la información que deseamos recabar de la base de datos.

Pero todo esto tiene un costo de performance en el servidor de base de datos. Dependiendo de varios factores como los filtros escogidos, el tipo de traza escogido para guardar la información, y desde donde se esté ejecutando el cliente SQL Profiler, se pueden presentar diferentes problemas de performance. Mientras más información se recolecte en un rango de tiempo corto, mas sufrirá el servidor, debido a que cada operación generada en la base de datos deberá ser enviada al SQL Profiler. Si la traza se guarda sobre el modo “Normal Trace” entonces la información se guardara en la memoria RAM de la maquina donde se está ejecutando el cliente, lo cual evitara que otras aplicaciones puedan usar el espacio en este recurso tan escaso, además de que si en algún momento la aplicación (SQL Profiler) se colgara o se cerrara intempestivamente se perdería toda la información capturada. Si la traza se guarda en un archivo de disco, se tendrá una penalidad en las operaciones de entrada y salida del disco duro donde se guardara el archivo, ya que la escritura hacia este archivo será constante; se recomienda guardar este archivo en discos duros no muy activos y que no contengan otros archivos de base de datos en ellos para no interferir con la velocidad de escritura o lectura de estos otros archivos de base de datos. Ahora si la traza se guarda directamente en una tabla de base de datos, el servidor de base de datos receptor de la traza deberá trabajar muy fuerte para poder recibir toda la información enviada por el SQL Profiler, dependiendo de la cantidad de información recolectada esto puede significar un problema muy grande.

Luego si el SQL Profiler se ejecuta en una maquina de cliente, lo que va a sufrir es el trafico de red debido a que la información es capturada en tiempo real esta debe ser enviada desde el servidor hacia la maquina cliente, y mientras más información se capture, más se saturara el trafico de red. Finalmente uno de los problemas más grandes con la ejecución del SQL Profiler, desde mi punto de vista, es que este al ser una herramienta grafica debe ser ejecutada en la sesión de algún usuario de Windows, y si se requiere monitorear la base de datos por un periodo de tiempo largo, la sesión del usuario de Windows debe mantenerse activa durante todo ese tiempo, y si hubiera algún problema de red o de energía con la PC, la traza se perdería. Normalmente en un ambiente productivo no se tiene acceso a los servidores de manera física, entonces se podría pensar en usar un escritorio remoto hasta el servidor de base de datos y ahí lanzare el SQL Profiler, de esta manera no causare problemas de tráfico de red, porque lo ejecuto en la maquina local, y tampoco necesito tener una maquina cliente prendida todo el día dedicada a capturar la información. Pero el problema es que las sesiones de escritorio remoto también usan bastante memoria y si se hace esto en un ambiente de producción esta memoria no podrá ser usada por el SQL Server para responder a las llamadas que se le hacen, adicionalmente si es que en el peor de los casos, alguien por alguna razón, eliminara todas las sesiones de escritorio remoto, la traza se cortaría.

Ahora que ya se tiene establecido los problemas de performance que puede causar el SQL Profiler en un sistema ocupado y de producción, veremos que la alternativa a esto sería el “Server Side Trace”, el cual cumple la misma función que el SQL Profiler pero corre en el lado del servidor y no tiene ninguna interface grafica. El manejo de este proceso se hace a través de procedimientos almacenados. Utilizando esto eliminamos los problemas relacionados con la interfase grafica y con el trafico de red hacia maquinas cliente, además de que ya no se necesitarían sesiones de usuario de Windows activas para correr el proceso.

Para configurar el “Server Side Trace”, se puede utilizar el mismo SQL Profiler para poder generar el script necesario con todos los filtros y configuraciones escogidas de manera grafica. Para poder hacer esto se debe de abrir el SQL Profiler, y conectarse a un servidor SQL Server.

Seguidamente se debe escoger los filtros tanto de eventos como de columnas, para efectos de simplificar el ejemplo usare una plantilla propio de SQL Server, Finalmente se ejecuta el trace haciendo clic sobre el botón “Run”.

Ahora se para la traza con el botón “Stop” y se procede a obtener el script de definición de la traza a través de la siguiente secuencia:

Como se puede observar, se puede obtener la definición tanto para SQL Server 2000 como para 2005 en adelante. Una vez que se tiene esta definición, se obtendrá un script parecido al siguiente:


/****************************************************/
/* Created by: SQL Server 2008 R2 Profiler          */
/* Date: 07/19/2011  12:10:43 AM         */
/****************************************************/

— Create a Queue
DECLARE @rc INT
DECLARE
@TraceID INT
DECLARE
@maxfilesize bigint
SET @maxfilesize = 5

— Please replace the text InsertFileNameHere, with an appropriate
— filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
— will be appended to the filename automatically. If you are writing from
— remote server to local drive, please use UNC path and make sure server has
— write access to your network share

EXEC @rc = sp_trace_create @TraceID output, 0, N’InsertFileNameHere’, @maxfilesize, NULL
IF (@rc != 0) GOTO error

— Client side File and Table cannot be scripted

— Set the events
DECLARE @on bit
SET @on = 1
EXEC sp_trace_setevent @TraceID, 10, 1, @on
EXEC sp_trace_setevent @TraceID, 10, 3, @on
EXEC sp_trace_setevent @TraceID, 10, 11, @on
EXEC sp_trace_setevent @TraceID, 10, 35, @on
EXEC sp_trace_setevent @TraceID, 10, 12, @on
EXEC sp_trace_setevent @TraceID, 10, 13, @on
EXEC sp_trace_setevent @TraceID, 45, 1, @on
EXEC sp_trace_setevent @TraceID, 45, 3, @on
EXEC sp_trace_setevent @TraceID, 45, 11, @on
EXEC sp_trace_setevent @TraceID, 45, 35, @on
EXEC sp_trace_setevent @TraceID, 45, 12, @on
EXEC sp_trace_setevent @TraceID, 45, 28, @on
EXEC sp_trace_setevent @TraceID, 45, 13, @on
EXEC sp_trace_setevent @TraceID, 12, 1, @on
EXEC sp_trace_setevent @TraceID, 12, 3, @on
EXEC sp_trace_setevent @TraceID, 12, 11, @on
EXEC sp_trace_setevent @TraceID, 12, 35, @on
EXEC sp_trace_setevent @TraceID, 12, 12, @on
EXEC sp_trace_setevent @TraceID, 12, 13, @on

— Set the Filters
DECLARE @intfilter INT
DECLARE
@bigintfilter bigint

— Set the trace status to start
EXEC sp_trace_setstatus @TraceID, 1

— display trace id for future references
SELECT TraceID=@TraceID
GOTO finish

error:
SELECT ErrorCode=@rc

finish:
GO

Luego se deberá modificar algunos valores como el nombre del archivo en el que se guardara la traza. Seguidamente se puede proceder a ejecutar este script sobre un servidor y automáticamente se creara el “Server Side Trace” con las opciones configuradas. Se puede consultar los “Server Side Trace” que se encuentran corriendo en el servidor con la siguiente sentencia:

SELECT * FROM ::fn_trace_getinfo(NULL)

/*
traceid     property    value
———– ———– ————————————
1           1           2
1           2           C:\Program Files\…\Log\log_277.trc
1           3           20
1           4           NULL
1           5           1
*/

Ahora para poder pausar o parar por completo un trace se pueden usar los siguientes procedimientos almacenados, donde se necesita pasar como parámetro del “TraceId”, el cual se obtiene de la sentencia anterior.

EXEC sp_trace_setstatus @traceid = 2 , @status = 0 --Pausar
EXEC sp_trace_setstatus @traceid = 2 , @status = 2 --Detener

En conclusión, como se, puede observar los “Server Side Trace”, ofrecen muchas ventajas sobre el SQL Profiler, y se recomienda su uso cuando se quiere ejecutar una traza en un sistema de producción, para no afectar tanto la performance del mismo. Si lo que se desea es ejecutar una traza corta o en un servidor de base de datos no muy ocupado, se puede seguir utilizando el SQL Profiler, no valdría la pena darse el trabajo de crear un “Server Side Trace”. Se deben sopesar el uso de ambos para poder ver cual es mejor en qué situación en especifico.

Finalmente quisiera dejar un par de links con estudios realizados sobre una comparación más cuantitativa entre ambas alternativas para terminar de demostrar que los “Server Side Trace” son mejores en cuanto a performance de base de datos.

http://www.sqlmag.com/article/performance/sql-server-profiler-or-server-side-trace-

http://sqlblog.com/blogs/linchi_shea/archive/2007/08/01/trace-profiler-test.aspx

Subscripciones “Data-Driven” en SSRS

SQL Server Reporting Services es un producto que trae muchas cosas útiles para las organizaciones. Una de las cosas más atrayentes de este producto desde mi punto de vista eran las subscripciones que uno podía hacer a los informes que se alojaban en él. Estas subscripciones permitían que un reporte en un momento dado sea distribuido a varias personas, a través de un correo electrónico o un archivo físico en una ruta compartida dentro de la organización. Pero estas subscripciones no permitían mucha flexibilidad en lo que corresponde a los parámetros que se le pueden enviar a los informes o a los contenidos que le correspondía ver a cada una de las personas a las que se les distribuía los informes.

Entonces para solucionar este problema se procedió a realizar la respectiva investigación del tema y me encontré con un concepto nuevo que son las subscripciones “Data-Driven”. Este tipo de subscripciones si permiten lograr un control más granular sobre los parámetros que se le enviara al reporte para cada persona a la que se le distribuirá, consiguiendo así también el aislamiento de la información por persona o grupo de personas. En este articulo se procederá a explicar la configuración de este tipo de subscripciones dentro del SQL Server Reporting Services 2005.

Configuraciones Previas

En mi caso en particular, yo quería distribuir los reportes a través de un correo electrónico, para poder hacer esto tuve que configurar la cuenta de correo electrónico que usara el SQL Server Reporting Services. Para realizar esta tarea se debe de ingresar a la opción de “Reporting Services Configuration”, dentro de esta pantalla debemos ir hasta la opción de “Email Settings”

Dentro de esta opción debemos colocar la cuenta de correo con la cual el Reporting Services enviara los correos, además del servidor de correos que se usara. Una vez que se ingresaron estos datos se debe dar clic en el botón “Apply”.

Funcionamiento de una Subscripción “Data-Driven”

Para poder realizar una subscripción “Data-Driven” se debe tener muy claro su funcionamiento. Para entender cómo funciona se debe partir por el principio que indica que una subscripción de este tipo permite que para un mismo reporte se puedan enviar varias combinaciones de parámetros, ¿Cómo lograr esto?, con un set de datos obviamente, entonces la parte central de una subscripción “Data-Driven” es un “Subcription query” que es una sentencia SQL la cual retornara la lista de receptores de los reportes, además otros campos que pueden ser enlazados a parámetros del reporte u otras configuraciones del correo electrónico que se enviara.

Creando una Subscripción “Data-Driven”

Para crear una subcripcion “Data-Driven”, se debe acceder al servidor de Reporting Services a través del Web Browser.

Seguidamente se debe acceder al reporte al cual se desea realizarle la subscripción, y se debe de hacer clic sobre la pestaña de “Subscriptions”, donde aparecerá la siguiente pantalla, acá se debe hacer clic sobre el botón “New Data-Driven Subscription”.

Aparecerá el asistente de creación de la subscripción “Data-Driven”, acá pedirá datos básicos como el nombre de la subscripción así como el método de distribución. Además solicitara que se indique que data source se usara para obtener los datos de los destinatarios, para este caso usaremos la opción 2 que indica que el data source que se usara será solo para esta subscripción.

Una vez que se llenaron los datos solicitados se debe dar clic en el botón “Next >” el cual nos llevara a la siguiente pantalla. En esta nueva pantalla se deben llenar los datos del data source de lo que será el “Subscription Query”.

Se debe tener en cuenta que si se usa un usuario de dominio para conectarse al data source se debe especificar el dominio antes del nombre del usuario tal y como lo muestra la pantalla de arriba. Cuando termine de llenar los datos del data source debe presionar el botón “Next >”

En esta pantalla es donde se debe ingresar el “Subscription Query” que debe obtener los datos de los receptores del reporte así como otros datos adicionales que pueden ser usados para algunas configuraciones adicionales del correo electrónico o para los parámetros del mismo reporte. Luego debe hacerse clic sobre el botón “Validate” para probar que la ejecución del query este correcta tomando las credenciales ingresadas en la pantalla anterior. Si todo esta ok aparecerá el siguiente mensaje en la parte baja de la pantalla.

Una vez validado el “Subscription Query” se debe hacer clic sobre el botón “Next >”. Aparecerá la siguiente pantalla de configuración del correo electrónico.

En esta pantalla como se puede observar se podía especificar valores estáticos o provenientes del “Subscription Query”, una vez que se llenaron todos los valores a usar se debe hacer clic sobre el botón “Next >”, aparecerá la siguiente pantalla:

En la pantalla de arriba se deben especificar los valores para los parámetros del informe. Los parámetros pueden tomar valores estáticos o provenientes del “Subscription Query”. Cuando termine de colocarse estos valores se debe hacer clic sobre el botón “Next >”; aparecerá la siguiente pantalla:

En esta pantalla pide los datos de cuándo será procesada la subscripción. En este caso se usara un “Schedule” para que los correos se envíen periódicamente. Ahora hacemos clic sobre el botón “Next >”.

En esta pantalla se pueden escoger distintos tipos de “Schedule”, además del detalle de cada tipo de “Schedule”. Además se debe especificar a partid de cuando empezara a ejecutarse hasta cuándo será su último día de ejecución. Finalmente se debe proceder a dar clic sobre el botón “Finish”, el cual terminara de crear nuestra subscripción “Data-Driven”.

Arquitectura de Bases de Datos SQL Server

La arquitectura interna de las bases de datos en SQL Server están compuestas por 2 tipos de estructura, la estructura lógica y la estructura física. Es muy importante conocer cómo es que estas estructuras están compuestas y cuál es la relación que tienen los objetos de base de datos con cada una de estas estructuras.

Estructura Lógica:

Desde el punto de vista lógico, la base de datos debe tener al menos 1 “FileGroup” el cual contiene a toda la metadata de la misma base de datos, es decir tablas y vistas de sistema, a este “FileGroup” inicial se le conoce como “Primario” y está presente en todas las bases de datos. Todos los objetos de usuario que contengan data, ya sean tablas o índices, deben estar ligados a un “FileGroup”, esto se puede definir al momento de ejecutar la sentencia DDL de creación del objeto, si no se indica a que “FileGroup” estará ligado ese objeto, este pertenecerá al “FileGroup” por defecto definido en la base de datos. La base de datos solo puede tener definido 1 solo default “FileGroup”.

Las bases de datos pueden tener hasta 32767 “FileGroups” definidos, según los límites establecidos para la última versión de SQL Server, la cual es SQL Server 2008 R2. Uno de los propósitos de los “FileGroups” es poder distribuir la data a través de varios discos duros físicos, de esta manera se puede obtener mayor rendimiento en las operaciones de I/O debido a que más de un disco trabajara al mismo tiempo. Otro de los propósitos es poder esconder la ubicación física real de la información a los programadores, ya que para ellos la tabla “X” pertenece al “FileGroup” “A”, pero no saben en que data files físicamente se encuentra la información de la tabla “X”.

Los “FileGroups” pueden contener 1 o más “Datafiles”, y cada uno de estos datafiles se pude encontrar en un discos diferentes, lo cual también agilizara las consultas y los ingresos de información a las tablas que se encuentren asignadas a este “FileGroup”, debido a que SQL Server distribuirá la información uniformemente a través de todos los “DataFiles” del “FileGroup”.

Estructura Física:

Desde el punto de vista físico, como ya hemos visto, tenemos los “DataFiles” que los en realidad los archivos de datos, es decir donde se guarda toda la información de la base de datos. Un “DataFile” solo puede pertenecer a 1 “FileGroup”.

Internamente los “DataFiles” están divididos en “Extends” y estos a su vez en “Pages”. Las “Pages” son la unidad minima de almacenamiento dentro de la base de datos. Un “Page” tiene 8 Kb de tamaño en espacio de disco. Un “Extend” tiene 8 “Pages” contiguas que lo conforman, es decir, un “Extend” tiene como tamaño 64 Kb de espacio en disco.

En un “Page” solo puede haber información de 1 sola tabla, es decir el espacio de un “Page” no es compartido entre tablas o índices. En el caso de los “Extends”, estos pueden ser de dos tipos:

  • “Mixed”: Los cuales son compartidos hasta por 8 objetos, uno por cada “Page”.
  • “Uniform”: Los cuales solo pertenecen a un solo objeto, es decir que todos los “Pages” pertenecen a un solo objeto.

Normalmente cuando se crea una nueva tabla esta es asignada a un “Extend” de tipo “Mixed”, hasta alcanzar la utilización de hasta 8 “Pages”, a partir de ese momento se asignan “Extends” de tipo “Uniform” para optimizar el uso del espacio en la tabla.

Los “DataFiles” normalmente tienen 2 extensiones de archivo, las cuales son estandar mas no obligarias, la extencion “mdf” que se utiliza para el primer “Datafile” perteneciente al “FileGroup” primario, y la extension “ndf” que se utiliza para los demas datafiles que se agregan posteriormente a los demas “FileGroups” de la base de datos.

En el caso del “LogFile”, este no pertenece a un “FileGroup” en especifico, en cambio archivo esta ligado directamente a la base de datos. Las bases de datos de SQL Server solo pueden tener un solo “LogFile” activo al mismo tiempo, si bien se pueden crear multiples “LogFiles” en la base de datos, solo uno podra ser escrito, ya que solo uno puede estar activo, cuando este archivo se llene, la base de datos pasara a escribir al siguiente archivo de transacciones, y asi sucesivamente. Por esta razon no es muy conveniente ni util tener mas de un “LogFile”.

En conclusión espero que sea de ayuda estas explicaciones sobre la arquitectura de una base de datos de SQL Server, si desean temas por favor no duden en solicitarlo, haré lo posible para poder cubrir los temas solicitados en el mas corto tiempo.

Recovery Models

Los modelos de recuperación de SQL Server son 3, Full, Bulk-Logged y Simple. Pero cuál es la diferencia y beneficios de cada uno de estos modelos de recuperación. Pues en este articulo exploraremos a mas detalle la diferencia entre ellos, lo cual radica en el manejo que le dan al log de transacciones de la base de datos y en el nivel de recuperación de información en un caso de desastre.

Modelo de Recuperación Simple: En caso de desastre en el servidor de base de datos, este modelo solo nos permitiría recuperar la información de la base de datos, solo hasta el momento en el cual se tomo el ultimo backup full de la base de datos. Es decir que si todos los días a media noche se hace un backup full de la base de datos, se está asumiendo que el negocio puede perder el equivalente de 1 día de información en la base de datos, en el peor de los casos si el desastre tiene lugar a las 23:59 se habrá perdido el día entero en información. Normalmente este modelo lo usan bases de datos pequeñas y medianas debido a que el backup full de la base de datos no toma mucho tiempo en realizarse.

Pero bajo la perspectiva de este modelo de recuperación no todo es malo, la ventaja principal que ofrece este modelo de recuperación es la facilidad en la administración que ofrece debido a que no se necesitan hacer backups de log de transacciones de la base de datos para que el log de transacciones no crezca sin control, es más bajo este modelo de recuperación no está permitido hacer este tipo de backups. Cuando la base de datos se encuentra en este modelo de recuperación el log de transacciones se comporta de manera cíclica por defecto, lo cual permite mantener controlado el tamaño del log de transacciones. Pero como es que el log de transacciones se mantiene pequeño, y es que cada vez que dentro de la base de datos se ejecuta el proceso de “checkpoint”, el log de transacciones es truncado, es decir, que las entradas activas que se tienen dentro del mismo son marcadas como inactivas, lo cual permite sobrescribirlas de una manera cíclica. Esto lo podemos comprobar muy fácilmente con el siguiente ejemplo:

USE master
GO
-- Elimino la base de datos en el caso que ya se encuentre creada
IF (DATABASEPROPERTY('DBSimple','Version') > 0)
DROP DATABASE DBSimple
GO
-- Creo la base de datos desde cero y con los valores por defecto
CREATE DATABASE DBSimple
GO
-- Modifico el modelo de recuperación de la base de datos a simple
-- debido a que el modelo de recuperacion por defecto que tengo 
-- configurado es FULL
ALTER DATABASE DBSimple SET RECOVERY SIMPLE
GO
-- Cambio el contexto de la base de datos
USE DBSimple
GO
-- Creare una tabla simple e insertare algunos registros
CREATE TABLE dbo.TablaPrueba (Codigo int)
GO
INSERT INTO dbo.TablaPrueba VALUES (1),(2),(3),(4),(5)
GO
-- Ahora consultare las entradas activas del log de transacciones con la 
-- siguiente sentencia
SELECT [Current LSN],[Operation],[Context] FROM ::fn_dblog(NULL,NULL)
GO
/*
Current LSN             Operation                       Context
----------------------- ------------------------------- -------------------------------
00000017:0000001b:0001  LOP_BEGIN_XACT                  LCX_NULL
00000017:0000001b:0002  LOP_MODIFY_ROW                  LCX_BOOT_PAGE
...
00000017:0000003c:0014  LOP_SET_FREE_SPACE              LCX_PFS
00000017:0000003c:0015  LOP_INSERT_ROWS                 LCX_HEAP
00000017:0000003c:0016  LOP_INSERT_ROWS                 LCX_HEAP
00000017:0000003c:0017  LOP_INSERT_ROWS                 LCX_HEAP
00000017:0000003c:0018  LOP_INSERT_ROWS                 LCX_HEAP
00000017:0000003c:0019  LOP_COMMIT_XACT                 LCX_NULL
*/

-- Ahora forzare el proceso de checkpoint en la base de datos
CHECKPOINT
GO
-- Finalmente verificaremos que el log de transacciones de la base de datos
-- ha sido truncado y las entradas anteriores fueron marcadas inactivas
SELECT [Current LSN],[Operation],[Context] FROM ::fn_dblog(null,null)
GO
/*
Current LSN             Operation                       Context
----------------------- ------------------------------- -------------------------------
00000017:00000041:008a  LOP_BEGIN_CKPT                  LCX_NULL
00000017:00000079:0001  LOP_END_CKPT                    LCX_NULL
*/

Modelo de Recuperación Full: En caso de desastre en el servidor de base de datos, este modelo nos permitiría recuperar la información de la base de datos hasta la última transacción confirmada en la base de datos. Claro que para poder lograr esto se debe poder tener acceso al log de transacciones para poder hacer un backup de tipo “Tail Log”, en el caso que no se tenga acceso al log de transacciones de la base de datos, la información podrá ser recuperada solo hasta el último backup de log o de transacciones, cualquiera que fuera el más reciente. Es importante resaltar que bajo este modelo de recuperación se pueden plantear diversas estrategias de backups para bases de datos medianas,  grandes y muy grandes, las estrategias que se pueden plantear bajo este modelo de recuperación pueden combinar los backups full, diferencial y de log para poder proteger la información guardada dentro de la base de datos.

La administración de la base de datos bajo este modelo de recuperación ya es un poco más compleja debido a que se tiene que tener un correcto manejo y administración de los backups de la base de datos, en especial de los backups del log. Si no se sacan los backups de log de la base de datos, este archivo crecerá sin control y no podrá darse su comportamiento cíclico natural. Debido a que la premisa bajo este modelo es poder recuperar hasta la última transacción confirmada en la base de datos, esta necesita tener una copia de seguridad de todos los registros que se tienen dentro del log de transacciones, para de esta manera ir aplicándolos uno por uno secuencialmente y así recuperar toda la información.

Entonces después de lo establecido en el párrafo anterior se puede inferir que el log de transacciones de la base de datos marca como inactivos los registros de las transacciones, solo si estos han sido descargados en algún backup de log, si no se ha sacado ningún backup de log este jamás podrá marcar como inactivos sus registros y por ende tendrá que crecer, lo cual como ya vimos en el post anterior es algo malo para la performance de base de datos. A continuación veremos un ejemplo que graficara el comportamiento del log bajo este modelo de recuperación:

USE master
GO
-- Elimino la base de datos en el caso que ya se encuentre creada
IF (DATABASEPROPERTY('DBFull','Version') > 0)
DROP DATABASE DBFull
GO
-- Creo la base de datos desde cero y con los valores por defecto
CREATE DATABASE DBFull
GO
-- Modifico el modelo de recuperación de la base de datos a full
-- solo para asegurarme que el modelo esta configurado a FULL
ALTER DATABASE DBFull SET RECOVERY FULL
GO
-- realizare un primer backup full de la base de datos
BACKUP DATABASE DBFull TO DISK = N'D:\Backups\DBFull_Full.bak' WITH INIT, STATS
GO
-- Cambio el contexto de la base de datos
USE DBFull
GO
-- Creare una tabla simple e insertare algunos registros
CREATE TABLE dbo.TablaPrueba (Codigo int)
GO
INSERT INTO dbo.TablaPrueba VALUES (1),(2),(3),(4),(5)
GO
-- Ahora consultare las entradas activas del log de transacciones con la    
-- siguiente sentencia
SELECT [Current LSN],[Operation],[Context] FROM ::fn_dblog(NULL,NULL)
GO
/*
Current LSN             Operation                       Context
----------------------- ------------------------------- -------------------------------
00000017:0000001b:0001  LOP_BEGIN_XACT                  LCX_NULL
00000017:0000001b:0002  LOP_MODIFY_ROW                  LCX_BOOT_PAGE
...
00000017:0000003c:0014  LOP_SET_FREE_SPACE              LCX_PFS
00000017:0000003c:0015  LOP_INSERT_ROWS                 LCX_HEAP
00000017:0000003c:0016  LOP_INSERT_ROWS                 LCX_HEAP
00000017:0000003c:0017  LOP_INSERT_ROWS                 LCX_HEAP
00000017:0000003c:0018  LOP_INSERT_ROWS                 LCX_HEAP
00000017:0000003c:0019  LOP_COMMIT_XACT                 LCX_NULL
*/

-- Ahora realizare un backup full de la base de datos
BACKUP DATABASE DBFull TO DISK = N'D:\Backups\DBFull_Full2.bak' WITH INIT, STATS
GO
-- Ahora volvemos a consultar las entradas activas del log de transacciones    
-- como vemos los registros del log aun siguien activos
SELECT [Current LSN],[Operation],[Context] FROM ::fn_dblog(NULL,NULL)
GO
/*
Current LSN             Operation                       Context
----------------------- ------------------------------- -------------------------------
00000017:0000001b:0001  LOP_BEGIN_XACT                  LCX_NULL
00000017:0000001b:0002  LOP_MODIFY_ROW                  LCX_BOOT_PAGE
...
00000017:0000003c:0014  LOP_SET_FREE_SPACE              LCX_PFS
00000017:0000003c:0015  LOP_INSERT_ROWS                 LCX_HEAP
00000017:0000003c:0016  LOP_INSERT_ROWS                 LCX_HEAP
00000017:0000003c:0017  LOP_INSERT_ROWS                 LCX_HEAP
00000017:0000003c:0018  LOP_INSERT_ROWS                 LCX_HEAP
00000017:0000003c:0019  LOP_COMMIT_XACT                 LCX_NULL
*/

-- Ahora haremos un checkpoint en la base de datos
CHECKPOINT
GO
-- Volvemos a consultar las entradas activas del log de transacciones    
-- como vemos el comportamiento de este modelo de recuperacion no es igual que el Simple
SELECT [Current LSN],[Operation],[Context] FROM ::fn_dblog(NULL,NULL)
GO
-- Ahora realizare un backup del log de la base de datos
BACKUP LOG DBFull TO DISK = N'D:\Backups\DBFull_Log.bak' WITH INIT,STATS=10
GO
-- Finalmente verificaremos que luego del backup del log de transacciones,
-- este ha sido truncado y las entradas anteriores fueron marcadas inactivas
SELECT [Current LSN],[Operation],[Context] FROM ::fn_dblog(null,null)
GO
/*
Current LSN             Operation                       Context
----------------------- ------------------------------- -------------------------------
00000017:00000041:008a  LOP_BEGIN_CKPT                  LCX_NULL
00000017:00000079:0001  LOP_END_CKPT                    LCX_NULL
*/

Modelo de Recuperación Bulk-Logged: Este modelo de recuperación es muy parecido en su funcionamiento y comportamiento al modelo “Full”, pero la gran diferencia con el “Full” es que bajo este modelo, el log de transacciones no graba toda la información de las operaciones Bulk, solo la asignación de páginas y extends, evitando de esta manera que el log de transacciones crezca en el caso que se haga una operación bulk que consuma muchos recursos de la base de datos.

Este modelo de recuperación se creó para que cuando se tiene definida ya una estrategia de backups en el modelo Full, se pueda cambiar al modelo “Bulk-Logged” y viceversa sin afectar la cadena de backups, en el caso que se vaya a realizar una operación “Bulk” bastante intensa lo cual causaría que el log de transacciones crezca de una manera descontrolada. Un ejemplo de una operación “Bulk” bastante intensa puede ser la reconstrucción de todos los índices de la base de datos, una carga de datos a un datawarehouse.

Ya que hemos mencionado al cadena de backups es importante conocer que cuando se tiene una base de datos en el modelo “Full” y se cambia al modelo “Simple”, la cadena de backups se pierde, es decir que cuando se regrese al modelo “Full” y se intente sacar un backup de log, este fallara y la única alternativa será sacar nuevamente un backup “Full” de la base de datos y reiniciar la cadena de backups. En el futuro se escribirá un post detallando un poco mas este tema.

En conclusión es importante conocer el comportamiento de cada uno de los modelos de recuperación de base de datos, debido a que cada uno de ellos nos ofrecen diversas ventajas, y conociendo su funcionamiento podremos sacar provecho de todas las capacidades que nos ofrece cada uno de ellos. Bueno espero que les haya servido este post y próximamente vendrán más.