SQL SERVER – Concept, Benefits and Use of Resource Governor in SQL Server

Concept Resource Governor in SQL Server

Benefits of Resource Governor in SQL Server 

Use of Resource Governor in SQL Server

Example of Resource Governor in SQL Server 

Resource POOL in Resource Governor in SQL Server  

Classification Resource Governor in SQL Server 

Workload Group Resource Governor in SQL Server   

How to Drop  Resource Governor in SQL Server   

How to  Disable Resource Governor in SQL Server

How to reset statistic Resource Governor in SQL Server   

Resource Governor in SQL Server  

Error: Cannot drop user-defined function dbo.ResourceClassifier. It is being used as a resource governor classifier.

 

SQL server 2008 has provided this feature to mange and workload of resources of SQL Server instance and system database.  Resource Governor can manage the resource utility for SQL Server and using it we can specify the memory and CUP limits for requesting application or task. It is available in SSMS, Object explorer -> Management tab.

There are only 3 concepts in Resource Governor in SQL Server

Resource POOL: When we install new SQL Server it’s create 2 Resource Pool that is Internal and Default. We can also add User Defined Resource Pool.

Workload Group: When we install new SQL Server it’s create 2 Resource Workload Group that is Internal and Default. We can also add User Defined Resource Workload Group. Work group are mapped with respective Resource Pool.

Classification: It is support is user defined function to classifier. Its work is to classify of incoming request and decides the way of internal rule.

Example:  I have to handle the work load of named MYPOOL application and this application work as Data mining which perform operation on huge databases. So now we can handle to define the limit of the resource and utilization of SQL Server.

Step 1: To Create POOL, Workload Group

--Resource pool to be used by named MyPool Application

CREATE RESOURCE POOL My_Pool

WITH

(

 MIN_CPU_PERCENT=50, MAX_CPU_PERCENT=100,

 MIN_MEMORY_PERCENT=50, MAX_MEMORY_PERCENT=100

)

GO

--create New resource pool to be used by named Reporting Application

CREATE RESOURCE POOL My_Report_Pool

WITH

(

 MIN_CPU_PERCENT=25, MAX_CPU_PERCENT=100,

 MIN_MEMORY_PERCENT=25, MAX_MEMORY_PERCENT=100

)

GO

--Workload Group to be used by named MyPool Application

CREATE WORKLOAD GROUP My_Group

    USING My_Pool ;

GO

--Workload Group to be used by named Reporting Application

CREATE WORKLOAD GROUP My_Report_Group

    USING My_Report_Pool ;

GO   

Step 2: To create User-Defined Function for User.

USE master;

GO

CREATE FUNCTION dbo.ResourceClassifier()

RETURNS SYSNAME

WITH SCHEMABINDING

AS

BEGIN

 --Declare the variable to hold the value returned in sysname.

 DECLARE @My_Workload_Group AS SYSNAME

 --If the user login is 'bulk_admin', than only map the connection to ---the My_Group Group workload group.

 IF (SUSER_NAME() = 'bulk_admin')

  SET @My_Workload_Group = 'My_Group'

 --If the user login is 'sa',than only map the connection to

 --the My_Report_Group workload group.

 ELSE IF (SUSER_NAME() = 'sa')

  SET @My_Workload_Group = 'My_Report_Group'

 ELSE

  SET @My_Workload_Group = 'default'

 RETURN @My_Workload_Group

END

GO

Step 3: To register created function and reconfigure, Disable and Resetting the statistics of Resource governor.

--Now we have to register the classifier for user-defined function and -- update the in memory configuration.

ALTER RESOURCE GOVERNOR

WITH (CLASSIFIER_FUNCTION=dbo.ResourceClassifier);

GO

--Enabling Resource Governor(By default when you install

--SQL Server, Resource Governor is disabled)

--It loads the stored configuration metadata into memory

ALTER RESOURCE GOVERNOR RECONFIGURE

GO

--To disabling whole Resource Governor

ALTER RESOURCE GOVERNOR DISABLE

GO

--The below T-SQL will used as resets statistics on all workload groups and resource pools.

ALTER RESOURCE GOVERNOR RESET STATISTICS

