Particionar Indice SQL

Hola nuevamente, y con la esperanza de que este post sea de utilidad para al menos una persona me dispongo a escribir.

Si alguien ya ha leído alguna otra entrada empezara a notar que tengo el tatuaje de Microsoft en la palma de las manos, y la realidad es que me gusta .NET y SQL y mucho, el que algunas soluciones tecnológicas aveces no corran como se espera tiene mas que ver con el echo de desconocer la tecnología e implementar de una manera equivocada algo.

Esta vez hablare de particionar Indices en SQL, y si bien es algo que en el escenario ideal debe contemplarse desde un inicio del proyecto el ejemplo que pondré se concentra en una base de datos ya poblada, esta es una técnica sumamente importante para mejorar el desempeño de nuestras bases de datos.
Para nuestro ejemplo hare referencia a la Base de datos ADVENTUREWORKS, la cual podemos confirmar tiene solo un FileGroup, para lo cual usaremos

USE ADVENTUREWORKS

Select * from sys.filegroups
Lo primero que haremos sera crear FileGroups adicionales para nuestra Base de datos


SQL Server nos permite un mejor manejo de tablas sumamente grandes mediante el uso de particiones.


I. Agregar FileGroups a la Base de datos

ALTER DATABASE ADVENTUREWORKS
ADD FILEGROUP Productos_200;
GO

ALTER DATABASE ADVENTUREWORKS
ADD FILEGROUP Productos_400;
GO

ALTER DATABASE ADVENTUREWORKS
ADD FILEGROUP Productos_600;
GO

ALTER DATABASE ADVENTUREWORKS
ADD FILEGROUP Productos_800;
GO

Alter database AdventureWorks
add Filegroup Productos_2000
GO
ALTER DATABASE ADVENTUREWORKS
ADD FILEGROUP Productos_X;
GO




II. Para consultar las particiones creadas lo pueden hacer mediante el Managment Studio o con el script :

Select * from sys.filegroups

III. El siguiente paso es agregar archivos de datos para cada FileGroup
a. En este punto aplicaremos las mejores prácticas respecto a
i. Inicializar los archivos de datos con espacio suficiente(tanto como tengamos disponible sin afectar otras aplicaciones y sin dejar sin espacio el o los discos duros disponibles).
ii. Respecto a la forma en que crecerán los archivos (Menos de un 1 GB al mismo tiempo)
iii. Todos los archivos de datos con el mismo tamaño.

Alter DataBASE ADVENTUREWORKS Add FILE
(
NAME=Productos_200,
FILENAME='C:\AdventureWorks\Productos_200.ndf',
SIZE=1000MB,
FILEGROWTH =150MB
) TO FILEGROUP Productos_200;
GO

Alter DataBASE ADVENTUREWORKS
Add FILE
(
NAME=DatosPromotorias_400,
FILENAME='C:\AdventureWorks\Productos_400.ndf',
SIZE=1000MB,
FILEGROWTH =150MB
) TO FILEGROUP Productos_400;
GO

Alter DataBASE ADVENTUREWORKS
Add FILE
(
NAME=Productos_600,
FILENAME='C:\AdventureWorks\Productos_600.ndf',
SIZE=1000MB,
FILEGROWTH =150MB
) TO FILEGROUP Productos_600;
GO

Alter DataBASE ADVENTUREWORKS
Add FILE
(
NAME=Productos_800,
FILENAME='C:\AdventureWorks\Productos_800.ndf',
SIZE=1000MB,
FILEGROWTH =150MB
) TO FILEGROUP Productos_800;
GO
Alter DataBASE ADVENTUREWORKS
Add FILE
(
NAME=Productos_2000,
FILENAME='C:\AdventureWorks\Productos_2000.ndf',
SIZE=1000MB,
FILEGROWTH =150MB
) TO FILEGROUP Productos_2000;
GO

Alter DataBASE ADVENTUREWORKS
Add FILE
(
NAME=Productos_X,
FILENAME='C:\AdventureWorks\Productos_X00.ndf',
SIZE=1000MB,
FILEGROWTH =150MB
) TO FILEGROUP Productos_X;
GO

IV. Posteriormente será necesario crear una función de partición

Create Partition Function FnParticionesProductos(Int)
as Range Left
For Values (200,400,600,800,2000);

a. Es posible consultar su creación mediante el query :

SELECT * FROM sys.partition_functions WHERE name = 'FnParticionesProductos'
SELECT * FROM sys.partition_range_values


V. Una vez creada la función de partición se procede a crear un schema de partición, el cual en conjunto con la función antes creada se encargaran de encapsular el que SQL sepa a que FileGroup y FileData se grabara o se leerá nuestra información


Create Partition Scheme SchemaProductos
as Partition FnParticionesProductos
TO (
Productos_200,
Productos_400,
Productos_600,
Productos_800,
Productos_2000,
Productos_X
)

a. Es posible consultar el schema de partición que se ha creado mediante el query :
Select * from sys.partition_schemes

b. La información en nuestra implementación caería de la siguiente manera

Filegroup Particion Min ProductoID Max ProductoID
Productos_200 1 -Max(Int) 200
Productos_400 2 201 400
Productos_600 3 401 600
Productos_800 4 601 800
Productos_2000 5 801 2000
Productos_X 6 2001 Max(Int)


VI. Utilizaremos la tabla Sales.SalesOrderDetail para mostrar cómo es posible particionar una tabla poblada
a. Consultamos sus índices,


Normalmente usaria el store procedure Sp_helpIndex para verificarlos, sin embargo al tener un schema diferente al que tengo como predeterminado en mi usuario , me quede pensando y arme un script que nos da los indices, finalmente un problema nos da una oportunidad de hacer las cosas de una forma diferente, asi que :

Select Distinct Indexes.Name,Indexes.Index_Id,Type_desc,Columns.Name from sys.indexes Indexes
Inner join sys.columns Columns on Indexes.Object_ID=Columns.Object_ID
Inner join sys.index_columns IndexColumns
ON Columns.Object_ID=IndexColumns.Object_ID and Columns.Column_ID=IndexColumns.Column_ID
where Columns.object_ID=(Select object_id
from sys.tables where name='SalesOrderDetail')
Order by Indexes.Index_Id





b. Se identifican los índices que están involucrados con la columna mediante la cual deseamos crear un índice particionado.

Drop index AK_SalesOrderDetail_rowguid on Sales.SalesOrderDetail
Drop index IX_SalesOrderDetail_ProductID on Sales.SalesOrderDetail



c. Recreamos el índice conforme a nuestros requerimientos


Create NonClustered Index NC_ProducID_SalesOrderDetail
On Sales.SalesOrderDetail(ProductID)
on SchemaProductos(ProductID)


d. Posteriormente podemos consultar la información de cómo esta ahora particionada nuestra tabla

Select * from sys.partitions where object_ID=(Select object_id
from sys.tables where name='SalesOrderDetail')



Ejemplo :Para identificar que tenemos grabado en una partición especifica

Select $Partition.FnParticionesProductos(777)

Ejemplo: Para identificar a que partición le corresponde un valor.

Nota: Particionar una tabla sin datos es aun más sencillo pues únicamente tenemos que hacer referencia al esquema de partición al crear la tabla.
Beneficios

Tener una estrategia de tablas particionadas le permite a SQL Server evitar leer del mismo FileGroup y FileData la información solicitada, si la condición SARG se hace sobre un índice particionado SQL sabrá en que segmento debe buscar por lo que es mas rápido el acceso a la información.

Si los archivos de datos se encuentran en unidades de disco duro independiente SQL puede efectuar lecturas paralelas.

Nota Final: Como se menciono, el detalle de la estrategia a implementar usando particiones depende de la infraestructura disponible, no es tarea sencilla y debe hacerse con el respeto que se merece esta tarea respaldando siempre la BD y los scripts de los constraints e índices a borrar y recrear en caso de tablas ya pobladas.

Referencias
http://msdn.microsoft.com/en-us/library/ms186307(SQL.90).aspx
http://msdn.microsoft.com/en-us/library/ms179854.aspx
http://msdn.microsoft.com/en-us/library/ms187802(v=SQL.90).aspx
http://msdn.microsoft.com/en-us/library/ms175012(v=SQL.90).aspx
http://msdn.microsoft.com/en-us/library/aa275464(SQL.80).aspx

Diccionario de datos

Si alguien se pregunta sobre la posibilidad de obtener la definiciòn de tablas (Nombre, tipo de dato, longitud, nulo) en SQL, la respuesta es si.





Es realmente muy sencillo, existen otras formas de obtenerlo , les comparto una :


Declare @Tabla varchar(100)
Set @Tabla= 'CAT_TIPOS_ARCHIVO'
Select Schemas.Table_Name [Tabla],schemas.Column_name [Columna],
EP.Value [Descripcion]
,Schemas.data_type [Tipo de dato],
col.max_length [Longitud],col.is_nullable [Permite Nullo]from
INFORMATION_SCHEMA.COLUMNS Schemas
Inner join (Select * from Sys.columns
where object_id=(Select Object_ID from sys.objects where name=@Tabla)
) Col
On Schemas.Column_name=Col.Name
Left Join sys.extended_properties EP
on EP.Major_id=Col.object_id and EP.minor_id=Col.Column_id
where Schemas.Table_Name=@Tabla
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'Clave del usuario que modifico el registro' ,
@level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',
@level1name=N'CAT_TIPOS_ARCHIVO', @level2type=N'COLUMN',
@level2name=N'TAR_CVE_USUARIO';





Espero sea de su utilidad



Saludos

Habilitar AWE

Por Default el máximo de memoria que puede utilizar SQL Server en un sistema operativo de 32 Bits es de 2 GB , sin deseamos poder acceder a mas memoria es necesario habilitar AWE lo cual no per-mitirá usar hasta 3 GB en un sistema operativo de 32 Bits. Describiremos los pasos para habilitarlo.

A. Procedemos a habilitar el mostrar opciones avanzadas en la instancia instalada y habili-tamos AWE

sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
B. Redimensionamos los limites de memoria a usar

sp_configure 'min server memory', 1024
RECONFIGURE
GO
sp_configure 'max server memory', 3144
RECONFIGURE
GO

C. Modificamos el archivo Boot.Ini ,


Agregue lo siguiente al final de la línea de inicio que incluye el modificador /fastdetect: /3GB



Referencias
http://msdn.microsoft.com/es-es/library/ms190731.aspx
http://technet.microsoft.com/es-es/library/bb124810(EXCHG.65).aspx

Indices perdidos

Cuando creamos nuestros objetos de base de datos, concretamente Tablas y vistas indexa-das suele ocurrir que no contemplemos todos los índices que serán necesarios para satisfacer nuestras llamadas a la Base de Datos.

Una forma de identificar índices perdidos (no existen) es hacer uso de la vista del sistema sys.dm_db_missing_index_details la cual nos regresa información detallada de los índices que hacen falta en nuestra Base de datos. Si nuestra labor fuera supervisar el funcionamiento de un servidor o un conjunto de servidores con una o más instancias de SQL Server lo correcto sería empezar a dar prioridad a las Bases de Datos con un número de índices no creados más alto, para lo cual podemos hacer uso del siguiente script:

SELECT
DB_NAME(database_id) [Nombre de base de datos]
, count(*) [Numero de indices perdidos]
FROM sys.dm_db_missing_index_details
GROUP BY DB_NAME(database_id)
ORDER BY 2 DESC;


Una vez que sabemos que Base de datos es la que nos puede involucrar en mayor medida índices que no fueron creados el siguiente paso es identificar las tablas y columnas que corresponden a dichos índices.



Hasta aquí hemos visto en que nos podemos apoyar, nuestro siguiente paso es generar un script que nos retorne el detalle de los índices perdidos:

SELECT TOP 100
DB_NAME(database_id) [Nombre de base de datos]
,[Costo total] = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0)
, avg_user_impact
, TableName = statement
, [EqualityUsage] = equality_columns
, [InequalityUsage] = inequality_columns
, [Include Cloumns] = included_columns
FROM sys.dm_db_missing_index_groups g
INNER JOIN sys.dm_db_missing_index_group_stats s
ON s.group_handle = g.index_group_handle
INNER JOIN sys.dm_db_missing_index_details d
ON d.index_handle = g.index_handle
ORDER BY [Nombre de base de datos],[Costo total] DESC;


Una vez analizado este punto, podemos darnos cuenta que el crear los índices perdidos no es un proceso que se pueda automatizar de una manera por decir sencilla, ya que la creación de un índice que involucra más de una columna y que incluso tiene columnas de tipo INCLUDE es una tarea que debe ser revisada detalladamente, pues el orden de las columnas en que establezcamos la creación de nuestros índices puede marcar la diferencia entre un índice bien construido y uno mal o regularmente construido.

Indices mas costosos no utilizados

Identificar indices mas costosos no utilizados

Las operaciones de Inserción, actualización y eliminación provocan un mantenimiento a los índices relacionados a nuestra tabla. Recordemos que SQL determina el mejor camino (plan de ejecución) con el que resolverá una consulta, se apoya en las estadísticas de las columnas de las tablas involucradas y en el porcentaje de fragmentación de los índices, si bien es cierto que podemos forzar el uso de un índice cuando se resuelve una consulta esto debe hacerse solo en casos extremos y con un amplio análisis.

El punto al cual pretende dirigir esta tema es que si un índice no esta siendo usado por SQL , su presencia puede afectar el desempeño de nuestras consultas ,por lo cual es importante identificarlos y borrarlos una vez que hemos comprobado que las columnas involucradas tie-nen sus estadísticas actualizadas y que a pesar de eso SQL no los usa.

Una vez mas nos apoyaremos en las vistas dinámicas del sistema , sys.dm_db_index_usage_stats es el nombre de la vista que usaremos y las columnas de nuestro interés son:

database_id : Id. de la base de datos en la que se define la tabla o vista.
object_id : Id. de la tabla o vista en la que se define el índice.
index_id : Id. del índice.
user_seeks : Número de consultas de búsqueda realizadas por el usuario.
user_scans : Número de consultas de recorrido realizadas por el usuario.
user_lookups : Número de búsquedas de marcadores realizadas por consultas de usuario.
user_updates : Número de consultas de actualización realizadas por el usuario.
last_user_seek : Hora en que el usuario realizó la última búsqueda.
last_user_scan : Hora en que el usuario realizó el último recorrido.
last_user_lookup : Hora de la última búsqueda del usuario.
last_user_update : Hora en que el usuario realizó la última actualiza-ción.
system_seeks : Número de consultas de búsqueda realizadas por el sistema.
system_scans : Número de consultas de recorrido realizadas por el sistema.
system_lookups : Número de búsquedas realizadas por consultas del sistema.
system_updates : Número de consultas de actualización realizadas por el sistema.

Para recolectar la información correspondiente a los índices más costosos no utilizados de-berán ejecutar el siguiente script :

SELECT OBJECT_NAME(ind.object_id) [Tabla], ind.name [Indice]
FROM Sys.Indexes ind
INNER JOIN Sys.Objects obj ON obj.object_id = in-d.object_id
WHERE OBJECTPROPERTY(obj.object_id,'IsUserTable') = 1
AND NOT EXISTS
(SELECT 1 FROM Sys.dm_db_index_usage_stats usg
WHERE usg.object_id = ind.object_id
AND usg.index_id = ind.index_id
AND ind.index_id = usg.index_id
) and ind.name is not null
ORDER BY
[Tabla],
[Indice]



Referencias
http://technet.microsoft.com/es-mx/library/ms188755.aspx

Optimizar TEMPDB

TEMPDB es una base de datos de SQL (sistema), optimizarla nos ayudara a que nuestras operaciones sean mas rapidas, en busca de dar una receta de cocina se dira lo siguiente :

I. Modo de recuperación : Establecer el modo de recuperación de la BD en SIMPLE , se puede consultar el modo actual de recuperación mediante el siguiente script

Select [name] ,state_desc ,recovery_model_desc From sys.databases where Name='TEMPDB'

Posteriormente en caso de encontrar en un modo diferente a [SIMPLE], se puede modificar con el script :

ALTER DATABASE tempdb SET RECOVERY SIMPLE;

II. Ubicación : Colocar TEMPDP en un Disco duro diferente al disco duro del sistema operativo y diferente al disco duro donde radica la BD del SIP.
a. SQL Server 2005 efectúa operaciones en TEMPDB al crear tablas tempora-les, agrupar, ordenar , utilizar cursores, entre algunas otras. Cuando movemos de ubicación TEMPDB logramos reducir el nivel de contención al disco duro.
b. Debe ser un disco duro independiente ,no una partición.

III. Data File : Crear un archivo de datos por cada procesador existente en el servidor, es decir si el servidor tuviera 8 procesadores deberíamos tener 8 archivos de datos. Asigne el mismo tamaño a cada archivo creado

IV. Establecer el tipo de crecimiento de los archivos en tamaño ,la configuración deberá corresponder a que la suma de los archivos de datos de TEMPDB no excedan 500 Megas. Si se desea un valor más exacto deberá hacerse un análisis de la capacidad de escritura en el disco duro del procesador,

a. Para evitar que se produzcan tiempos de espera de bloqueo temporal, se recomienda limitar la operación de crecimiento automático a unos dos minutos

Por ejemplo, si el subsistema de E/S puede inicializar un archivo a 50 MB por segundo, se deberá establecer el incremento de FILEGROWTH en 6 GB como máximo.

Como siempre podemos recurrir a las recomendaciones de Microsoft, aqui una excelente referencia

http://technet.microsoft.com/en-us/library/cc966545.aspx