Introducción

He estado impartiendo una serie de charlas para nuestros Summits en Latam (http://learning.solidq.com/la/CourseDetail.aspx?CourseScheduleId=211), y ayer tuve un webcast similar para CAPA8 en Argentina (http://www.microsoft.com/conosur/technet/realityit/).

Durante las sesiones hice referencia a conocer el uso que se hace de los ficheros del servidor, cual es la topología de uso de cada fichero, cuales son los más usados, etc.

En SQL Server 2005, aparece una DMV (dm_io_virtual_file_stats, http://msdn2.microsoft.com/es-es/library/ms190326.aspx), que nos reporta información tan interesante como:

Obviamente es copy/paste de la url anterior, pero fíjate que la DMV reporta el número de lecturas, escrituras, incluso bytes, realizados en cada fichero de base de datos.

Esto es absolutamente indispensable para elegir cómo deben estar distribuidos los ficheros, en qué unidades físicas, incluso, qué nivel RAID es adecuado. La literatura dice que debes utilizar tal o cual nivel RAID para cada tipo de fichero, pero la realidad es que esa decisión la tomarás en función del número de discos disponibles, así que por ejemplo, si tienes 9 discos, ¿qué te conviene montar?

• Un RAID10 con 4 discos, y
• Un RAID5 con 5 discos.

O quizás:

• Un RAID 1 con 2 discos, y
• Un RAID10 con 6 discos.

O quizás:

• Un RAID 1 con 2 discos, y
• Un RAID5 con 7 discos.

Igual estás pensando: "Madre mia, cuanto lio, y yo que pensaba que había que montar topologías concretas para cada tipo de fichero; ¿no habíamos quedado que RAID10 para logs, y RAID5 para datos si no tenemos más discos?" Esa decisión la tendrás que tomar en función de uso que se haga de tus ficheros, y esa información la obtienes con el procedimiento que veremos a continuación J

Si claro, ¿pero quién justifica a los tíos de sistemas que en una instalación que ha estado funcionando "bien" durante un par de años, vamos a cambiar los niveles RAID porque lo ponga en este blog? Si lo necesitas en tu sistema, pues tendrás que justificarlo y proponer el cambio; piensa que depende del beneficio que tengas… ¿conviene montar toda esa historia para ganar una media de digamos 100 milisegundos por consulta? Pues depende… hace no mucho estuve con una empresa en Costa Rica que gestionaban créditos hipotecarios para miles de agencias… piensa un momento que esos 100 milisegundos que podrías ganar, supondrían mayor concurrencia en tu sistema (las transacciones son más cortas), y mayor tiempo de respuesta (sensación de usuario)… como siempre, depende la inversión que vas a hacer con el retorno de beneficios…

El procedimiento almacenado

El siguiente código sirve para identificar a nivel de fichero de base de datos, qué tipo de uso se hace. El procedimiento almacenado está basado en el procedimiento anterior de waitstats, así que esta vez me permitiré no explicarlo; recordar: se hacen dos capturas, y se calculan los deltas, ok?

IF EXISTS (SELECT * FROM SYS.PROCEDURES WHERE name = 'sproc_get_fileiostats_percentage')

DROP PROC dbo.sproc_get_fileiostats_percentage

GO

CREATE PROC dbo.sproc_get_fileiostats_percentage

    @hours tinyint = null,

    @minutes tinyint = null,

    @seconds tinyint = null

AS

SET NOCOUNT ON

IF @hours IS NULL

    SET @hours = 0

IF @minutes IS NULL

    SET @minutes = 0

IF @seconds IS NULL

    SET @seconds = 0

--

-- Validaciones iniciales

--

IF @hours < 0 OR @hours > 24

BEGIN

RAISERROR ('Hours range is not valid.', 16, 1 )

RETURN

END

IF @minutes < 0 OR @minutes > 60

BEGIN

RAISERROR ('Minutes range is not valid.', 16, 1 )

RETURN

END

IF @seconds < 0 OR @seconds > 60

BEGIN

RAISERROR ('Seconds range is not valid.', 16, 1 )

RETURN

END

IF @hours = 0 and @minutes = 0 and @seconds = 0

BEGIN

RAISERROR ('The measure time must be greater than zero.', 16, 1 )

RETURN

END

DECLARE @t TABLE (

id int identity

, database_id smallint

, file_id smallint

, num_of_reads bigint

, num_of_bytes_read bigint

, io_stall_read_ms bigint

, num_of_writes bigint

, num_of_bytes_written bigint

, io_stall_write_ms bigint

)

--

-- Inserción de primera captura

--

INSERT @t

(     database_id

    , file_id

    , num_of_reads

    , num_of_bytes_read

    , io_stall_read_ms

    , num_of_writes

    , num_of_bytes_written

    , io_stall_write_ms )

SELECT

    database_id

    , file_id

    , num_of_reads

    , num_of_bytes_read

    , io_stall_read_ms

    , num_of_writes

    , num_of_bytes_written

    , io_stall_write_ms

FROM

sys.dm_io_virtual_file_stats(-1,-1)

--

-- A esperar n tiempo

--

DECLARE @s CHAR(8)

SET @s =

RIGHT ('00' + CAST (@hours as VARCHAR(2)), 2) + ':'

    + RIGHT ('00' + CAST (@minutes as VARCHAR(2)), 2) + ':'

    + RIGHT ('00' + CAST (@seconds as VARCHAR(2)), 2)

WAITFOR DELAY @s

--

-- Inserción de segunda captura

--

INSERT @t

(     database_id

    , file_id

    , num_of_reads

    , num_of_bytes_read

    , io_stall_read_ms

    , num_of_writes

    , num_of_bytes_written

    , io_stall_write_ms )

SELECT

    database_id

    , file_id

    , num_of_reads

    , num_of_bytes_read

    , io_stall_read_ms

    , num_of_writes

    , num_of_bytes_written

    , io_stall_write_ms

FROM

sys.dm_io_virtual_file_stats(-1,-1)

SELECT s.name, v.* FROM (

SELECT

    T1.database_id

    , T1.file_id

    , (AVG(T2.num_of_reads - T1.num_of_reads)) num_of_reads

    , (AVG(T2.num_of_bytes_read - T1.num_of_bytes_read)) num_of_bytes_read

    , (AVG(T2.io_stall_read_ms - T1.io_stall_read_ms)) io_stall_read_ms

    , (AVG(T2.num_of_writes - T1.num_of_writes)) num_of_writes

    , (AVG(T2.num_of_bytes_written - T1.num_of_bytes_written)) num_of_bytes_written

    , (AVG(T2.io_stall_write_ms - T1.io_stall_write_ms)) io_stall_write_ms

FROM @T t1

JOIN @T t2

ON T1.database_id = T2.database_id

AND T1.file_id = T2.file_id

AND t1.id < T2.id

GROUP BY

    T1.database_id

    , T1.file_id

) v

JOIN sys.databases s

ON v.database_id = s.database_id

WHERE num_of_reads <> 0 OR num_of_writes <> 0

GO

Y un ejemplo de uso sería el siguiente:

EXEC dbo.sproc_get_fileiostats_percentage

    @hours = 1,

    @minutes = null,

    @seconds = null

GO

• ¿Qué hará? Pues como habrás imaginado, tardará en finalizar su ejecución 1 hora, y te mostrará el uso que se hace de cada fichero durante ese periodo de tiempo.
• ¿Siguiente paso? Copiarlo a un documento Excel, y calcular porcentajes. Recuerda, será tan sencillo como contar habas, ¿verdad Carlos? J
• Si bien la DMV no existe en SQL Server 2000, existe la función ::fn_virtualfilestats(-1, -1), que expone prácticamente lo mismo