Skip to main content

Collect and store historical performance counter data using SQL Server DMV sys.dm_os_performance

http://www.mssqltips.com/tip.asp?tip=2188

This process involves three steps, each described separately below.

Step One

Create the Registered Server Group for the SQL Instances you intend to push this solution. I tend to do this step in three phases:

Two test instances (one default instance and one named instance – you’ll see why shortly when we look at the results that are returned for queries against sys.dm_os_performance_counters for named instances.)
A group of remaining non-production SQL instances since I intend to push out the solution and monitor its behavior for a couple days before I push to production servers.
The production instances of SQL Server that I intend to run this process for monitoring.
It should be noted that since we’re dealing with Dynamic Management Views that this solution will only work for SQL Server 2005 and newer. Only for the purposes of this article have I created the MSSQLTips registered servers group. It contains two instances: the default instance and the MAPS named instance.



Before proceeding I'd like to show you quickly what to expect when you query sys.dm_os_performance_counters. If you read the tip I provided above you'll have a good understanding of what to expect - for the default instance. However, the results are different for named instances when it comes to the [object_name] column values. Let's take a look at the results of a simple query against this DMV for both a default and named instance and you'll see what I mean, furthermore it will lead you to understand some of the dynamic T/SQL I employ in the next step when I populate the list of counters I intend to persist.

SELECT [object_name], [counter_name], [instance_name], [cntr_value]
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio';
When run against the default instance the results appear as such:



When run against a named instance you'll see the results differ when it comes to the object_name column:



You'll see in the next step how I account for the fact that the instance name is integrated into the object_name value.


--------------------------------------------------------------------------------

Step Two

I utilize a dedicated database on all my SQL Servers to host tables, views, stored procedures and functions to run in a uniform fashion against all my SQL Server databases and instances.

Here at MSSQLTips I’ve always referred to it as iDBA (named changed to protect the innocent DBA.) So, in my iDBA database (or whatever name you prefer on your SQL Servers) I create the necessary objects to support the data collection process from sys.dm_os_performance_counters:

MetaBOT schema
MetaBOT.dm_os_performance_counters table – hosts the collected results from sys.dm_os_performance_counters
MetaBOT.watched_counters table – hosts the counters that I intend to collect
USE [iDBA];
GO

CREATE SCHEMA MetaBOT AUTHORIZATION dbo;
GO

IF NOT EXISTS (SELECT name FROM iDBA.sys.[tables] T WHERE name = 'dm_os_performance_counters')
CREATE TABLE [MetaBOT].[dm_os_performance_counters]
(
[object_name] NVARCHAR(128),
[counter_name] NVARCHAR(128),
[instance_name] NVARCHAR(128),
[cntr_value] bigint,
[date_stamp] DATETIME
);

IF NOT EXISTS (SELECT name FROM iDBA.sys.[tables] T WHERE name = 'watched_counters')
CREATE TABLE [iDBA].[MetaBOT].[watched_counters]
(
[object_name] NVARCHAR(128),
[counter_name] NVARCHAR(128),
[active] bit
);

GO
I then populate the MetaBOT.watched_counters table with values and finally create a stored procedure that I can call from inside a scheduled SQL Server Agent job to populate the MetaBOT.dm_os_performance_counters table on a consistent basis. This is where I account for the named instance's impact on the object_name column by building the value dynamically based upon the core object name and the instance name as derived from use of the @@SERVICENAME constant:

--+-- Now populate the watched counters table based upon instance properties
DECLARE @NamedInstance bit
DECLARE @ObjectNamePrefix VARCHAR(50)
SELECT @NamedInstance = 1

