--==================================================
-- Create the Table
--==================================================
CREATE TABLE [dbo].[DatabaseTPM](
[DatabaseTPMID] [bigint] IDENTITY(1,1) NOT NULL,
DatabaseTPMDateID int,
DatabaseTPMDateHour int,
DatabaseTPMDateMinute int ,
DatabaseName varchar(120),
[ServerName] [varchar](100) NOT NULL,
[PerformanceIndicatorCategoryName] [varchar](100) NOT NULL,
[PerformanceIndicatorName] [varchar](100) NOT NULL,
[PerformanceAVGIndicatorValue] [float] NULL,
[PerformanceIndicatorValue] BIGINT NOT NULL,
[CreatedDateOn] [datetime] NOT NULL,
CONSTRAINT [DatabaseTPMID_pk] PRIMARY KEY CLUSTERED
(
[DatabaseTPMID] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY]
GO
--==================================================
-- Store Procedure to collect the Data
--==================================================
use DBA
Go
Create or alter Proc sp_DBAWEB_DatabaseTPM (@DatabaseName Varchar(60))
as
/*****************************************************************************************************************
Name: dbo.sp_DBAWEB_DatabaseTPM
View all the SQL Transactions per minute.
*****************************************************************************************************************
Change History
*****************************************************************************************************************
Date: Author: Description:
----- ------- -------------------
19 Jan 2019 Leandro Buffone Created Initial Version
*****************************************************************************************************************
Usage:
set statistics io on
set statistics time on
Exec sp_DBAWEB_DatabaseTPM 'DatabaseName'
*****************************************************************************************************************/
DECLARE @First BIGINT
DECLARE @Second BIGINT
SELECT @First = cntr_value
FROM sys.dm_os_performance_counters
WHERE
counter_name = 'Transactions/sec'
AND instance_name = @DatabaseName
--AND instance_name = '_Total';
-- Following is the delay
WAITFOR DELAY '00:01:00'
-- Second PASS
SELECT @Second = cntr_value
FROM sys.dm_os_performance_counters
WHERE
counter_name = 'Transactions/sec'
AND instance_name = @DatabaseName
--AND instance_name = '_Total';
SELECT
DatabaseTPMDateID = dbo.fnDateKey(getdate()),
DatabaseTPMDateHour = Datepart(hour,getdate()),
DatabaseTPMDateMinute = Datepart(minute,getdate()),
ServerName = @@ServerName,
DatabaseName = @DatabaseName,
[PerformanceIndicatorCategoryName] = 'SQL Server',
[PerformanceIndicatorName] = 'Transaction Per Minute',
[PerformanceIndicatorValue] = (@Second - @First),
[CreatedDateOn] = GetDate()
GO
--==================================================
-- Save the Data through the Table
--==================================================
Insert [dbo].[DatabaseTPM] ( DatabaseTPMDateID,
DatabaseTPMDateHour,
DatabaseTPMDateMinute,
ServerName,
DatabaseName,
PerformanceIndicatorCategoryName,
PerformanceIndicatorName,
PerformanceIndicatorValue,
CreatedDateOn
)
Exec sp_DBAWEB_DatabaseTPM 'TurnersGroup_MSCRM'
--==================================================
-- Create the Table
--==================================================
CREATE TABLE [dbo].[DatabaseTPM](
[DatabaseTPMID] [bigint] IDENTITY(1,1) NOT NULL,
DatabaseTPMDateID int,
DatabaseTPMDateHour int,
DatabaseTPMDateMinute int ,
DatabaseName varchar(120),
[ServerName] [varchar](100) NOT NULL,
[PerformanceIndicatorCategoryName] [varchar](100) NOT NULL,
[PerformanceIndicatorName] [varchar](100) NOT NULL,
[PerformanceAVGIndicatorValue] [float] NULL,
[PerformanceIndicatorValue] BIGINT NOT NULL,
[CreatedDateOn] [datetime] NOT NULL,
CONSTRAINT [DatabaseTPMID_pk] PRIMARY KEY CLUSTERED
(
[DatabaseTPMID] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY]
GO