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 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- How To Stop and Restart to default Instance of SQL Server Using Command Prompt

It is very easy and useful. Follow the below step to perform it.

Open Command Prompt-

Click on Start -> Run -> Type cmd  ->Ok

See if SQL Server instance is already running then below net start mssqlserver command will return below result.

  Now we will STOP the SQL Server using net stop mssqlserver command

  

Now SQL Server has been Stooped

Now again we will restart the SQL Server default Instance using net start mssqlserver command

SQL SERVER – Hints On Some function’s on Data & Time

The following DateTime Function are mentioning below-

1) GETDATE()

2) SYSDATETIME()

3) SYSDATETIMEOFFSET()

4) TODATETIMEOFFSET()

5) SWITCHOFFSET()

Examples:

-- Get date fucntion

SELECT GETDATE() GetDate_function;

-- get the sysdatetime function

Select SYSDATETIME() SysDateTime_function;

-- get SYSDATETIMEOFFSET function

SELECT SYSDATETIMEOFFSET() GetCurrentOffSet;

-- get TODATETIMEOFFSET fuction with SYSDATETIMEOFFSET function

SELECT TODATETIMEOFFSET(SYSDATETIMEOFFSET(), '-02:00') 'GetCurrentOffSet-2';

SELECT TODATETIMEOFFSET(SYSDATETIMEOFFSET(), '+00:00') 'GetCurrentOffSet+0';

SELECT TODATETIMEOFFSET(SYSDATETIMEOFFSET(), '+02:00') 'GetCurrentOffSet+2';

--get TODATETIMEOFFSET fuction with SWITCHOFFSET function

SELECT SYSDATETIMEOFFSET() GetCurrentOffSet;

SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '-02:00') 'GetCurrentOffSet-2';

SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '+00:00') 'GetCurrentOffSet+0';

SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '+02:00') 'GetCurrentOffSet+2';

Result: