#MSSQL #Research #OneStop Quick Analysis

quick_analysis.sql

SELECT
  req.session_id AS [session],
  ses.program_name AS [program],
  jobs.name AS [job],
  sqltext.TEXT AS [query],
  DB_NAME(req.database_id) AS [database],
  req.status,
  wg.name AS [resource_group],
  req.command,
  CONVERT(varchar(10), (req.cpu_time / 86400000)) + ':' +
  CONVERT(varchar(10), ((req.cpu_time % 86400000) / 3600000)) + ':' +
  CONVERT(varchar(10), (((req.cpu_time % 86400000) % 3600000) / 60000)) + ':' +
  CONVERT(varchar(10), ((((req.cpu_time % 86400000) % 3600000) % 60000) / 1000)) + ':' +
  CONVERT(varchar(10), (((req.cpu_time % 86400000) % 3600000) % 1000)) AS [cpu_DD:HH:MM:SS:MS],
  CONVERT(varchar(10), (req.total_elapsed_time / 86400000)) + ':' +
  CONVERT(varchar(10), ((req.total_elapsed_time % 86400000) / 3600000)) + ':' +
  CONVERT(varchar(10), (((req.total_elapsed_time % 86400000) % 3600000) / 60000)) + ':' +
  CONVERT(varchar(10), ((((req.total_elapsed_time % 86400000) % 3600000) % 60000) / 1000)) + ':' +
  CONVERT(varchar(10), (((req.total_elapsed_time % 86400000) % 3600000) % 1000)) AS [elapsed_DD:HH:MM:SS:MS],
  ses.login_name AS [login],
  ses.host_name AS [host]
FROM sys.dm_exec_requests req
LEFT JOIN sys.dm_exec_sessions ses
  ON ses.session_id = req.session_id
LEFT JOIN msdb.dbo.sysjobs jobs
  ON SUBSTRING(ISNULL(ses.[program_name], ''), CHARINDEX('0x', ISNULL(ses.[program_name], '')) + 18, 16)
  = SUBSTRING(REPLACE(ISNULL(jobs.[job_id], ''), '-', ''), 17, 16)
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
LEFT JOIN sys.resource_governor_workload_groups AS wg ON req.group_id = wg.group_id
WHERE req.session_id != @@spid
ORDER BY [elapsed_DD:HH:MM:SS:MS] DESC
GO
SELECT 
  DB_NAME() AS [database], 
  name AS [file], 
  size*8.0/1024 AS [size_mb],  
  size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS [free_mb],
  size*8.0/1024/1024 AS [size_gb],
  (size*8.0/1024/1024) - (CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)*8.0/1024/1024) AS [free_gb]
FROM sys.database_files; 
GO
SELECT
  session_id AS [session],
  DB_NAME(database_id) AS [database],
  command,
  percent_complete,
  CAST((estimated_completion_time / 3600000) AS varchar) + ' hour(s), '
  + CAST((estimated_completion_time % 3600000) / 60000 AS varchar) + 'min, '
  + CAST((estimated_completion_time % 60000) / 1000 AS varchar) + ' sec' AS EstimatedTimeToGo,
  DATEADD(ms, estimated_completion_time, GETDATE()) AS EstimatedEndTime,
  start_time,
  status,
  cpu_time
FROM sys.dm_exec_requests
WHERE percent_complete > 0
ORDER BY session_id DESC
GO