JiMetrics

User Tools

Site Tools

Trace:
db:sqlserver:jimetrics

Differences

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

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
db:sqlserver:jimetrics [2013/11/05 03:10] – Change SysMetrics to JiMetrics haidongdb:sqlserver:jimetrics [2018/05/15 03:00] (current) – external edit 127.0.0.1
Line 1: Line 1:
 ==== JiMetrics 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. 
 + 
 +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.
  
 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. 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:sqlserversysmetrics.png }}+{{ 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 JiMetrics.sql> <code tsql JiMetrics.sql>
-CREATE DATABASE JiMetrics+CREATE DATABASE JiMetrics;
 GO GO
 ALTER DATABASE JiMetrics SET RECOVERY SIMPLE; ALTER DATABASE JiMetrics SET RECOVERY SIMPLE;
 GO GO
-USE [JiMetrics]+USE [JiMetrics];
 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
  
 CREATE TABLE [Windows].[Host]( CREATE TABLE [Windows].[Host](
-        [HostID] [int] IDENTITY(1,1) NOT NULL, + [HostID] [int] IDENTITY(1,1) NOT NULL, 
-        [HostName] [nvarchar](20) NOT NULL, + [HostName] [nvarchar](20) NOT NULL, 
-        [Domain] [nvarchar](50) NULL, + [Domain] [nvarchar](50) NULL, 
-        [OS] [nvarchar](50) NULL, + [OS] [nvarchar](50) NULL, 
-        [OSArchitecture] [nchar](6) NULL, + [OSArchitecture] [nchar](6) NULL, 
-        [OSServicePack] [nvarchar](20) NULL, + [OSServicePack] [nvarchar](20) NULL, 
-        [OSVersionNumber] [nvarchar](20) NULL, + [OSVersionNumber] [nvarchar](20) NULL, 
-        [HardwareModel] [nvarchar](50) NULL, + [HardwareModel] [nvarchar](50) NULL, 
-        [HardwareVendor] [nvarchar](50) NULL, + [HardwareVendor] [nvarchar](50) NULL, 
-        [MemorySizeGB] [int] NULL, + [MemorySizeGB] [int] NULL, 
-        [CPUType] [nvarchar] (50) NULL, + [CPUType] [nvarchar] (50) NULL, 
-        [CoreCount] [int] NULL, + [CoreCount] [int] NULL, 
-        [IsActive] [nchar](1) NOT NULL, + [SMBIOSVersion] [nvarchar](50) NULL, 
-        [LastUpdate] [datetime2] NULL + [BIOSReleaseDate] [DATE] NULL, 
- CONSTRAINT [PK_Host] PRIMARY KEY CLUSTERED + [SerialNumber] [nvarchar](50) NULL, 
-( + [IsVM] AS (CASE HardwareVendor  
-        [HostID] ASC + WHEN 'innotek GmbH' THEN 'Y' 
-)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON), + WHEN 'microsoft' THEN 'Y' 
- CONSTRAINT [UNQ_HostName] UNIQUE NONCLUSTERED + WHEN 'xen' THEN 'Y' 
-( + ELSE 'N' 
-        [HostName] ASC +   END) PERSISTED, 
-)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) + [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 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
  
-CREATE PROCEDURE Windows.Host_Select_HostID_HostName +CREATE PROCEDURE Windows.Host_Select_HostID_HostName  
-        @IsActive nchar(1) = 'Y'+ @IsActive nchar(1) = 'Y'
 AS AS
 BEGIN BEGIN
-        SET NOCOUNT ON;+ SET NOCOUNT ON;
  
-        SELECT HostID, HostName FROM Windows.Host + SELECT HostID, HostName FROM Windows.Host 
-        WHERE IsActive = @IsActive;+ WHERE IsActive = @IsActive;
 END END
 GO GO
-CREATE PROCEDURE Windows.Host_Update +CREATE PROCEDURE Windows.Host_Update  
-         @HostID int = 0 +   @HostID int = 0 
-        , @Domain nvarchar(50) = NULL + , @Domain nvarchar(50) = NULL 
-        , @OS nvarchar(50) = NULL + , @OS nvarchar(50) = NULL 
-        , @OSArchitecture nchar(6) = NULL + , @OSArchitecture nchar(6) = NULL 
-        , @OSServicePack nvarchar(20) = NULL + , @OSServicePack nvarchar(20) = NULL 
-        , @OSVersionNumber nvarchar(20) = NULL + , @OSVersionNumber nvarchar(20) = NULL 
-        , @HardwareModel nvarchar(50) = NULL + , @HardwareModel nvarchar(50) = NULL 
-        , @HardwareVendor nvarchar(50) = NULL + , @HardwareVendor nvarchar(50) = NULL 
-        , @MemorySizeGB int = NULL + , @MemorySizeGB int = NULL 
-        , @CPUType nvarchar(50) = NULL + , @CPUType nvarchar(50) = NULL 
-        , @CoreCount int = NULL + , @CoreCount int = NULL 
-        , @IsActive nchar(1) = 'Y'+ , @SMBIOSVersion nvarchar(50) = NULL 
 + , @BIOSReleaseDate DATE = NULL 
 + , @SerialNumber nvarchar(50) = NULL 
 + , @IsActive nchar(1) = 'Y'
 AS AS
 BEGIN BEGIN
-        SET NOCOUNT ON;+ SET NOCOUNT ON;
  
-        UPDATE Windows.Host SET + UPDATE Windows.Host SET 
-         Domain = @Domain +   Domain = @Domain 
-        , OS = @OS + , OS = @OS 
-        , OSArchitecture = @OSArchitecture + , OSArchitecture = @OSArchitecture 
-        , OSServicePack = @OSServicePack + , OSServicePack = @OSServicePack 
-        , OSVersionNumber = @OSVersionNumber + , OSVersionNumber = @OSVersionNumber 
-        , HardwareModel = @HardwareModel + , HardwareModel = @HardwareModel 
-        , HardwareVendor = @HardwareVendor + , HardwareVendor = @HardwareVendor 
-        , MemorySizeGB = @MemorySizeGB + , MemorySizeGB = @MemorySizeGB 
-        , CPUType = @CPUType + , CPUType = @CPUType 
-        , CoreCount = @CoreCount + , CoreCount = @CoreCount 
-        , IsActive = @IsActive + , SMBIOSVersion = @SMBIOSVersion 
-        WHERE HostID = @HostID;+ , BIOSReleaseDate = @BIOSReleaseDate 
 + , SerialNumber = @SerialNumber 
 + , IsActive = @IsActive 
 + 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');
  
  
 CREATE TABLE [Windows].[Instance]( CREATE TABLE [Windows].[Instance](
-        [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, 
-        [InstanceEdition] [nvarchar](50) NULL, + [StartupAcct] [nvarchar](30) NOT NULL, 
-        [InstanceEditionID] [bigint] NULL, + [InstanceEdition] [nvarchar](50) NULL, 
-        [InstanceVersion] [nchar](20) NULL, + [InstanceEditionID] [bigint] NULL, 
-        [InstanceServicePack] [nvarchar](20) NULL, + [InstanceVersion] [nchar](20) NULL, 
-        [IsActive] [nchar](1) NOT NULL, + [InstanceServicePack] [nvarchar](20) NULL, 
-        [LastUpdate] [datetime2] NULL + [IsActive] [nchar](1) NOT NULL, 
- CONSTRAINT [PK_Instance] PRIMARY KEY CLUSTERED + [LastUpdate] [datetime2] NULL 
-( + CONSTRAINT [PK_Instance] PRIMARY KEY CLUSTERED ([InstanceID]), 
-        [InstanceID] ASC + CONSTRAINT [UNQ_InstanceName] UNIQUE NONCLUSTERED ([InstanceName]) 
-)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON), + WITH (DATA_COMPRESSION = PAGE));
- 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 GO
  
-ALTER TABLE [Windows].[Instance] ADD CONSTRAINT [FK_Instance_HostID] FOREIGN KEY (HostID) REFERENCES [Windows].[Host] (HostID);+ALTER TABLE [Windows].[Instance] ADD CONSTRAINT [FK_Instance_HostID] 
 +  FOREIGN KEY (HostID) REFERENCES [Windows].[Host] (HostID);
 GO GO
  
-CREATE PROCEDURE Windows.Instance_Select_InstanceID_InstanceName +CREATE PROCEDURE Windows.Instance_Select_InstanceID_InstanceName  
-        @IsActive nchar(1) = 'Y'+ @IsActive nchar(1) = 'Y'
 AS AS
 BEGIN BEGIN
-        SET NOCOUNT ON;+ SET NOCOUNT ON;
  
-        SELECT InstanceID, InstanceName FROM Windows.Instance + SELECT InstanceID, InstanceName FROM Windows.Instance 
-        WHERE IsActive = @IsActive;+ WHERE IsActive = @IsActive;
 END END
 GO GO
  
-CREATE PROCEDURE Windows.Instance_Insert +CREATE PROCEDURE Windows.Instance_Insert  
-               @HostID int +       @HostID int 
-        , @InstanceName nvarchar(30) + , @InstanceName nvarchar(30) 
-        , @IsActive nvarchar(1)+ , @StartupAcct nvarchar(30) 
 + , @IsActive nvarchar(1)
 AS AS
 BEGIN BEGIN
-        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
  
-CREATE PROCEDURE Windows.Instance_Update +CREATE PROCEDURE Windows.Instance_Update  
-         @InstanceID int = 0 +   @InstanceID int = 0 
-        , @InstanceEdition nvarchar(50) = NULL + , @InstanceEdition nvarchar(50) = NULL 
-        , @InstanceEditionID bigint = NULL + , @InstanceEditionID bigint = NULL 
-        , @InstanceVersion nvarchar(20) = NULL + , @InstanceVersion nvarchar(20) = NULL 
-        , @InstanceServicePack nvarchar(20) = NULL + , @InstanceServicePack nvarchar(20) = NULL 
-        , @IsActive nchar(1) = 'Y'+ , @StartupAcct nvarchar(30) = NULL 
 + , @IsActive nchar(1) = 'Y'
 AS AS
 BEGIN BEGIN
-        SET NOCOUNT ON;+ SET NOCOUNT ON;
  
-        UPDATE Windows.Instance SET + UPDATE Windows.Instance SET 
-         InstanceEdition = @InstanceEdition +   InstanceEdition = @InstanceEdition 
-        , InstanceEditionID = @InstanceEditionID + , InstanceEditionID = @InstanceEditionID 
-        , InstanceVersion = @InstanceVersion + , InstanceVersion = @InstanceVersion 
-        , InstanceServicePack = @InstanceServicePack + , InstanceServicePack = @InstanceServicePack 
-        , IsActive = @IsActive + , StartupAcct = @StartupAcct 
-        WHERE InstanceID = @InstanceID;+ , IsActive = @IsActive 
 + WHERE InstanceID = @InstanceID;
 END END
 GO GO
  
 CREATE TABLE [Windows].[Storage]( CREATE TABLE [Windows].[Storage](
-         [RecordID] [int] IDENTITY(1,1) NOT NULL +   [RecordID] [int] IDENTITY(1,1) NOT NULL 
-        , [HostID] [int] NOT NULL + , [HostID] [int] NOT NULL 
-        , [DiskPath] [nvarchar](300) NOT NULL + , [DiskPath] [nvarchar](300) NOT NULL 
-        , [DiskFormat] [nvarchar](25) NOT NULL + , [DiskFormat] [nvarchar](25) NOT NULL 
-        , [DiskLabel] [nvarchar](100) NULL + , [DiskLabel] [nvarchar](100) NULL 
-        , [DiskSizeGB] [int] NOT NULL + , [DiskSizeGB] [int] NOT NULL 
-        , [DiskFreeGB] [int] NOT NULL + , [DiskFreeGB] [int] NOT NULL 
-        , [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 GO
  
-ALTER TABLE [Windows].[Storage] ADD CONSTRAINT [FK_Storage_HostID] FOREIGN KEY (HostID) REFERENCES [Windows].[Host] (HostID);+ALTER TABLE [Windows].[Storage] ADD CONSTRAINT [FK_Storage_HostID]   
 +      FOREIGN KEY (HostID) REFERENCES [Windows].[Host] (HostID);
 GO GO
    
 CREATE procedure [Windows].[Storage_Insert] CREATE procedure [Windows].[Storage_Insert]
-         @HostID [int] +   @HostID [int] 
-        , @DiskPath [nvarchar](1000) + , @DiskPath [nvarchar](1000) 
-        , @DiskFormat [nvarchar](50) + , @DiskFormat [nvarchar](50) 
-        , @DiskLabel [nvarchar](100) + , @DiskLabel [nvarchar](100) 
-        , @DiskSizeGB [int] + , @DiskSizeGB [int] 
-        , @DiskFreeGB [int]+ , @DiskFreeGB [int]
 AS AS
  
 BEGIN BEGIN
-        SET NOCOUNT ON; + SET NOCOUNT ON; 
-        INSERT INTO Windows.Storage ( + INSERT INTO Windows.Storage ( 
-                HostID + HostID 
-                , DiskPath + , DiskPath 
-                , DiskFormat + , DiskFormat 
-                , DiskLabel + , DiskLabel 
-                , DiskSizeGB + , DiskSizeGB 
-                , DiskFreeGB) + , DiskFreeGB) 
-        VALUES ( + VALUES ( 
-                @HostID + @HostID 
-                , @DiskPath + , @DiskPath 
-                , @DiskFormat + , @DiskFormat 
-                , @DiskLabel + , @DiskLabel 
-                , @DiskSizeGB + , @DiskSizeGB 
-                , @DiskFreeGB);+ , @DiskFreeGB);
 END END
-GO 
-  
-SET ANSI_NULLS ON 
-GO 
-  
-SET QUOTED_IDENTIFIER ON 
 GO GO
    
 CREATE TABLE [Windows].[DbFileStats]( CREATE TABLE [Windows].[DbFileStats](
-        [DbFileStatsID] [int] IDENTITY(1,1) NOT NULL, + [DbFileStatsID] [int] IDENTITY(1,1) NOT NULL, 
-        [InstanceID] [int] NOT NULL, + [InstanceID] [int] NOT NULL, 
-        [DbName] [sysname] NOT NULL, + [DbName] [sysname] NOT NULL, 
-        [FileLogicalName] [sysname] NOT NULL, + [FileLogicalName] [sysname] NOT NULL, 
-        [FilePhysicalName] [sysname] NOT NULL, + [FilePhysicalName] [sysname] NOT NULL, 
-        [FileGroupName] [sysname] NOT NULL, + [FileGroupName] [sysname] NOT NULL, 
-        [FileSizeInMB] [int] NOT NULL, + [FileSizeInMB] [int] NOT NULL, 
-        [FreeSizeInMB] [int] NOT NULL, + [FreeSizeInMB] [int] NOT NULL, 
-        [Max_Size] [int] NOT NULL, + [Max_Size] [int] NOT NULL, 
-        [Growth] [int] NOT NULL, + [Growth] [int] NOT NULL, 
-        [Is_Percent_Growth] nchar(1) NOT NULL, + [Is_Percent_Growth] nchar(1) NOT NULL, 
-        [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 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]  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 GO
  
-ALTER TABLE [Windows].[DbFileStats] ADD CONSTRAINT [DF_DbFileStats_Is_Percent_Growth] DEFAULT ('N') FOR [Is_Percent_Growth]+ALTER TABLE [Windows].[DbFileStats] ADD CONSTRAINT  
 +[DF_DbFileStats_Is_Percent_Growth]  DEFAULT ('N') FOR  
 +[Is_Percent_Growth];
 GO GO
  
-ALTER TABLE [Windows].[DbFileStats] ADD CONSTRAINT [FK_DbFileStats_InstanceID] FOREIGN KEY (InstanceID) REFERENCES [Windows].[Instance] (InstanceID);+ALTER TABLE [Windows].[DbFileStats] ADD CONSTRAINT  
 +      [FK_DbFileStats_InstanceID]  FOREIGN KEY (InstanceID) REFERENCES  
 +      [Windows].[Instance] (InstanceID);
 GO GO
  
 CREATE PROCEDURE [Windows].[DbFileStats_Insert] CREATE PROCEDURE [Windows].[DbFileStats_Insert]
-        @InstanceID int + @InstanceID int 
-        , @DbName sysname + , @DbName sysname 
-        , @FileLogicalName sysname + , @FileLogicalName sysname 
-        , @FilePhysicalName nvarchar(500) + , @FilePhysicalName nvarchar(500) 
-        , @FileGroupName sysname + , @FileGroupName sysname 
-        , @FileSizeInMB [int] + , @FileSizeInMB [int] 
-        , @FreeSizeInMB [int] + , @FreeSizeInMB [int] 
-        , @max_size [int] + , @max_size [int] 
-        , @growth [int] + , @growth [int] 
-        , @is_percent_growth nchar(1)+ , @is_percent_growth nchar(1)
 AS AS
 BEGIN BEGIN
-        SET NOCOUNT ON;+ SET NOCOUNT ON;
  
-        INSERT INTO Windows.DbFileStats ( + INSERT INTO Windows.DbFileStats ( 
-                InstanceID + InstanceID 
-                , DbName + , DbName 
-                , FileLogicalName + , FileLogicalName 
-                , FilePhysicalName + , FilePhysicalName 
-                , FileGroupName + , FileGroupName 
-                , FileSizeInMB + , FileSizeInMB 
-                , FreeSizeInMB + , FreeSizeInMB 
-                , max_size + , max_size 
-                , growth + , growth 
-                , is_percent_growth) + , is_percent_growth) 
-        VALUES ( + VALUES ( 
-                @InstanceID + @InstanceID 
-                , @DbName + , @DbName 
-                , @FileLogicalName + , @FileLogicalName 
-                , @FilePhysicalName + , @FilePhysicalName 
-                , @FileGroupName + , @FileGroupName 
-                , @FileSizeInMB + , @FileSizeInMB 
-                , @FreeSizeInMB + , @FreeSizeInMB 
-                , @max_size + , @max_size 
-                , @growth + , @growth 
-                , @is_percent_growth);+ , @is_percent_growth);
 END END
  
Line 342: Line 366:
    
 CREATE TABLE [Windows].[TableStats]( CREATE TABLE [Windows].[TableStats](
-        [TableStatsID] [int] IDENTITY(1,1) NOT NULL, + [TableStatsID] [int] IDENTITY(1,1) NOT NULL, 
-        [InstanceID] [int] NOT NULL, + [InstanceID] [int] NOT NULL, 
-        [DbName] [sysname] NOT NULL, + [DbName] [sysname] NOT NULL, 
-        [SchemaName] [sysname] NOT NULL, + [SchemaName] [sysname] NOT NULL, 
-        [TableName] [sysname] NOT NULL, + [TableName] [sysname] NOT NULL, 
-        [TotalRowCount] [bigint] NOT NULL, + [TotalRowCount] [bigint] NOT NULL, 
-        [DataSizeInMB] [int] NOT NULL, + [DataSizeInMB] [int] NOT NULL, 
-        [IndexSizeInMB] [int] NOT NULL, + [IndexSizeInMB] [int] NOT NULL, 
-        [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, 
-        @DbName sysname, + @DbName sysname, 
-        @SchemaName sysname, + @SchemaName sysname, 
-        @TableName sysname, + @TableName sysname, 
-        @TotalRowCount bigint, + @TotalRowCount bigint, 
-        @DataSizeInMB int, + @DataSizeInMB int, 
-        @IndexSizeInMB int+ @IndexSizeInMB int
 AS AS
 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) 
 + VALUES (@InstanceID, @DbName, @SchemaName, @TableName,  
 +        @TotalRowCount, @DataSizeInMB, @IndexSizeInMB);
 END END
    
 GO GO
  
-IF NOT EXISTS ( SELECT * +IF NOT EXISTS ( SELECT  * 
-                FROM [sys].[tables] +                FROM    [sys].[tables] 
-                WHERE [name] = N'InstanceConfig' +                WHERE   [name] = N'InstanceConfig' 
-                        AND [type] = N'U' )+                        AND [type] = N'U' ) 
     CREATE TABLE [Windows].[InstanceConfig]     CREATE TABLE [Windows].[InstanceConfig]
         (         (
-         [InstanceConfigID] [int] IDENTITY(1,1) NOT NULL, +   [InstanceConfigID] [int] IDENTITY(1,1) NOT NULL, 
-         [InstanceID] [int] NOT NULL,+   [InstanceID] [int] NOT NULL,
           [ConfigurationID] [int] NOT NULL ,           [ConfigurationID] [int] NOT NULL ,
           [Name] [nvarchar](35) NOT NULL ,           [Name] [nvarchar](35) NOT NULL ,
-          [Value] [sql_variant] NULL , +          [Value] [int] NULL , 
-          [ValueInUse] [sql_variant] NULL , +          [ValueInUse] [int] NULL , 
-          [CollectionDate] [datetime2] NOT NULL, +          [CollectionDate] [datetime2] NOT NULL) 
-CONSTRAINT [pk__InstanceConfigID_ID] PRIMARY KEY CLUSTERED + WITH (DATA_COMPRESSION = PAGE);
-( +
-[InstanceConfigID] 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].[InstanceConfig] ADD DEFAULT (getdate()) FOR [CollectionDate]+ALTER TABLE [Windows].[InstanceConfig]  
 +      ADD DEFAULT (getdate()) FOR [CollectionDate];
 GO GO
  
-ALTER TABLE [Windows].[InstanceConfig] ADD CONSTRAINT [FK_InstanceConfig_InstanceID] FOREIGN KEY (InstanceID) REFERENCES [Windows].[Instance] (InstanceID);+CREATE CLUSTERED COLUMNSTORE INDEX cci_InstanceConfig ON [Windows].[InstanceConfig]
 GO GO
  
 CREATE PROCEDURE [Windows].[InstanceConfig_Insert] CREATE PROCEDURE [Windows].[InstanceConfig_Insert]
-        @InstanceID int + @InstanceID int 
-        , @ConfigurationID int + , @ConfigurationID int 
-        , @Name nvarchar(35) + , @Name nvarchar(35) 
-        , @Value sql_variant + , @Value int 
-        , @ValueInUse sql_variant+ , @ValueInUse int
 AS AS
 BEGIN BEGIN
-        SET NOCOUNT ON;+ SET NOCOUNT ON;
  
-        INSERT INTO Windows.InstanceConfig ( + INSERT INTO Windows.InstanceConfig ( 
-                InstanceID + InstanceID 
-                , ConfigurationID + , ConfigurationID 
-                , Name + , Name 
-                , Value + , Value 
-                , ValueInUse) + , ValueInUse) 
-        VALUES ( + VALUES ( 
-                @InstanceID + @InstanceID 
-                , @ConfigurationID + , @ConfigurationID 
-                , @Name + , @Name 
-                , @Value + , @Value 
-                , @ValueInUse);+ , @ValueInUse);
 END END
 GO GO
-IF NOT EXISTS ( SELECT * +IF NOT EXISTS ( SELECT  * 
-                FROM [sys].[tables] +                FROM    [sys].[tables] 
-                WHERE [name] = N'InstanceDmvPerfCounter' +                WHERE   [name] = N'InstanceDmvPerfCounter' 
-                        AND [type] = N'U' )+                        AND [type] = N'U' ) 
     CREATE TABLE [Windows].[InstanceDmvPerfCounter]     CREATE TABLE [Windows].[InstanceDmvPerfCounter]
         (         (
-         [InstanceDmvPerfCounterID] [int] IDENTITY(1,1) NOT NULL, +   [InstanceDmvPerfCounterID] [int] IDENTITY(1,1) NOT NULL, 
-         [InstanceID] [int] NOT NULL,+   [InstanceID] [int] NOT NULL,
           [object_name] [nvarchar](128) NOT NULL ,           [object_name] [nvarchar](128) NOT NULL ,
           [counter_name] [nvarchar](128) NOT NULL ,           [counter_name] [nvarchar](128) NOT NULL ,
Line 455: Line 958:
           [CollectionDate] [datetime2] NOT NULL,           [CollectionDate] [datetime2] NOT NULL,
 CONSTRAINT [pk__InstanceDmvPerfCounter_ID] PRIMARY KEY CLUSTERED CONSTRAINT [pk__InstanceDmvPerfCounter_ID] PRIMARY KEY CLUSTERED
-( +([InstanceDmvPerfCounterID] ASC) 
-[InstanceDmvPerfCounterID] 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].[InstanceDmvPerfCounter] ADD DEFAULT (getdate()) FOR [CollectionDate]+ALTER TABLE [Windows].[InstanceDmvPerfCounter]  
 +      ADD DEFAULT (getdate()) FOR [CollectionDate];
 GO GO
  
-ALTER TABLE [Windows].[InstanceDmvPerfCounter] ADD CONSTRAINT [FK_InstanceDmvPerfCounter_InstanceID] FOREIGN KEY (InstanceID) REFERENCES [Windows].[Instance] (InstanceID);+ALTER TABLE [Windows].[InstanceDmvPerfCounter] ADD CONSTRAINT  
 +[FK_InstanceDmvPerfCounter_InstanceID]  FOREIGN KEY (InstanceID)  
 +REFERENCES [Windows].[Instance] (InstanceID);
 GO GO
  
 CREATE PROCEDURE [Windows].[InstanceDmvPerfCounter_Insert] CREATE PROCEDURE [Windows].[InstanceDmvPerfCounter_Insert]
-        @InstanceID int + @InstanceID int 
-        , @object_name nvarchar(128) + , @object_name nvarchar(128) 
-        , @counter_name nvarchar(128) + , @counter_name nvarchar(128) 
-        , @instance_name nvarchar(128) + , @instance_name nvarchar(128) 
-        , @cntr_value bigint + , @cntr_value bigint 
-        , @cntr_type int+ , @cntr_type int
 AS AS
 BEGIN BEGIN
-        SET NOCOUNT ON;+ SET NOCOUNT ON;
  
-        INSERT INTO Windows.InstanceDmvPerfCounter ( + INSERT INTO Windows.InstanceDmvPerfCounter ( 
-                InstanceID + InstanceID 
-                , object_name + , object_name 
-                , counter_name + , counter_name 
-                , instance_name + , instance_name 
-                , cntr_value + , cntr_value 
-                , cntr_type) + , cntr_type) 
-        VALUES ( + VALUES ( 
-                @InstanceID + @InstanceID 
-                , @object_name + , @object_name 
-                , @counter_name + , @counter_name 
-                , @instance_name + , @instance_name 
-                , @cntr_value + , @cntr_value 
-                , @cntr_type);+ , @cntr_type);
 END END
 GO GO
 </code> </code>
db/sqlserver/jimetrics.1383621043.txt.gz · Last modified: (external edit)