If you want to record the results of some useful, instance level DMV queries somewhere where you can easily query them later, you can use this technique.  This gives you a pretty decent overview of the health of your instance by capturing AvgTaskCount, AvgRunnableTaskCount, AvgPendingIOCount, SQlServerCPUUtilization, and PageLifeExpectancy on a periodic basis.

I like to create a utility database called ServerMonitor on each one of my SQL instances. Then, I can use that database to hold instance level DMV queries wrapped in stored procedures. This way, I can ensure that the database is always available regardless of the mirroring status of other user databases on the instance.

The script below creates a table called SQLServerInstanceMetricHistory in that database. Then, after checking for SQL Server 2008 or greater and for Enterprise Edition, it enables Page compression on the clustered index for that table. Next, it creates a stored procedure called DBAdminRecordSQLServerMetrics that inserts rows into the SQLServerInstanceMetricHistory table. The next step would be to create a SQL Server Agent job that runs once a minute. The SQL Agent job would simply call this command:

EXEC dbo.DBAdminRecordSQLServerMetrics;

You could then query the SQLServerInstanceMetricHistory table to get a better idea of the average workload over time for that instance of SQL Server.

-- Logging SQL Server instance metrics
-- Glenn Berry 
-- March 2010
-- http://glennberrysqlperformance.spaces.live.com/
-- Twitter: GlennAlanBerry
-- Drop table if it exists
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = 
            OBJECT_ID(N'[dbo].[SQLServerInstanceMetricHistory]') 
            AND type in (N'U'))
DROP TABLE [dbo].[SQLServerInstanceMetricHistory]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Create table to hold metrics
CREATE TABLE [dbo].[SQLServerInstanceMetricHistory]
(
    [SQLServerInstanceMetricHistoryID] [bigint] IDENTITY(1,1) NOT NULL,
    [MeasurementTime] [datetime] NOT NULL,
    [AvgTaskCount] [int] NOT NULL,
    [AvgRunnableTaskCount] [int] NOT NULL,
    [AvgPendingIOCount] [int] NOT NULL,
    [SQLServerCPUUtilization] [int] NOT NULL,
    [PageLifeExpectancy] [int] NOT NULL,
 CONSTRAINT [PK_SQLServerInstanceMetricHistory] PRIMARY KEY CLUSTERED 
(
    [SQLServerInstanceMetricHistoryID] ASC
)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
-- Check for SQL Server 2008 or 2008 R2 and Enterprise Edition
IF LEFT(CONVERT(CHAR(2),SERVERPROPERTY('ProductVersion')), 2) = '10' 
   AND SERVERPROPERTY('EngineEdition') = 3
    BEGIN
        -- Use Page Compression on the clustered index 
        -- if we have SQL Server 2008 Enterprise Edition
        ALTER TABLE [dbo].[SQLServerInstanceMetricHistory] REBUILD PARTITION = ALL
        WITH (DATA_COMPRESSION = PAGE);
    END
GO
-- Drop stored procedure if it exists
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = 
            OBJECT_ID(N'[dbo].[DBAdminRecordSQLServerMetrics]') 
            AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[DBAdminRecordSQLServerMetrics]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* DBAdminRecordSQLServerMetrics ===================================================
Description : Used to keep track of instance level SQL Server Metrics
                        
Author: Glenn Berry    
Date: 3/9/2010    
Input:                            
Output:    
Used By: Only used to maintain the database                
Last Modified          Developer        Description
-------------------------------------------------------------------------------------
3/9/2010            Glenn Berry        Added Modification Comment
===================================================================================*/CREATE PROCEDURE [dbo].[DBAdminRecordSQLServerMetrics]
AS
    SET NOCOUNT ON;
    SET QUOTED_IDENTIFIER ON;
    SET ANSI_NULLS ON;
    
    DECLARE @PageLifeExpectancy int = 0;
    DECLARE @SQLProcessUtilization int = 0;
    
    -- Get PLE info
    SET @PageLifeExpectancy = (SELECT cntr_value AS [PageLifeExpectancy]
    FROM sys.dm_os_performance_counters
    WHERE object_name = 'SQLServer:Buffer Manager'
    AND counter_name = 'Page life expectancy'); 
    
    
    -- Get CPU Utilization History (SQL 2008 Only)
    SET @SQLProcessUtilization = (SELECT TOP(1) SQLProcessUtilization AS [SQLServerProcessCPUUtilization]              
    FROM ( 
          SELECT record.value('(./Record/@id)[1]', 'int') AS record_id, 
                record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') 
                AS [SystemIdle], 
                record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 
                'int') 
                AS [SQLProcessUtilization], [timestamp] 
          FROM ( 
                SELECT [timestamp], CONVERT(xml, record) AS [record] 
                FROM sys.dm_os_ring_buffers 
                WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' 
                AND record LIKE '%<SystemHealth>%') AS x 
          ) AS y 
    ORDER BY record_id DESC);
    
    
    -- Add metrics info to SQLServerInstanceMetricHistory
    INSERT INTO dbo.SQLServerInstanceMetricHistory
    (MeasurementTime, AvgTaskCount, AvgRunnableTaskCount, AvgPendingIOCount, 
     SQLServerCPUUtilization, PageLifeExpectancy)
    (SELECT GETDATE() AS [MeasurementTime], AVG(current_tasks_count)AS [AvgTaskCount], 
            AVG(runnable_tasks_count) AS [AvgRunnableTaskCount], 
            AVG(pending_disk_io_count) AS [AvgPendingDiskIOCount], 
            @SQLProcessUtilization, @PageLifeExpectancy
     FROM sys.dm_os_schedulers
     WHERE scheduler_id < 255);
     
    RETURN;
    
GO