User Tools

Site Tools


db:sqlserver:jimetrics

This is an old revision of the document!


JiMetrics database creation

Since I started with Windows, I will present a SQL Server database script first. Please note that metrics data is typically write-once and read-only, so column-store tables are perfect fits. InfoBright's Community Edition is a column store database engine and it is free. I recommend using it and I really doubt that we will need to purge historical metrics data if we use that. Or at a minimum, we would be able to keep that history much longer.

Here is the ER diagram of the JiMetrics database. For now there are only 7 tables involved under the Windows schema, so it is pretty easy to understand and query.

Note that syntax highlighting is not completely correct, but the script is just fine.

JiMetrics.sql
CREATE DATABASE JiMetrics
GO
ALTER DATABASE JiMetrics SET RECOVERY SIMPLE;
GO
USE [JiMetrics]
GO
 
IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'Windows')
DROP SCHEMA [Windows]
GO
 
CREATE SCHEMA [Windows] AUTHORIZATION [dbo]
GO
 
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [Windows].[Host](
        [HostID] [int] IDENTITY(1,1) NOT NULL,
        [HostName] [nvarchar](20) NOT NULL,
        [Domain] [nvarchar](50) NULL,
        [OS] [nvarchar](50) NULL,
        [OSArchitecture] [nchar](6) NULL,
        [OSServicePack] [nvarchar](20) NULL,
        [OSVersionNumber] [nvarchar](20) NULL,
        [HardwareModel] [nvarchar](50) NULL,
        [HardwareVendor] [nvarchar](50) NULL,
        [MemorySizeGB] [int] NULL,
        [CPUType] [nvarchar] (50) NULL,
        [CoreCount] [int] NULL,
        [IsActive] [nchar](1) NOT NULL,
        [LastUpdate] [datetime2] NULL
 CONSTRAINT [PK_Host] PRIMARY KEY CLUSTERED
(
        [HostID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON),
 CONSTRAINT [UNQ_HostName] UNIQUE NONCLUSTERED
(
        [HostName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
 
GO
 
ALTER TABLE [Windows].[Host] WITH CHECK ADD CONSTRAINT [CK_Host_IsActive] CHECK (([IsActive]='N' OR [IsActive]='n' OR [IsActive]='Y' OR [IsActive]='y'))
GO
 
ALTER TABLE [Windows].[Host] CHECK CONSTRAINT [CK_Host_IsActive]
GO
 
ALTER TABLE [Windows].[Host] ADD CONSTRAINT [DF_Host_IsActive] DEFAULT ('Y') FOR [IsActive]
GO
 
ALTER TABLE [Windows].[Host] ADD CONSTRAINT [DF_Host_LastUpdate] DEFAULT (getdate()) FOR [LastUpdate]
GO
 
CREATE PROCEDURE Windows.Host_Select_HostID_HostName
        @IsActive nchar(1) = 'Y'
AS
BEGIN
        SET NOCOUNT ON;
 
        SELECT HostID, HostName FROM Windows.Host
        WHERE IsActive = @IsActive;
END
GO
CREATE PROCEDURE Windows.Host_Update
         @HostID int = 0
        , @Domain nvarchar(50) = NULL
        , @OS nvarchar(50) = NULL
        , @OSArchitecture nchar(6) = NULL
        , @OSServicePack nvarchar(20) = NULL
        , @OSVersionNumber nvarchar(20) = NULL
        , @HardwareModel nvarchar(50) = NULL
        , @HardwareVendor nvarchar(50) = NULL
        , @MemorySizeGB int = NULL
        , @CPUType nvarchar(50) = NULL
        , @CoreCount int = NULL
        , @IsActive nchar(1) = 'Y'
AS
BEGIN
        SET NOCOUNT ON;
 
        UPDATE Windows.Host SET
         Domain = @Domain
        , OS = @OS
        , OSArchitecture = @OSArchitecture
        , OSServicePack = @OSServicePack
        , OSVersionNumber = @OSVersionNumber
        , HardwareModel = @HardwareModel
        , HardwareVendor = @HardwareVendor
        , MemorySizeGB = @MemorySizeGB
        , CPUType = @CPUType
        , CoreCount = @CoreCount
        , IsActive = @IsActive
        WHERE HostID = @HostID;
END
GO
INSERT INTO Windows.Host (HostName) VALUES ('sql1')
INSERT INTO Windows.Host (HostName) VALUES ('sql2')
 
 
CREATE TABLE [Windows].[Instance](
        [InstanceID] [int] IDENTITY(1,1) NOT NULL,
        [HostID] [int] NOT NULL,
        [InstanceName] [nvarchar](30) NULL,
        [InstanceEdition] [nvarchar](50) NULL,
        [InstanceEditionID] [bigint] NULL,
        [InstanceVersion] [nchar](20) NULL,
        [InstanceServicePack] [nvarchar](20) NULL,
        [IsActive] [nchar](1) NOT NULL,
        [LastUpdate] [datetime2] NULL
 CONSTRAINT [PK_Instance] PRIMARY KEY CLUSTERED
(
        [InstanceID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON),
 CONSTRAINT [UNQ_InstanceName] UNIQUE NONCLUSTERED
(
        [InstanceName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
 
GO
 
ALTER TABLE [Windows].[Instance] WITH CHECK ADD CONSTRAINT [CK_Instance_IsActive] CHECK (([IsActive]='N' OR [IsActive]='n' OR [IsActive]='Y' OR [IsActive]='y'))
GO
 
ALTER TABLE [Windows].[Instance] CHECK CONSTRAINT [CK_Instance_IsActive]
GO
 
ALTER TABLE [Windows].[Instance] ADD CONSTRAINT [DF_Instance_IsActive] DEFAULT ('Y') FOR [IsActive]
GO
 
ALTER TABLE [Windows].[Instance] ADD CONSTRAINT [DF_Instance_LastUpdate] DEFAULT (getdate()) FOR [LastUpdate]
GO
 
ALTER TABLE [Windows].[Instance] ADD CONSTRAINT [FK_Instance_HostID] FOREIGN KEY (HostID) REFERENCES [Windows].[Host] (HostID);
GO
 
CREATE PROCEDURE Windows.Instance_Select_InstanceID_InstanceName
        @IsActive nchar(1) = 'Y'
AS
BEGIN
        SET NOCOUNT ON;
 
        SELECT InstanceID, InstanceName FROM Windows.Instance
        WHERE IsActive = @IsActive;
END
GO
 
CREATE PROCEDURE Windows.Instance_Insert
               @HostID int
        , @InstanceName nvarchar(30)
        , @IsActive nvarchar(1)
AS
BEGIN
        SET NOCOUNT ON;
 
        IF NOT EXISTS (SELECT * FROM Windows.Instance WHERE InstanceName = @InstanceName)
        INSERT INTO [Windows].[Instance] (HostID, InstanceName, IsActive) VALUES (@HostID, @InstanceName, @IsActive);
END
GO
 
CREATE PROCEDURE Windows.Instance_Update
         @InstanceID int = 0
        , @InstanceEdition nvarchar(50) = NULL
        , @InstanceEditionID bigint = NULL
        , @InstanceVersion nvarchar(20) = NULL
        , @InstanceServicePack nvarchar(20) = NULL
        , @IsActive nchar(1) = 'Y'
AS
BEGIN
        SET NOCOUNT ON;
 
        UPDATE Windows.Instance SET
         InstanceEdition = @InstanceEdition
        , InstanceEditionID = @InstanceEditionID
        , InstanceVersion = @InstanceVersion
        , InstanceServicePack = @InstanceServicePack
        , IsActive = @IsActive
        WHERE InstanceID = @InstanceID;
END
GO
 
CREATE TABLE [Windows].[Storage](
         [RecordID] [int] IDENTITY(1,1) NOT NULL
        , [HostID] [int] NOT NULL
        , [DiskPath] [nvarchar](300) NOT NULL
        , [DiskFormat] [nvarchar](25) NOT NULL
        , [DiskLabel] [nvarchar](100) NULL
        , [DiskSizeGB] [int] NOT NULL
        , [DiskFreeGB] [int] NOT NULL
        , [DiskUsedGB] AS ([DiskSizeGB]-[DiskFreeGB]) PERSISTED
        , [CollectionDate] [datetime2] NOT NULL,
CONSTRAINT [pk__Storage_RecordID] PRIMARY KEY CLUSTERED
(
[RecordID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
 
GO
 
ALTER TABLE [Windows].[Storage] ADD DEFAULT (getdate()) FOR [CollectionDate]
GO
 
ALTER TABLE [Windows].[Storage] WITH CHECK ADD CONSTRAINT [fk__Storage_HostID] FOREIGN KEY([HostID])
REFERENCES [Windows].[Host] ([HostID])
GO
 
ALTER TABLE [Windows].[Storage] CHECK CONSTRAINT [fk__Storage_HostID]
GO
 
ALTER TABLE [Windows].[Storage] WITH CHECK ADD CONSTRAINT [ck__Storage_DiskSize] CHECK (([DiskUsedGB]>=(0) AND [DiskSizeGB]>=(0) AND [DiskFreeGB]>=(0)))
GO
 
ALTER TABLE [Windows].[Storage] CHECK CONSTRAINT [ck__Storage_DiskSize]
GO
 
ALTER TABLE [Windows].[Storage] ADD CONSTRAINT [FK_Storage_HostID] FOREIGN KEY (HostID) REFERENCES [Windows].[Host] (HostID);
GO
 
CREATE procedure [Windows].[Storage_Insert]
         @HostID [int]
        , @DiskPath [nvarchar](1000)
        , @DiskFormat [nvarchar](50)
        , @DiskLabel [nvarchar](100)
        , @DiskSizeGB [int]
        , @DiskFreeGB [int]
AS
 
BEGIN
        SET NOCOUNT ON;
        INSERT INTO Windows.Storage (
                HostID
                , DiskPath
                , DiskFormat
                , DiskLabel
                , DiskSizeGB
                , DiskFreeGB)
        VALUES (
                @HostID
                , @DiskPath
                , @DiskFormat
                , @DiskLabel
                , @DiskSizeGB
                , @DiskFreeGB);
END
GO
 
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [Windows].[DbFileStats](
        [DbFileStatsID] [int] IDENTITY(1,1) NOT NULL,
        [InstanceID] [int] NOT NULL,
        [DbName] [sysname] NOT NULL,
        [FileLogicalName] [sysname] NOT NULL,
        [FilePhysicalName] [sysname] NOT NULL,
        [FileGroupName] [sysname] NOT NULL,
        [FileSizeInMB] [int] NOT NULL,
        [FreeSizeInMB] [int] NOT NULL,
        [Max_Size] [int] NOT NULL,
        [Growth] [int] NOT NULL,
        [Is_Percent_Growth] nchar(1) NOT NULL,
        [CollectionDate] [datetime2] NOT NULL,
CONSTRAINT [pk__DbFileStatsID_SID] PRIMARY KEY CLUSTERED
(
[DbFileStatsID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
 
GO
 
ALTER TABLE [Windows].[DbFileStats] ADD DEFAULT (getdate()) FOR [CollectionDate]
GO
 
ALTER TABLE [Windows].[DbFileStats] WITH CHECK ADD CONSTRAINT [CK_DbFileStats_Is_Percent_Growth] CHECK (([Is_Percent_Growth]='N' OR [Is_Percent_Growth]='n' OR [Is_Percent_Growth]='Y' OR [Is_Percent_Growth]='y'))
GO
 
ALTER TABLE [Windows].[DbFileStats] ADD CONSTRAINT [DF_DbFileStats_Is_Percent_Growth] DEFAULT ('N') FOR [Is_Percent_Growth]
GO
 
ALTER TABLE [Windows].[DbFileStats] ADD CONSTRAINT [FK_DbFileStats_InstanceID] FOREIGN KEY (InstanceID) REFERENCES [Windows].[Instance] (InstanceID);
GO
 
CREATE PROCEDURE [Windows].[DbFileStats_Insert]
        @InstanceID int
        , @DbName sysname
        , @FileLogicalName sysname
        , @FilePhysicalName nvarchar(500)
        , @FileGroupName sysname
        , @FileSizeInMB [int]
        , @FreeSizeInMB [int]
        , @max_size [int]
        , @growth [int]
        , @is_percent_growth nchar(1)
AS
BEGIN
        SET NOCOUNT ON;
 
        INSERT INTO Windows.DbFileStats (
                InstanceID
                , DbName
                , FileLogicalName
                , FilePhysicalName
                , FileGroupName
                , FileSizeInMB
                , FreeSizeInMB
                , max_size
                , growth
                , is_percent_growth)
        VALUES (
                @InstanceID
                , @DbName
                , @FileLogicalName
                , @FilePhysicalName
                , @FileGroupName
                , @FileSizeInMB
                , @FreeSizeInMB
                , @max_size
                , @growth
                , @is_percent_growth);
END
 
GO
 
CREATE TABLE [Windows].[TableStats](
        [TableStatsID] [int] IDENTITY(1,1) NOT NULL,
        [InstanceID] [int] NOT NULL,
        [DbName] [sysname] NOT NULL,
        [SchemaName] [sysname] NOT NULL,
        [TableName] [sysname] NOT NULL,
        [TotalRowCount] [bigint] NOT NULL,
        [DataSizeInMB] [int] NOT NULL,
        [IndexSizeInMB] [int] NOT NULL,
        [CollectionDate] [datetime2] NOT NULL,
CONSTRAINT [pk__TableStatsID_ID] PRIMARY KEY CLUSTERED
(
[TableStatsID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
 
GO
 
ALTER TABLE [Windows].[TableStats] ADD DEFAULT (getdate()) FOR [CollectionDate]
GO
 
ALTER TABLE [Windows].[TableStats] WITH CHECK ADD FOREIGN KEY([InstanceID])
REFERENCES [Windows].[Instance] ([InstanceID])
GO
 
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
 
CREATE procedure [Windows].[TableStats_Insert]
        @InstanceID int,
        @DbName sysname,
        @SchemaName sysname,
        @TableName sysname,
        @TotalRowCount bigint,
        @DataSizeInMB int,
        @IndexSizeInMB int
AS
BEGIN
        SET NOCOUNT ON;
        INSERT INTO Windows.TableStats (InstanceID, DbName, SchemaName, TableName, TotalRowCount, DataSizeInMB, IndexSizeInMB)
        VALUES (@InstanceID, @DbName, @SchemaName, @TableName, @TotalRowCount, @DataSizeInMB, @IndexSizeInMB);
END
 
GO
 
IF NOT EXISTS ( SELECT *
                FROM [sys].[tables]
                WHERE [name] = N'InstanceConfig'
                        AND [type] = N'U' )
    CREATE TABLE [Windows].[InstanceConfig]
        (
         [InstanceConfigID] [int] IDENTITY(1,1) NOT NULL,
         [InstanceID] [int] NOT NULL,
          [ConfigurationID] [int] NOT NULL ,
          [Name] [nvarchar](35) NOT NULL ,
          [Value] [sql_variant] NULL ,
          [ValueInUse] [sql_variant] NULL ,
          [CollectionDate] [datetime2] NOT NULL,
CONSTRAINT [pk__InstanceConfigID_ID] PRIMARY KEY CLUSTERED
(
[InstanceConfigID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
GO
 
ALTER TABLE [Windows].[InstanceConfig] ADD DEFAULT (getdate()) FOR [CollectionDate]
GO
 
ALTER TABLE [Windows].[InstanceConfig] ADD CONSTRAINT [FK_InstanceConfig_InstanceID] FOREIGN KEY (InstanceID) REFERENCES [Windows].[Instance] (InstanceID);
GO
 
CREATE PROCEDURE [Windows].[InstanceConfig_Insert]
        @InstanceID int
        , @ConfigurationID int
        , @Name nvarchar(35)
        , @Value sql_variant
        , @ValueInUse sql_variant
AS
BEGIN
        SET NOCOUNT ON;
 
        INSERT INTO Windows.InstanceConfig (
                InstanceID
                , ConfigurationID
                , Name
                , Value
                , ValueInUse)
        VALUES (
                @InstanceID
                , @ConfigurationID
                , @Name
                , @Value
                , @ValueInUse);
END
GO
IF NOT EXISTS ( SELECT *
                FROM [sys].[tables]
                WHERE [name] = N'InstanceDmvPerfCounter'
                        AND [type] = N'U' )
    CREATE TABLE [Windows].[InstanceDmvPerfCounter]
        (
         [InstanceDmvPerfCounterID] [int] IDENTITY(1,1) NOT NULL,
         [InstanceID] [int] NOT NULL,
          [object_name] [nvarchar](128) NOT NULL ,
          [counter_name] [nvarchar](128) NOT NULL ,
          [instance_name] [nvarchar](128) NULL ,
          [cntr_value] [bigint] NOT NULL ,
          [cntr_type] [int] NOT NULL ,
          [CollectionDate] [datetime2] NOT NULL,
CONSTRAINT [pk__InstanceDmvPerfCounter_ID] PRIMARY KEY CLUSTERED
(
[InstanceDmvPerfCounterID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
GO
 
ALTER TABLE [Windows].[InstanceDmvPerfCounter] ADD DEFAULT (getdate()) FOR [CollectionDate]
GO
 
ALTER TABLE [Windows].[InstanceDmvPerfCounter] ADD CONSTRAINT [FK_InstanceDmvPerfCounter_InstanceID] FOREIGN KEY (InstanceID) REFERENCES [Windows].[Instance] (InstanceID);
GO
 
CREATE PROCEDURE [Windows].[InstanceDmvPerfCounter_Insert]
        @InstanceID int
        , @object_name nvarchar(128)
        , @counter_name nvarchar(128)
        , @instance_name nvarchar(128)
        , @cntr_value bigint
        , @cntr_type int
AS
BEGIN
        SET NOCOUNT ON;
 
        INSERT INTO Windows.InstanceDmvPerfCounter (
                InstanceID
                , object_name
                , counter_name
                , instance_name
                , cntr_value
                , cntr_type)
        VALUES (
                @InstanceID
                , @object_name
                , @counter_name
                , @instance_name
                , @cntr_value
                , @cntr_type);
END
GO
db/sqlserver/jimetrics.1383621043.txt.gz · Last modified: 2018/05/14 21:58 (external edit)