T-SQL系列之性能统计信息/数据库IO(MSSQL)

--查看性能统计信息  -dbcc freeproccachegoselect t.text as "执行的文本", st.total_logical_reads  as "逻辑读取总次数",st.total_physical_reads  as "物理读取总次数",st.total_elapsed_time/1000000 as "占用的总时间",st.total_logical_writes  as "逻辑写入总次数"from sys.dm_exec_query_stats stcross apply sys.dm_exec_sql_text(st.sql_handle) tgo--查询数据库IO  WITH IOFORDATABASE AS(SELECT DB_NAME(VFS.database_id) AS DatabaseName,CASE WHEN smf.type = 1 THEN 'LOG_FILE' ELSE 'DATA_FILE' END AS DatabaseFile_Type,SUM(VFS.num_of_bytes_written) AS IO_Write,SUM(VFS.num_of_bytes_read) AS IO_Read,SUM(VFS.num_of_bytes_read + VFS.num_of_bytes_written) AS Total_IO,SUM(VFS.io_stall) AS IO_STALLFROM sys.dm_io_virtual_file_stats(NULL, NULL) AS VFSJOIN sys.master_files AS smf  ON VFS.database_id = smf.database_id AND VFS.file_id = smf.file_idGROUP BY  DB_NAME(VFS.database_id),smf.type)SELECT  --ltrim(ROW_NUMBER() OVER(ORDER BY io_stall DESC)) AS RowNumber, substring(DatabaseName,1,30) as DatabaseName,DatabaseFile_Type,ltrim(CAST(1.0 * IO_Read/ (1024 * 1024) AS DECIMAL(12, 2))) AS IO_Read_MB,ltrim(CAST(1.0 * IO_Write/ (1024 * 1024) AS DECIMAL(12, 2))) AS IO_Write_MB,ltrim(CAST(1. * Total_IO / (1024 * 1024) AS DECIMAL(12, 2))) AS IO_TOTAL_MB,ltrim(CAST(IO_STALL / 1000. AS DECIMAL(12, 2))) AS IO_STALL_Seconds,ltrim(CAST(100. * IO_STALL / SUM(IO_STALL) OVER() AS DECIMAL(10, 2))) AS IO_STALL_PctFROM IOFORDATABASEORDER BY IO_STALL_Seconds DESC;go