Differences

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

--- db:sqlserver:sysmetrics [2013/10/21 21:40]
haidong Removed sentence on using SQL Server 2012 columnstore because it is not quite like InfoBright's column store engine. Perhaps SQL Server 2014 will bring something similar to what InfoBright and Vertica has.
+++ — (current)
@@ Line 1: / Line 1: @@
-==== SysMetrics 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.
-Note that syntax highlighting is not completely correct, but the script is just fine.
-<code tsql SysMetrics.sql>
-CREATE DATABASE SysMetrics
-GO
-USE [SysMetrics]
-GO
-ALTER DATABASE SysMetrics SET RECOVERY SIMPLE;
-GO
-IF  EXISTS (SELECT * FROM sys.schemas WHERE name = N'Windows')
-DROP SCHEMA [Windows]
-GO
-CREATE SCHEMA [Windows] AUTHORIZATION [dbo]
-GO
-SET ANSI_NULLS ON
-GO
-SET QUOTED_IDENTIFIER ON
-GO
-CREATE TABLE [Windows].[Host](
-	[HostID] [int] IDENTITY(1,1) NOT NULL,
-	[HostName] [nvarchar](20) NOT NULL,
-	[Domain] [nvarchar](50) NULL,
-	[OS] [nvarchar](50) NULL,
-	[OSArchitecture] [nchar](6) NULL,
-	[OSServicePack] [nvarchar](20) NULL,
-	[OSVersionNumber] [nvarchar](20) NULL,
-	[HardwareModel] [nvarchar](50) NULL,
-	[HardwareVendor] [nvarchar](50) NULL,
-	[MemorySizeGB] [int] NULL,
-	[CPUType] [nvarchar] (50) NULL,
-	[CoreCount] [int] NULL,
-	[IsActive] [nchar](1) NOT NULL,
-	[LastUpdate] [datetime2] NULL
- CONSTRAINT [PK_Host] PRIMARY KEY CLUSTERED
-(
-	[HostID] ASC
-)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON),
- CONSTRAINT [UNQ_HostName] UNIQUE NONCLUSTERED
-(
-	[HostName] ASC
-)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
-)
-GO
-ALTER TABLE [Windows].[Host]  WITH CHECK ADD  CONSTRAINT [CK_Host_IsActive] CHECK  (([IsActive]='N' OR [IsActive]='n' OR [IsActive]='Y' OR [IsActive]='y'))
-GO
-ALTER TABLE [Windows].[Host] CHECK CONSTRAINT [CK_Host_IsActive]
-GO
-ALTER TABLE [Windows].[Host] ADD  CONSTRAINT [DF_Host_IsActive]  DEFAULT ('Y') FOR [IsActive]
-GO
-ALTER TABLE [Windows].[Host] ADD CONSTRAINT [DF_Host_LastUpdate] DEFAULT (getdate()) FOR [LastUpdate]
-GO
-CREATE PROCEDURE Windows.Host_Select_HostID_HostName
-	@IsActive nchar(1) = 'Y'
-AS
-BEGIN
-	SET NOCOUNT ON;
-	SELECT HostID, HostName FROM Windows.Host
-	WHERE IsActive = @IsActive;
-END
-GO
-CREATE PROCEDURE Windows.Host_Update
-	  @HostID int = 0
-	, @Domain nvarchar(50) = NULL
-	, @OS nvarchar(50) = NULL
-	, @OSArchitecture nchar(6) = NULL
-	, @OSServicePack nvarchar(20) = NULL
-	, @OSVersionNumber nvarchar(20) = NULL
-	, @HardwareModel nvarchar(50) = NULL
-	, @HardwareVendor nvarchar(50) = NULL
-	, @MemorySizeGB int = NULL
-	, @CPUType nvarchar(50) = NULL
-	, @CoreCount int = NULL
-	, @IsActive nchar(1) = 'Y'
-AS
-BEGIN
-	SET NOCOUNT ON;
-	UPDATE Windows.Host SET
-	  Domain = @Domain
-	, OS = @OS
-	, OSArchitecture = @OSArchitecture
-	, OSServicePack = @OSServicePack
-	, OSVersionNumber = @OSVersionNumber
-	, HardwareModel = @HardwareModel
-	, HardwareVendor = @HardwareVendor
-	, MemorySizeGB = @MemorySizeGB
-	, CPUType = @CPUType
-	, CoreCount = @CoreCount
-	, IsActive = @IsActive
-	WHERE HostID = @HostID;
-END
-GO
---INSERT INTO Windows.Host (HostName) VALUES ('sql1')
---INSERT INTO Windows.Host (HostName) VALUES ('sql2')
-CREATE TABLE [Windows].[Instance](
-	[InstanceID] [int] IDENTITY(1,1) NOT NULL,
-	[HostID] [int] NOT NULL,
-	[InstanceName] [nvarchar](30) NULL,
-	[InstanceEdition] [nvarchar](50) NULL,
-	[InstanceEditionID] [bigint] NULL,
-	[InstanceVersion] [nchar](20) NULL,
-	[InstanceServicePack] [nvarchar](20) NULL,
-	[IsActive] [nchar](1) NOT NULL,
-	[LastUpdate] [datetime2] NULL
- CONSTRAINT [PK_Instance] PRIMARY KEY CLUSTERED
-(
-	[InstanceID] ASC
-)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON),
- CONSTRAINT [UNQ_InstanceName] UNIQUE NONCLUSTERED
-(
-	[InstanceName] ASC
-)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
-)
-GO
-ALTER TABLE [Windows].[Instance]  WITH CHECK ADD  CONSTRAINT [CK_Instance_IsActive] CHECK  (([IsActive]='N' OR [IsActive]='n' OR [IsActive]='Y' OR [IsActive]='y'))
-GO
-ALTER TABLE [Windows].[Instance] CHECK CONSTRAINT [CK_Instance_IsActive]
-GO
-ALTER TABLE [Windows].[Instance] ADD  CONSTRAINT [DF_Instance_IsActive]  DEFAULT ('Y') FOR [IsActive]
-GO
-ALTER TABLE [Windows].[Instance] ADD CONSTRAINT [DF_Instance_LastUpdate] DEFAULT (getdate()) FOR [LastUpdate]
-GO
-CREATE PROCEDURE Windows.Instance_Select_InstanceID_InstanceName
-	@IsActive nchar(1) = 'Y'
-AS
-BEGIN
-	SET NOCOUNT ON;
-	SELECT InstanceID, InstanceName FROM Windows.Instance
-	WHERE IsActive = @IsActive;
-END
-GO
-CREATE PROCEDURE Windows.Instance_Insert
-       	@HostID int
-	, @InstanceName nvarchar(30)
-	, @IsActive nvarchar(1)
-AS
-BEGIN
-	SET NOCOUNT ON;
-	IF NOT EXISTS (SELECT * FROM Windows.Instance WHERE InstanceName = @InstanceName)
-	INSERT INTO [Windows].[Instance] (HostID, InstanceName, IsActive) VALUES (@HostID, @InstanceName, @IsActive);
-END
-GO
-CREATE PROCEDURE Windows.Instance_Update
-	  @InstanceID int = 0
-	, @InstanceEdition nvarchar(50) = NULL
-	, @InstanceEditionID bigint = NULL
-	, @InstanceVersion nvarchar(20) = NULL
-	, @InstanceServicePack nvarchar(20) = NULL
-	, @IsActive nchar(1) = 'Y'
-AS
-BEGIN
-	SET NOCOUNT ON;
-	UPDATE Windows.Instance SET
-	  InstanceEdition = @InstanceEdition
-	, InstanceEditionID = @InstanceEditionID
-	, InstanceVersion = @InstanceVersion
-	, InstanceServicePack = @InstanceServicePack
-	, IsActive = @IsActive
-	WHERE InstanceID = @InstanceID;
-END
-GO
-CREATE TABLE [Windows].[Storage](
-	  [RecordID] [int] IDENTITY(1,1) NOT NULL
-	, [HostID] [int] NOT NULL
-	, [DiskPath] [nvarchar](300) NOT NULL
-	, [DiskFormat] [nvarchar](25) NOT NULL
-	, [DiskLabel] [nvarchar](100) NULL
-	, [DiskSizeGB] [int] NOT NULL
-	, [DiskFreeGB] [int] NOT NULL
-	, [DiskUsedGB] AS ([DiskSizeGB]-[DiskFreeGB]) PERSISTED
-	, [CollectionDate] [datetime2] NOT NULL,
-CONSTRAINT [pk__Storage_RecordID] PRIMARY KEY CLUSTERED
-(
-[RecordID] ASC
-)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
-)
-GO
-ALTER TABLE [Windows].[Storage] ADD DEFAULT (getdate()) FOR [CollectionDate]
-GO
-ALTER TABLE [Windows].[Storage] WITH CHECK ADD CONSTRAINT [fk__Storage_HostID] FOREIGN KEY([HostID])
-REFERENCES [Windows].[Host] ([HostID])
-GO
-ALTER TABLE [Windows].[Storage] CHECK CONSTRAINT [fk__Storage_HostID]
-GO
-ALTER TABLE [Windows].[Storage] WITH CHECK ADD CONSTRAINT [ck__Storage_DiskSize] CHECK (([DiskUsedGB]>=(0) AND [DiskSizeGB]>=(0) AND [DiskFreeGB]>=(0)))
-GO
-ALTER TABLE [Windows].[Storage] CHECK CONSTRAINT [ck__Storage_DiskSize]
-GO
-CREATE procedure [Windows].[Storage_Insert]
-	  @HostID [int]
-	, @DiskPath [nvarchar](1000)
-	, @DiskFormat [nvarchar](50)
-	, @DiskLabel [nvarchar](100)
-	, @DiskSizeGB [int]
-	, @DiskFreeGB [int]
-AS
-BEGIN
-	SET NOCOUNT ON;
-	INSERT INTO Windows.Storage (
-		HostID
-		, DiskPath
-		, DiskFormat
-		, DiskLabel
-		, DiskSizeGB
-		, DiskFreeGB)
-	VALUES (
-		@HostID
-		, @DiskPath
-		, @DiskFormat
-		, @DiskLabel
-		, @DiskSizeGB
-		, @DiskFreeGB);
-END
-GO
-SET ANSI_NULLS ON
-GO
-SET QUOTED_IDENTIFIER ON
-GO
-CREATE TABLE [Windows].[DbFileStats](
-	[DbFileStatsID] [int] IDENTITY(1,1) NOT NULL,
-	[InstanceID] [int] NOT NULL,
-	[DbName] [sysname] NOT NULL,
-	[FileLogicalName] [sysname] NOT NULL,
-	[FilePhysicalName] [sysname] NOT NULL,
-	[FileGroupName] [sysname] NOT NULL,
-	[FileSizeInMB] [int] NOT NULL,
-	[FreeSizeInMB] [int] NOT NULL,
-	[Max_Size] [int] NOT NULL,
-	[Growth] [int] NOT NULL,
-	[Is_Percent_Growth] nchar(1) NOT NULL,
-	[CollectionDate] [datetime2] NOT NULL,
-CONSTRAINT [pk__DbFileStatsID_SID] PRIMARY KEY CLUSTERED
-(
-[DbFileStatsID] ASC
-)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
-)
-GO
-ALTER TABLE [Windows].[DbFileStats] ADD DEFAULT (getdate()) FOR [CollectionDate]
-GO
-ALTER TABLE [Windows].[DbFileStats] WITH CHECK ADD FOREIGN KEY([InstanceID])
-REFERENCES [Windows].[Instance] ([InstanceID])
-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] CHECK CONSTRAINT [CK_DbFileStats_Is_Percent_Growth]
-GO
-ALTER TABLE [Windows].[DbFileStats] ADD  CONSTRAINT [DF_DbFileStats_Is_Percent_Growth]  DEFAULT ('N') FOR [Is_Percent_Growth]
-GO
-CREATE PROCEDURE [Windows].[DbFileStats_Insert]
-	@InstanceID int
-	, @DbName sysname
-	, @FileLogicalName sysname
-	, @FilePhysicalName nvarchar(500)
-	, @FileGroupName sysname
-	, @FileSizeInMB [int]
-	, @FreeSizeInMB [int]
-	, @max_size [int]
-	, @growth [int]
-	, @is_percent_growth nchar(1)
-AS
-BEGIN
-	SET NOCOUNT ON;
-	INSERT INTO Windows.DbFileStats (
-		InstanceID
-		, DbName
-		, FileLogicalName
-		, FilePhysicalName
-		, FileGroupName
-		, FileSizeInMB
-		, FreeSizeInMB
-		, max_size
-		, growth
-		, is_percent_growth)
-	VALUES (
-		@InstanceID
-		, @DbName
-		, @FileLogicalName
-		, @FilePhysicalName
-		, @FileGroupName
-		, @FileSizeInMB
-		, @FreeSizeInMB
-		, @max_size
-		, @growth
-		, @is_percent_growth);
-END
-GO
-CREATE TABLE [Windows].[TableStats](
-	[TableStatsID] [int] IDENTITY(1,1) NOT NULL,
-	[InstanceID] [int] NOT NULL,
-	[DbName] [sysname] NOT NULL,
-	[SchemaName] [sysname] NOT NULL,
-	[TableName] [sysname] NOT NULL,
-	[TotalRowCount] [bigint] NOT NULL,
-	[DataSizeInMB] [int] NOT NULL,
-	[IndexSizeInMB] [int] NOT NULL,
-	[CollectionDate] [datetime2] NOT NULL,
-CONSTRAINT [pk__TableStatsID_ID] PRIMARY KEY CLUSTERED
-(
-[TableStatsID] ASC
-)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
-)
-GO
-ALTER TABLE [Windows].[TableStats] ADD DEFAULT (getdate()) FOR [CollectionDate]
-GO
-ALTER TABLE [Windows].[TableStats] WITH CHECK ADD FOREIGN KEY([InstanceID])
-REFERENCES [Windows].[Instance] ([InstanceID])
-GO
-SET ANSI_NULLS ON
-GO
-SET QUOTED_IDENTIFIER ON
-GO
-CREATE procedure [Windows].[TableStats_Insert]
-	@InstanceID int,
-	@DbName sysname,
-	@SchemaName sysname,
-	@TableName sysname,
-	@TotalRowCount bigint,
-	@DataSizeInMB int,
-	@IndexSizeInMB int
-AS
-BEGIN
-	SET NOCOUNT ON;
-	INSERT INTO Windows.TableStats (InstanceID, DbName, SchemaName, TableName, TotalRowCount, DataSizeInMB, IndexSizeInMB)
-	VALUES (@InstanceID, @DbName, @SchemaName, @TableName, @TotalRowCount, @DataSizeInMB, @IndexSizeInMB);
-END
-GO
-</code>

JiMetrics

User Tools

Site Tools

Differences

Page Tools