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

If you haven't created such a database, then create the following admin database now:

create database admin
go
use admin
go

Now create the following tables that store the SQL Server-related performance data. You can execute the
following T-SQL commands on every server or make it a part of the SQL Server installation. Here we are
creating five tables in the admin database:

❑ AWEAllocated

❑ LogFileUsage

❑ TopQueries

❑ TaskCount

❑ TopMemConsumption

The table AWEAllocated stores data related to the amount of memory allocated by the memory clerks
used by Address Windowing Extensions (AWE). SQL Server supports AWE, which enables the use of
physical memory over 4 gigabytes (GB) on 32-bit versions of Microsoft Windows operating systems.
The table LogFileUsage stores data related to log file usage in every database on the server. The table
TopQueries will store data related to the top ten queries being executed based on CPU usage. The table
TaskCount will store data related to SQL Scheduler on the server. The table TopMemConsumption stores
data related to all the memory clerks that are currently active in the server. Here is the code to create the
tables:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

USE [admin]
GO

/****** Object: Table [dbo].[AWEAllocated] Script Date: 08/21/2008 11:47:19 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].
[AWEAllocated]') AND type in (N'U'))
DROP TABLE [dbo].[AWEAllocated]
GO

CREATE TABLE [dbo].[AWEAllocated](
[AWE allocated, Mb] [bigint] NULL,
[servername] [sysname] NULL,
[datestamp] [datetime] NULL
) ON [PRIMARY]

GO

/****** Object: Table [dbo].[LogFileUsage] Script Date: 08/21/2008 11:47:49 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].
[LogFileUsage]') AND type in (N'U'))