Oracle has historical table that you can query to find out active session history etc, sqlserver does not have that, the script below build the history table in sqlserver and create procedure that can be scheduled to run by sqlserver agent to collect the snapshot information.
The historical tables are focusing on wait events and cpu scheduler, you can use the following query to check the blocking situation which last more than 10 seconds.
select timeofsnapshot,wait_duration_ms,wait_type,session_id,blocking_session_id,login_name,wait_resource,command,nt_user_name,blocking_nt_user_name,text
from dm_os_waiting_tasks where wait_duration_ms>10000 and wait_type like 'LCK%'
order by wait_duration_ms
--order by snapshot_id desc
the result is something like the screenshot below
you can schedule sqlserver agent to run every 10 seconds to get the snapshot, to collect the snapshot, run procedure “exec collect_dmv_all”
Below are the DDLs to create the database objects
USE [dba]
GO
/****** Object: StoredProcedure [dbo].[collect_dm_sysprocesses_v2] Script Date: 29/10/2013 9:38:40 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[collect_dm_sysprocesses_v2] @snapshot_id int, @timeofsnapshot datetime
as
insert into dm_sysprocesses
select top 8 @snapshot_id,@timeofsnapshot,last_batch, stmt_end,spid,blocked,waittype,waittime,lastwaittype,dbid,hostname,program_name,cmd,nt_username,sql_handle,stmt_start
from sys.sysprocesses order by stmt_end,last_batch desc
GO
/****** Object: StoredProcedure [dbo].[collect_dmosschedulers_v2] Script Date: 29/10/2013 9:38:40 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[collect_dmosschedulers_v2]
@snapshot_id int, @timeofsnapshot datetime
as
insert into dm_os_schedulers
select @snapshot_id,@timeofsnapshot, t1.runnable_tasks_count,t1.is_idle, t1.load_factor,t1.pending_disk_io_count,t1.active_workers_count,t1.work_queue_count
from sys.dm_os_schedulers t1
GO
/****** Object: StoredProcedure [dbo].[collect_dmoswaitingtasks_v2] Script Date: 29/10/2013 9:38:40 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[collect_dmoswaitingtasks_v2] @snapshot_id int, @timeofsnapshot datetime as
insert into dm_os_waiting_tasks
SELECT @snapshot_id ,
@timeofsnapshot,
dm_ws.wait_duration_ms,
dm_ws.wait_type,
dm_es.status,
dm_t.TEXT,
dm_qp.query_plan,
dm_ws.session_ID,
dm_es.cpu_time,
dm_es.memory_usage,
dm_es.logical_reads,
dm_es.total_elapsed_time,
dm_es.program_name,
DB_NAME(dm_r.database_id) DatabaseName,
-- Optional columns
dm_ws.blocking_session_id,
dm_r.wait_resource,
dm_es.login_name,
dm_r.command,
dm_r.last_wait_type,
dm_es.nt_user_name,
dm_es.host_name,
dm_es2.login_name blocking_login_name,
dm_es2.host_name blocking_host_name,
dm_es2.nt_user_name blocking_nt_user_name
FROM sys.dm_os_waiting_tasks dm_ws
INNER JOIN sys.dm_exec_requests dm_r ON dm_ws.session_id = dm_r.session_id
INNER JOIN sys.dm_exec_sessions dm_es ON dm_es.session_id = dm_r.session_id
left outer join sys.dm_exec_sessions dm_es2 ON dm_ws.blocking_session_id = dm_es2.session_id
CROSS APPLY sys.dm_exec_sql_text (dm_r.sql_handle) dm_t
CROSS APPLY sys.dm_exec_query_plan (dm_r.plan_handle) dm_qp
WHERE dm_es.is_user_process = 1
GO
/****** Object: StoredProcedure [dbo].[collect_dmoswaitstats_v2] Script Date: 29/10/2013 9:38:40 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[collect_dmoswaitstats_v2] @snapshot_id int, @timeofsnapshot datetime as
insert into DM_OS_WAIT_STATS select @snapshot_id,@timeofsnapshot, wait_type,waiting_tasks_count,wait_time_ms,max_wait_time_ms,signal_wait_time_ms
from sys.dm_os_wait_stats
GO
/****** Object: StoredProcedure [dbo].[collect_dmv_all] Script Date: 29/10/2013 9:38:40 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[collect_dmv_all] as
declare @snapshot_id int
declare @timeofsnapshot datetime
select @snapshot_id=isnull(max(snapshot_id)+1,1) from snapshot
select @timeofsnapshot=getdate()
insert into snapshot values (@snapshot_id,@timeofsnapshot)
exec collect_dm_sysprocesses_v2 @snapshot_id,@timeofsnapshot
exec collect_dmosschedulers_v2 @snapshot_id,@timeofsnapshot
exec collect_dmoswaitingtasks_v2 @snapshot_id,@timeofsnapshot
exec collect_dmoswaitstats_v2 @snapshot_id,@timeofsnapshot
GO
/****** Object: StoredProcedure [dbo].[compare_dmoswaitstas] Script Date: 29/10/2013 9:38:40 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[compare_dmoswaitstas] @begin_snapshotid int, @end_snapshotid int
as
select l.wait_type,(l.wait_time_ms-o.wait_time_ms) accum_wait_ms from dm_os_wait_stats o
inner join dm_os_wait_stats l on o.wait_type=l.wait_type
where o.snapshot_id=@begin_snapshotid and l.snapshot_id=@end_snapshotid
and l.wait_time_ms > o.wait_time_ms
order by accum_wait_ms desc
GO
/****** Object: StoredProcedure [dbo].[purge_dm] Script Date: 29/10/2013 9:38:40 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[purge_dm] @retention_days int as
declare @snapshot_id int
select @snapshot_id=isnull(max(snapshot_id),1) from snapshot where timeofsnapshot< getdate()-@retention_days
delete from snapshot where snapshot_id< @snapshot_id
delete from dm_os_schedulers where snapshot_id< @snapshot_id
delete from dm_os_wait_stats where snapshot_id< @snapshot_id
delete from dm_os_waiting_tasks where snapshot_id< @snapshot_id
delete from dm_sysprocesses where snapshot_id< @snapshot_id
GO
/****** Object: Table [dbo].[dm_os_schedulers] Script Date: 29/10/2013 9:38:40 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[dm_os_schedulers](
[snapshot_id] [int] NOT NULL,
[timeofsnapshot] [datetime] NOT NULL,
[runnable_tasks_count] [int] NOT NULL,
[is_idle] [bit] NOT NULL,
[load_factor] [int] NOT NULL,
[pending_disk_io_count] [int] NOT NULL,
[active_workers_count] [int] NOT NULL,
[work_queue_count] [bigint] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[DM_OS_WAIT_STATS] Script Date: 29/10/2013 9:38:40 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DM_OS_WAIT_STATS](
[snapshot_id] [int] NOT NULL,
[timeofsnapshot] [datetime] NOT NULL,
[wait_type] [nvarchar](60) NOT NULL,
[waiting_tasks_count] [bigint] NOT NULL,
[wait_time_ms] [bigint] NOT NULL,
[max_wait_time_ms] [bigint] NOT NULL,
[signal_wait_time_ms] [bigint] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[dm_os_waiting_tasks] Script Date: 29/10/2013 9:38:40 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[dm_os_waiting_tasks](
[snapshot_id] [int] NOT NULL,
[timeofsnapshot] [datetime] NULL,
[wait_duration_ms] [bigint] NULL,
[wait_type] [nvarchar](60) NULL,
[status] [nvarchar](30) NOT NULL,
[TEXT] [nvarchar](max) NULL,
[query_plan] [xml] NULL,
[session_ID] [smallint] NULL,
[cpu_time] [int] NOT NULL,
[memory_usage] [int] NOT NULL,
[logical_reads] [bigint] NOT NULL,
[total_elapsed_time] [int] NOT NULL,
[program_name] [nvarchar](128) NULL,
[DatabaseName] [nvarchar](128) NULL,
[blocking_session_id] [smallint] NULL,
[wait_resource] [nvarchar](256) NOT NULL,
[login_name] [nvarchar](128) NOT NULL,
[command] [nvarchar](16) NOT NULL,
[last_wait_type] [nvarchar](60) NOT NULL,
[nt_user_name] [nvarchar](256) NULL,
[host_name] [nvarchar](256) NULL,
[blocking_login_name] [nvarchar](256) NULL,
[blocking_host_name] [nvarchar](256) NULL,
[blocking_nt_user_name] [nvarchar](256) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[dm_sysprocesses] Script Date: 29/10/2013 9:38:40 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[dm_sysprocesses](
[snapshot_id] [int] NOT NULL,
[timeofsnapshot] [datetime] NOT NULL,
[last_batch] [datetime] NOT NULL,
[stmt_end] [int] NOT NULL,
[spid] [smallint] NOT NULL,
[blocked] [smallint] NOT NULL,
[waittype] [binary](2) NOT NULL,
[waittime] [bigint] NOT NULL,
[lastwaittype] [nchar](32) NOT NULL,
[dbid] [smallint] NOT NULL,
[hostname] [nchar](128) NOT NULL,
[program_name] [nchar](128) NOT NULL,
[cmd] [nchar](16) NOT NULL,
[nt_username] [nchar](128) NOT NULL,
[sql_handle] [binary](20) NOT NULL,
[stmt_start] [int] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[snapshot] Script Date: 29/10/2013 9:38:40 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[snapshot](
[snapshot_id] [int] NOT NULL,
[timeofsnapshot] [datetime] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Index [dm_osschedulers_idx] Script Date: 29/10/2013 9:38:40 AM ******/
CREATE NONCLUSTERED INDEX [dm_osschedulers_idx] ON [dbo].[dm_os_schedulers]
(
[snapshot_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [snapshotid_idx] Script Date: 29/10/2013 9:38:40 AM ******/
CREATE NONCLUSTERED INDEX [snapshotid_idx] ON [dbo].[DM_OS_WAIT_STATS]
(
[snapshot_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [dm_oswaitingtasks_idx] Script Date: 29/10/2013 9:38:40 AM ******/
CREATE NONCLUSTERED INDEX [dm_oswaitingtasks_idx] ON [dbo].[dm_os_waiting_tasks]
(
[snapshot_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [dm_sysprocesses_idx] Script Date: 29/10/2013 9:38:40 AM ******/
CREATE NONCLUSTERED INDEX [dm_sysprocesses_idx] ON [dbo].[dm_sysprocesses]
(
[snapshot_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [snapshot_idx] Script Date: 29/10/2013 9:38:40 AM ******/
CREATE NONCLUSTERED INDEX [snapshot_idx] ON [dbo].[snapshot]
(
[snapshot_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
The historical tables are focusing on wait events and cpu scheduler, you can use the following query to check the blocking situation which last more than 10 seconds.
select timeofsnapshot,wait_duration_ms,wait_type,session_id,blocking_session_id,login_name,wait_resource,command,nt_user_name,blocking_nt_user_name,text
from dm_os_waiting_tasks where wait_duration_ms>10000 and wait_type like 'LCK%'
order by wait_duration_ms
--order by snapshot_id desc
the result is something like the screenshot below
you can schedule sqlserver agent to run every 10 seconds to get the snapshot, to collect the snapshot, run procedure “exec collect_dmv_all”
Below are the DDLs to create the database objects
USE [dba]
GO
/****** Object: StoredProcedure [dbo].[collect_dm_sysprocesses_v2] Script Date: 29/10/2013 9:38:40 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[collect_dm_sysprocesses_v2] @snapshot_id int, @timeofsnapshot datetime
as
insert into dm_sysprocesses
select top 8 @snapshot_id,@timeofsnapshot,last_batch, stmt_end,spid,blocked,waittype,waittime,lastwaittype,dbid,hostname,program_name,cmd,nt_username,sql_handle,stmt_start
from sys.sysprocesses order by stmt_end,last_batch desc
GO
/****** Object: StoredProcedure [dbo].[collect_dmosschedulers_v2] Script Date: 29/10/2013 9:38:40 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[collect_dmosschedulers_v2]
@snapshot_id int, @timeofsnapshot datetime
as
insert into dm_os_schedulers
select @snapshot_id,@timeofsnapshot, t1.runnable_tasks_count,t1.is_idle, t1.load_factor,t1.pending_disk_io_count,t1.active_workers_count,t1.work_queue_count
from sys.dm_os_schedulers t1
GO
/****** Object: StoredProcedure [dbo].[collect_dmoswaitingtasks_v2] Script Date: 29/10/2013 9:38:40 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[collect_dmoswaitingtasks_v2] @snapshot_id int, @timeofsnapshot datetime as
insert into dm_os_waiting_tasks
SELECT @snapshot_id ,
@timeofsnapshot,
dm_ws.wait_duration_ms,
dm_ws.wait_type,
dm_es.status,
dm_t.TEXT,
dm_qp.query_plan,
dm_ws.session_ID,
dm_es.cpu_time,
dm_es.memory_usage,
dm_es.logical_reads,
dm_es.total_elapsed_time,
dm_es.program_name,
DB_NAME(dm_r.database_id) DatabaseName,
-- Optional columns
dm_ws.blocking_session_id,
dm_r.wait_resource,
dm_es.login_name,
dm_r.command,
dm_r.last_wait_type,
dm_es.nt_user_name,
dm_es.host_name,
dm_es2.login_name blocking_login_name,
dm_es2.host_name blocking_host_name,
dm_es2.nt_user_name blocking_nt_user_name
FROM sys.dm_os_waiting_tasks dm_ws
INNER JOIN sys.dm_exec_requests dm_r ON dm_ws.session_id = dm_r.session_id
INNER JOIN sys.dm_exec_sessions dm_es ON dm_es.session_id = dm_r.session_id
left outer join sys.dm_exec_sessions dm_es2 ON dm_ws.blocking_session_id = dm_es2.session_id
CROSS APPLY sys.dm_exec_sql_text (dm_r.sql_handle) dm_t
CROSS APPLY sys.dm_exec_query_plan (dm_r.plan_handle) dm_qp
WHERE dm_es.is_user_process = 1
GO
/****** Object: StoredProcedure [dbo].[collect_dmoswaitstats_v2] Script Date: 29/10/2013 9:38:40 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[collect_dmoswaitstats_v2] @snapshot_id int, @timeofsnapshot datetime as
insert into DM_OS_WAIT_STATS select @snapshot_id,@timeofsnapshot, wait_type,waiting_tasks_count,wait_time_ms,max_wait_time_ms,signal_wait_time_ms
from sys.dm_os_wait_stats
GO
/****** Object: StoredProcedure [dbo].[collect_dmv_all] Script Date: 29/10/2013 9:38:40 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[collect_dmv_all] as
declare @snapshot_id int
declare @timeofsnapshot datetime
select @snapshot_id=isnull(max(snapshot_id)+1,1) from snapshot
select @timeofsnapshot=getdate()
insert into snapshot values (@snapshot_id,@timeofsnapshot)
exec collect_dm_sysprocesses_v2 @snapshot_id,@timeofsnapshot
exec collect_dmosschedulers_v2 @snapshot_id,@timeofsnapshot
exec collect_dmoswaitingtasks_v2 @snapshot_id,@timeofsnapshot
exec collect_dmoswaitstats_v2 @snapshot_id,@timeofsnapshot
GO
/****** Object: StoredProcedure [dbo].[compare_dmoswaitstas] Script Date: 29/10/2013 9:38:40 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[compare_dmoswaitstas] @begin_snapshotid int, @end_snapshotid int
as
select l.wait_type,(l.wait_time_ms-o.wait_time_ms) accum_wait_ms from dm_os_wait_stats o
inner join dm_os_wait_stats l on o.wait_type=l.wait_type
where o.snapshot_id=@begin_snapshotid and l.snapshot_id=@end_snapshotid
and l.wait_time_ms > o.wait_time_ms
order by accum_wait_ms desc
GO
/****** Object: StoredProcedure [dbo].[purge_dm] Script Date: 29/10/2013 9:38:40 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[purge_dm] @retention_days int as
declare @snapshot_id int
select @snapshot_id=isnull(max(snapshot_id),1) from snapshot where timeofsnapshot< getdate()-@retention_days
delete from snapshot where snapshot_id< @snapshot_id
delete from dm_os_schedulers where snapshot_id< @snapshot_id
delete from dm_os_wait_stats where snapshot_id< @snapshot_id
delete from dm_os_waiting_tasks where snapshot_id< @snapshot_id
delete from dm_sysprocesses where snapshot_id< @snapshot_id
GO
/****** Object: Table [dbo].[dm_os_schedulers] Script Date: 29/10/2013 9:38:40 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[dm_os_schedulers](
[snapshot_id] [int] NOT NULL,
[timeofsnapshot] [datetime] NOT NULL,
[runnable_tasks_count] [int] NOT NULL,
[is_idle] [bit] NOT NULL,
[load_factor] [int] NOT NULL,
[pending_disk_io_count] [int] NOT NULL,
[active_workers_count] [int] NOT NULL,
[work_queue_count] [bigint] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[DM_OS_WAIT_STATS] Script Date: 29/10/2013 9:38:40 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DM_OS_WAIT_STATS](
[snapshot_id] [int] NOT NULL,
[timeofsnapshot] [datetime] NOT NULL,
[wait_type] [nvarchar](60) NOT NULL,
[waiting_tasks_count] [bigint] NOT NULL,
[wait_time_ms] [bigint] NOT NULL,
[max_wait_time_ms] [bigint] NOT NULL,
[signal_wait_time_ms] [bigint] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[dm_os_waiting_tasks] Script Date: 29/10/2013 9:38:40 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[dm_os_waiting_tasks](
[snapshot_id] [int] NOT NULL,
[timeofsnapshot] [datetime] NULL,
[wait_duration_ms] [bigint] NULL,
[wait_type] [nvarchar](60) NULL,
[status] [nvarchar](30) NOT NULL,
[TEXT] [nvarchar](max) NULL,
[query_plan] [xml] NULL,
[session_ID] [smallint] NULL,
[cpu_time] [int] NOT NULL,
[memory_usage] [int] NOT NULL,
[logical_reads] [bigint] NOT NULL,
[total_elapsed_time] [int] NOT NULL,
[program_name] [nvarchar](128) NULL,
[DatabaseName] [nvarchar](128) NULL,
[blocking_session_id] [smallint] NULL,
[wait_resource] [nvarchar](256) NOT NULL,
[login_name] [nvarchar](128) NOT NULL,
[command] [nvarchar](16) NOT NULL,
[last_wait_type] [nvarchar](60) NOT NULL,
[nt_user_name] [nvarchar](256) NULL,
[host_name] [nvarchar](256) NULL,
[blocking_login_name] [nvarchar](256) NULL,
[blocking_host_name] [nvarchar](256) NULL,
[blocking_nt_user_name] [nvarchar](256) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[dm_sysprocesses] Script Date: 29/10/2013 9:38:40 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[dm_sysprocesses](
[snapshot_id] [int] NOT NULL,
[timeofsnapshot] [datetime] NOT NULL,
[last_batch] [datetime] NOT NULL,
[stmt_end] [int] NOT NULL,
[spid] [smallint] NOT NULL,
[blocked] [smallint] NOT NULL,
[waittype] [binary](2) NOT NULL,
[waittime] [bigint] NOT NULL,
[lastwaittype] [nchar](32) NOT NULL,
[dbid] [smallint] NOT NULL,
[hostname] [nchar](128) NOT NULL,
[program_name] [nchar](128) NOT NULL,
[cmd] [nchar](16) NOT NULL,
[nt_username] [nchar](128) NOT NULL,
[sql_handle] [binary](20) NOT NULL,
[stmt_start] [int] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[snapshot] Script Date: 29/10/2013 9:38:40 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[snapshot](
[snapshot_id] [int] NOT NULL,
[timeofsnapshot] [datetime] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Index [dm_osschedulers_idx] Script Date: 29/10/2013 9:38:40 AM ******/
CREATE NONCLUSTERED INDEX [dm_osschedulers_idx] ON [dbo].[dm_os_schedulers]
(
[snapshot_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [snapshotid_idx] Script Date: 29/10/2013 9:38:40 AM ******/
CREATE NONCLUSTERED INDEX [snapshotid_idx] ON [dbo].[DM_OS_WAIT_STATS]
(
[snapshot_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [dm_oswaitingtasks_idx] Script Date: 29/10/2013 9:38:40 AM ******/
CREATE NONCLUSTERED INDEX [dm_oswaitingtasks_idx] ON [dbo].[dm_os_waiting_tasks]
(
[snapshot_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [dm_sysprocesses_idx] Script Date: 29/10/2013 9:38:40 AM ******/
CREATE NONCLUSTERED INDEX [dm_sysprocesses_idx] ON [dbo].[dm_sysprocesses]
(
[snapshot_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [snapshot_idx] Script Date: 29/10/2013 9:38:40 AM ******/
CREATE NONCLUSTERED INDEX [snapshot_idx] ON [dbo].[snapshot]
(
[snapshot_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Comments
Post a Comment