Transacciones Implícitas en SQL Server

Una transacción es un conjunto de operaciones Transact SQL que se ejecutan como un único bloque, es decir, si falla una operación Transact SQL fallan todas. Si una transacción tiene éxito, todas las modificaciones de los datos realizadas durante la transacción se confirman y se convierten en una parte permanente de la base de datos. Si una transacción encuentra errores y debe cancelarse o revertirse, se borran todas las modificaciones de los datos.

  Para agrupar varias sentencias Transact SQL en una única transacción, disponemos de los siguientes métodos:

• Transacciones explícitas
  Cada transacción se inicia explícitamente con la instrucción BEGIN TRANSACTION y se termina explícitamente con una instrucción COMMIT o ROLLBACK.
• Transacciones implícitas
  Se inicia automáticamente una nueva transacción cuando se ejecuta una instrucción que realiza modificaciones en los datos, pero cada transacción se completa explícitamente con una instrucción COMMIT o ROLLBACK.

 En este post vamos a tratar sobre Transacciones Implícitas,   para conocer en qué orden y cuáles son las restricciones que ejecuta SQL Server Internamente para completar una transacción implícita.

En fin, una transacción implícita es cuando no existen declaraciones de  transacción. Se inician con las siguientes sentencias: ALTER TABLE, CREATE, DROP, GRANT, REVOKE, OPEN, FECTH, INSERT, UPDATE, DELETE, SELECT, TRUNCATE TABLE.

Flujo de la Transacción

1 - Identity Insert Check: Verifica si hay alguna Identidad declarada en la Tabla

2 - Nullability Constraint: Verifica las restricciones de valores Nulos (Allow Null)

3 - Data Type Check: Verifica el tipo de Dato que se está recibiendo para ver si corresponde con el de la Tabla

4 - Instead of Trigger:  Dispara el Evento INSTEAD OF  para los trigger que protegen la Tabla.  Este evento se ejecuta en lugar de la sentencia programada.

5 - Primary Key Constraint:   Verifica la duplicidad de registros en base a la llave primaria.

6 - Check Constraints:  Verifica las restricciones que se crearon para la tabla

7 - Foreign Key Constraint:   Verifica registros referenciados que deberán existir con las tablas relacionadas

8 - DML execution and update to Transaction Log:  Ejecución de la sentencia Transact como tal.

9 - After Trigger:  El último paso de verificación será la ejecución del Trigger como tal que protege la Tabla

10 - Commit Transaction

Indices en SQL Server

Un índice es una estructura de datos definida sobre una columna de tabla (o varias) y que permite localizar de forma rápida las filas de la tabla en base a su contenido en la columna indexada además de permitir recuperar las filas de la tabla ordenadas por esa misma columna.

Los índices funcionan igual que un Libro,   veamos,  si queremos buscar un tema especifico tenemos 3 Opciones:  

 La primera consiste un ir pagina por pagina buscando la información hasta encontrar el tema que requerimos; esto nos tomara mucho tiempo y esfuerzo.  

 La Segunda  nos vamos al Indice del Libro y buscamos lo que necesitamos,  eso nos dará el numero físico exacto del pagina del tema que buscamos nos dirigimos a él y la búsqueda seria rápida, a esto lo llamamos índices Clustered, serian por defecto las llaves primarias (Primary key).   

 La tercera opción siempre en el ejemplo del libro,  seria nos vamos al Glosario y ahí encontraremos un conjunto de paginas donde buscar información sobre el tema a investigar,  a esto lo llamamos índices NON-Clustered.

Entonces una definición más ortodoxa seria:

Los Clustered Indexes son índices que controlan el orden físico de las filas en la tabla, por lo cual solo puede existir uno para cada tabla. 

Los Non-Clustered indexes son índices que mantienen un sub conjunto de las columnas de la tabla en orden.  Estos índices no modifican el orden de las filas de la tabla, en lugar de esto mantienen una lista ordenada de referencias a filas de la tabla original. 

Ventajas

La utilización de índices puede mejorar el rendimiento de las consultas, ya que los datos necesarios para satisfacer las necesidades de la consulta existen en el propio índice. Es decir, sólo se necesitan las páginas de índice y no las páginas de datos de la tabla o el índice agrupado para recuperar los datos solicitados; por tanto, se reduce la E/S global en el disco. Por ejemplo, una consulta de las columnas a y b de una tabla que dispone de un índice compuesto creado en las columnas a, b y c puede recuperar los datos especificados del propio índice.

Los índices en vistas pueden mejorar de forma significativa el rendimiento si la vista contiene agregaciones, combinaciones de tabla o una mezcla de agregaciones y combinaciones. 

Inconvenientes

Las tablas utilizadas para almacenar los índices ocupan espacio.
Los índices consumen recursos ya que cada vez que se realiza una operación de actualización, inserción o borrado en la tabla indexada, se tienen que actualizar todas las tablas de índice definidas sobre ella (en la actualización sólo es necesaria la actualización de los índices definidos sobre las columnas que se actualizan

Recomendaciones para crear indices

Las columnas que se aconseja indexar son:

- Las que son clave primaria o ajena

- Aquellas que se usan frecuentemente en búsquedas de rangos de valores con BETWEEN

- Aquellas que se usan frecuentemente en ordenaciones con ORDER BY

- Aquellas que se usan frecuentemente en cruces de tabla o JOIN

- Aquellas que se usan frecuentemente en agrupaciones con GROUP BY

Procedimientos Importantes

EXEC sp_helpindex  Customers      

Mostrara los índices que contiene una tabla,  en el ejemplo Customers de la base Northwind

SET STATISTICS IO ON 

Habilitara la estadísticas de Lecturas Logicas utilizados en cada consulta:

Por ejemplo:

(12 row(s) affected)

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Facturas'. Scan count 1, logical reads 835, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Clientes'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Cómo se almacenan los datos en SQL Server?. Calculando el tamaño de una Tabla

Al crear una base de datos, es importante comprender cómo SQL Server almacena los datos para poder calcular y especificar la cantidad de espacio en disco que hay que asignar a los archivos de datos y registros de transacciones. 

Todas las bases de datos tienen un archivo de datos principal (.mdf), y uno o varios archivos de registro de transacciones (.ldf). Una base de datos también puede tener archivos de datos secundarios (.ndf). Estos archivos físicos tienen nombres del sistema operativo y nombres de archivo lógicos que se pueden utilizar en instrucciones Transact-SQL. La ubicación predeterminada para todos los archivos de datos y registros de transacciones es C:\Archivos de programa\Microsoft SQL Server\MSSQL\Data. 

Los datos se almacenan en bloques de 8 kilobytes (KB) de espacio contiguo en disco, llamados páginas. Esto significa que una base de datos puede almacenar 128 páginas por megabyte (MB )

Las filas no pueden abarcar más de una página. Por tanto, la máxima cantidad de datos de una fila, quitando el espacio necesario para la cabecera de la fila, es 8060 bytes.

Las tablas y los índices se almacenan en extensiones. Una extensión son ocho páginas contiguas, o 64 KB. Por tanto, una base de datos tiene 16 extensiones por megabyte. Las tablas pequeñas pueden compartir extensiones con otros objetos de la base de datos

Procedimientos Importantes:
sp_helpdb baseDeDatos

Informa sólo acerca de la base de datos especificada. Proporciona el nombre, el tamaño, el propietario, el Id., la fecha de creación y las opciones de la base de datos. También enumera los archivos de datos y de registro

sp_spaceused [nombreObjeto]
    Resume el espacio de almacenamiento que utiliza una base de datos o un objeto de base de datos   

 

    Estimación de la cantidad de datos en las tablas

Puede calcular el número de páginas necesarias para una tabla y el espacio de disco que ocupa la tabla si conoce el número de caracteres de cada fila y el número aproximado de filas que la tabla va a tener.  Utilice el siguiente método: ·         Calcule el número de bytes de una fila sumando el número de bytes que contiene cada columna. Si una o varias columnas están definidas con longitud variable (por ejemplo, una columna de nombres), puede sumar el valor medio de la columna para hallar el total.  ·         Determine el número de filas que caben en cada página de datos. Para hacerlo, divida 8060 entre el número de bytes de una fila. Redondee hacia abajo el resultado al número entero siguiente.  ·         Divida el número aproximado de filas de la tabla por el número de filas que caben en cada página de datos. El resultado es igual al número de páginas que se necesitan para almacenar la tabla.  Nota: Una fila no puede ser mayor que una página

Ejemplo:  Tabla Orders base Northwind:

Cantidad de Byte por Fila :  210 BYTE

Cantidad de Byte por Página: 8060 BYTE

Cantidad de Filas por Página: 38.38

Cantiad de Filas en la Tabla: 830

Cantidad de Paginas Utilizadas: 21.625

KB en la Tabla 21.625 * 8  =   173 KB

En el procedimiento sp_spaceused 'orders'  nos da un total de :  160 KB

Por lo cual esta correcto debido a los valores de longitud variable no se utilizan 100% tendríamos en nuestro calculo 13KB de mas considerando un promedio de uso en los valores varchar.

Sql server 2008

Plataforma SQL Server 2008 y Caracteristicas.

View more presentations from brobelo.