Azure SQL Quick Analysis

azure_sql_quick_analysis.sql

SELECT
  req.session_id AS [session],
  ses.program_name AS [program],
  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
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 node_id,
    physical_operator_name,
    SUM(row_count) row_count, 
    SUM(estimate_row_count) AS estimate_row_count,
    CAST(SUM(row_count)*100 AS float)/NULLIF(SUM(estimate_row_count),0)  as estimate_percent_complete
FROM sys.dm_exec_query_profiles
GROUP BY node_id,physical_operator_name
HAVING SUM(estimate_row_count) <> 0 AND SUM(row_count) <> 0
ORDER BY node_id desc;
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 status NOT IN ('background', 'sleeping')
ORDER BY session_id DESC
GO
SELECT 
	eqs.query_plan, 
	er.session_id,
	er.database_id,
	er.start_time,
	er.[status], 
	er.wait_type,
	er.wait_resource,
	(er.cpu_time/1000) AS cpu_time_sec,
	(er.total_elapsed_time/1000)/60 AS elapsed_time_minutes,
	(er.logical_reads*8)/1024 AS logical_reads_KB,
	er.granted_query_memory,
	er.dop,
	er.row_count, 
	er.query_hash
FROM sys.dm_exec_requests er
	CROSS APPLY sys.dm_exec_query_statistics_xml(session_id) eqs
WHERE er.session_id <> @@spid;
GO
SELECT [s_tst].[session_id],
[database_name] = DB_NAME (s_tdt.database_id),
[s_tdt].[database_transaction_begin_time], 
[sql_text] = [s_est].[text] 
FROM sys.dm_tran_database_transactions [s_tdt]
INNER JOIN sys.dm_tran_session_transactions [s_tst] ON [s_tst].[transaction_id] = [s_tdt].[transaction_id]
INNER JOIN sys.dm_exec_connections [s_ec] ON [s_ec].[session_id] = [s_tst].[session_id]
CROSS APPLY sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est];
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