Dedicated SQL pool Synapse Workspace

//Query run times with join to SynapseSessionDW_CL table to get username info. 
//Please add the server name in the variable please note that this will be case sensitive. 
let AzuresynapseDW = 'servernameherelowercase';  //<--add server name lower case here. 
SynapseSqlPoolExecRequests
| where Category == 'ExecRequests'
| where LogicalServerName == AzuresynapseDW
| where StatementType !in ('Batch','Execute')
| summarize TimeGenerated=max(TimeGenerated),
Start_Time=max(StartTime),
End_Time=max(EndTime),
Command=max(Command),
Last_Status=min(Status),
Statement_Type=max(StatementType),
Resource_class=max(ResourceClass)
by RequestId
| extend elapsedTime_min = (case(End_Time =='1/1/1601, 12:00:00.000 AM', now(),End_Time) - case(Start_Time =='1/1/1601, 12:00:00.000 AM', now(),Start_Time ))/1m
| where elapsedTime_min > 1
| order by elapsedTime_min desc
| join kind=leftouter (
SynapseSessionDW_CL
|where isnotnull(Login_Name_s)
| project Login_Name_s, RequestId_s, result_cache_hit_d
)
on $left.RequestId==$right.RequestId_s



//Same as the above without the join. 
//Please add the server name in the variable please note that this will be case sensitive.  
let AzuresynapseDW = 'servernameherelowercase';  //<--add server name lower case here. 
SynapseSqlPoolExecRequests
| where Category == 'ExecRequests'
| where LogicalServerName == AzuresynapseDW
| where StatementType !in ('Batch','Execute')
| summarize TimeGenerated=max(TimeGenerated),
Start_Time=max(StartTime),
End_Time=max(EndTime),
Command=max(Command),
Last_Status=min(Status),
Statement_Type=max(StatementType),
Resource_class=max(ResourceClass)
by RequestId
| extend elapsedTime_min = (case(End_Time =='1/1/1601, 12:00:00.000 AM', now(),End_Time) - case(Start_Time =='1/1/1601, 12:00:00.000 AM', now(),Start_Time ))/1m
| where elapsedTime_min > 1
| order by elapsedTime_min desc


//Session record processed with join on username. 
let AzuresynapseDW = 'servernameherelowercase';  //<--add server name lower case here. 
SynapseSqlPoolRequestSteps
| where Category  == 'RequestSteps'
| where LogicalServerName  == AzuresynapseDW
| where RowCount > 1
| project  TimeGenerated,LogicalServerName, RequestId, StepIndex, RowCount, Command
| order  by RowCount desc  
| join kind=leftouter (
SynapseSessionDW_CL
|where isnotnull(Login_Name_s)
| project Login_Name_s, RequestId_s, Command_s
)
on $left.RequestId==$right.RequestId_s


//Same as the above without the join
let AzuresynapseDW = 'servernameherelowercase';  //<--add server name lower case here. 
SynapseSqlPoolRequestSteps
| where Category  == 'RequestSteps'
| where LogicalServerName  == AzuresynapseDW
| where RowCount > 1
| project  TimeGenerated,LogicalServerName, RequestId, StepIndex, RowCount, Command
| order  by RowCount desc  


//The below will work on both formerly SQL DW & a SQL Pool created in the Workspace. 

//Custom table using API Call to get session level waits
SynapseWaitsDW_CL
| project  TimeGenerated, request_id_s, session_id_s, Login_Name_s, TimeGenerated-request_time_t , state_s, type_s, status_s, object_type_s, command_s, Login_Time_t
| project-rename  waittime=Column1


//Custom table using API Call to get tempdb usage
SynapseTempDBDW_CL 
| project  TimeGenerated,request_id_s, LoginName_s, session_id_d, submit_time_t, start_time_t, command_s, Space_Allocated_For_User_Objects_KB_d, Space_Deallocated_For_User_Objects_KB_d, Space_Allocated_For_Internal_Objects_KB_d, Space_Deallocated_For_Internal_Objects_KB_d, MemoryUsage_in_KB_d, SessionType_s


