-- First create database [stats]
--Then use:
--USE [stats]
--GO
--[set nocount on
--go]
/*
CREATE DATABASE [stats] CONTAINMENT = NONE ON PRIMARY
( NAME = N'stats', FILENAME = N'D:\Data\stats.mdf' , SIZE = 65536KB , MAXSIZE = UNLIMITED, FILEGROWTH = 32768KB )
LOG ON
( NAME = N'stats_log', FILENAME = N'D:\Data\stats_log.ldf' , SIZE = 65536KB , MAXSIZE = 2048GB , FILEGROWTH = 32768KB )
GO
ALTER DATABASE [stats] SET RECOVERY SIMPLE
GO
*/
--exec [dbo].[stats_WriteWaitStats]
--GO
--exec [dbo].[stats_WriteQueryStats]
--GO
--**************************************************************
Use [stats]
GO
CREATE FUNCTION fn_GetSQLHash(@TSQL nvarchar(4000))
RETURNS varchar(4000)
AS
BEGIN
DECLARE @TmpTableName nvarchar(4000)
DECLARE @i int
SET @TmpTableName = ''
IF (CHARINDEX('#', @TSQL) > 0)
BEGIN
SET @TmpTableName = SUBSTRING(@TSQL, CHARINDEX('#', @TSQL), 4000)
IF (CHARINDEX(' ', @TmpTableName) > 0)
BEGIN
SET @TmpTableName = SUBSTRING(@TmpTableName, 1, CHARINDEX(' ', @TmpTableName) - 1)
END
END
SET @TSQL = REPLACE ( @TSQL , @TmpTableName , 'TEMPTABLE')
SET @TSQL = REPLACE ( @TSQL , ' ' , '')
SET @TSQL = REPLACE ( @TSQL , ' ' , '')
SET @TSQL = REPLACE ( @TSQL , ' ' , '')
SET @TSQL = REPLACE ( @TSQL , 'execsp_executesqlN''' , '')
SET @TSQL = REPLACE ( @TSQL , CHAR(10) , '')
IF (CHARINDEX(''',N''', @TSQL) > 0)
SET @TSQL = SUBSTRING(@TSQL, 1, CHARINDEX(''',N''', @TSQL) - 1)
SET @i = 1
WHILE @i < 30
BEGIN
SET @TSQL = REPLACE ( @TSQL , '@P' + CAST(@i AS varchar), '')
SET @i = @i + 1
END
SET @TSQL = REPLACE ( @TSQL , '{', '')
SET @TSQL = REPLACE ( @TSQL , '}', '')
SET @TSQL = REPLACE ( @TSQL , '''', '')
SET @TSQL = REPLACE ( @TSQL , '"', '')
SET @TSQL = REPLACE ( @TSQL , '.', '')
SET @TSQL = REPLACE ( @TSQL , ',', '')
SET @TSQL = REPLACE ( @TSQL , ';', '')
SET @TSQL = REPLACE ( @TSQL , ':', '')
SET @TSQL = REPLACE ( @TSQL , '@', '')
SET @TSQL = REPLACE ( @TSQL , '?', '')
SET @TSQL = REPLACE ( @TSQL , '=', '')
SET @TSQL = UPPER(@TSQL)
SET @TSQL = LEFT(@TSQL, 4000)
RETURN(@TSQL);
END
GO
CREATE PROCEDURE stats_WriteWaitStats
AS
BEGIN
IF NOT (EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'wait_stats'))
BEGIN -- create table
Create table [dbo].[wait_stats](
[fix_date] datetime,
[wait_type] nvarchar(60),
[wait_sec] numeric(26, 6) NULL,
[resource_sec] [numeric](26, 6) NULL,
[signal_sec] [numeric](26, 6) NULL,
[wait_count] [bigint] NULL
)
CREATE CLUSTERED INDEX [wait_stats_by_date_type] ON [dbo].[wait_stats]
(
[fix_date] ASC,
[wait_type] 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]
END -- create table
--******************************************************************* save actual stats
Insert into [wait_stats]
SELECT
GETDATE() AS fix_date,
[wait_type] as wait_type,
[wait_time_ms] / 1000.0 AS [wait_sec],
([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [resource_sec],
[signal_wait_time_ms] / 1000.0 AS [signal_sec],
[waiting_tasks_count] AS [wait_count]
FROM sys.dm_os_wait_stats
WHERE [wait_type] NOT IN (
N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR',
N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH',
N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
N'CHKPT', N'CLR_AUTO_EVENT',
N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',
N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE',
N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD',
N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
N'EXECSYNC', N'FSAGENT',
N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE',
N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',
N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP',
N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE',
N'PWAIT_ALL_COMPONENTS_INITIALIZED',
N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',
N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH',
N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP',
N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP',
N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT',
N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS',
N'WAITFOR', N'WAITFOR_TASKSHUTDOWN',
N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',
N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT')
DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR)
END -- end procedure stats_WriteWaitStats
--******************************************************************
GO
CREATE PROCEDURE stats_WriteQueryStats
AS
BEGIN
IF NOT (EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'query_stats'))
BEGIN -- create table
Create table [dbo].[query_stats](
[last_update] datetime,
[query_text] [nvarchar](max) NULL,
[query_plan] [xml] NULL,
[creation_time] [datetime] NULL,
[execution_count] [bigint] NOT NULL,
[last_execution_time] [datetime] NULL,
[total_worker_time] [bigint] NOT NULL,
[total_logical_reads] [bigint] NOT NULL,
[total_logical_writes] [bigint] NOT NULL,
[total_physical_reads] [bigint] NOT NULL,
HashSQL VARCHAR(4000) NULL,
HashSQLMD5 varbinary(32) NULL
)
END -- create table
IF OBJECT_ID('tempdb..#ttab1') IS NOT NULL
DROP TABLE #ttab1
Select TOP 100
GETDATE() as last_update,
SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1) AS query_text,
qp.query_plan,
qs.creation_time,
qs.execution_count,
qs.last_execution_time,
qs.total_worker_time,
qs.total_logical_reads,
qs.total_logical_writes,
qs.total_physical_reads
into #ttab1
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ALTER TABLE #ttab1 ADD HashSQL VARCHAR(4000) NULL, HashSQLMD5 varbinary(32) NULL
UPDATE #ttab1 SET [HashSQL] = dbo.fn_GetSQLHash(SUBSTRING([query_text], 1, 4000))
UPDATE #ttab1 SET [HashSQLMD5] = HashBytes('MD5', HashSQL)
insert into [dbo].[query_stats] select * from #ttab1
DROP TABLE #ttab1
DBCC FREEPROCCACHE
END
-- *************************************** End procedure stats_WriteQueryStats
GO
CREATE PROCEDURE stats_CountHashOnTable @tableName Nvarchar(100)
AS
BEGIN
SET NOCOUNT ON;
Declare @sql varchar(max)
set @sql = 'ALTER TABLE [stats].[dbo].['+@tableName+'] ADD HashSQL VARCHAR(4000) NULL, HashSQLMD5 varbinary(32) NULL'
exec sp_sqlexec @sql
set @sql = 'UPDATE [stats].[dbo].['+@tableName+'] SET [HashSQL] = dbo.fn_GetSQLHash(SUBSTRING([TextData], 1, 4000))'
exec sp_sqlexec @sql
set @sql = 'UPDATE [stats].[dbo].['+@tableName+'] SET [HashSQLMD5] = HashBytes(''MD5'', HashSQL)'
exec sp_sqlexec @sql
END
GO
CREATE PROCEDURE stats_lastwaits
AS
BEGIN
SET NOCOUNT ON;
SELECT top 30 [wait_type] as typewait
,SUM([wait_sec]) as totalwait
,SUM([resource_sec]) as res
,SUM([signal_sec]) as sig
,SUM([wait_count]) as countwait
,CASE WHEN SUM(wait_count) = 0 THEN 0 ELSE SUM([wait_sec])/SUM(wait_count) END as midwait
FROM [stats].[dbo].[wait_stats]
where fix_date > DATEADD(hour, -1, GETDATE())
group by [wait_type]
order by totalwait desc
END
GO
exec [dbo].[stats_WriteWaitStats]
GO
-- exec [dbo].[stats_WriteQueryStats]
-- GO
USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'stats_writer',
@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'[Uncategorized (Local)]',
@owner_login_name=N'sa', @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'waits_writer',
@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'exec [dbo].[stats_WriteWaitStats]
GO',
@database_name=N'stats',
@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'hourly',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=8,
@freq_subday_interval=1,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20181126,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
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