GO

Step 4: All data clean if it is not required

--NOTE- Clean all data after testing this example

Drop RESOURCE POOL My_Pool

drop WORKLOAD GROUP My_Group

DROP WORKLOAD GROUP My_Report_Group

DROP RESOURCE POOL My_Report_Pool

ALTER RESOURCE GOVERNOR WITH  (CLASSIFIER_FUNCTION=null)

DROP FUNCTION dbo.ResourceClassifier

--NOTE- if you will direct use the DROP FUNCTION <FUNCTION NAME> Commnad then it will return the following error so you have to use below commnad to resolve it.

Msg 10920, Level 16, State 1, Line 1
Cannot drop user-defined function dbo.ResourceClassifier. It is being used as a resource governor classifier.

ALTER RESOURCE GOVERNOR WITH  (CLASSIFIER_FUNCTION=null)

DROP FUNCTION dbo.ResourceClassifier

Step 5: The Following snap shot shown the create POOL, Workload Group and Classification:-

This above example can be done using also Window wizards

SQL SERVER - Explanation on Policy-Based Management (PBM) with example

SQL SERVER - Policy-Based Management (PBM)

• What is Policy-Based Management (PBM)?
• Explanation on Policy-Based Management (PBM)?
• Concept of Policy-Based Management (PBM)? 
• Describe on Policy-Based Management (PBM)? 
• what is Facet in Policy-Based Management in SQL Server
• what is Condtion Policy-Based Management in SQL Server
• What is Traget in Policy-Based Management in SQL Server
• What is Policy Policy-Based Management in SQL Server
• What in on Demand and on schedule in Policy-Based Management in SQL Server
• Example of Policy-Based Management in SQL Server
• Step by Step example of Policy-Based Management in SQL Server
• Question and Answer on Policy-Based Management in SQL Server 

Policy-Based Management is new feature available in Object explorer into the management Tab in SQL server Management Studio 2008. PBM allows creating and enforcing to configuring and managing SQL Server all the enterprise. PBM have some term to define and user to managing SQL Server.

• Target – In the target means where we have to manage entity (like talbe, view, index etc) by using PBM.
• Facet – It’s the system defined set of properties that can be manage. In simple way facets is the target type and each facet contains many property. Explore to facet tab you will get predefined facets and double click on facets.
• Condition – Here we can create a property expression that evaluates to True or False and set the State of facet.
• Policy – Here you have to select the condition to be check and/or forced and set the evaluation mode as on demand or on schedule.

 On Demand: you can right click on policy and click on evaluate.

 On schedule: it gives the option of SQL AGENT.

Example: Very Easy

We will configure and mange PMB for index facets. Just go into the facet tab you will find named index double click on it.

Now Right click on the condition table and click in ‘New condition’. Type the Name of Condition and select INDEX facet from below drop down list, than create expression as showing in below image.

 Than right click on Policy tab and click on ‘new policy’ and Give the name of Policy, than check to Checkbox in ‘Against targets’ to first option. Click on Ok button. Policy has created. See the bellow image:-

  Now we have set the evaluation mode as On demand. So that we have to go in policy tab and right click on the create policy named as My_Index_Policy’.

The symbols are showing state of policy. A Green Symbols means is condition is True and a Red symbols means condition is false on the table you can see the detail click on View Link. As shown in above screen.

SQL SERVER - What is user defined data type, and how to create

 It is very esay dont worry about how to use and create it below example just run only. 

It allows defining its own T- SQL User Defined Data Type and you can use this UDDT to entire Database.

See the following example-

-- TO CREATE USER DEFINED DATA TYPE WITH VARCHAR DATA TYPE

EXEC SP_ADDTYPE TYPE1, 'VARCHAR(50)','NULL'

-- TO CREATE USER DEFINED DATA TYPE WITH INT DATA TYPE 

--Example 2 (run this Full Script)

EXEC SP_ADDTYPE  @TYPENAME =TYPE2,

@PHYSTYPE= INT,

@NULLTYPE=NULL,

@OWNER= DBO

-- Declare the variable and data type is 'TYPE2'

DECLARE @MYVARIALBE TYPE2

