Guangning Yu's Blog
Home
Code
Data
Setup
Industry
Archive
SQL Server Queries
2020-11-18 14:26:27
|
sql_server
### Check currently running queries reference: https://stackoverflow.com/a/29400789 ``` SELECT SPID = er.session_id ,STATUS = ses.STATUS ,[Login] = ses.login_name ,Host = ses.host_name ,BlkBy = er.blocking_session_id ,DBName = DB_Name(er.database_id) ,CommandType = er.command ,ObjectName = OBJECT_NAME(st.objectid) ,CPUTime = er.cpu_time ,StartTime = er.start_time ,TimeElapsed = CAST(GETDATE() - er.start_time AS TIME) ,SQLStatement = st.text FROM sys.dm_exec_requests er OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id = er.session_id LEFT JOIN sys.dm_exec_connections con ON con.session_id = ses.session_id WHERE st.text IS NOT NULL ``` ### Check currently running jobs reference: https://stackoverflow.com/a/54684796 ``` WITH CTE_Sysession (AgentStartDate) AS ( SELECT MAX(AGENT_START_DATE) AS AgentStartDate FROM MSDB.DBO.SYSSESSIONS ) SELECT sjob.name AS JobName ,CASE WHEN SJOB.enabled = 1 THEN 'Enabled' WHEN sjob.enabled = 0 THEN 'Disabled' END AS JobEnabled ,sjob.description AS JobDescription ,CASE WHEN ACT.start_execution_date IS NOT NULL AND ACT.stop_execution_date IS NULL THEN 'Running' WHEN ACT.start_execution_date IS NOT NULL AND ACT.stop_execution_date IS NOT NULL AND HIST.run_status = 1 THEN 'Stopped' WHEN HIST.run_status = 0 THEN 'Failed' WHEN HIST.run_status = 3 THEN 'Canceled' END AS JobActivity ,DATEDIFF(MINUTE,act.start_execution_date, GETDATE()) DurationMin ,hist.run_date AS JobRunDate ,run_DURATION/10000 AS Hours ,(run_DURATION%10000)/100 AS Minutes ,(run_DURATION%10000)%100 AS Seconds ,hist.run_time AS JobRunTime ,hist.run_duration AS JobRunDuration ,'tulsql11\dba' AS JobServer ,act.start_execution_date AS JobStartDate ,act.last_executed_step_id AS JobLastExecutedStep ,act.last_executed_step_date AS JobExecutedStepDate ,act.stop_execution_date AS JobStopDate ,act.next_scheduled_run_date AS JobNextRunDate ,sjob.date_created AS JobCreated ,sjob.date_modified AS JobModified FROM MSDB.DBO.syssessions AS SYS1 INNER JOIN CTE_Sysession AS SYS2 ON SYS2.AgentStartDate = SYS1.agent_start_date JOIN msdb.dbo.sysjobactivity act ON act.session_id = SYS1.session_id JOIN msdb.dbo.sysjobs sjob ON sjob.job_id = act.job_id LEFT JOIN msdb.dbo.sysjobhistory hist ON hist.job_id = act.job_id AND hist.instance_id = act.job_history_id WHERE ACT.start_execution_date IS NOT NULL AND ACT.stop_execution_date IS NULL ORDER BY ACT.start_execution_date DESC ``` ### Check indexes usage reference: https://www.mssqltips.com/sqlservertip/1239/how-to-get-index-usage-information-in-sql-server/ ``` SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], I.[NAME] AS [INDEX NAME], USER_SEEKS, USER_SCANS, USER_LOOKUPS, USER_UPDATES FROM SYS.DM_DB_INDEX_USAGE_STATS AS S INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID WHERE OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1 AND OBJECT_NAME(S.[OBJECT_ID]) like 'table_name_prefix%'; ``` ### Create temp table ``` drop table if exists dbo.temp_gyu_1; select top 100 * into dbo.temp_gyu_1 from [ZAWTW_RDW_branch].[dbo].[FactSalesTransaction]; ```
Previous:
AWS Solution Architect Professional Notes
Next:
Mount the Amazon EFS File System on the EC2 Instance