USE [DBA] -- Database to collect monitoring / counter information
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--This table is to store information from the SQL Server Agent below Job
CREATE TABLE [dbo].[T_TempDBMonitor](
[System Name] [nvarchar](128) NULL,
[Application Name] [nvarchar](128) NULL,
[Database Name] [nvarchar](128) NULL,
[User Name] [nvarchar](128) NULL,
[Connection ID] [uniqueidentifier] NULL,
[Current Session ID] [smallint] NOT
NULL,
[Blocking Session ID] [smallint] NULL,
[Request Start Time] [datetime] NOT
NULL,
[Status] [nvarchar](30) NOT NULL,
[Command Type] [nvarchar](16) NOT NULL,
[Query Text] [nvarchar](max) NULL,
[Waiting Type] [nvarchar](60) NULL,
[Waiting Duration] [int] NOT NULL,
[Waiting for Resource] [nvarchar](256) NOT NULL,
[Transaction ID] [bigint] NOT NULL,
[Percent Completed] [real] NOT NULL,
[Estimated Completion Time (in mili sec)] [bigint] NOT NULL,
[CPU time used (in mili sec)] [int] NOT
NULL,
[Memory Usage (in KB)] [int] NULL,
[Elapsed Time (in mili sec)] [int] NOT
NULL,
[TempDB Size] [xml] NULL,
[ExecutedAT] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON
[PRIMARY]
GO
USE [msdb]
GO
BEGIN TRANSACTION
DECLARE
@ReturnCode INT
SELECT
@ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[KE Perf Testing]' AND
category_class=1)
BEGIN
EXEC
@ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[KE Perf Testing]'
IF (@@ERROR <> 0 OR @ReturnCode <>
0) GOTO
QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC
@ReturnCode =
msdb.dbo.sp_add_job @job_name=N'Montor-TempDB',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[KE Perf Testing]',
@owner_login_name=NDomain\Username',
@job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <>
0) GOTO
QuitWithRollback
EXEC
@ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TEMPDB',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'SET NOCOUNT ON;
select COUNT(*) as
Counts
from
sys.dm_exec_requests
inner join
sys.dm_exec_sessions
on sys.dm_exec_requests.session_id =
sys.dm_exec_sessions.session_id
where
DB_NAME(database_id) = ''tempdb'' and sys.dm_exec_requests.session_id >50
if @@ROWCOUNT = 0
return
declare @script
nvarchar(max)
set @script =
''SELECT
[Name] as [Logical_Name],
[size_on_disk_bytes] / 1024 /1024 as
[Total_Size_MB],
[num_of_bytes_written] / 1024/1024 as [Space_Used_MB]
FROM
sys.dm_io_virtual_file_stats(2, NULL)
inner
join
sys.sysfiles
on
sys.dm_io_virtual_file_stats.file_id = sys.sysfiles.fileid
ORDER
BY [Name]
for xml raw''
declare
@ReportResultXml table (RXml XML)
declare @ResultXml
xml
declare @sql
nvarchar(max)
set @sql = ''set @ResultXml
= ('' + @script + '')''
execute
sp_ExecuteSQL @sql, N''@ResultXml xml output'', @ResultXml output
--select
@ResultXml;
INSERT INTO
[DBA].[dbo].[T_TempDBMonitor]
([System Name]
,[Application Name]
,[Database Name]
,[User Name]
,[Connection ID]
,[Current Session ID]
,[Blocking Session ID]
,[Request Start Time]
,[Status]
,[Command Type]
,[Query Text]
,[Waiting Type]
,[Waiting Duration]
,[Waiting for Resource]
,[Transaction ID]
,[Percent Completed]
,[Estimated Completion Time (in mili
sec)]
,[CPU time used (in mili sec)]
,[Memory Usage (in KB)]
,[Elapsed Time (in mili sec)]
,[TempDB Size]
,[ExecutedAT])
select
host_name as
[System Name],
program_name as [Application Name],
DB_NAME(database_id) as
[Database Name],
User_Name(user_id) as
[User Name],
connection_id as [Connection ID],
sys.dm_exec_requests.session_id as
[Current Session ID],
blocking_session_id as [Blocking Session ID],
start_time as [Request Start Time],
sys.dm_exec_requests.status as [Status],
command as [Command Type],
(select text from
sys.dm_exec_sql_text(sql_handle)) AS [Query Text],
wait_type as
[Waiting Type],
wait_time as
[Waiting Duration],
wait_resource as [Waiting for Resource],
sys.dm_exec_requests.transaction_id as
[Transaction ID],
percent_complete as [Percent Completed],
estimated_completion_time as [Estimated Completion Time (in mili
sec)],
sys.dm_exec_requests.cpu_time as [CPU time used (in mili sec)],
(memory_usage * 8) as
[Memory Usage (in KB)],
sys.dm_exec_requests.total_elapsed_time as
[Elapsed Time (in mili sec)] ,
@ResultXml,
GETDATE()
from
sys.dm_exec_requests
inner join
sys.dm_exec_sessions
on sys.dm_exec_requests.session_id =
sys.dm_exec_sessions.session_id
where
DB_NAME(database_id) = ''tempdb'' and sys.dm_exec_requests.session_id >50
SET NOCOUNT OFF;',
@database_name=N'tempdb',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <>
0) GOTO
QuitWithRollback
EXEC
@ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <>
0) GOTO
QuitWithRollback
EXEC
@ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'TempDB',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=3,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20140827,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=N'224d01bf-fc46-4e1c-bc27-90a0b4ac7ef1'
IF (@@ERROR <> 0 OR @ReturnCode <>
0) GOTO
QuitWithRollback
EXEC
@ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <>
0) GOTO
QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
