Collecting SQL Server Performance and Host Performance Data - Professional SQL Server 2008 Administration with Windows PowerShell - page 404

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].
[TopMemConsumption]') AND type in (N'U'))
DROP TABLE [dbo].[TopMemConsumption]
GO

CREATE TABLE [dbo].[TopMemConsumption](
[servername] [nvarchar](128) NULL,
[type] [nvarchar](60) NOT NULL,
[SPA Mem, Kb] [bigint] NULL,
[datestamp] [datetime] NOT NULL
) ON [PRIMARY]

GO

Now create the following procedure that retrieves the data from various dynamic management views
(DMVs) and stores the SQL server-related performance data into the tables you just created. The DMVs
used in this procedure are as follows:

❑

sys.dm_os_schedulers

❑

sys.dm_os_memory_clerks

❑

sys.dm_exec_query_stats

❑

sys.dm_exec_sql_text

❑

sys.dm_os_performance_counters

Microsoft introduced dynamic management views and functions to get server state information that can
be used to monitor the health of a server instance, diagnose problems, and tune performance. We use the
preceding DMVs to get specific information:

❑ Log file usage from sys.dm_os_performance_counters

❑ AWE memory from sys.dm_os_memory_clerks

❑ Top ten queries from sys.dm_exec_query_stats and sys.dm_exec_sql_text

❑ SQL Server Scheduler task counts from sys.dm_os_schedulers

❑ Top memory consumption by type of memory clerk from sys.dm_os_memory_clerks

USE [admin]
GO

/****** Object: StoredProcedure [dbo].[uspCollectPerfData] Script Date: 08/21/2008

11:53:23 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].
[uspCollectPerfData]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[uspCollectPerfData]
GO

CREATE PROCEDURE uspCollectPerfData as
set nocount on

INSERT INTO TaskCount (servername,scheduler_id,current_tasks_count,
runnable_tasks_count,datestamp )