Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- select *
- from sys.database_connection_stats_ex
- where start_time >= CAST(FLOOR(CAST(getdate() AS float)) AS DATETIME)
- order by start_time desc
- select *
- from sys.event_log
- where event_type <> 'connection_successful' and
- start_time >= CAST(FLOOR(CAST(getdate() AS float)) AS DATETIME)
- order by start_time desc
- SELECT
- (COUNT(end_time) - SUM(CASE WHEN avg_cpu_percent > 80 THEN 1 ELSE 0 END) * 1.0) / COUNT(end_time) AS 'CPU Fit Percent',
- (COUNT(end_time) - SUM(CASE WHEN avg_log_write_percent > 80 THEN 1 ELSE 0 END) * 1.0) / COUNT(end_time) AS 'Log Write Fit Percent',
- (COUNT(end_time) - SUM(CASE WHEN avg_data_io_percent > 80 THEN 1 ELSE 0 END) * 1.0) / COUNT(end_time) AS 'Physical Data Read Fit Percent'
- FROM sys.dm_db_resource_stats
- -- Top 10 long running queries
- SELECT TOP 10 q.query_id, p.plan_id,
- rs.count_executions,
- qsqt.query_sql_text,
- CONVERT(NUMERIC(10,2), (rs.avg_cpu_time/1000)) as 'avg_cpu_time_seconds',
- CONVERT(NUMERIC(10,2),(rs.avg_duration/1000)) as 'avg_duration_seconds',
- CONVERT(NUMERIC(10,2),rs.avg_logical_io_reads ) as 'avg_logical_io_reads',
- CONVERT(NUMERIC(10,2),rs.avg_logical_io_writes ) as 'avg_logical_io_writes',
- CONVERT(NUMERIC(10,2),rs.avg_physical_io_reads ) as 'avg_physical_io_reads',
- CONVERT(NUMERIC(10,0),rs.avg_rowcount ) as 'avg_rowcount'
- from sys.query_store_query q
- JOIN sys.query_store_plan p ON q.query_id = p.query_id
- JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
- INNER JOIN sys.query_store_query_text qsqt
- ON q.query_text_id = qsqt.query_text_id
- WHERE rs.last_execution_time > dateadd(hour, -1, getutcdate())
- ORDER BY rs.avg_duration DESC
Add Comment
Please, Sign In to add comment