Activity Monitor

The Activity Monitor page has the following sections:

• Overview – Shows graphical displays of the percent of processor time, number of waiting tasks, database I/O (megabytes per second), and batch requests (number per second).
• Processes – Shows information for active user connections to SQL Server Database Engine.
• Resource Waits – Shows wait state information.
• Data File I/O – Shows I/O information for database data and log files.
• Recent Expensive Queries – Shows information about the most expensive queries.

A user must have VIEW SERVER STATE permission.

Futher reading – http://beyondrelational.com/blogs/nakul/archive/2011/01/27/activity-monitor-underappreciated-features-of-sql-server.aspx

 

Resource Governor

Resource Governor can be used to limit CPU and memory allocation for inbound requests. Cannot place limitations on internal SQL Server operations.

• Manage and limit the resource consumption  of inbound requests
• Prioritize workload

Consists of 3 main components

• Resource pool – allocates resources within a SQL Server instance – 2 types of resource pooI
• Internal – Used for all internal database engine processing. Cant be modified. CPU and memory are never limited here. It uses what it needs, and rest is available for others
• Default – Processes that have not been assigned to any specific resource pool (internal or user defined), gets assigned here.
• Workload Group – Used to group similar queries, so that they can be treated as a single unit. This is assigned to a resource pool.
• Classier – Used to assign an incoming request to the appropriate workload group. This function is created in the master database – only one function can be active at a given time for  resource governor.

Important The (Dedicated Administrator Connection) DAC is not subject to Resource Governor classification and can be used to monitor and troubleshoot a classifier function.

Example
–Create resource Pools
CREATE RESOURCE POOL ReportingPool
GO

CREATE RESOURCE POOL InteractivePool
Go

–Create Groups
CREATE WORKLOAD GROUP ReportingGroup
USING ReportingPool
GO

CREATE WORKLOAD GROUP InteractiveGroup
USING InteractivePool
GO

–Create classifier function
CREATE FUNCTION classifier()
RETURNS SYSNAME WITH SCHEMABINDING
BEGIN
DECLARE @retVal SYSNAME
IF SUSER_SNAME() = ‘SCOTT’
SET @retval = ‘InteractiveGroup’
ELSE IF SUSER_SNAME() = ‘REPORTS’
SET @retval = ‘ReportingGroup’
RETURN @retVal
END
GO

–SET THE classifying function
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.classifier)
GO

ALTER RESOURCE GOVERNOR RECONFIGURE
GO

Sources:
1. http://www.sqlserver-training.com/video-how-to-implement-resource-governor/-
2. Pro SQL Server 2008 Aministration (Managing Query performance) – Ken Simmons, Sylvester Carstarphen