Monitor SQL Performance

Here is a list of Windows’ performance counters to use in monitoring performance of an SQL server. Create Performance Collection rules targeted to a SQL Server computer group for the following performance counters: LogicalDisk(*)Avg Disk sec/Read Should be under 20ms. Beyond 50ms is very bad LogicalDisk(*)Avg Disk sec/Write Should be under 20ms. Beyond 50ms is … Read more Monitor SQL Performance

SQL Server: Find Highest Wait on Server

From Paul Randal’s survey of highest wait on server TSQL.   WITH Waits AS (SELECT wait_type, wait_time_ms / 1000.0 AS WaitS, (wait_time_ms – signal_wait_time_ms) / 1000.0 AS ResourceS, signal_wait_time_ms / 1000.0 AS SignalS, waiting_tasks_count AS WaitCount, 100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage, ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum FROM sys.dm_os_wait_stats WHERE … Read more SQL Server: Find Highest Wait on Server

SQL Server: Top 10 IO Queries

From Sunil’s blog to identify the top 10 queries generating most IO.   SELECT TOP 10 (total_logical_reads/execution_count) AS avg_logical_reads, (total_logical_writes/execution_count) AS avg_logical_writes, (total_physical_reads/execution_count) AS avg_phys_reads, execution_count, statement_start_offset as stmt_start_offset, (SELECT SUBSTRING(text, statement_start_offset/2 + 1, (CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(MAX),text)) * 2 ELSE statement_end_offset END – statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS query_text, plan_handle FROM sys.dm_exec_query_stats … Read more SQL Server: Top 10 IO Queries

SQL Server: Cumulative IO By Database since Last Restart

Here’s a TSQL script to get cumulative IO by database since last restart. WITH Agg_IO_Stats AS ( SELECT DB_NAME(database_id) AS database_name, CAST(SUM(num_of_bytes_read + num_of_bytes_written) / 1048576 / 1024. AS DECIMAL(12, 2)) AS io_in_gb FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS DM_IO_Stats GROUP BY database_id ), Rank_IO_Stats AS ( SELECT ROW_NUMBER() OVER(ORDER BY io_in_gb DESC) AS row_num, database_name, io_in_gb, … Read more SQL Server: Cumulative IO By Database since Last Restart