Como mejorar el rendimiento en SQL Server

Conseguir una mejora de rendimiento en I/O es una de las primeras opciones que debemos valorar en los trabajos de performance y tuning en nuestro entorno SQL Server. Configurar de forma óptima nuestro almacenamiento a través de los Filegroup es una excelente manera de racionalizar nuestro espacio, evitando además innecesarias entradas a disco.  A continuación os detallamos cómo:

¿Qué es un Filegroup?

Un Filegroup es una unidad lógica que almacena archivos físicos que pueden estar en distintas unidades de disco o en distintos discos físicos.

¿Para qué sirve un Filegroup?

La idea del Filegroup es la de aprovechar el procesamiento paralelo cuando el motor de Base de Datos requiere realizar operaciones de I/O al disco duro.

Ventajas a la hora de trabajar con Filegroups:

–          Mejora el rendimiento de la Base de Datos

–          Al crear más de un Filegroup podremos tener una mejor administración de nuestros archivos en el futuro y proveemos de mayor aislamiento posible I/O

–          Una buena práctica al crear Filegroups adicionales, es que los archivos pueden estar en unidades de disco o puntos de montaje (NAS) separadas. Con estos planteamientos es posible crear una estrategia de almacenamiento / consultas tal que nos permita reducir los cuellos de botella I/O en el disco duro.

¿Qué es el Filegroup por defecto “PRIMARY” de SQL Server?

Es el Filegroup que contiene el archivo de datos principal y cualquier otro archivo no asignado específicamente a otro grupo de archivos.

Todas las páginas de las tablas del sistema se asignan en el grupo de archivos principal (PRIMARY).

Si no se crean Filegroups en la instancia SQL Server y si no se indica en la instrucción de creación de objectos (CREATE), irán al Filegroup PRIMARY, ya que es el Filegroup por defecto.

Por ejemplo:

 

CREATE TABLE [dbo].[Prueba](

       [name] [nvarchar] (128) NOT NULL,

       [name2] [nvarchar](128) NOT NULL)  Filegroup PRIMARY (Por defecto)

 

CREATE TABLE [dbo].[Prueba](

       [name] [nvarchar] (128) NOT NULL,

       [name2] [nvarchar](128) NOT NULL)

ON [FILEGORUP_DATA]  Filegroup FILEGROUP_DATA (Creado por usuario)

 

Siempre que se creen objetos y estos se quieran en Filegroups específicos hay que decírselo al final de la instrucción (véase el ejemplo de arriba)

 

Ejemplos de scripts sobre Filegroups SQL Server

 

Objetos por Filegroup (Tablas e índices):

Este script lista las tablas e índices creadas en los Filegroups de una instancia SQL Server. Se realiza para todas las BBBDD de usuario.

 

/****** Object:  Table [dbo].[Temp_Filegroups]    Script Date: 15/06/2015 13:56:45 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Temp_Filegroups](

       [DB_Name] [nvarchar](100) NULL,

       [FileGroup] [nvarchar](100) NULL,

       [TableName] [nvarchar](100) NULL,

       [IndexName] [nvarchar](100) NULL,

       [Index_Size_KB] [nvarchar](100) NULL

) ON [PRIMARY]

GO

EXECUTE master.sys.sp_MSforeachdb’USE [?];

INSERT INTO [MSDB].[dbo].[Temp_Filegroups]

SELECT DB_NAME() as DB_Name,

FileGroup = FILEGROUP_NAME(a.data_space_id),

TableName = OBJECT_NAME(p.object_id),

IndexName = i.name,

Index_Size_KB = (SUM(s.used_page_count) * 8 )

FROM sys.allocation_units a

INNER JOIN sys.partitions p ON a.container_id = CASE WHEN a.type in(1,3) THEN p.hobt_id ELSE p.partition_id END AND p.object_id > 1024

LEFT JOIN sys.indexes i ON i.object_id = p.object_id AND i.index_id = p.index_id

inner join sys.dm_db_partition_stats AS s ON s.[object_id] = i.[object_id]  AND s.[index_id] = i.[index_id]

group by a.data_space_id, p.object_id, i.name, s.object_id, s.index_id

ORDER BY FileGroup’

—- Resultados —-

select * from [MSDB].[dbo].[Temp_Filegroups]

where DB_Name not in(‘distribution’,’master’,’model’,’msdb’,’tempdb’)  –> Se excluyen las BBDD de sistema

and Filegroup not in(‘Index_Primary’, ‘Indices’) –> Se excluyen los índices bien creados

and tablename not like ‘sys%’ –> Se excluyen las tablas de sistema

and IndexName not like ‘PK%’ –> Se excluyen las Primary Keys

and IndexName not like ‘FK%’ –> Se excluyen las Foreign Keys

—- Se borra la Tabla Temporal —

drop table [MSDB].[dbo].[Temp_Filegroups]

 

Resumiendo, si desea un óptimo rendimiento de sus servidores SQL Server a nivel de lecturas y escrituras en disco, es fundamental el tratar de gestionar sus BBDD a nivel de Filegroups, gestionando discos y/o puntos de montaje en NAS para el reparto de Datafiles (*.mdf) entre diferentes rutas para aprovechar el paralelismo de consulta.

Hay que tener en cuenta que los LogFiles (*.ldf log de transacciones) no son configurables a nivel de Filegroups.

 

Más info en:

https://msdn.microsoft.com/en-us/library/ms189563%28v=sql.110%29.aspx

http://blog.sqlauthority.com/2013/08/15/sql-server-sql-basics-what-are-filegroups-day-9-of-10/

 

Espero que os haya sido de utilidad.

Equipo de BBDD

Uso de cookies

GPS Open Source, S.L, le informa que este sitio web utiliza Cookies tanto propias como de terceros para facilitar la navegación y para recopilar información estadística sobre su navegación. Si pulsa aceptar o continúa navegando entenderemos que acepta la instalación de las mismas. Más Info, política de cookies

ACEPTAR
Show Buttons
Hide Buttons