//Custom table using API Call to get memory usage
SynapseMemoryDW_CL 
| project TimeGenerated, session_id_d, dop_d, request_id_d, grant_time_t, requested_memory_kb_d, required_memory_kb_d, used_memory_kb_d, max_used_memory_kb_d, query_cost_d, sql_handle_s, text_s, login_name_s


//Custom table using API Call to get stored procedure usage
SynapseStoredProcDW_CL 
| project TimeGenerated, trim('.0' ,replace_regex(tostring(object_id_d),',', '')) , execution_count_d, cached_time_t,  last_elapsed_time_d, total_elapsed_time_d,  max_worker_time_d,  total_physical_reads_d, min_physical_reads_d, max_physical_reads_d, total_num_physical_reads_d, last_physical_reads_d,  total_logical_writes_d, max_logical_writes_d, total_logical_reads_d, max_logical_reads_d, last_logical_reads_d, min_logical_reads_d,  total_spills_d   
| project-rename object_id=Column1


//Custom table using API Call to get stored procedure usage in seconds for time and only top 20
SynapseStoredProcDW_CL 
| project TimeGenerated, trim('.0' ,replace_regex(tostring(object_id_d),',', '')) , execution_count_d, cached_time_t,  toint(last_elapsed_time_d/1000000),  toint(total_elapsed_time_d/1000000),   toint(max_worker_time_d/1000000),  total_physical_reads_d, min_physical_reads_d, max_physical_reads_d, total_num_physical_reads_d, last_physical_reads_d,  total_logical_writes_d, max_logical_writes_d, total_logical_reads_d, max_logical_reads_d, last_logical_reads_d, min_logical_reads_d,  total_spills_d   
| project-rename object_id=Column1, last_elapsed_time_sec=Column2, total_elapsed_time_sec=Column3, max_worker_time_sec=Column4
| order by total_elapsed_time_sec desc
| take 20

/////////////////////////////////////////////////Data Point Charts Section Below///////////////////////////////////////////////////////////////////////////////////////

//Total Waits by QID over time: 
SynapseSqlPoolExecRequests
| where Category == 'ExecRequests'
| where LogicalServerName == AzuresynapseDW
| where StatementType !in ('Batch','Execute')
| summarize TimeGenerated=max(TimeGenerated),
StartTime=min(StartTime),
End_Time=max(EndTime),
Command=max(Command),
Last_Status=min(Status),
Statement_Type=max(StatementType),
Resource_class=max(ResourceClass)
by RequestId
| join kind=leftouter  (SynapseWaitsDW_CL 
| project  request_id_s, bin(TimeGenerated, 5m),  type_s, waittime=datetime_diff('minute', TimeGenerated,request_time_t))
on $left.RequestId==$right.request_id_s 
| extend bin(TimeGenerated, 5m), elapsedTime_min = ((case(End_Time =='1/1/1601, 12:00:00.000 AM', now(),End_Time) - case(StartTime =='1/1/1601, 12:00:00.000 AM', now(),StartTime ))/1m)-case( isnull(waittime), 0,waittime) , elapsedTime_min_check = ((case(End_Time =='1/1/1601, 12:00:00.000 AM', now(),End_Time) - case(StartTime =='1/1/1601, 12:00:00.000 AM', now(),StartTime ))/1m)
| where waittime > 1
| order  by elapsedTime_min_check desc 
| extend elapsedTime_min, request_id_s, bin(TimeGenerated, 5m)
| take 30
| order by waittime desc 
| render columnchart with ( kind=stacked )

