跟踪数据库中执行时间超过1.5秒的语句及SP，导入数据库
时间：2021-07-01 10:21:17
帮助过：9人阅读
============================================================================
--新建两个目录 D:\InOut\TraceDB D:\InOut\TraceLog\
--建数据库，建跟踪执行时间超过1.5秒的语句及SP
--建作业，每天在固定时间将跟踪文件导入数据库
--============================================================================
USE [master]
GO
/****** Object: Database [TraceDB] Script Date: 2017/2/15 11:16:02 ******/
CREATE DATABASE [TraceDB]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N
‘TraceDB‘, FILENAME
= N
‘D:\inout\TraceDB\TraceDB.mdf‘ , SIZE
= 102400KB , MAXSIZE
= UNLIMITED, FILEGROWTH
= 102400KB )
LOG ON
( NAME = N
‘TraceDB_log‘, FILENAME
= N
‘D:\inout\TraceDB\TraceDB_log.ldf‘ , SIZE
= 20416KB , MAXSIZE
= 2048GB , FILEGROWTH
= 102400KB )
GO
--ALTER DATABASE [TraceDB] SET COMPATIBILITY_LEVEL = 120
--GO
IF (
1 = FULLTEXTSERVICEPROPERTY(
‘IsFullTextInstalled‘))
begin
EXEC [TraceDB].
[dbo].
[sp_fulltext_database] @action = ‘enable‘
end
GO
ALTER DATABASE [TraceDB] SET ANSI_NULL_DEFAULT
OFF
GO
ALTER DATABASE [TraceDB] SET ANSI_NULLS
OFF
GO
ALTER DATABASE [TraceDB] SET ANSI_PADDING
OFF
GO
ALTER DATABASE [TraceDB] SET ANSI_WARNINGS
OFF
GO
ALTER DATABASE [TraceDB] SET ARITHABORT
OFF
GO
ALTER DATABASE [TraceDB] SET AUTO_CLOSE
OFF
GO
ALTER DATABASE [TraceDB] SET AUTO_SHRINK
OFF
GO
ALTER DATABASE [TraceDB] SET AUTO_UPDATE_STATISTICS
ON
GO
ALTER DATABASE [TraceDB] SET CURSOR_CLOSE_ON_COMMIT
OFF
GO
ALTER DATABASE [TraceDB] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [TraceDB] SET CONCAT_NULL_YIELDS_NULL
OFF
GO
ALTER DATABASE [TraceDB] SET NUMERIC_ROUNDABORT
OFF
GO
ALTER DATABASE [TraceDB] SET QUOTED_IDENTIFIER
OFF
GO
ALTER DATABASE [TraceDB] SET RECURSIVE_TRIGGERS
OFF
GO
ALTER DATABASE [TraceDB] SET ENABLE_BROKER
GO
ALTER DATABASE [TraceDB] SET AUTO_UPDATE_STATISTICS_ASYNC
OFF
GO
ALTER DATABASE [TraceDB] SET DATE_CORRELATION_OPTIMIZATION
OFF
GO
ALTER DATABASE [TraceDB] SET TRUSTWORTHY
OFF
GO
ALTER DATABASE [TraceDB] SET ALLOW_SNAPSHOT_ISOLATION
OFF
GO
ALTER DATABASE [TraceDB] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [TraceDB] SET READ_COMMITTED_SNAPSHOT
OFF
GO
ALTER DATABASE [TraceDB] SET HONOR_BROKER_PRIORITY
OFF
GO
ALTER DATABASE [TraceDB] SET RECOVERY
FULL
GO
ALTER DATABASE [TraceDB] SET MULTI_USER
GO
ALTER DATABASE [TraceDB] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [TraceDB] SET DB_CHAINING
OFF
GO
ALTER DATABASE [TraceDB] SET FILESTREAM( NON_TRANSACTED_ACCESS
= OFF )
GO
ALTER DATABASE [TraceDB] SET TARGET_RECOVERY_TIME
= 0 SECONDS
GO
ALTER DATABASE [TraceDB] SET DELAYED_DURABILITY
= DISABLED
GO
EXEC sys.sp_db_vardecimal_storage_format N
‘TraceDB‘, N
‘ON‘
GO
USE [TraceDB]
GO
/****** Object: UserDefinedFunction [dbo].[Split] Script Date: 2017/2/15 11:16:02 ******/
SET ANSI_NULLS
ON
GO
SET QUOTED_IDENTIFIER
ON
GO
--表值函数用以截取字符串
--如果为其添加一列主键id,则其顺序就会固定了
create FUNCTION [dbo].
[Split](
@text NVARCHAR(
max))
RETURNS @tempTable TABLE(value
NVARCHAR(
1000))
AS
BEGIN
DECLARE @StartIndex INT --开始查找的位置
DECLARE @FindIndex INT --找到的位置
DECLARE @Content VARCHAR(
4000)
--找到的值
--初始化一些变量
SET @StartIndex = 1 --T-SQL中字符串的查找位置是从1开始的
SET @FindIndex=0
--开始循环查找字符串逗号
WHILE(
@StartIndex <= LEN(
@Text))
BEGIN
--查找字符串函数 CHARINDEX 第一个参数是要找的字符串
-- 第二个参数是在哪里查找这个字符串
-- 第三个参数是开始查找的位置
--返回值是找到字符串的位置
SELECT @FindIndex = CHARINDEX(
‘,‘ ,
@Text,
@StartIndex)
--判断有没找到 没找到返回0
IF(
@FindIndex =0 OR @FindIndex IS NULL)
BEGIN
--如果没有找到就表示找完了
SET @FindIndex = LEN(
@Text)
+1
END
--截取字符串函数 SUBSTRING 第一个参数是要截取的字符串
-- 第二个参数是开始的位置
-- 第三个参数是截取的长度
SET @Content =SUBSTRING(
@Text,
@StartIndex,
@FindIndex-@StartIndex)
--初始化下次查找的位置
SET @StartIndex = @FindIndex+1
--把找的的值插入到要返回的Table类型中
INSERT INTO @tempTable (Value)
VALUES (
@Content)
END
RETURN
END
GO
/****** Object: Table [dbo].[CommandLog] Script Date: 2017/2/15 11:16:02 ******/
SET ANSI_NULLS
ON
GO
SET QUOTED_IDENTIFIER
ON
GO
SET ANSI_PADDING
ON
GO
CREATE TABLE [dbo].
[CommandLog](
[ID] [int] IDENTITY(
1,
1)
NOT NULL,
[DatabaseName] [sysname] NULL,
[SchemaName] [sysname] NULL,
[ObjectName] [sysname] NULL,
[ObjectType] [char](
2)
NULL,
[IndexName] [sysname] NULL,
[IndexType] [tinyint] NULL,
[StatisticsName] [sysname] NULL,
[PartitionNumber] [int] NULL,
[ExtendedInfo] [xml] NULL,
[Command] [nvarchar](
max)
NOT NULL,
[CommandType] [nvarchar](
60)
NOT NULL,
[StartTime] [datetime] NOT NULL,
[EndTime] [datetime] NULL,
[ErrorNumber] [int] NULL,
[ErrorMessage] [nvarchar](
max)
NULL,
CONSTRAINT [PK_CommandLog] PRIMARY KEY CLUSTERED
(
[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] TEXTIMAGE_ON
[PRIMARY]
GO
SET ANSI_PADDING
OFF
GO
/****** Object: Table [dbo].[TraceLog] Script Date: 2017/2/15 11:16:02 ******/
SET ANSI_NULLS
ON
GO
SET QUOTED_IDENTIFIER
ON
GO
CREATE TABLE [dbo].
[TraceLog](
[RowNumber] [int] IDENTITY(
0,
1)
NOT NULL,
[EventClass] [int] NULL,
[Duration] [bigint] NULL,
[TextData] [ntext] NULL,
[SPID] [int] NULL,
[BinaryData] [image] NULL,
[CPU] [int] NULL,
[EndTime] [datetime] NULL,
[ObjectName] [nvarchar](
128)
NULL,
[StartTime] [datetime] NULL,
[Reads] [bigint] NULL,
[Writes] [bigint] NULL,
[DataBaseName] [nvarchar](
256)
NULL,
[ApplicationName] [nvarchar](
256)
NULL,
[HostName] [nvarchar](
256)
NULL,
PRIMARY KEY CLUSTERED
(
[RowNumber] ASC
)WITH (PAD_INDEX
= OFF, STATISTICS_NORECOMPUTE
= OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS
= ON)
ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON
[PRIMARY]
GO
/****** Object: StoredProcedure [dbo].[CommandExecute] Script Date: 2017/2/15 11:16:02 ******/
SET ANSI_NULLS
ON
GO
SET QUOTED_IDENTIFIER
ON
GO
CREATE PROCEDURE [dbo].
[CommandExecute]
@Command nvarchar(
max),
@CommandType nvarchar(
max),
@Mode int,
@Comment nvarchar(
max)
= NULL,
@DatabaseName nvarchar(
max)
= NULL,
@SchemaName nvarchar(
max)
= NULL,
@ObjectName nvarchar(
max)
= NULL,
@ObjectType nvarchar(
max)
= NULL,
@IndexName nvarchar(
max)
= NULL,
@IndexType int = NULL,
@StatisticsName nvarchar(
max)
= NULL,
@PartitionNumber int = NULL,
@ExtendedInfo xml
= NULL,
@LogToTable bit,
@Exec bit
AS
BEGIN
SET NOCOUNT
ON
DECLARE @StartMessage nvarchar(
max)
DECLARE @EndMessage nvarchar(
max)
DECLARE @ErrorMessage nvarchar(
max)
DECLARE @ErrorMessageOriginal nvarchar(
max)
DECLARE @StartTime datetime
DECLARE @EndTime datetime
DECLARE @StartTimeSec datetime
DECLARE @EndTimeSec datetime
DECLARE @ID int
DECLARE @Error int
DECLARE @ReturnCode int
SET @Error = 0
SET @ReturnCode = 0
----------------------------------------------------------------------------------------------------
--// Check core requirements //--
----------------------------------------------------------------------------------------------------
IF @LogToTable = 1 AND NOT EXISTS (
SELECT * FROM sys.objects objects
INNER JOIN sys.schemas schemas
ON objects.
[schema_id] = schemas.
[schema_id] WHERE objects.
[type] = ‘U‘ AND schemas.
[name] = ‘dbo‘ AND objects.
[name] = ‘CommandLog‘)
BEGIN
SET @ErrorMessage = ‘The table CommandLog is missing. Download https://ola.hallengren.com/scripts/CommandLog.sql.‘ + CHAR(
13)
+ CHAR(
10)
+ ‘ ‘
RAISERROR(
@ErrorMessage,
16,
1)
WITH NOWAIT
SET @Error = @@ERROR
END
IF @Error <> 0
BEGIN
SET @ReturnCode = @Error
GOTO ReturnCode
END
----------------------------------------------------------------------------------------------------
--// Check input parameters //--
----------------------------------------------------------------------------------------------------
IF @Command IS NULL OR @Command = ‘‘
BEGIN
SET @ErrorMessage = ‘The value for the parameter @Command is not supported.‘ + CHAR(
13)
+ CHAR(
10)
+ ‘ ‘
RAISERROR(
@ErrorMessage,
16,
1)
WITH NOWAIT
SET @Error = @@ERROR
END
IF @CommandType IS NULL OR @CommandType = ‘‘ OR LEN(
@CommandType)
> 60
BEGIN
SET @ErrorMessage = ‘The value for the parameter @CommandType is not supported.‘ + CHAR(
13)
+ CHAR(
10)
+ ‘ ‘
RAISERROR(
@ErrorMessage,
16,
1)
WITH NOWAIT
SET @Error = @@ERROR
END
IF @Mode NOT IN(
1,
2)
OR @Mode IS NULL
BEGIN
SET @ErrorMessage = ‘The value for the parameter @Mode is not supported.‘ + CHAR(
13)
+ CHAR(
10)
+ ‘ ‘
RAISERROR(
@ErrorMessage,
16,
1)
WITH NOWAIT
SET @Error = @@ERROR
END
IF @Error <> 0
BEGIN
SET @ReturnCode = @Error
GOTO ReturnCode
END
----------------------------------------------------------------------------------------------------
--// Log initial information //--
----------------------------------------------------------------------------------------------------
SET @StartTime = GETDATE()
SET @StartTimeSec = CONVERT(
datetime,
CONVERT(
nvarchar,
@StartTime,
120),
120)
IF @LogToTable=0
BEGIN
SET @StartMessage = ‘Date and time: ‘ + CONVERT(
nvarchar,
@StartTimeSec,
120)
+ CHAR(
13)
+ CHAR(
10)
SET @StartMessage = @StartMessage + ‘Command: ‘ + @Command
IF @Comment IS NOT NULL SET @StartMessage = @StartMessage + CHAR(
13)
+ CHAR(
10)
+ ‘Comment: ‘ + @Comment
SET @StartMessage = REPLACE(
@StartMessage,
‘%‘,
‘%%‘)
RAISERROR(
@StartMessage,
10,
1)
WITH NOWAIT
END
IF @LogToTable = 1
BEGIN
INSERT INTO dbo.CommandLog (DatabaseName, SchemaName, ObjectName, ObjectType, IndexName, IndexType, StatisticsName, PartitionNumber, ExtendedInfo, CommandType, Command, StartTime)
VALUES (
@DatabaseName,
@SchemaName,
@ObjectName,
@ObjectType,
@IndexName,
@IndexType,
@StatisticsName,
@PartitionNumber,
@ExtendedInfo,
@CommandType,
@Command,
@StartTime)
END
SET @ID = SCOPE_IDENTITY()
----------------------------------------------------------------------------------------------------
--// Execute command //--
----------------------------------------------------------------------------------------------------
IF @Mode = 1 AND @Exec = 1
BEGIN
EXECUTE(
@Command)
SET @Error = @@ERROR
SET @ReturnCode = @Error
END
IF @Mode = 2 AND @Exec =1
BEGIN
BEGIN TRY
EXECUTE(
@Command)
END TRY
BEGIN CATCH
SET @Error = ERROR_NUMBER()
SET @ReturnCode = @Error
SET @ErrorMessageOriginal = ERROR_MESSAGE()
SET @ErrorMessage = ‘Msg ‘ + CAST(
@Error AS nvarchar)
+ ‘, ‘ + ISNULL(
@ErrorMessageOriginal,
‘‘)
RAISERROR(
