Synapse SQL Pool KQL Main.kql

//You can remove the top function from the commands to get the entire output. 
//In addition, you can uncomment the last line to view the graphical representation in Log Analytics. 
//If using Power BI, you can do not need the last line render column charts. 
//Please note that if you are sending multiple synapse logs to the same workspace, you need to add the logical server name to the query to that results to not get mixed up


//Request run times
SynapseSqlPoolExecRequests
| where Category == 'ExecRequests'
| 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),
ExplainOutput=max(ExplainOutput)
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

//Total row count 
SynapseSqlPoolRequestSteps
| where Category  == 'RequestSteps'
| where RowCount > 1
| project  TimeGenerated,LogicalServerName, RequestId, StepIndex, RowCount, Command
| order  by RowCount desc  



//Get the percentage of memory usage. Please note that you need to get the overall memory on your current systems DWU. You can use something like 
// select * from sys.dm_pdw_nodes_resource_governor_resource_pools and get that info from the default pool max memory KB and add it into the second line replacing 55481872 both times 

SynapseMemoryDW_CL 
//| summarize max(max_used_memory_kb_d) -1 by request_id_s
| summarize abs(((todouble(55481872)-max(max_used_memory_kb_d))/todouble(55481872)*100) - (100))  by request_id_d,  bin(TimeGenerated, 5m) // please alter 55481872 to the correct memroy on the system, this should be dynamic next version
| project-rename percentused=Column1
//| render columnchart with ( kind=stacked) 


//Stored procedure usage in minutes with a 30min window for TimeGenerated
SynapseStoredProcDW_CL
| summarize sum(total_elapsed_time_d)/60000000 by trim('.0' ,replace_regex(tostring(object_id_d),',', '')), bin(TimeGenerated, 30m)
| where Column2 >= 10
| project-rename sp_elapsed_time_min=Column2
//| render columnchart with ( kind=stacked)



//Request runtime over query ID over compute level waits
SynapseSqlPoolExecRequests 
| where Category == 'ExecRequests'
| where StatementType !in ('Batch','Execute')
| summarize TimeGenerated=max(TimeGenerated),
SubmitTime=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  (SynapseComputeWaitsDW_CL 
| project  request_id_s, bin(TimeGenerated, 5m),  wait_type_s, wait_time_d)
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(SubmitTime =='1/1/1601, 12:00:00.000 AM', now(),SubmitTime ))/1m) , elapsedTime_min_check = ((case(End_Time =='1/1/1601, 12:00:00.000 AM', now(),End_Time) - case(SubmitTime =='1/1/1601, 12:00:00.000 AM', now(),SubmitTime ))/1m)
| order by elapsedTime_min desc 
| summarize max(elapsedTime_min), sum(wait_time_d/60000)  by    request_id_s
| project-rename total_runtime_min=max_elapsedTime_min, total_wait_time_min=sum_
| top 30 by total_runtime_min desc 
//| render columnchart with ( kind=stacked)   


//Compute level waits, checks for waits over 5 seconds you can alter where condition time as needed. 
SynapseComputeWaitsDW_CL 
| where  wait_type_s != ''
| where toint(wait_time_d) > 5000
| summarize   max(toint(wait_time_d)) by  wait_type_s , request_id_s
| project-rename max_wait_time=max_wait_time_d
//| render columnchart with ( kind=stacked  )


//TempDB usage by Query ID only top 20
SynapseTempDBDW_CL
| summarize  count() by  bin(TimeGenerated, 5m),request_id_s, (Space_Allocated_For_User_Objects_KB_d +  Space_Allocated_For_Internal_Objects_KB_d)/1024 
| project-rename Total_TempDB_Usage=Column1
| order by Total_TempDB_Usage  desc
| project-rename TempDB_in_MB=Total_TempDB_Usage
| top 20
//| render columnchart with ( kind=stacked)


//Resource waits longer than 20 seconds
SynapseWaitsDW_CL
    | project  request_id_s, bin(TimeGenerated, 5m), datetime_diff('second', TimeGenerated,request_time_t)
	| project-rename seconds=Column1
	| where seconds > 30
   	| distinct request_id_s ,seconds,TimeGenerated
       	| summarize sum(seconds) by  request_id_s, bin(TimeGenerated, 5m)
//| render columnchart