Script SQL Server
Chequear velocidad de la base de datos TempDB
Con la siguiente consulta podemos analizar el rendimiento de nuestra base de datos temporal, lo importante es que si el promedio de lectura y escritura no esta por debajo de 20 por segundo tendremos que realizar optimizar las consultas que impactan nuestra base de datos
SELECT files.physical_name AS Ruta,
files.NAME AS NombreDB,
stats.num_of_writes,
( 1.0 * stats.io_stall_write_ms / stats.num_of_writes ) AS
avg_write_stall_ms,
stats.num_of_reads,
( 1.0 * stats.io_stall_read_ms / stats.num_of_reads ) AS
avg_read_stall_ms
FROM sys.Dm_io_virtual_file_stats(2, NULL) AS stats
INNER JOIN master.sys.master_files AS files
ON stats.database_id = files.database_id
AND stats.file_id = files.file_id
WHERE files.type_desc = ‘ROWS’
files.NAME AS NombreDB,
stats.num_of_writes,
( 1.0 * stats.io_stall_write_ms / stats.num_of_writes ) AS
avg_write_stall_ms,
stats.num_of_reads,
( 1.0 * stats.io_stall_read_ms / stats.num_of_reads ) AS
avg_read_stall_ms
FROM sys.Dm_io_virtual_file_stats(2, NULL) AS stats
INNER JOIN master.sys.master_files AS files
ON stats.database_id = files.database_id
AND stats.file_id = files.file_id
WHERE files.type_desc = ‘ROWS’
