Mover objetos de SQL Server a otro grupo de ficheros

Un problema típico de rendimiento de la BBDD a la que nos enfrentamos muchos es el cuello de botella que suponen los discos físicos. Imaginemos un entorno perfecto en el que todo son "index seeks", no tenemos "table scans" y encima la poca cantidad de datos y sobrada RAM, hacen que SQL Server tenga que ir pocas veces a disco para leer un dato. Pues bien, aun en esa situación que rara vez y entornos muy reducidos es posible , podemos mejorar el acceso a datos, paralelizando los accesos a disco.

Pongamonos en el caso mas simple: 2 tablas y un solo disco que además tiene un solo cabezal de lectura. Si hacemos una consulta que incluya las dos tablas (suponiendo tambien que es la primera y que no tiene datos en la caché ) , el cabezal va a tener que leer los datos de las dos tablas el solito; cuando si tuviéramos cada tabla en un disco físico distinto, cada lectura seria llevada a cabo por un cabezal diferente y evidentemente se dispondria del resultado antes.

No todos estamos en disposicion siempre de diseñar e implementar una BBDD desde 0 y distribuir las tablas en diferentes discos a sabiendas de la carga que van a tener cada una de ellas. Los cuellos de botella I/O se suele ver a posteriori cuando realizamos pruebas de carga de la aplicación y en la mayoria de casos por desgracia ya en producción.

No es el objeto de este post descubrir cuando tenemos problemas de disco, pero una vez ya las hemos dectectado, lo que hemos de analizar es como evitarlos o minimizarlos lo mejor que podamos.

Muchas veces, lo que vamos a hacer es comprar discos físicos en RAID y realizar la mejor configuración de tempdb, Grupos de ficheros y log de transacciones que podamos. Eso va a incluir en la mayoria de veces, que se creen grupos de ficheros en discos con la configuración mas óptima en RAID, para las tablas que mas sobrecarga conllevan.

Dichos grupos de ficheros van a albergar objetos, y claro el problema nos viene cuando queremos mover una tabla de un grupo de ficheros a otro. La solución es bien sencilla, pasa por utilizar la cláusula "MOVE TO" de DROP INDEX o ALTER TABLE ... DROP CONSTRAINT.

Dicha cláusula, nos va a permitir especificar donde mover una tabla en última instancia despues de borrar su índice clustered, puesto que lo que va a hacer es mover el contenido del "leaf level" del ya borrado índice al grupo de ficheros designado. Dicho "leaf level" no es ni mas ni menos que la tabla con sus datos en sí.

Tendremos que tener cuidado con el resto de índices "nonclustered" que pueda tener la tabla, puesto que estos se van a quedar en el grupo de ficheros antiguo, con lo que lo mejor si no queremos que esto sea así será borrarlos y volverlos a crear, pero esta vez bajo el grupo de ficheros que les pertenezca ( no tien porque ser el mismo donde se encuentra la tabla que acabamos de mover ).

Un ejemplo práctico de esto lo podeis ver aqui:

-- Creamos una tabla cualquiera
--
create table tablaprueba(a int not null, b varchar(2))
go
-- Le damos un nombre a la clave primaria
-- ( que va a hacer ademas por definición,
-- de indice clústered de nuestra tabla )
alter table tablaprueba
add constraint pk_tablaprueba primary key(a)
go
-- Ahora, le metemos ademas un índice no clustered,
-- para que veamos que es lo que pasa
create nonclustered index idx_tablaprueba_b on tablaprueba(b)
go
-- Le metemos unas filitas de datos...
insert into tablaprueba (a,b) values (1,'a')
go
insert into tablaprueba (a,b) values (2,'b')
go
insert into tablaprueba (a,b) values (3,'c')
go

-- Hacemos una consulta para ver que tenemos datos, obviamente
select * from tablaprueba with(nolock)
go

Ahora, podemos comprobar como tenemos la tabla creada bajo el grupo de ficheros por defecto de la BBDD en cuestión, ya que no le hemos indicado al crear la tabla bajo que grupo de ficheros la queríamos.

Lo que nos interesa ahora es mover la tabla al grupo de ficheros que previamente he creado y llamado GUSENET_1, valiéndome de la sentencia antes mencionada.

-- Y ahora lo movemos al grupo de ficheros que queramos. En este caso lo he llamado GUSENET_1, que previamente he creado ya.
alter table tablaprueba drop constraint pk_tablaprueba with(move to GUSENET_1)
/*
-- Notese que si el índice clústered no hubiera sido una clave primaria, en vez de usar
-- la sentencia alter table... tendriamos que haber utilizado DROP INDEX, con idénticos resultados, la tabla se hubiera movido al mismo sitio
DROP INDEX pk_tablaprueba
ON tablaprueba WITH ( MOVE TO GUSENET_1);
GO
*/

Una vez ejecutado esto, podemos ver que realmente nos la ha movido:


-- Una vez ya hemos movido la tabla, si volvemos a realizar la consulta, vemos que seguimos teniendo datos, pero la planificación de la consulta, al no tener índice nos hace un Table Scan
select * from tablaprueba with(nolock) where a = 2
go

-- Puesto que queremos que todo siga como estaba, le haremos el índice de nuevo y lo pondremos en el grupo de ficheros que queramos, en este ejemplo nos lo llevamos al mismo grupo de ficheros donde hemos movido la tabla
alter table tablaprueba
add constraint pk_tablaprueba primary key(a) on GUSENET_1
go

--Ahora ya podemos ver , como realiza la consulta utilizando el índice clústered.
select * from tablaprueba with(nolock) where a = 2
go