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