Guest User

Untitled

a guest
Aug 14th, 2018
94
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.59 KB | None | 0 0
  1. select *
  2. from sys.database_connection_stats_ex
  3. where start_time >= CAST(FLOOR(CAST(getdate() AS float)) AS DATETIME)
  4. order by start_time desc
  5.  
  6. select *
  7. from sys.event_log
  8. where event_type <> 'connection_successful' and
  9. start_time >= CAST(FLOOR(CAST(getdate() AS float)) AS DATETIME)
  10. order by start_time desc
  11.  
  12. SELECT
  13. (COUNT(end_time) - SUM(CASE WHEN avg_cpu_percent > 80 THEN 1 ELSE 0 END) * 1.0) / COUNT(end_time) AS 'CPU Fit Percent',
  14. (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',
  15. (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'
  16. FROM sys.dm_db_resource_stats
  17.  
  18. -- Top 10 long running queries
  19. SELECT TOP 10 q.query_id, p.plan_id,
  20. rs.count_executions,
  21. qsqt.query_sql_text,
  22. CONVERT(NUMERIC(10,2), (rs.avg_cpu_time/1000)) as 'avg_cpu_time_seconds',
  23. CONVERT(NUMERIC(10,2),(rs.avg_duration/1000)) as 'avg_duration_seconds',
  24. CONVERT(NUMERIC(10,2),rs.avg_logical_io_reads ) as 'avg_logical_io_reads',
  25. CONVERT(NUMERIC(10,2),rs.avg_logical_io_writes ) as 'avg_logical_io_writes',
  26. CONVERT(NUMERIC(10,2),rs.avg_physical_io_reads ) as 'avg_physical_io_reads',
  27. CONVERT(NUMERIC(10,0),rs.avg_rowcount ) as 'avg_rowcount'
  28. from sys.query_store_query q
  29. JOIN sys.query_store_plan p ON q.query_id = p.query_id
  30. JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
  31. INNER JOIN sys.query_store_query_text qsqt
  32. ON q.query_text_id = qsqt.query_text_id
  33. WHERE rs.last_execution_time > dateadd(hour, -1, getutcdate())
  34. ORDER BY rs.avg_duration DESC
Add Comment
Please, Sign In to add comment