Useful queries

 -- Recovery model, log reuse wait description, log file size, log usage size -- and compatibility level for all databases on instance  

SELECT db.[name] AS [Database Name], db.recovery_model_desc AS [Recovery Model], db.log_reuse_wait_desc AS [Log Reuse Wait Description], ls.cntr_value AS [Log Size (KB)], lu.cntr_value AS [Log Used (KB)], CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)AS DECIMAL(18,2)) * 100 AS [Log Used %], db.[compatibility_level] AS [DB Compatibility Level], db.page_verify_option_desc AS [Page Verify Option], db.is_auto_create_stats_on, db.is_auto_update_stats_on, db.is_auto_update_stats_async_on, db.is_parameterization_forced, db.snapshot_isolation_state_desc, db.is_read_committed_snapshot_on, is_auto_shrink_on, is_auto_close_on  

FROM sys.databases AS db WITH (NOLOCK)  

INNER JOIN sys.dm_os_performance_counters AS lu WITH (NOLOCK) ON db.name = lu.instance_name INNER JOIN sys.dm_os_performance_counters AS ls WITH (NOLOCK) ON db.name = ls.instance_name WHERE lu.counter_name LIKE N'Log File(s) Used Size (KB)%'  

AND ls.counter_name LIKE N'Log File(s) Size (KB)%' AND ls.cntr_value > 0 OPTION (RECOMPILE); 

 

 

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] 

 FROM sys.dm_io_virtual_file_stats(null,null) AS fs  

INNER JOIN sys.master_files AS mf WITH (NOLOCK) ON fs.database_id = mf.database_id AND fs.[file_id] = mf.[file_id]  

ORDER BY avg_io_stall_ms DESC OPTION (RECOMPILE);  

Helps determine which database files on -- the entire instance have the most I/O bottlenecks 

 

 

-Get total buffer usage by database for current instance  

SELECT DB_NAME(database_id) AS [Database Name], COUNT(*) * 8/1024.0 AS [Cached Size (MB)]  

FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)  

WHERE database_id > 4 -- system databases  

AND database_id <> 32767 -- ResourceDB  

GROUP BY DB_NAME(database_id)  

ORDER BY [Cached Size (MB)] DESC OPTION (RECOMPILE) 

 

 

-- Find single-use, ad-hoc queries that are bloating the plan cache  

SELECT TOP(20) [text] AS [QueryText], cp.size_in_bytes  

FROM sys.dm_exec_cached_plans AS cp WITH (NOLOCK)  

CROSS APPLY sys.dm_exec_sql_text(plan_handle)  

WHERE cp.cacheobjtype = N'Compiled Plan'  

AND cp.objtype = N'Adhoc' AND cp.usecounts = 1  

ORDER BY cp.size_in_bytes DESC OPTION (RECOMPILE); 

 

 

-- Get VLF count for transaction log for the current database, -- number of rows equals the VLF count. Lower is better! 

DBCC LOGINFO;