所有資料庫資訊匯入

SELECT DB_NAME(mf.database_id) AS databaseName ,
 mf.physical_name ,
 divfs.num_of_reads ,
 --other columns removed in this section. See Listing 6.14 for complete code
 GETDATE() AS baselineDate
INTO #baseline
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS divfs
 JOIN sys.master_files AS mf ON mf.database_id = divfs.database_id
 AND mf.file_id = divfs.file_id

查看os_wait_stats狀態

查看os_wait_stats狀態
SELECT wait_type , SUM(wait_time_ms / 1000) AS [wait_time_s]
FROM sys.dm_os_wait_stats DOWS WHERE wait_type NOT IN ( 'SLEEP_TASK', 'BROKER_TASK_STOP', 'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT', 'LAZYWRITER_SLEEP' ) GROUP BY wait_type ORDER BY SUM(wait_time_ms) DESC

查看DMOs所有物件清單

查看DMOs所有物件清單如下:

SELECT [name] ,

 CASE [type]

 WHEN 'V' THEN 'DMV'

 WHEN 'IF' THEN 'DMF'

 END AS [DMO Type]

FROM [sys].[sysobjects]

WHERE [name] LIKE 'dm_%'

ORDER BY [name] ;