IF @@SERVICENAME = 'MSSQLSERVER'
BEGIN
SELECT @NamedInstance = 0 --This is the default instance

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Buffer Manager', 'Buffer cache hit ratio', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Buffer Manager', 'Buffer cache hit ratio base', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Buffer Manager', 'Database pages', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Buffer Manager', 'Free pages', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Buffer Manager', 'Page life expectancy', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Buffer Manager', 'Page lookups/sec', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Buffer Manager', 'Page reads/sec', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Buffer Manager', 'Page writes/sec', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Buffer Manager', 'Reserved pages', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Buffer Manager', 'Stolen pages', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Buffer Manager', 'Target pages', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Buffer Manager', 'Total pages', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Cursor Manager by Type', 'Active cursors', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Databases', 'Active Transactions', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Databases', 'Data File(s) Size (KB)', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Databases', 'Log File(s) Size (KB)', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Databases', 'Log File(s) Used Size (KB)', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Databases', 'Percent Log Used', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Databases', 'Transactions/sec', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Exec Statistics', 'DTC calls', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Exec Statistics', 'OLEDB calls', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_namme], [counter_name], [active])
VALUES ('SQLServer:General Statistics', 'Transactions', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:General Statistics', 'User Connections', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Latches', 'Latch Waits/sec', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Latches', 'Total Latch Wait Time (ms)', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Locks', 'Average Wait Time (ms)', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Locks', 'Lock Wait Time (ms)', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Memory Manager', 'Target Server Memory (KB)', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Memory Manager', 'Total Server Memory (KB)', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Plan Cache', 'Cache Hit Ratio', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Plan Cache', 'Cache Hit Ratio Base', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Plan Cache', 'Cache Pages', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:SQL Statistics', 'Batch Requests/sec', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Transactions', 'Free Space in tempdb (KB)', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Transactions', 'Longest Transaction Running Time', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Transactions', 'NonSnapshot Version Transactions', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Transactions', 'Snapshot Transactions', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Transactions', 'Transactions', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Transactions', 'Update Snapshot Transactions', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Transactions', 'Version Store Size (KB)', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Wait Statistics', 'Lock waits', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Wait Statistics', 'Log buffer waits', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Wait Statistics', 'Log write waits', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Wait Statistics', 'Memory grant queue waits', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Wait Statistics', 'Network IO waits', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Wait Statistics', 'Non-Page latch waits', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Wait Statistics', 'Page IO latch waits', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Wait Statistics', 'Page latch waits', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Wait Statistics', 'Thread-safe memory objects waits', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Wait Statistics', 'Transaction ownership waits', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Wait Statistics', 'Wait for the worker', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Wait Statistics', 'Workspace synchronization waits', 1);
END

ELSE
BEGIN
--Account for named instance when adding object names to watch
SELECT @ObjectNamePrefix = 'MSSQL$' + @@SERVICENAME

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Buffer Manager', 'Buffer cache hit ratio', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Buffer Manager', 'Buffer cache hit ratio base', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Buffer Manager', 'Database pages', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Buffer Manager', 'Free pages', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Buffer Manager', 'Page life expectancy', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Buffer Manager', 'Page lookups/sec', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Buffer Manager', 'Page reads/sec', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Buffer Manager', 'Page writes/sec', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Buffer Manager', 'Reserved pages', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Buffer Manager', 'Stolen pages', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Buffer Manager', 'Target pages', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Buffer Manager', 'Total pages', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Cursor Manager by Type', 'Active cursors', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Databases', 'Active Transactions', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Databases', 'Data File(s) Size (KB)', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Databases', 'Log File(s) Size (KB)', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Databases', 'Log File(s) Used Size (KB)', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Databases', 'Percent Log Used', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Databases', 'Transactions/sec', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Exec Statistics', 'DTC calls', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Exec Statistics', 'OLEDB calls', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':General Statistics', 'Transactions', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':General Statistics', 'User Connections', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Latches', 'Latch Waits/sec', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Latches', 'Total Latch Wait Time (ms)', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Locks', 'Average Wait Time (ms)', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Locks', 'Lock Wait Time (ms)', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Memory Manager', 'Target Server Memory (KB)', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Memory Manager', 'Total Server Memory (KB)', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Plan Cache', 'Cache Hit Ratio', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Plan Cache', 'Cache Hit Ratio Base', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Plan Cache', 'Cache Pages', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':SQL Statistics', 'Batch Requests/sec', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Transactions', 'Free Space in tempdb (KB)', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Transactions', 'Longest Transaction Running Time', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Transactions', 'NonSnapshot Version Transactions', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Transactions', 'Snapshot Transactions', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Transactions', 'Transactions', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Transactions', 'Update Snapshot Transactions', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Transactions', 'Version Store Size (KB)', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Wait Statistics', 'Lock waits', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Wait Statistics', 'Log buffer waits', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Wait Statistics', 'Log write waits', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Wait Statistics', 'Memory grant queue waits', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Wait Statistics', 'Network IO waits', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Wait Statistics', 'Non-Page latch waits', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Wait Statistics', 'Page IO latch waits', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Wait Statistics', 'Page latch waits', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Wait Statistics', 'Thread-safe memory objects waits', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Wait Statistics', 'Transaction ownership waits', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Wait Statistics', 'Wait for the worker', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Wait Statistics', 'Workspace synchronization waits', 1);
END
You may note that I track quite a few counters here - I figure if I go through the effort of setting this up then I may as well capture too much than not enough. Depending upon your environment and the number of databases you're hosting you may wish to persist fewer counters. Some of these counters exist in a 1:1 relationship to your database count. I did calculations based upon my most-populous instance and determined that each row of data persisted averaged 232 bytes; from there I then looked at how frequently I ran the associated collection job impacted the amount of data I was storing. Running the collection process every 10 minutes was estimated to yeild 1.5 gb/month. Increasing the frequency to every two minutes meant increasing the storage required to 8 gb/month - and this was for a single instance.