//Total runtime minus waits time only top 30 
let AzuresynapseDW = 'servernameherelowercase';  //<--add server name lower case here. 
SynapseSqlPoolExecRequests
| where Category == 'ExecRequests'
| where LogicalServerName == AzuresynapseDW
| where StatementType !in ('Batch','Execute')
| summarize TimeGenerated=max(TimeGenerated),
StartTime=min(StartTime),
End_Time=max(EndTime),
Command=max(Command),
Last_Status=min(Status),
Statement_Type=max(StatementType),
Resource_class=max(ResourceClass)
by RequestId
| join kind=leftouter  (SynapseWaitsDW_CL 
| project  request_id_s, bin(TimeGenerated, 5m),  type_s, waittime=datetime_diff('minute', TimeGenerated,request_time_t))
on $left.RequestId==$right.request_id_s 
| extend bin(TimeGenerated, 5m), elapsedTime_min = ((case(End_Time =='1/1/1601, 12:00:00.000 AM', now(),End_Time) - case(StartTime =='1/1/1601, 12:00:00.000 AM', now(),StartTime ))/1m)-case( isnull(waittime), 0,waittime) , elapsedTime_min_check = ((case(End_Time =='1/1/1601, 12:00:00.000 AM', now(),End_Time) - case(StartTime =='1/1/1601, 12:00:00.000 AM', now(),StartTime ))/1m)
| where elapsedTime_min_check > 1
| order  by elapsedTime_min_check desc 
| extend elapsedTime_min, request_id_s, bin(TimeGenerated, 5m)
| take 30
| order by elapsedTime_min desc 
| project-away waittime
| render columnchart with ( kind=stacked  )


//Total Waits
SynapseWaitsDW_CL
| summarize  TotalWaits=count() by type_s, request_id_s, TimeGenerated
| render columnchart


//Total Waits
SynapseWaitsDW_CL
| summarize  TotalWaits=count() by type_s, request_id_s, TimeGenerated
| render timechart

//Total Memory Used
SynapseMemoryDW_CL 
| summarize  sum(used_memory_kb_d) by request_id_d, TimeGenerated
|render  timechart  

//Total Memory Requested
SynapseMemoryDW_CL 
| summarize  sum(requested_memory_kb_d) by request_id_d, TimeGenerated
|render  timechart  

//Total Required Memory 
SynapseMemoryDW_CL 
| summarize  sum(requested_memory_kb_d) by request_id_d, TimeGenerated
|render  timechart  


//Total Store Procedures Executions
SynapseStoredProcDW_CL
 | summarize sum(execution_count_d) by trim('.0' ,replace_regex(tostring(object_id_d),',', '')), TimeGenerated
 | render columnchart   


//Total Store Procedures Logical Reads
 SynapseStoredProcDW_CL
 | summarize sum( total_logical_reads_d) by trim('.0' ,replace_regex(tostring(object_id_d),',', '')), TimeGenerated
 | render columnchart   


//Total Store Procedures Physical Reads
  SynapseStoredProcDW_CL
 | summarize sum( total_physical_reads_d) by trim('.0' ,replace_regex(tostring(object_id_d),',', '')), TimeGenerated
 | render columnchart   


//Total Store Procedures TempDB Spills
  SynapseStoredProcDW_CL
 | summarize sum( total_spills_d) by trim('.0' ,replace_regex(tostring(object_id_d),',', '')), TimeGenerated
 | render columnchart   


//Total Store Procedures Elapse Time
 SynapseStoredProcDW_CL
 | summarize sum(total_elapsed_time_d) by trim('.0' ,replace_regex(tostring(object_id_d),',', '')), TimeGenerated
 | render columnchart   


//Total Request Records Returned
let AzuresynapseDW = 'servernameherelowercase';  //<--add server name lower case here. 
SynapseSqlPoolRequestSteps
| where Category  == 'RequestSteps'
| where LogicalServerName  == AzuresynapseDW
| where RowCount > 1
| summarize  sum(RowCount) by  RequestId, TimeGenerated
| render columnchart   

//Total Duration For Request
let AzuresynapseDW = 'servernameherelowercase';  //<--add server name lower case here. 
SynapseSqlPoolExecRequests
| where Category == 'ExecRequests'
| where LogicalServerName == AzuresynapseDW
| where StatementType !in ('Batch','Execute')
| summarize sum( elapsedTime_min = (case(EndTime =='1/1/1601, 12:00:00.000 AM', now(),EndTime) - case(StartTime =='1/1/1601, 12:00:00.000 AM', now(),StartTime ))/1m)
by TimeGenerated, RequestId
| render columnchart