This shows you the differences between two versions of the page.
Next revision | Previous revision | ||
db:sqlserver:jimetrics [2013/10/29 21:43] 127.0.0.1 external edit |
db:sqlserver:jimetrics [2018/05/14 22:00] (current) |
||
---|---|---|---|
Line 1: | Line 1: | ||
- | ==== SysMetrics database creation ==== | + | ==== 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. | + | Since I started with Windows, I will present a SQL Server database design first. Metrics data is typically write-once and read-only, so column-store tables are perfect fits. Toward that end, I've created two versions: the first one, listed below as JiMetrics.sql, is a regular row-based database. The second one uses columnstore for many tables. It is based on SQL Server 2014. It is listed below as JiMetricsColumnstore.sql. |
- | Here is the ER diagram of the SysMetrics database. For now there are only 7 tables involved under the Windows schema, so it is pretty easy to understand and query. | + | I doubt that we will need to purge historical metrics data if we use columnstore. Or at a minimum, we would be able to keep that history much longer. |
- | {{ db:sqlserversysmetrics.png }} | + | 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. | Note that syntax highlighting is not completely correct, but the script is just fine. | ||
- | <code tsql SysMetrics.sql> | + | <code tsql JiMetrics.sql> |
- | CREATE DATABASE SysMetrics | + | CREATE DATABASE JiMetrics; |
GO | GO | ||
- | USE [SysMetrics] | + | ALTER DATABASE JiMetrics SET RECOVERY SIMPLE; |
GO | GO | ||
- | + | USE [JiMetrics]; | |
- | ALTER DATABASE SysMetrics SET RECOVERY SIMPLE; | + | |
GO | GO | ||
IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'Windows') | IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'Windows') | ||
- | DROP SCHEMA [Windows] | + | DROP SCHEMA [Windows]; |
GO | GO | ||
- | CREATE SCHEMA [Windows] AUTHORIZATION [dbo] | + | CREATE SCHEMA [Windows] AUTHORIZATION [dbo]; |
GO | GO | ||
- | SET ANSI_NULLS ON | + | SET ANSI_NULLS ON; |
GO | GO | ||
- | SET QUOTED_IDENTIFIER ON | + | SET QUOTED_IDENTIFIER ON; |
GO | GO | ||
Line 43: | Line 44: | ||
[CPUType] [nvarchar] (50) NULL, | [CPUType] [nvarchar] (50) NULL, | ||
[CoreCount] [int] 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, | [IsActive] [nchar](1) NOT NULL, | ||
[LastUpdate] [datetime2] NULL | [LastUpdate] [datetime2] NULL | ||
- | CONSTRAINT [PK_Host] PRIMARY KEY CLUSTERED | + | CONSTRAINT [PK_Host] PRIMARY KEY CLUSTERED ([HostID]), |
- | ( | + | CONSTRAINT [UNQ_HostName] UNIQUE NONCLUSTERED ([HostName]) |
- | [HostID] ASC | + | WITH (DATA_COMPRESSION = PAGE)); |
- | )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 | GO | ||
- | ALTER TABLE [Windows].[Host] WITH CHECK ADD CONSTRAINT [CK_Host_IsActive] CHECK (([IsActive]='N' OR [IsActive]='n' OR [IsActive]='Y' OR [IsActive]='y')) | + | ALTER TABLE [Windows].[Host] WITH CHECK ADD CONSTRAINT |
+ | [CK_Host_IsActive] CHECK (([IsActive]='N' OR [IsActive]='n' OR | ||
+ | [IsActive]='Y' OR [IsActive]='y')); | ||
GO | GO | ||
- | ALTER TABLE [Windows].[Host] CHECK CONSTRAINT [CK_Host_IsActive] | + | ALTER TABLE [Windows].[Host] CHECK CONSTRAINT [CK_Host_IsActive]; |
GO | GO | ||
- | ALTER TABLE [Windows].[Host] ADD CONSTRAINT [DF_Host_IsActive] DEFAULT ('Y') FOR [IsActive] | + | ALTER TABLE [Windows].[Host] ADD CONSTRAINT [DF_Host_IsActive] |
+ | DEFAULT ('Y') FOR [IsActive]; | ||
GO | GO | ||
- | ALTER TABLE [Windows].[Host] ADD CONSTRAINT [DF_Host_LastUpdate] DEFAULT (getdate()) FOR [LastUpdate] | + | ALTER TABLE [Windows].[Host] ADD CONSTRAINT [DF_Host_LastUpdate] |
+ | DEFAULT (getdate()) FOR [LastUpdate]; | ||
GO | GO | ||
Line 91: | Line 99: | ||
, @CPUType nvarchar(50) = NULL | , @CPUType nvarchar(50) = NULL | ||
, @CoreCount int = NULL | , @CoreCount int = NULL | ||
+ | , @SMBIOSVersion nvarchar(50) = NULL | ||
+ | , @BIOSReleaseDate DATE = NULL | ||
+ | , @SerialNumber nvarchar(50) = NULL | ||
, @IsActive nchar(1) = 'Y' | , @IsActive nchar(1) = 'Y' | ||
AS | AS | ||
Line 107: | Line 118: | ||
, CPUType = @CPUType | , CPUType = @CPUType | ||
, CoreCount = @CoreCount | , CoreCount = @CoreCount | ||
+ | , SMBIOSVersion = @SMBIOSVersion | ||
+ | , BIOSReleaseDate = @BIOSReleaseDate | ||
+ | , SerialNumber = @SerialNumber | ||
, IsActive = @IsActive | , IsActive = @IsActive | ||
WHERE HostID = @HostID; | WHERE HostID = @HostID; | ||
END | END | ||
GO | GO | ||
- | --INSERT INTO Windows.Host (HostName) VALUES ('sql1') | + | --INSERT INTO Windows.Host (HostName) VALUES ('sql1'); |
- | --INSERT INTO Windows.Host (HostName) VALUES ('sql2') | + | --INSERT INTO Windows.Host (HostName) VALUES ('sql2'); |
Line 118: | Line 132: | ||
[InstanceID] [int] IDENTITY(1,1) NOT NULL, | [InstanceID] [int] IDENTITY(1,1) NOT NULL, | ||
[HostID] [int] NOT NULL, | [HostID] [int] NOT NULL, | ||
- | [InstanceName] [nvarchar](30) NULL, | + | [InstanceName] [nvarchar](30) NOT NULL, |
+ | [StartupAcct] [nvarchar](30) NOT NULL, | ||
[InstanceEdition] [nvarchar](50) NULL, | [InstanceEdition] [nvarchar](50) NULL, | ||
[InstanceEditionID] [bigint] NULL, | [InstanceEditionID] [bigint] NULL, | ||
Line 125: | Line 140: | ||
[IsActive] [nchar](1) NOT NULL, | [IsActive] [nchar](1) NOT NULL, | ||
[LastUpdate] [datetime2] NULL | [LastUpdate] [datetime2] NULL | ||
- | CONSTRAINT [PK_Instance] PRIMARY KEY CLUSTERED | + | CONSTRAINT [PK_Instance] PRIMARY KEY CLUSTERED ([InstanceID]), |
- | ( | + | CONSTRAINT [UNQ_InstanceName] UNIQUE NONCLUSTERED ([InstanceName]) |
- | [InstanceID] ASC | + | WITH (DATA_COMPRESSION = PAGE)); |
- | )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 | GO | ||
- | ALTER TABLE [Windows].[Instance] WITH CHECK ADD CONSTRAINT [CK_Instance_IsActive] CHECK (([IsActive]='N' OR [IsActive]='n' OR [IsActive]='Y' OR [IsActive]='y')) | + | ALTER TABLE [Windows].[Instance] WITH CHECK ADD CONSTRAINT |
+ | [CK_Instance_IsActive] CHECK (([IsActive]='N' OR [IsActive]='n' OR | ||
+ | [IsActive]='Y' OR [IsActive]='y')); | ||
GO | GO | ||
- | ALTER TABLE [Windows].[Instance] CHECK CONSTRAINT [CK_Instance_IsActive] | + | ALTER TABLE [Windows].[Instance] CHECK CONSTRAINT |
+ | [CK_Instance_IsActive]; | ||
GO | GO | ||
- | ALTER TABLE [Windows].[Instance] ADD CONSTRAINT [DF_Instance_IsActive] DEFAULT ('Y') FOR [IsActive] | + | ALTER TABLE [Windows].[Instance] ADD CONSTRAINT [DF_Instance_IsActive] |
+ | DEFAULT ('Y') FOR [IsActive]; | ||
GO | GO | ||
- | ALTER TABLE [Windows].[Instance] ADD CONSTRAINT [DF_Instance_LastUpdate] DEFAULT (getdate()) FOR [LastUpdate] | + | 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 | GO | ||
Line 163: | Line 181: | ||
@HostID int | @HostID int | ||
, @InstanceName nvarchar(30) | , @InstanceName nvarchar(30) | ||
+ | , @StartupAcct nvarchar(30) | ||
, @IsActive nvarchar(1) | , @IsActive nvarchar(1) | ||
AS | AS | ||
Line 168: | Line 187: | ||
SET NOCOUNT ON; | SET NOCOUNT ON; | ||
- | IF NOT EXISTS (SELECT * FROM Windows.Instance WHERE InstanceName = @InstanceName) | + | IF NOT EXISTS (SELECT * FROM Windows.Instance |
- | INSERT INTO [Windows].[Instance] (HostID, InstanceName, IsActive) VALUES (@HostID, @InstanceName, @IsActive); | + | WHERE InstanceName = @InstanceName) |
+ | INSERT INTO [Windows].[Instance] (HostID, InstanceName, StartupAcct, | ||
+ | IsActive) VALUES (@HostID, @InstanceName, @StartupAcct, @IsActive); | ||
END | END | ||
GO | GO | ||
Line 179: | Line 200: | ||
, @InstanceVersion nvarchar(20) = NULL | , @InstanceVersion nvarchar(20) = NULL | ||
, @InstanceServicePack nvarchar(20) = NULL | , @InstanceServicePack nvarchar(20) = NULL | ||
+ | , @StartupAcct nvarchar(30) = NULL | ||
, @IsActive nchar(1) = 'Y' | , @IsActive nchar(1) = 'Y' | ||
AS | AS | ||
Line 189: | Line 211: | ||
, InstanceVersion = @InstanceVersion | , InstanceVersion = @InstanceVersion | ||
, InstanceServicePack = @InstanceServicePack | , InstanceServicePack = @InstanceServicePack | ||
+ | , StartupAcct = @StartupAcct | ||
, IsActive = @IsActive | , IsActive = @IsActive | ||
WHERE InstanceID = @InstanceID; | WHERE InstanceID = @InstanceID; | ||
Line 204: | Line 227: | ||
, [DiskUsedGB] AS ([DiskSizeGB]-[DiskFreeGB]) PERSISTED | , [DiskUsedGB] AS ([DiskSizeGB]-[DiskFreeGB]) PERSISTED | ||
, [CollectionDate] [datetime2] NOT NULL, | , [CollectionDate] [datetime2] NOT NULL, | ||
- | CONSTRAINT [pk__Storage_RecordID] PRIMARY KEY CLUSTERED | + | CONSTRAINT [pk__Storage_RecordID] PRIMARY KEY CLUSTERED ([RecordID]) |
- | ( | + | WITH (DATA_COMPRESSION = PAGE)); |
- | [RecordID] ASC | + | |
- | )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) | + | |
- | ) | + | |
GO | GO | ||
- | ALTER TABLE [Windows].[Storage] ADD DEFAULT (getdate()) FOR [CollectionDate] | + | ALTER TABLE [Windows].[Storage] |
+ | ADD DEFAULT (getdate()) FOR [CollectionDate]; | ||
GO | GO | ||
- | ALTER TABLE [Windows].[Storage] WITH CHECK ADD CONSTRAINT [fk__Storage_HostID] FOREIGN KEY([HostID]) | + | ALTER TABLE [Windows].[Storage] WITH CHECK ADD CONSTRAINT |
- | REFERENCES [Windows].[Host] ([HostID]) | + | [fk__Storage_HostID] FOREIGN KEY([HostID]) |
+ | REFERENCES [Windows].[Host] ([HostID]); | ||
GO | GO | ||
- | ALTER TABLE [Windows].[Storage] CHECK CONSTRAINT [fk__Storage_HostID] | + | ALTER TABLE [Windows].[Storage] CHECK CONSTRAINT [fk__Storage_HostID]; |
GO | GO | ||
- | ALTER TABLE [Windows].[Storage] WITH CHECK ADD CONSTRAINT [ck__Storage_DiskSize] CHECK (([DiskUsedGB]>=(0) AND [DiskSizeGB]>=(0) AND [DiskFreeGB]>=(0))) | + | ALTER TABLE [Windows].[Storage] WITH CHECK ADD CONSTRAINT |
+ | [ck__Storage_DiskSize] CHECK (([DiskUsedGB]>=(0) | ||
+ | AND [DiskSizeGB]>=(0) AND [DiskFreeGB]>=(0))); | ||
GO | GO | ||
- | ALTER TABLE [Windows].[Storage] CHECK CONSTRAINT [ck__Storage_DiskSize] | + | 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 | GO | ||
Line 254: | Line 283: | ||
, @DiskFreeGB); | , @DiskFreeGB); | ||
END | END | ||
- | GO | ||
- | |||
- | SET ANSI_NULLS ON | ||
- | GO | ||
- | |||
- | SET QUOTED_IDENTIFIER ON | ||
GO | GO | ||
Line 276: | Line 299: | ||
[CollectionDate] [datetime2] NOT NULL, | [CollectionDate] [datetime2] NOT NULL, | ||
CONSTRAINT [pk__DbFileStatsID_SID] PRIMARY KEY CLUSTERED | CONSTRAINT [pk__DbFileStatsID_SID] PRIMARY KEY CLUSTERED | ||
- | ( | + | ([DbFileStatsID] ASC) |
- | [DbFileStatsID] ASC | + | WITH (DATA_COMPRESSION = PAGE)); |
- | )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) | + | |
- | ) | + | |
GO | GO | ||
- | ALTER TABLE [Windows].[DbFileStats] ADD DEFAULT (getdate()) FOR [CollectionDate] | + | ALTER TABLE [Windows].[DbFileStats] |
+ | ADD DEFAULT (getdate()) FOR [CollectionDate]; | ||
GO | GO | ||
- | ALTER TABLE [Windows].[DbFileStats] WITH CHECK ADD FOREIGN KEY([InstanceID]) | + | ALTER TABLE [Windows].[DbFileStats] WITH CHECK ADD CONSTRAINT |
- | REFERENCES [Windows].[Instance] ([InstanceID]) | + | [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 | 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')) | + | ALTER TABLE [Windows].[DbFileStats] ADD CONSTRAINT |
+ | [DF_DbFileStats_Is_Percent_Growth] DEFAULT ('N') FOR | ||
+ | [Is_Percent_Growth]; | ||
GO | GO | ||
- | ALTER TABLE [Windows].[DbFileStats] CHECK CONSTRAINT [CK_DbFileStats_Is_Percent_Growth] | + | ALTER TABLE [Windows].[DbFileStats] ADD CONSTRAINT |
- | GO | + | [FK_DbFileStats_InstanceID] FOREIGN KEY (InstanceID) REFERENCES |
- | + | [Windows].[Instance] (InstanceID); | |
- | ALTER TABLE [Windows].[DbFileStats] ADD CONSTRAINT [DF_DbFileStats_Is_Percent_Growth] DEFAULT ('N') FOR [Is_Percent_Growth] | + | |
GO | GO | ||
Line 351: | Line 376: | ||
[CollectionDate] [datetime2] NOT NULL, | [CollectionDate] [datetime2] NOT NULL, | ||
CONSTRAINT [pk__TableStatsID_ID] PRIMARY KEY CLUSTERED | CONSTRAINT [pk__TableStatsID_ID] PRIMARY KEY CLUSTERED | ||
- | ( | + | ([TableStatsID] ASC) |
- | [TableStatsID] ASC | + | WITH (DATA_COMPRESSION = PAGE)); |
- | )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) | + | |
- | ) | + | |
GO | GO | ||
- | ALTER TABLE [Windows].[TableStats] ADD DEFAULT (getdate()) FOR [CollectionDate] | + | ALTER TABLE [Windows].[TableStats] ADD |
+ | DEFAULT (getdate()) FOR [CollectionDate]; | ||
GO | GO | ||
- | ALTER TABLE [Windows].[TableStats] WITH CHECK ADD FOREIGN KEY([InstanceID]) | + | ALTER TABLE [Windows].[TableStats] WITH CHECK ADD FOREIGN |
- | REFERENCES [Windows].[Instance] ([InstanceID]) | + | KEY([InstanceID]) REFERENCES [Windows].[Instance] ([InstanceID]); |
GO | GO | ||
- | SET ANSI_NULLS ON | + | |
+ | 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 | 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 with columnstore, designed and tested against SQL Server 2014. | ||
+ | <code tsql JiMetricsColumnstore.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 | ||
+ | , [CollectionDate] [datetime2] NOT NULL) | ||
+ | WITH (DATA_COMPRESSION = PAGE); | ||
- | SET QUOTED_IDENTIFIER ON | ||
GO | GO | ||
+ | ALTER TABLE [Windows].[Storage] | ||
+ | ADD DEFAULT (getdate()) FOR [CollectionDate]; | ||
+ | GO | ||
+ | |||
+ | ALTER TABLE [Windows].[Storage] WITH CHECK ADD CONSTRAINT | ||
+ | [ck__Storage_DiskSize] CHECK (([DiskSizeGB]>=(0) AND [DiskFreeGB]>=(0))); | ||
+ | GO | ||
+ | |||
+ | ALTER TABLE [Windows].[Storage] | ||
+ | CHECK CONSTRAINT [ck__Storage_DiskSize]; | ||
+ | GO | ||
+ | |||
+ | CREATE CLUSTERED COLUMNSTORE INDEX cci_Storage_ ON [Windows].[Storage] | ||
+ | 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) | ||
+ | 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 | ||
+ | |||
+ | CREATE CLUSTERED COLUMNSTORE INDEX cci_DbFileStats ON [Windows].[DbFileStats] | ||
+ | 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) | ||
+ | WITH (DATA_COMPRESSION = PAGE); | ||
+ | |||
+ | GO | ||
+ | |||
+ | ALTER TABLE [Windows].[TableStats] ADD | ||
+ | DEFAULT (getdate()) FOR [CollectionDate]; | ||
+ | GO | ||
+ | |||
+ | CREATE CLUSTERED COLUMNSTORE INDEX cci_TableStats ON [Windows].[TableStats] | ||
+ | GO | ||
+ | |||
CREATE procedure [Windows].[TableStats_Insert] | CREATE procedure [Windows].[TableStats_Insert] | ||
@InstanceID int, | @InstanceID int, | ||
Line 381: | Line 888: | ||
BEGIN | BEGIN | ||
SET NOCOUNT ON; | SET NOCOUNT ON; | ||
- | INSERT INTO Windows.TableStats (InstanceID, DbName, SchemaName, TableName, TotalRowCount, DataSizeInMB, IndexSizeInMB) | + | INSERT INTO Windows.TableStats (InstanceID, DbName, SchemaName, |
- | VALUES (@InstanceID, @DbName, @SchemaName, @TableName, @TotalRowCount, @DataSizeInMB, @IndexSizeInMB); | + | TableName, TotalRowCount, DataSizeInMB, IndexSizeInMB) |
- | END | + | 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] [int] NULL , | ||
+ | [ValueInUse] [int] NULL , | ||
+ | [CollectionDate] [datetime2] NOT NULL) | ||
+ | WITH (DATA_COMPRESSION = PAGE); | ||
+ | GO | ||
+ | |||
+ | ALTER TABLE [Windows].[InstanceConfig] | ||
+ | ADD DEFAULT (getdate()) FOR [CollectionDate]; | ||
+ | GO | ||
+ | |||
+ | CREATE CLUSTERED COLUMNSTORE INDEX cci_InstanceConfig ON [Windows].[InstanceConfig] | ||
+ | GO | ||
+ | |||
+ | CREATE PROCEDURE [Windows].[InstanceConfig_Insert] | ||
+ | @InstanceID int | ||
+ | , @ConfigurationID int | ||
+ | , @Name nvarchar(35) | ||
+ | , @Value int | ||
+ | , @ValueInUse int | ||
+ | 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 | GO | ||
</code> | </code> |