Now it's time to create the stored procedure that you'll call from inside that SQL Server Agent job. It is a simple INSERT statement from sys.dm_os_performance_counters filtered through an INNER JOIN against the MetaBOT.watched_counters table we created and populated in the last step. I didn't bother with creating any indexes in that table as it is so small that the optimizer would choose to scan the table instead of using the index. The maintance overhead of an unused index is unwarranted.

CREATE PROCEDURE MetaBOT.usp_collect_perfmon_counters AS
DECLARE @datestamp DATETIME
SELECT @datestamp = GETDATE()
INSERT INTO MetaBOT.[dm_os_performance_counters]
(
[object_name],
[counter_name],
[instance_name],
[cntr_value],
[date_stamp]
)
SELECT
DOPC.[object_name],
DOPC.[counter_name],
DOPC.[instance_name],
DOPC.[cntr_value],
@datestamp
FROM sys.[dm_os_performance_counters] DOPC
INNER JOIN iDBA.[MetaBOT].[watched_counters] WC
ON [DOPC].[object_name] = [WC].[object_name]
AND [DOPC].[counter_name] = [WC].[counter_name]
ORDER BY [object_name], [counter_name];
--------------------------------------------------------------------------------

Step Three

The final step involves creating the actual job to run on each instance for collection of counter values.

What I find easiset to do when coding SQL Agent Job creation is to build the job in the SQL Server Mangement Studio GUI and then script it out. That is what I did here. There is one change you need to make when doing this however - you must either remove or comment-out the line of code that presents the job_id GUID for output. It's not needed and will cause the script to fail across all but the initial instance when run. There is a single variable in this code that allows you to set how frequently the job runs (@RunEveryXMinutes); set the value equal to the number of minutes you want to expire between job runs.

