If you are sending to server requests under the form of a query( not call to a stored procedure) , this is call ad hoc queries, and you are doing a ad hoc workload.
It is important to keep this under control , so that your plan cache will not grow under control. So, you have to check to plan cache , daily, and maybe hourly, depending on your system.
For this, you need some objects:
This is keeping the list of counters:
IF OBJECT_ID(‘dbo._Counters’,’U’) IS NULL
BEGIN
CREATE TABLE dbo._Counters
(
cnt_ID INT NOT NULL IDENTITY(1,1)
,cnt_Name VARCHAR(50) NOT NULL
,cnt_Description VARCHAR(200) NOT NULL
,CONSTRAINT pk__Counters_cntID PRIMARY KEY CLUSTERED(cnt_ID)
);
END
Next two tables, are keeping the raw data.
IF OBJECT_ID(‘dbo.Monitoring_Header’,’U’) IS NULL
BEGIN
CREATE TABLE dbo.Monitoring_Header
(
mnh_ID INT NOT NULL IDENTITY(1,1)
,mnh_Date DATE NOT NULL
,mnh_InsertedDate DATETIME2(0) NOT NULL DEFAULT SYSDATETIME()
,CONSTRAINT pk_Monitoring_Header_mnhID PRIMARY KEY CLUSTERED(mnh_ID)
–,CONSTRAINT df_Monitoring_Header_InsertedDate DEFAULT GETDATE() FOR mnh_InsertedDate
);
END
DROP TABLE IF EXISTS dbo.Monitoring_Details;
IF OBJECT_ID(‘dbo.Monitoring_Details’,’U’) IS NULL
BEGIN
CREATE TABLE dbo.Monitoring_Details
(
mnd_ID INT NOT NULL IDENTITY(1,1)
,mnd_mnh_ID INT NOT NULL
,mnd_cnt_ID INT NOT NULL
,mnd_dbID SMALLINT NOT NULL
,mnd_Value DECIMAL(16,2) NOT NULL
,CONSTRAINT pk_Monitoring_Details_mndID_cntID_dbName PRIMARY KEY NONCLUSTERED(mnd_ID)
,CONSTRAINT fk_Monitoring_Details_Header_mnh_ID
FOREIGN KEY ( mnd_mnh_id)
REFERENCES dbo.Monitoring_Header
,CONSTRAINT fk_Monitoring_Details__Counters_cntID
FOREIGN KEY (mnd_cnt_id)
REFERENCES dbo._Counters
,CONSTRAINT UQ_C_Monitoring_Details_mnhID_cntID_dbID UNIQUE CLUSTERED
(mnd_mnh_id
,mnd_cnt_id
,mnd_dbID)
);
/*CREATE UNIQUE CLUSTERED INDEX idx_Monitoring_Details_mnhID_cntID_dbName ON dbo.Monitoring_Details
(mnd_mnh_id
,mnd_cnt_id
,mnd_dbName)
*/
END
Next 2 tables, are some aggregation, for better understand the trends.
One is aggregation by DAY.
IF OBJECT_ID(‘dbo.Monitoring_AGG_ByDay’,’U’) IS NULL
BEGIN
CREATE TABLE dbo.Monitoring_AGG_ByDay
(
mad_ID INT NOT NULL IDENTITY(1,1)
,mad_Date DATE NOT NULL
,mad_cnt_ID INT NOT NULL
,mad_dbID SMALLINT NOT NULL
,mad_MinValue DECIMAL(16,2) NOT NULL
,mad_MaxValue DECIMAL(16,2) NOT NULL
,mad_AvgValue DECIMAL(16,2) NOT NULL
,CONSTRAINT pk_Monitoring_AGG_ByDay_madID PRIMARY KEY NONCLUSTERED(mad_ID)
,CONSTRAINT fk_Monitoring_AGG_ByDay__Counters_cntID
FOREIGN KEY (mad_cnt_ID)
REFERENCES dbo._Counters
,CONSTRAINT UQ_C_Monitoring_AGG_ByDay_mnhID_cntID_dbID UNIQUE CLUSTERED
(mad_Date
,mad_cnt_ID
,mad_dbID)
);
END
And this by Month
IF OBJECT_ID(‘dbo.Monitoring_AGG_ByMonth’,’U’) IS NULL
BEGIN
CREATE TABLE dbo.Monitoring_AGG_ByMonth
(
mam_ID INT NOT NULL IDENTITY(1,1)
,mam_Date DATE NOT NULL
,mam_cnt_ID INT NOT NULL
,mam_dbID SMALLINT NOT NULL
,mam_MinValue DECIMAL(16,2) NOT NULL
,mam_MaxValue DECIMAL(16,2) NOT NULL
,mam_AvgValue DECIMAL(16,2) NOT NULL
,CONSTRAINT pk_Monitoring_AGG_ByMonth_mamID PRIMARY KEY NONCLUSTERED(mam_ID)
,CONSTRAINT fk_Monitoring_AGG_ByMonth__Counters_cntID
FOREIGN KEY (mam_cnt_ID)
REFERENCES dbo._Counters
,CONSTRAINT UQ_C_Monitoring_AGG_ByMonth_mnhID_cntID_dbID UNIQUE CLUSTERED
(mam_Date
,mam_cnt_ID
,mam_dbID)
);
END
INSERT INTO dbo._Counters(cntName,cntDescription)
VALUES(‘adHoc cached plan size’,’ad Hoc size in cached plan’);
For this, you need some stored procedure, that are good to be place inside some jobs.
One stored procedure is populating the raw tables Header and Details:
CREATE OR ALTER PROCEDURE dbo.Monitoring_adHoc
AS
BEGIN
SET NOCOUNT ON;
DECLARE @d_date AS DATE = CAST(SYSDATETIME() AS DATE)
,@i_mnhID INT = 0
,@i_cntID INT = 1; — tbl dbo._Counters – adHoc size
BEGIN TRY
–BEGIN TRANSACTION
INSERT INTO dbo.Monitoring_Header(mnh_Date)
VALUE(@d_date)
SELECT @i_mnhID = SCOPE_IDENTITY();
INSERT INTO dbo.Monitoring_Details(mnd_mnh_ID,mnd_cnt_ID,mnd_dbID,mnd_Value)
SELECT @i_mnhID, @i_cntID, dbid, SUM(CAST(size_in_bytes AS bigint))/1024/1024
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE
cp.cacheobjtype = N’Compiled Plan’
AND cp.objtype IN(N’Adhoc’, N’Prepared’)
AND cp.usecounts = 1
GROUP BY dbid
OPTION (RECOMPILE);
–COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRAN;
/* you can log to logTable…*/
END CATCH
END
Stored procedure , for doing Aggregation by day and populate the right table:
/* grouping informations from Monitoring and inserting into the AGG_ByDay table for previous day*/
CREATE OR ALTER PROCEDURE dbo.Monitoring_AGG_ByDay
AS
BEGIN
INSERT INTO dbo.Monitoring_AGG_ByDay(mad_Date,mad_cnt_ID,mad_dbID,mad_MinValue,mad_MaxValue,mad_AvgValue)
SELECT mh.mnh_Date
,md.mnd_cnt_ID
,md.mnd_dbID
,MIN(mnd_Value),MAX(mnd_Value),AVG(mnd_Value)
FROM
dbo.Monitoring_Header AS mh
INNER JOIN dbo.Monitoring_Details AS md
ON mh.mnh_id = md.mnd_mnh_id
WHERE mh.mnh_Date >= CAST(DATEADD(dAY,-1,GETDATE()) AS DATE)
AND mh.mnh_Date < CAST(GETDATE()AS DATE)
GROUP BY mh.mnh_Date,md.mnd_cnt_ID,md.mnd_dbID
END
Stored procedure for agg by month
CREATE OR ALTER PROCEDURE dbo.Monitoring_AGG_ByMonth
AS
BEGIN
INSERT INTO dbo.Monitoring_AGG_ByDay(mad_Date,mad_cnt_ID,mad_dbID,mad_MinValue,mad_MaxValue,mad_AvgValue)
SELECT CAST(DATEADD(MONTH,DATEDIFF(MONTH,0,mh.mnh_Date)-1,0) AS DATE)
,md.mnd_cnt_ID
,md.mnd_dbID
,MIN(mnd_Value),MAX(mnd_Value),AVG(mnd_Value)
FROM
dbo.Monitoring_Header AS mh
INNER JOIN dbo.Monitoring_Details AS md
ON mh.mnh_id = md.mnd_mnh_id
WHERE mh.mnh_Date >= CAST(DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())-1,0) AS DATE) –previous month
AND mh.mnh_Date < CAST(DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0) AS DATE) — current month
GROUP BY mh.mnh_Date,md.mnd_cnt_ID,md.mnd_dbID
END
And also, for old records in the raw table , maybe need to delete/purge from Details and Header.
CREATE OR ALTER PROCEDURE dbo.Monitoring_Purge_old_records
AS
BEGIN
DECLARE @i_rc AS INT = 0
,@i_j AS INT = 0
BEGIN TRY
WHILE (1=1)
BEGIN
BEGIN TRANSACTION;
DELETE TOP(100) md
FROM
dbo.Monitoring_Details as md
INNER JOIN dbo.Monitoring_Header as mh
ON md.mnd_mnh_ID = mh.mnh_ID
WHERE
mh.mnh_Date < CAST(DATEADD(DAY,-92,GETDATE()) AS DATE);
SET @i_rc = @@ROWCOUNT;
IF @i_rc < 100 BREAK;
SET @i_j = @i_j + 1;
IF @i_j = 10 /* less log activity*/
BEGIN
SET @i_j = 0;
COMMIT TRANSACTION;
END
END /* from while*/
IF @@TRANCOUNT > 0
COMMIT TRANSACTION; /* expected @@tranCount to be 1 ; so commit and close*/
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION; /* in this step @@tranCount should be 0 , in other way , rollback it*/
DELETE mh
FROM dbo.Monitoring_Header AS mh
WHERE mh.mnh_Date < CAST(DATEADD(DAY,-92,GETDATE()) AS DATE);
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH
END
Now , place this stored procedure , each one, in a right job , and start to monitor it.
S
Sql Server 