How to Programmatically Capture SQL Server Performance Metrics
Automating the Capturing of Performance Metrics:

USE DBA;
GO
CREATE PROC GetMetrics
AS
SET NOCOUNT ON;

-- Variables for Counters
DECLARE @BatchRequestsPerSecond BIGINT;
DECLARE @CompilationsPerSecond BIGINT;
DECLARE @ReCompilationsPerSecond BIGINT;
DECLARE @LockWaitsPerSecond BIGINT;
DECLARE @PageSplitsPerSecond BIGINT;
DECLARE @CheckpointPagesPerSecond BIGINT;

-- Variable for date
DECLARE @stat_date DATETIME;

-- Table for First Sample
DECLARE @RatioStatsX TAbLE(
      [object_name] varchar(128)
      ,[counter_name] varchar(128)
      ,[instance_name] varchar(128)
      ,[cntr_value] bigint
      ,[cntr_type] int
      )

-- Table for Second Sample
DECLARE @RatioStatsY TABLE(
      [object_name] VARCHAR(128)
      ,[counter_name] VARCHAR(128)
      ,[instance_name] VARCHAR(128)
      ,[cntr_value] BIGINT
      ,[cntr_type] INT
      );

-- Capture stat time
SET @stat_date = getdate();

INSERT INTO @RatioStatsX (
     [object_name]
      ,[counter_name]
      ,[instance_name]
      ,[cntr_value]
      ,[cntr_type] )
      SELECT [object_name]
            ,[counter_name]
            ,[instance_name]
            ,[cntr_value]
            ,[cntr_type] FROM sys.dm_os_performance_counters;

-- Capture each per second counter for first sampling
SELECT TOP 1 @BatchRequestsPerSecond = cntr_value
      FROM @RatioStatsX
    WHERE counter_name = 'Batch Requests/sec'
      AND object_name LIKE '%SQL Statistics%';

SELECT TOP 1 @CompilationsPerSecond = cntr_value
      FROM @RatioStatsX
    WHERE counter_name = 'SQL Compilations/sec'
      AND object_name LIKE '%SQL Statistics%';

SELECT TOP 1 @ReCompilationsPerSecond = cntr_value
      FROM @RatioStatsX
    WHERE counter_name = 'SQL Re-Compilations/sec'
      AND object_name LIKE '%SQL Statistics%';

SELECT TOP 1 @LockWaitsPerSecond = cntr_value
      FROM @RatioStatsX
    WHERE counter_name = 'Lock Waits/sec'
      AND instance_name = '_Total'
      AND object_name LIKE '%Locks%';

SELECT TOP 1 @PageSplitsPerSecond = cntr_value
      FROM @RatioStatsX
    WHERE counter_name = 'Page Splits/sec'
      AND object_name LIKE '%Access Methods%';

SELECT TOP 1 @CheckpointPagesPerSecond = cntr_value
      FROM @RatioStatsX
      WHERE counter_name = 'Checkpoint Pages/sec'
        AND object_name LIKE '%Buffer Manager%';

WAITFOR DELAY '00:00:01'

-- Table for second sample
INSERT INTO @RatioStatsY (
            [object_name]
            ,[counter_name]
            ,[instance_name]
            ,[cntr_value]
            ,[cntr_type] )
   SELECT [object_name]
            ,[counter_name]
            ,[instance_name]
            ,[cntr_value]
            ,[cntr_type] FROM sys.dm_os_performance_counters

SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 [BufferCacheHitRatio]
      ,c.cntr_value AS [PageLifeExpectency]
      ,d.[BatchRequestsPerSecond]
      ,e.[CompilationsPerSecond]
      ,f.[ReCompilationsPerSecond]
      ,g.cntr_value AS [UserConnections]
      ,h.LockWaitsPerSecond
      ,i.PageSplitsPerSecond
      ,j.cntr_value AS [ProcessesBlocked]
      ,k.CheckpointPagesPerSecond
      ,GETDATE() AS StatDate