USE [msdb]
GO
/****** Object: Job [Metadata_Collect_SQL_Perfmon_Counters] Script Date: 12/09/2010 12:14:38 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
DECLARE @RunEveryXMinutes TINYINT
SELECT @ReturnCode = 0
SELECT @RunEveryXMinutes = 10
/****** Object: JobCategory [Tuning and Optimization] Script Date: 12/09/2010 12:14:39 ******/
IF NOT EXISTS (
SELECT name
FROM msdb.dbo.syscategories
WHERE name=N'Tuning and Optimization'
AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Tuning and Optimization'
IF (@@ERROR <> 0
OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Metadata_Collect_SQL_Perfmon_Counters',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Poll and store output from sys.dm_os_performance_counters',
@category_name=N'Tuning and Optimization',
@owner_login_name=N'SPECTRUM-HEALTH\svcSQLNotify', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0
OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Collect Metadata] Script Date: 12/09/2010 12:14:39 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Collect Metadata',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC MetaBOT.usp_collect_perfmon_counters;',
@database_name=N'iDBA',
@flags=0
IF (@@ERROR <> 0
OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0
OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'dm_os_perfmon_counters Job',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=@RunEveryXMinutes,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20101209,
@active_end_date=99991231,
@active_start_time=30,
@active_end_time=235959
-- ,@schedule_uid=N'519325e6-3114-453c-bfeb-6597300a45d2' <---Comment this line out of auto-generated script
IF (@@ERROR <> 0
OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0
OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0)
ROLLBACK TRANSACTION
EndSave:
GO
Once activated it's just the matter of querying the persisted values in the MetaBOT.dm_os_performance_counters table. I also recommend that you set up a purge command, this can be done as part of the MetaBOT.usp_collect_perfmon_counters stored procedure as a DELETE from the MetaBOT.dm_os_performance_counters table where the date_stamp column is less than whatever threshold you want to set. Depending upon your needs I would also recommend creating an index on that column if your performance reviews warrant doing so.

In the next tip of this series I'll examine different ways to disect this data and use it for analysis for such things as consolidation planning or reviewing for good people doing bad things in your databases. (Think CURSORS! folks!)

Comments

Post a Comment

Popular posts from this blog

Opatch apply/lsinventory error: oneoff is corrupted or does not exist

I am applying the quarterly patch for 19c RDBMS, I tried using napply but failed, but somehow it corrupted the inventory though nothing applied. further apply and lsinventory command ran into error like this: $ ./OPatch/opatch lsinventory Oracle Interim Patch Installer version 12.2.0.1.21 Copyright (c) 2020, Oracle Corporation.  All rights reserved. Oracle Home       : /u02/app/oracle/19.0.0 Central Inventory : /u01/app/oraInventory    from           : /u02/app/oracle/19.0.0/oraInst.loc OPatch version    : 12.2.0.1.21 OUI version       : 12.2.0.7.0 Log file location : /u02/app/oracle/19.0.0/cfgtoollogs/opatch/opatch2020-09-08_13-35-59PM_1.log Lsinventory Output file location : /u02/app/oracle/19.0.0/cfgtoollogs/opatch/lsinv/lsinventory2020-09-08_13-35-59PM.txt -------------------------------------------------------------------------------- Inventory load failed... OPatch cannot load inventory for the given Oracle Home. LsInventorySession failed: Unable to create patchObject Possible ca
Post a Comment
Read more

non-existent process lock port on windows server

I have a database link created between oracle and sqlserver using oracle tg4odbc, the product is installed on windows server and run as service "OracleOraGtw11g_home1TNSListener", but sometime the service cannot started, the root cause of this problem is that the port number 1521 is used by an non-existent process. The first step is to use netstat -bano|find "1521" to get the process id, in my case it's 5844, which shows the connection is from my oracle server 10.8.0.169 H:\>netstat -bano|find "1521"   TCP    0.0.0.0:1521           0.0.0.0:0              LISTENING       5844   TCP    10.14.45.33:1521       10.8.0.169:42987       ESTABLISHED     5844 however the process id does not show in either task manager or process explorer. The next step is to run tcpview, which shows non-existent under process column, there are three rows, two show status as "listening", the other one shows status "established", right click and k
3 comments
Read more

oracle dba_hist_sysmetric_summary

found this blog is helpful to get CPU and IO statistics on oracle database. http://shob-dbadmin.blogspot.ca/2012/12/how-to-find-total-io-of-database.html courtesy to  Shomil Bansal , below are hist writing, not mine. How to find total IO of the database instance Total IO of database instance is sum of the physical reads, physical writes and redo writes. There are several views to find these values. v$sysmetric  - Reports metric values for only the most current time sample 60 secs. v$sysmetric_summary  - Reports metric values for time sample of 1 hour. v$sysmetric_history  - Reports metric values every 60 sec from the time instance is up. Better way to analyse IO using this view to take deltas between two time periods. dba_hist_sysmetric_history  - All the above views are refreshed when the instance is restarted. This view, part of AWR, stores the historical stats. I have used this view for my report. Query: ====== set lines 350 pages 50 feedback off set markup html
Post a Comment
Read more