January 13, 2016 at 4:58 am
i've wuold like to use this query to collect activity into a table evevry 1-2 minutes
the problem is that if i run only select it tooks less than 1 second, but when i do insert into select, the same tooks about 6 or more seconds
create view [dbo].[activity] as
WITH
ctethread_addrs (SPID, THREAD_ADDRESS)
AS
(
select B.SPID, A.thread_address from SYS.SYSPROCESSES B, SYS.DM_OS_THREADS A
where b.dbid > 4
and b.program_name not like '%Microsoft%'
and b.kpid = A.os_thread_id
),
cteActiveSessions (log_time,session_id,text,status,cpu_time,start_time,wait_type,
host_name,program_name,login_name,dbname,command,blocked_by,query_plan)
AS
(
SELECT GETDATE() as log_time,
r.session_id,
s.text,
r.status,
r.cpu_time,
start_time,
r.wait_type,
se.host_name,
program_name = se.program_name,
se.login_name,
Db_name(r.database_id) AS dbname,
r.command,
r.blocking_session_id AS Blocked_By,
p.query_plan AS query_plan
FROM sys.dm_exec_requests r WITH (NOLOCK)
INNER JOIN sys.dm_exec_sessions se WITH (NOLOCK) ON r.session_id = se.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) s
OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) p
WHERE r.session_id <> @@SPID
AND se.is_user_process = 1
and login_name <> 'NT AUTHORITY\SYSTEM'
)
SELECT
ac.log_time,
ta.thread_address,
ac.session_id,
ac.text,
ac.status,
ac.cpu_time,
ac.start_time,
ac.wait_type,
ac.host_name,
ac.program_name,
ac.login_name,
ac.dbname,
ac.command,
ac.blocked_by,
ac.query_plan
FROM cteActiveSessions AS ac
INNER JOIN ctethread_addrs AS ta
ON ac.session_id = ta.spid
CREATE TABLE activty_log (
[log_time] [datetime] NULL,
[thread_address] [bigint] null,
[session_id] [int] NULL,
[query] [nvarchar](max) NULL,
[status] [nvarchar](30) NULL,
[cpu_time] [int] NULL,
[total_elapsed_time] [datetime] NULL,
[wait_type] [nvarchar](50) NULL,
[HOST_NAME] [nvarchar](50) NULL,
[PROGRAM_NAME] [nvarchar](70) NULL,
[LOGIN_NAME] [nvarchar](70) NULL,
[DBNAME] [nvarchar](70) NULL,
[COMMAND] [nvarchar](70) NULL,
[BLOCKED_BY] [int] NULL,
[query_plan] xml
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
insert into activty_log
select * from activity;
the slow part is on SYS.DM_OS_THREADS, indeed if i remove it the insert runs fast as select,
any ideas?
thank you
January 14, 2016 at 2:05 am
The plain SELECT and the INSERT...SELECT are probably using two different query plans: compare the two and find out where the perf problem lies.
Another possibility is that the INSERT itself is slowing down the whole process: too many indexes on that table? Transaction log on a slow disk? You can trace the wait stats for that single INSERT...SELECT using the technique described here[/url] and see where SQL Server is spending its time.
-- Gianluca Sartori
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply