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],
    DATABASEPROPERTYEX(DB_NAME(), 'Updateability') AS [updateability],
    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 *
FROM sys.dm_operation_status
GO
SELECT TOP 5
    p.query_plan
    ,[creation_time]
    ,[last_execution_time]
    ,[execution_count]
    ,[last_elapsed_time]
    ,t.[text]
FROM sys.dm_exec_query_stats AS s
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) as t
CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) as p
WHERE 
s.execution_count > 1
AND s.last_execution_time >= CAST(DATEADD(MINUTE, -30, GETUTCDATE()) AS DATETIME)
ORDER BY s.last_elapsed_time DESC
GO