Differences

This shows you the differences between two versions of the page.

--- db:sqlserver:jimetrics [2014/09/16 20:56]
127.0.0.1 external edit
+++ db:sqlserver:jimetrics [2018/05/14 22:00]
@@ Line 1: / Line 1: @@
-==== 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.
-{{ db:sqlserverjimetrics.png }}
-Note that syntax highlighting is not completely correct, but the script is just fine.
-<code tsql 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,
-	[SMBIOSVersion] [nvarchar](50) NULL,
-	[BIOSReleaseDate] [DATE] NULL,
-	[SerialNumber] [nvarchar](50) NULL,
-	[IsVM] AS (CASE HardwareVendor
-			WHEN 'innotek GmbH' THEN 'Y'
-			WHEN 'microsoft' THEN 'Y'
-			WHEN 'xen' THEN 'Y'
-			ELSE 'N'
-		  END) PERSISTED,
-	[IsActive] [nchar](1) NOT NULL,
-	[LastUpdate] [datetime2] NULL
- CONSTRAINT [PK_Host] PRIMARY KEY CLUSTERED ([HostID]),
- CONSTRAINT [UNQ_HostName] UNIQUE NONCLUSTERED ([HostName])
- WITH (DATA_COMPRESSION = PAGE));
-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
-	, @SMBIOSVersion nvarchar(50) = NULL
-	, @BIOSReleaseDate DATE = NULL
-	, @SerialNumber nvarchar(50) = 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
-	, SMBIOSVersion = @SMBIOSVersion
-	, BIOSReleaseDate = @BIOSReleaseDate
-	, SerialNumber = @SerialNumber
-	, 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) NOT NULL,
-	[StartupAcct] [nvarchar](30) NOT 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]),
- CONSTRAINT [UNQ_InstanceName] UNIQUE NONCLUSTERED ([InstanceName])
- WITH (DATA_COMPRESSION = PAGE));
-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)
-	, @StartupAcct 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, StartupAcct,
-	   	 IsActive) VALUES (@HostID, @InstanceName, @StartupAcct, @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
-	, @StartupAcct nvarchar(30) = NULL
-	, @IsActive nchar(1) = 'Y'
-AS
-BEGIN
-	SET NOCOUNT ON;
-	UPDATE Windows.Instance SET
-	  InstanceEdition = @InstanceEdition
-	, InstanceEditionID = @InstanceEditionID
-	, InstanceVersion = @InstanceVersion
-	, InstanceServicePack = @InstanceServicePack
-	, StartupAcct = @StartupAcct
-	, 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])
- WITH (DATA_COMPRESSION = PAGE));
-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
-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 (DATA_COMPRESSION = PAGE));
-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 (DATA_COMPRESSION = PAGE));
-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
-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 (DATA_COMPRESSION = PAGE));
-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 (DATA_COMPRESSION = PAGE));
-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
-</code>

JiMetrics

User Tools

Site Tools

Differences

Page Tools