1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77
|
CREATE TABLE [dbo].[QUERY_FOLLOW](
[QFO_I_ID] [int] IDENTITY(1,1) NOT NULL,
[QFO_I_SPID] [int] NULL,
[QFO_C_LOGINNAME] [varchar](128) NULL,
[QFO_I_BLOCKED] [int] NULL,
[QFO_I_CPU] [int] NULL,
[QFO_I_PHYSICAL_IO] [int] NULL,
[QFO_D_last_request_start_time] [datetime] NULL,
[QFO_C_LAUNCHED] [varchar](128) NULL,
[QFO_C_HOSTNAME] [varchar](128) NULL,
[QFO_C_STATUS] [varchar](128) NULL,
[QFO_C_Command] [varchar](128) NULL,
[QFO_C_DB] [varchar](128) NULL,
[QFO_C_PROGRAMNAME] [varchar](128) NULL,
[QFO_L_QUERY] [varchar](8000) NULL,
[QFO_D_CREATE] [datetime] NOT NULL DEFAULT GETDATE(),
CONSTRAINT [PK_QUERY_FOLLOW] PRIMARY KEY CLUSTERED
(
[QFO_I_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE PROCEDURE [dbo].[GET_QUERY_FOLLOW]
as
BEGIN
INSERT INTO QUERY_FOLLOW
(QFO_I_SPID,QFO_C_LOGINNAME,QFO_I_BLOCKED,QFO_I_CPU,QFO_I_PHYSICAL_IO,QFO_D_last_request_start_time,QFO_C_LAUNCHED
,QFO_C_HOSTNAME,QFO_C_STATUS,QFO_C_Command,QFO_C_DB,QFO_C_PROGRAMNAME,QFO_L_QUERY)
SELECT
PROCESS.SPID,
CONVERT(SYSNAME, RTRIM(PROCESS.LOGINAME)) LOGINNAME,
PROCESS.BLOCKED,
PROCESS.CPU,
PROCESS.PHYSICAL_IO,
SESSION.last_request_start_time,
CASE(CONVERT(VARCHAR, DATEDIFF(second, SESSION.last_request_start_time, GETDATE())/3600) + 'h ')
WHEN '0h' THEN ''
ELSE CONVERT(VARCHAR, DATEDIFF(second , SESSION.last_request_start_time, GETDATE())/3600) + 'h '
END +
CASE(CONVERT(VARCHAR, DATEDIFF(second, SESSION.last_request_start_time, GETDATE())/60%60) + 'min ')
WHEN '0min' THEN ''
ELSE CONVERT(VARCHAR, DATEDIFF(second, SESSION.last_request_start_time, GETDATE())/60%60) + 'min '
END +
CONVERT(VARCHAR, DATEDIFF(second, SESSION.last_request_start_time, GETDATE())%60) + 's' AS LAUNCHED,
PROCESS.HOSTNAME,
UPPER(PROCESS.STATUS) STATUS,
UPPER(PROCESS.CMD) Command,
DB_NAME(PROCESS.DBID) DB,
CASE SUBSTRING(PROCESS.PROGRAM_NAME, 1, 38)
WHEN 'Microsoft SQL Server Management Studio' THEN 'SSMS Query'
ELSE PROCESS.PROGRAM_NAME
END PROGRAM_NAME,
CONVERT(VARCHAR(8000), ST.text) SQL
FROM sys.sysprocesses (nolock) PROCESS
JOIN sys.dm_exec_sessions SESSION (nolock) ON PROCESS.spid = SESSION.session_id
CROSS APPLY sys.dm_exec_sql_text(PROCESS.sql_handle) ST
WHERE SPID > 50
AND SPID != @@SPID
AND CMD != 'AWAITING COMMAND'
AND PROCESS.PROGRAM_NAME NOT LIKE 'DatabaseMail90%'
ORDER BY PROCESS.SPID
END;
CREATE VIEW [dbo].[VIEW_CPU_QUERY_FOLLOW]
AS
SELECT
QFO_I_SPID,QFO_C_LOGINNAME,QFO_I_BLOCKED,MAX(QFO_I_CPU) as conso_cpu,QFO_D_last_request_start_time as debut,MAX(QFO_D_CREATE) as fin
,QFO_C_HOSTNAME,QFO_C_STATUS,QFO_C_Command,QFO_C_DB,QFO_C_PROGRAMNAME,QFO_L_QUERY
FROM QUERY_FOLLOW
group by
QFO_I_SPID,QFO_C_LOGINNAME,QFO_I_BLOCKED,QFO_D_last_request_start_time
,QFO_C_HOSTNAME,QFO_C_STATUS,QFO_C_Command,QFO_C_DB,QFO_C_PROGRAMNAME,QFO_L_QUERY |
Partager