SQL Server 2008 Resource Governor

Resource Govenor enables to manage SQL Server workload and resource utilization.

Terminology:

Resource Pool – It is a collection of physical resources. SQL Server 2008 has two built in pools, default and internal. It allows to create maximum of 18 user defined pools.

Workload Group – It is a container for the requests that are similar to the classification criteria applied to each request. Like Resource pools there are two built in workload groups, default and internal.

Classifier Function – It is a user written criteria that is being applied to the requests.Each user session would be assigned to a particular workgroup load using the classifier function.

Limitations:

1. Limited to only CPU bandwidth and memory management
2. We can only control resource utlization for database engine. It cannot be used with SSIS, Analysis or Reporting services
3. Only one instance of SQL Server can be managed

Here is just an example of creating resource pool and workload group

--Enable Resource Governor

ALTER RESOURCE GOVERNOR RECONFIGURE

GO

USE [master]

GO

-- Create resource pool to be used

CREATE RESOURCE POOL poolAdhoc

WITH (

-- allocate at least % of the CPU bandwidth for admin queries

MIN_CPU_PERCENT = 10

-- do not let them exceed % either

, MAX_CPU_PERCENT = 20

-- admin queries should be small and we will allocate about %

-- of the server memory for them

, MIN_MEMORY_PERCENT = 5

, MAX_MEMORY_PERCENT = 5 )

GO

-- Create adhoc workload group

CREATE WORKLOAD GROUP groupAdhoc

WITH (

-- use all defaults

IMPORTANCE = MEDIUM

, REQUEST_MAX_MEMORY_GRANT_PERCENT =25

, REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 0

, REQUEST_MAX_CPU_TIME_SEC = 50

, MAX_DOP = 0

, GROUP_MAX_REQUESTS = 0

)

USING

poolAdhoc

GO

-- Create admin workload group

CREATE WORKLOAD GROUP groupAdmin

WITH (

-- use all defaults

IMPORTANCE = MEDIUM

, REQUEST_MAX_MEMORY_GRANT_PERCENT =25

, REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 0

, REQUEST_MAX_CPU_TIME_SEC = 30

, MAX_DOP = 0

, GROUP_MAX_REQUESTS = 0

)

USING

poolAdhoc

GO

CREATE FUNCTION [dbo].[rgClassifier_Adhoc]()

RETURNS sysname

WITH SCHEMABINDING

AS

BEGIN

-- Define the return sysname variable for the function

DECLARE @grp_name AS sysname;

SET @grp_name = 'default';

-- Specify the T-SQL statements for mapping session information

-- with Workload Groups defined for the Resource Governor.

IF (IS_SRVROLEMEMBER ('sysadmin') = 1)

SET @grp_name = 'groupAdmin'

IF (APP_NAME() LIKE '%Management Studio%')

SET @grp_name = 'groupAdhoc'

RETURN @grp_name;

END

GO

-- Set the classifier function for Resource Governor

ALTER RESOURCE GOVERNOR

WITH (

CLASSIFIER_FUNCTION = dbo.[rgClassifier_Adhoc]

)

GO

-- Make changes effective

ALTER RESOURCE GOVERNOR RECONFIGURE

GO

SQL Server 2008 – Where is Activity Monitor?

For a minute I thought they deprecated the feature. I use it a lot to check the activity going on the databases.

They moved it to the launch point and the new one is really cool. Here is a blog with screenshots.

http://blogs.msdn.com/dtjones/archive/2008/07/10/where-the-heck-is-activity-monitor.aspx

SQL Server 2005 Pivots with Dynamic columns

http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx

Report Development - Report Requirements Document

The purpose of report requirements document is to define the requirements for each report or group of reports.

Personally I don’t like to read 80 pages of technical documentation for a report. Nor would I want to scratch my head wondering what the report is doing Vs what it is supposed to do.

Here is a quick checklist of items to include in the report requirements documentation

• Business need for the report
• Requirements from the point of view of the end users
• The selection and the filter criteria
• Data elements in the report
• Calculations if any
• Parameters in the report
• Sort, grouping and totaling requirements
• Appearance and style requirements (i.e. layout of the report, column names to be shown etc)
• Performance requirements (speed and latency)
• Security requirements (who needs access to the report)

Microsoft Chart Controls

Great blog about ASP.net charting control

http://weblogs.asp.net/scottgu/archive/2008/11/24/new-asp-net-charting-control-lt-asp-chart-runat-quot-server-quot-gt.aspx

Bad Data Models

We all know what a good data model is and spend significant time designing a good data model. So if there are good data models then there are bad data models too.

There is a difference between incorrect data models and bad data models. An incorrect data model would not satisfy business requirements and hence they don't make to the implementation. But bad data models would get implemented. They do more harm to the system. Every part of the application has to compensate for the bad data model.

Bad data models evolve due to various reasons – time and resource constraints, inexperience, over engineered modeling and sometimes sloppiness.

SQL Server 2008 – Maximum Capacity Specs

I found this article which gives the maximum size specifications for SQL Server 2008

http://msdn.microsoft.com/en-us/library/ms143432.aspx