FROM (SELECT * FROM @RatioStatsY
               WHERE counter_name = 'Buffer cache hit ratio'
               AND object_name LIKE '%Buffer Manager%') a
     CROSS JOIN
      (SELECT * FROM @RatioStatsY
                WHERE counter_name = 'Buffer cache hit ratio base'
                AND object_name LIKE '%Buffer Manager%') b
     CROSS JOIN
      (SELECT * FROM @RatioStatsY
                WHERE counter_name = 'Page life expectancy '
                AND object_name LIKE '%Buffer Manager%') c
     CROSS JOIN
     (SELECT (cntr_value - @BatchRequestsPerSecond) /
                     (CASE WHEN datediff(ss,@stat_date, getdate()) = 0
                           THEN 1
                           ELSE datediff(ss,@stat_date, getdate()) END) AS [BatchRequestsPerSecond]
                FROM @RatioStatsY
                WHERE counter_name = 'Batch Requests/sec'
                AND object_name LIKE '%SQL Statistics%') d
     CROSS JOIN
     (SELECT (cntr_value - @CompilationsPerSecond) /
                     (CASE WHEN datediff(ss,@stat_date, getdate()) = 0
                           THEN 1
                           ELSE datediff(ss,@stat_date, getdate()) END) AS [CompilationsPerSecond]
                FROM @RatioStatsY
                WHERE counter_name = 'SQL Compilations/sec'
                AND object_name LIKE '%SQL Statistics%') e
     CROSS JOIN
     (SELECT (cntr_value - @ReCompilationsPerSecond) /
                     (CASE WHEN datediff(ss,@stat_date, getdate()) = 0
                           THEN 1
                           ELSE datediff(ss,@stat_date, getdate()) END) AS [ReCompilationsPerSecond]
                FROM @RatioStatsY
                WHERE counter_name = 'SQL Re-Compilations/sec'
                AND object_name LIKE '%SQL Statistics%') f
     CROSS JOIN
     (SELECT * FROM @RatioStatsY
               WHERE counter_name = 'User Connections'
               AND object_name LIKE '%General Statistics%') g
     CROSS JOIN
     (SELECT (cntr_value - @LockWaitsPerSecond) /
                     (CASE WHEN datediff(ss,@stat_date, getdate()) = 0
                           THEN 1
                           ELSE datediff(ss,@stat_date, getdate()) END) AS [LockWaitsPerSecond]
                FROM @RatioStatsY
                WHERE counter_name = 'Lock Waits/sec'
                AND instance_name = '_Total'
                AND object_name LIKE '%Locks%') h
     CROSS JOIN
     (SELECT (cntr_value - @PageSplitsPerSecond) /
                     (CASE WHEN datediff(ss,@stat_date, getdate()) = 0
                           THEN 1
                           ELSE datediff(ss,@stat_date, getdate()) END) AS [PageSplitsPerSecond]
                FROM @RatioStatsY
                WHERE counter_name = 'Page Splits/sec'
                AND object_name LIKE '%Access Methods%') i
     CROSS JOIN
     (SELECT * FROM @RatioStatsY
               WHERE counter_name = 'Processes blocked'
               AND object_name LIKE '%General Statistics%') j
     CROSS JOIN
     (SELECT (cntr_value - @CheckpointPagesPerSecond) /
                     (CASE WHEN datediff(ss,@stat_date, getdate()) = 0
                           THEN 1
                           ELSE datediff(ss,@stat_date, getdate()) END) AS [CheckpointPagesPerSecond]
                FROM @RatioStatsY
                WHERE counter_name = 'Checkpoint Pages/sec'
                AND object_name LIKE '%Buffer Manager%') k
To complete the automation process I need do two additional things. One is to create the table that will store these metrics, which can be accomplished by running the following CREATE TABLE statement. Note this code again assumes that there is a DBA database where the table will be created:
USE DBA;
GO
CREATE TABLE [dbo].[PerformanceMetricHistory](
      [BufferCacheHitRatio] [numeric](38, 13) NULL,
      [PageLifeExpectency] [bigint] NULL,
      [BatchRequestsPerSecond] [bigint] NULL,
      [CompilationsPerSecond] [bigint] NULL,
      [ReCompilationsPerSecond] [bigint] NULL,
      [UserConnections] [bigint] NULL,
      [LockWaitsPerSecond] [bigint] NULL,
      [PageSplitsPerSecond] [bigint] NULL,
      [ProcessesBlocked] [bigint] NULL,
      [CheckpointPagesPerSecond] [bigint] NULL,
      [StatDate] [datetime] NOT NULL
) ON [PRIMARY]

The second thing is to create a SQL Server Agent job that is schedule to run however often I want to capture these statistics. Below is the code to create a SQL Server Agent Job name 'Collect Performance Metrics' that runs every minute:

USE [msdb]
GO
/****** Object: Job [Collect Performance Metrics]    Script Date: 07/22/2011 14:01:05 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]]    Script Date: 07/22/2011 14:01:05 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Collect Performance Metrics',
            @enabled=1,
            @notify_level_eventlog=0,
            @notify_level_email=0,
            @notify_level_netsend=0,
            @notify_level_page=0,
            @delete_level=0,
            @description=N'No description available.',
            @category_name=N'[Uncategorized (Local)]',
            @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Collect Metrics]    Script Date: 07/22/2011 14:01:05 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Collect Metrics',
            @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'INSERT INTO EXEC dbo.GetMetrics',
            @database_name=N'DBA',
            @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'Every Minute',
            @enabled=1,
            @freq_type=4,
            @freq_interval=1,
            @freq_subday_type=4,
            @freq_subday_interval=1,
            @freq_relative_interval=0,
            @freq_recurrence_factor=0,
            @active_start_date=20110722,
            @active_end_date=99991231,
            @active_start_time=0,
            @active_end_time=235959
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:

Monitor SQL Server using Performance Monitor on Windows Server 2008 R2

Open Performance Monitor (AKA perfmon)
Setup the Data Collector Set
Start the Data Collector Set
Stop the Data Collector Set

Step 1: Open Performance Monitor (AKA perfmon)

Press the Start + R keys to get the Run dialog.
Type “perfmon”:
Click the “OK” button.

Step 2: Setup the Data Collector Set

Left click the arrow found to the left of "Data Collector Sets" to expand the tree:
Right click "User Defined", and select "New > Data Collector Set".
Specify a unique name for the Data Collector Set:
Select the "Create manually (Advanced)" radio button.
Click the "Next" button.
Check off "Performance counter", found under the "Create data logs":
Click the "Next" button.
Click the "Add" button.
OPTIONAL: If using Performance Monitor to watch a remote host.
1. Type the hostname into the “Select counters from computer” text field:
2. Press the Tab key to trigger the dialog to query the remote host specified for available counters, and refresh the list of counters (can take a couple of minutes).
Scroll to "MSSQL$[appropriate instance name]: General Statistics".
Click the "+" to expand the node (found to the right of "MSSQL$[appropriate instance name]: General Statistics)
Select "User Connections".
Click the "Add" button.
Scroll to "MSSQL$[appropriate instance name]: Locking".
Click the "+" to expand the node (found to the right of "MSSQL$[appropriate instance name]: Locking)
Select "Average Wait Time (ms)".
Select "Database" for "Instances of selected object".
Click the "Add" button.
Click the "OK" button.
OPTIONAL: Change the log location.
Click the "Next" button.
OPTIONAL: Change the user account this Data Collector Set will use for credentials.
Click the "Finish" button.