SQL server Performance || SQL Server Execution Plan || Query Execution Plan || SQL Server Blocking

SQL server Performance || SQL Server Execution Plan || Query Execution Plan || SQL Server Blocking
QUERY:-

SELECT SPID,ER.percent_complete,
/* This piece of code has been taken from article. Nice code to get time criteria's
*/
CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
+ CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
+ CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
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 est_time_to_go,
DATEADD(second,estimated_completion_time/1000, getdate()) as est_completion_time,
/* End of Article Code */
ER.command,ER.blocking_session_id, SP.DBID,LASTWAITTYPE,
DB_NAME(SP.DBID) AS DBNAME,
((CASE ER.statement_end_offset
ELSE ER.statement_end_offset
END - ER.statement_start_offset)/2) + 1) AS QueryText, DEQP.QUERY_PLAN,
TEXT,CPU,HOSTNAME,LOGIN_TIME,LOGINAME,
SP.status,PROGRAM_NAME,NT_DOMAIN, NT_USERNAME
FROM SYSPROCESSES SP
CROSS APPLY SYS.DM_EXEC_QUERY_PLAN(er.PLAN_HANDLE) DEQP
ORDER BY running_time DESC
----------------------END------------------

SQL, SQL DATABASE, SQL BACKUP, RESTORE, RECOVERY, USERS, LOGINS, AGENT, SQL JOBS, SQL ARCHITECTURE, SQL INSTANCE, SQL UPGRADE, SQL PATCHING, SQL PERFORMANCE, SQL MANITINEMANC, SQL SLOWNESS, SQL HARDWARE, SQL LIFE CYCLE, SQL DR, SQL MIRRORING, SQL REPLICATION SQL ALWAYS ON, SQL LOGSHIPING, SQL CLUSTERING, SQL INSTALLATION, SQL MIGRATION, SQL TO AZURE,CLOUDE,AZURE,AMAZON,GOOGLE, SQL TO AWS,AWS,SQL MIGRATION TO CLOUDE,AZURE FUNDAMENTAL, AZURE CERTIFICATION, AZURE ARCHITECTURE, SQL ON LINUX, SQL LINIX INSTALLATION, SQL PERFORMANCE QUERY, SQL BLOCKING,SQL DEADLOCK,SQL DATABASE CRASHED, SQL DATABASE DOWN,SQL SERVICES , SQL SERVICES STOP, SQL INSTALLATION FAILED, SQL UPGRADE FAILED,

#sqlserver #sqlserverdba #techandart

[gitanshlamba]

Easy to Understand, very well explained.

I have 4 doubts :
(1) in Login_Time, how will we come to know that for how long a particular query is running.
(Do we need to subtract the coming time from the Current Now Time ) ?

(2) If you can explain the Execution Plan diagram in respect of COST
like Cost is 22% for Sort, and 77% for Hash Match (Inner Join) in the current scenario that tou have shared.

(3) Will it show us the number of queries being executed / List of queries being executed like in above you have QueryText, so if in a case 4-5 queries are causing the performance issue, so will we get 4-5 rows aa output while running this Script ?

(4) How to know whether the query that caused the issue have fired any Trigger or Stored Procedure ?

A bit lengthy, but that's what is currently required.

Hoping to get the answers 😊

Thank you once again.!!

[tonyettah]

I appreciate the great work you're doing. thank you

[vinithankam]

This particular result how much time to flush,

If example: Application team facing issue on loading transactions into databases from Application to DB so some times job is failed and some was successfully imported so is it helpful to identify anything to analyse

[tonyettah]

Pls, where i can i find this query? thank you

[gislentim]

Where can we get the query, thanks for this, it was helpful