-- Set the value in vairaible

SET @MYVARIALBE = 100

-- Get the value from varaible

SELECT @MYVARIALBE AS MYVALUE

-- Drop user defined data type

Drop  type  TYPE2

SQL SERVER - What is the role of resource database WITH example

Before run below script run the detail  of resource database.

 

·        The resource database is read only database which contain the all Sys.Objects is SQL Server.

·        Resource database physical location is <drive>:\Program Files\Microsoft SQL Server\MSSQL11.<instance_name>\MSSQL\Binn\. And the file name is always mssqlsystemresource.ldf.

·        This file cannot backup and restore. But if it required than you have to simple copy and paste option.

·        We cannot overwrite resource database.

·        Resource database can only modify by Microsoft customer support service specialist. Resource database ID is always 32767

·        To get the version number, last update date time and to access the sql server object definition of resource date base we can use  

SELECT SERVERPROPERTY('ResourceVersion');

GO

SELECT SERVERPROPERTY('ResourceLastUpdateDateTime');

GO

SELECT OBJECT_DEFINITION(OBJECT_ID('sys.objects'));

GO

SQL SERVER - What is transaction and What type of block of code you will use control transaction error and T-SQL Error

Before read this article i would like to say it best  to know for any any sql server user specially for sql server dba and developer.

This is very important is learn how to working transaction and how to use try and catch block in sql server within the transaction and how to get the run time error.

1. What is Transaction?

A transaction is a batch of statements that are treated as a single event and Any changes made to the database by a transaction are guaranteed either to go to completion or to have no effect at all.

2. What type of block of code you will use control transaction error and T-SQL Error?

To control transaction errors I will prefer use of begin tran commit tran, and rollback tran method and to control the T-SQL error I will use try and catch method.

Example:-(Must run this example)

USE AdventureWorks;

GO

BEGIN TRANSACTION; -- opening new transaction here

BEGIN TRY  -- try stmt started
 -- we are here writing error code as Generate a constraint violation error.

    DELETE FROM Production.Product

    WHERE ProductID = 677;

END TRY

BEGIN CATCH -- catch stmt strated

    SELECT

         ERROR_NUMBER() AS ErrorNumber

        ,ERROR_SEVERITY() AS ErrorSeverity       

        ,ERROR_LINE() AS ErrorLine
        ,ERROR_STATE() AS ErrorState

  ,ERROR_PROCEDURE() AS ErrorProcedure

        ,ERROR_MESSAGE() AS ErrorMessage;

    IF @@TRANCOUNT > 0

        ROLLBACK TRANSACTION;

END CATCH;

IF @@TRANCOUNT > 0

    COMMIT TRANSACTION;

GO

OutPut:-

SQL SERVER - Difference between RDBMS and DBMS

RDBMS and DBMS:-

1.      RDBMS DBMS + Referential Integrity

2.      An RDBMS is one that follows 12 rules of CODD.

DBMS:

·        In DBMS no relationship concept

·        It supports Single User only

·        It treats Data as Files internally

·        It supports 3 rules of E.F.CODD out off 12 rules

·        It requires low Software and Hardware Requirements.

·        FoxPro, IMS are Examples

RDBMS:

·        It is used to establish the relationship concept between Two database objects, i.e, tables

·        It supports multiple users

·        It treats data as Tables internally

·        It supports minimum 6 rules of E.F.CODD

·        It requires High software and hardware requirements.

SQL-Server, Oracle is examples

SQL SERVER - What is Database

Database is object in SQL Server and we can create user defined database and there are also available system database. 

Database contains the table objects and some other type of object. 

Database is available in SQL server instance.

SQL SERVER - What is DBMS

·        In DBMS no relationship concept

·        It supports Single User only

·        It treats Data as Files internally

·        It supports 3 rules of E.F.CODD out off 12 rules

·        It requires low Software and Hardware Requirements.

SQL SERVER - What is basically RDBMS

RDBM is stand for Relational Database Management System, it is the basic of SQL and it provide the relation between table object which contain in the database object.

 RDBMS has provided referential integrity facility to SQL server to maintain the database.

 RDBMS follows 12 rules which were defined by E.F. Codd.