SQL Script for Storing MiniProfiler Timing in SQL Azure


#1

Hi,

Please forgive me if this is not the right place for this but here’s a script I’ve developed today to allow me to port an on-premise application into Azure.

Nothing earth shattering other than SQL Azure does’nt support non-clustered indexes so I had to tweak the SQL a tiny bit.

/****** Object:  Table [dbo].[MiniProfilerClientTimings]    Script Date: 10/01/2013 13:50:31 ******/

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[MiniProfilerClientTimings](
[MiniProfilerId] [uniqueidentifier] NOT NULL,
[Name] nvarchar NOT NULL,
[Start] [decimal](7, 1) NULL,
[Duration] [decimal](7, 1) NULL
PRIMARY KEY CLUSTERED
(
[MiniProfilerId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

GO

/****** Object: Table [dbo].[MiniProfilers] Script Date: 10/01/2013 13:50:33 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[MiniProfilers](
[Id] [uniqueidentifier] NOT NULL,
[Name] nvarchar NOT NULL,
[Started] [datetime] NOT NULL,
[MachineName] nvarchar NULL,
[User] nvarchar NULL,
[Level] [tinyint] NULL,
[RootTimingId] [uniqueidentifier] NULL,
[DurationMilliseconds] [decimal](7, 1) NOT NULL,
[DurationMillisecondsInSql] [decimal](7, 1) NULL,
[HasSqlTimings] [bit] NOT NULL,
[HasDuplicateSqlTimings] [bit] NOT NULL,
[HasTrivialTimings] [bit] NOT NULL,
[HasAllTrivialTimings] [bit] NOT NULL,
[TrivialDurationThresholdMilliseconds] [decimal](5, 1) NULL,
[HasUserViewed] [bit] NOT NULL,
PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

GO

/****** Object: Table [dbo].[MiniProfilerSqlTimingParameters] Script Date: 10/01/2013 13:50:38 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[MiniProfilerSqlTimingParameters](
[MiniProfilerId] [uniqueidentifier] NOT NULL,
[ParentSqlTimingId] [uniqueidentifier] NOT NULL,
[Name] nvarchar NOT NULL,
[DbType] nvarchar NULL,
[Size] [int] NULL,
[Value] nvarchar NULL
PRIMARY KEY CLUSTERED
(
[MiniProfilerId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

GO

/****** Object: Table [dbo].[MiniProfilerSqlTimings] Script Date: 10/01/2013 13:50:40 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[MiniProfilerSqlTimings](
[RowId] [int] IDENTITY(1,1) NOT NULL,
[Id] [uniqueidentifier] NOT NULL,
[MiniProfilerId] [uniqueidentifier] NOT NULL,
[ParentTimingId] [uniqueidentifier] NOT NULL,
[ExecuteType] [tinyint] NOT NULL,
[StartMilliseconds] [decimal](7, 1) NOT NULL,
[DurationMilliseconds] [decimal](7, 1) NOT NULL,
[FirstFetchDurationMilliseconds] [decimal](7, 1) NULL,
[IsDuplicate] [bit] NOT NULL,
[StackTraceSnippet] nvarchar NOT NULL,
[CommandString] nvarchar NOT NULL,
PRIMARY KEY CLUSTERED
(
[RowId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

GO

/****** Object: Table [dbo].[MiniProfilerTimings] Script Date: 10/01/2013 13:50:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[MiniProfilerTimings](
[RowId] [int] IDENTITY(1,1) NOT NULL,
[Id] [uniqueidentifier] NOT NULL,
[MiniProfilerId] [uniqueidentifier] NOT NULL,
[ParentTimingId] [uniqueidentifier] NULL,
[Name] nvarchar NOT NULL,
[Depth] [smallint] NOT NULL,
[StartMilliseconds] [decimal](7, 1) NOT NULL,
[DurationMilliseconds] [decimal](7, 1) NOT NULL,
[DurationWithoutChildrenMilliseconds] [decimal](7, 1) NOT NULL,
[SqlTimingsDurationMilliseconds] [decimal](7, 1) NULL,
[IsRoot] [bit] NOT NULL,
[HasChildren] [bit] NOT NULL,
[IsTrivial] [bit] NOT NULL,
[HasSqlTimings] [bit] NOT NULL,
[HasDuplicateSqlTimings] [bit] NOT NULL,
[ExecutedReaders] [smallint] NOT NULL,
[ExecutedScalars] [smallint] NOT NULL,
[ExecutedNonQueries] [smallint] NOT NULL,
PRIMARY KEY CLUSTERED
(
[RowId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

GO

Thanks