DMV och felsökning/prestandaoptimering

När man felsöker eller prestandaoptimerar en installation av SQL Server är det ofta vi använder de sk DMV’na. Om man skulle fråga hur många som använder dessa av dem som jobbar med SQL Server så är det väldigt få som gör det, kanske 1 av hundra. Det är synd för i SQL Server finns ju sparat rätt mycket information som kan användas just för felsökning och prestandaoptimering. Rätt många av dessa värden räknas upp från det att servern är startad och ofta vill man också ha en ögonblicksbild. Nedan ser ni ett exempel på hur man kan lösa detta genom att ta ett snapshot på t.ex. minut för att också visa nuläget.

Jag använder här sys.dm_io_virtual_file_stats för att se vilka filer som är läs/skrivintensiva. I praktiken upptäckte jag hos en kund att tempdb hade skyhöga historiska värden och jag ville förstås se om detta var nuläget också efter att vi optimerat och gjort konfigurationsförändringar.

– Sparar värden i en temptabell SELECT DB_NAME(fs.database_id) AS [Database Name], mf.physical_name, io_stall_read_ms, num_of_reads, CAST(io_stall_read_ms/(1.0 + num_of_reads) AS NUMERIC(10,1)) AS [avg_read_stall_ms],io_stall_write_ms, num_of_writes,CAST(io_stall_write_ms/(1.0+num_of_writes) AS NUMERIC(10,1)) AS [avg_write_stall_ms], io_stall_read_ms + io_stall_write_ms AS [io_stalls], num_of_reads + num_of_writes AS [total_io], CAST((io_stall_read_ms + io_stall_write_ms)/(1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1)) AS [avg_io_stall_ms] INTO #DBA_io_stall_1 FROM sys.dm_io_virtual_file_stats(null,null) AS fs INNER JOIN sys.master_files AS mf ON fs.database_id = mf.database_id AND fs.[file_id] = mf.[file_id] OPTION (RECOMPILE);

– Väntar 1 minut WAITFOR DELAY ’00:01:00′

– Sparar värden i nästa temptabell SELECT DB_NAME(fs.database_id) AS [Database Name], mf.physical_name, io_stall_read_ms, num_of_reads, CAST(io_stall_read_ms/(1.0 + num_of_reads) AS NUMERIC(10,1)) AS [avg_read_stall_ms],io_stall_write_ms, num_of_writes,CAST(io_stall_write_ms/(1.0+num_of_writes) AS NUMERIC(10,1)) AS [avg_write_stall_ms], io_stall_read_ms + io_stall_write_ms AS [io_stalls], num_of_reads + num_of_writes AS [total_io], CAST((io_stall_read_ms + io_stall_write_ms)/(1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1)) AS [avg_io_stall_ms] INTO #DBA_io_stall_2 FROM sys.dm_io_virtual_file_stats(null,null) AS fs INNER JOIN sys.master_files AS mf ON fs.database_id = mf.database_id AND fs.[file_id] = mf.[file_id] OPTION (RECOMPILE);

– Presenterar resultat för alla datafiler SELECT GETDATE() AS Stat_time, DBA_io_stall_1.[Database Name], DBA_io_stall_1.physical_name, DBA_io_stall_1.io_stall_read_ms, DBA_io_stall_1.num_of_reads, DBA_io_stall_1.avg_read_stall_ms, DBA_io_stall_2.io_stall_read_ms – DBA_io_stall_1.io_stall_read_ms AS now_io_stall_read_ms, DBA_io_stall_2.num_of_reads – DBA_io_stall_1.num_of_reads AS now_num_of_reads, CAST((DBA_io_stall_2.io_stall_read_ms – DBA_io_stall_1.io_stall_read_ms) /(1.0 + (DBA_io_stall_2.num_of_reads – DBA_io_stall_1.num_of_reads)) AS NUMERIC(10,1)) AS [now_avg_read_stall_ms], DBA_io_stall_1.io_stall_write_ms, DBA_io_stall_1.num_of_writes, DBA_io_stall_1.avg_write_stall_ms, DBA_io_stall_2.io_stall_write_ms – DBA_io_stall_1.io_stall_write_ms AS now_io_stall_write_ms, DBA_io_stall_2.num_of_writes – DBA_io_stall_1.num_of_writes AS now_num_of_writes, CAST((DBA_io_stall_2.io_stall_write_ms – DBA_io_stall_1.io_stall_write_ms) /(1.0 + (DBA_io_stall_2.num_of_writes – DBA_io_stall_1.num_of_writes)) AS NUMERIC(10,1)) AS [now_avg_write_stall_ms] FROM #DBA_io_stall_1 AS DBA_io_stall_1 INNER JOIN #DBA_io_stall_2 AS DBA_io_stall_2 ON DBA_io_stall_1.[Database Name] = DBA_io_stall_2.[Database Name] AND DBA_io_stall_1.physical_name = DBA_io_stall_2.physical_name ORDER BY DBA_io_stall_1.[Database Name], DBA_io_stall_1.physical_name

– Listar 10 datafiler med största now_avg_read_stall_ms SELECT TOP 10 GETDATE() AS Stat_time, DBA_io_stall_1.[Database Name], DBA_io_stall_1.physical_name, DBA_io_stall_2.io_stall_read_ms – DBA_io_stall_1.io_stall_read_ms AS now_io_stall_read_ms, DBA_io_stall_2.num_of_reads – DBA_io_stall_1.num_of_reads AS now_num_of_reads, CAST((DBA_io_stall_2.io_stall_read_ms – DBA_io_stall_1.io_stall_read_ms) /(1.0 + (DBA_io_stall_2.num_of_reads – DBA_io_stall_1.num_of_reads)) AS NUMERIC(10,1)) AS [now_avg_read_stall_ms] FROM #DBA_io_stall_1 AS DBA_io_stall_1 INNER JOIN #DBA_io_stall_2 AS DBA_io_stall_2 ON DBA_io_stall_1.[Database Name] = DBA_io_stall_2.[Database Name] AND DBA_io_stall_1.physical_name = DBA_io_stall_2.physical_name ORDER BY [now_avg_read_stall_ms] DESC —

– Listar 10 datafiler med största now_avg_read_write_ms SELECT TOP 10 GETDATE() AS Stat_time, DBA_io_stall_1.[Database Name], DBA_io_stall_1.physical_name, DBA_io_stall_2.io_stall_write_ms – DBA_io_stall_1.io_stall_write_ms AS now_io_stall_write_ms, DBA_io_stall_2.num_of_writes – DBA_io_stall_1.num_of_writes AS now_num_of_writes, CAST((DBA_io_stall_2.io_stall_write_ms – DBA_io_stall_1.io_stall_write_ms) /(1.0 + (DBA_io_stall_2.num_of_writes – DBA_io_stall_1.num_of_writes)) AS NUMERIC(10,1)) AS [now_avg_write_stall_ms] FROM #DBA_io_stall_1 AS DBA_io_stall_1 INNER JOIN #DBA_io_stall_2 AS DBA_io_stall_2 ON DBA_io_stall_1.[Database Name] = DBA_io_stall_2.[Database Name] AND DBA_io_stall_1.physical_name = DBA_io_stall_2.physical_name ORDER BY [now_avg_write_stall_ms] DESC DROP TABLE #DBA_io_stall_1 DROP TABLE #DBA_io_stall_2