SQL Server Resource Governor Webcast – Demo Scripts

Hi Friends,

Last week I took a webcast “Tap is the only Solution Ver.2 (SQL Server Resource Governor)“. The webcast was very well received and there were a lot of questions regarding the Demo and the scripts. So in this blog post I’ll provide all the scripts to implement, test, tweak & remove Resource Governor. You can also use these scripts to do Proof of Concept (POC) on Resource Governor.

Hope this will help you.

Happy Learning\Testing – DO leave a comment to let us know how we are doing.

Implement\ configure Resource Governor

 

Create database simple

go

—Login creation for all users, I disabled strong password checking for demo

 

–purposes only but this is against best practices

USE [master]

GO

CREATE LOGIN [Sales_app] WITH PASSWORD=N’sales_app’, DEFAULT_DATABASE=[simple], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

CREATE LOGIN [Reporting_app] WITH PASSWORD=N’reporting_app’, DEFAULT_DATABASE=[simple], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

CREATE LOGIN [Fin_App] WITH PASSWORD=N’fin_app’, DEFAULT_DATABASE=[simple],CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

Go

use simple

Go

CREATE USER [Sales_app] FOR LOGIN [Sales_app]

CREATE USER [Reporting_app] FOR LOGIN [Reporting_app]

CREATE USER [Fin_App] FOR LOGIN [Fin_App]

GO

–limiting resources to test Resource Governor because I am using a laptop with 8 proc and 8GB ram

 

–which will be too much and complex to do POC

sp_configure ‘show ‘,1

go

reconfigure

go

sp_configure ‘min server’, 2048;

go

sp_configure ‘max server’, 2048;

RECONFIGURE

GO

— create user pools

— note that we are using all default parameters

CREATE RESOURCE POOL Pool_Sales_fin_app

CREATE RESOURCE POOL Pool_reporting

— create user groups also note that all groups created with default parameters only pointing to the

 

–corresponding pools (and not ‘default’ pool)

CREATE WORKLOAD GROUP Sales_Group

USING Pool_Sales_fin_app

CREATE WORKLOAD GROUP Reporting_Group

USING Pool_reporting

CREATE WORKLOAD GROUP Fin_Group

USING Pool_Sales_fin_app

GO

— now create the classifier function

Use master

go

IF OBJECT_ID(‘DBO.CLASSIFIER_1′,’FN’) IS NOT NULL

DROP FUNCTION DBO.CLASSIFIER_1

GO

— note that this is just a regular (UDF) User Defined Function

CREATE FUNCTION DBO.CLASSIFIER_1()

RETURNS SYSNAME WITH SCHEMABINDING

BEGIN

DECLARE @val varchar(32)

SET @val = ‘default’;

if ‘Sales_app’ = SUSER_SNAME()

SET @val = ‘Sales_Group’;

else if ‘Reporting_app’ = SUSER_SNAME()

SET @val = ‘Reporting_Group’;

else if ‘Fin_App’ = SUSER_SNAME()

SET @val = ‘Fin_Group’;

return @val;

END

GO

— make function known to the Resource Governor

ALTER RESOURCE GOVERNOR

WITH (CLASSIFIER_FUNCTION = DBO.CLASSIFIER_1)

GO

–Check if Reconfigure is pending for any setting of Resource Governor

SELECT * FROM sys.dm_resource_governor_configuration

— make the changes effective

ALTER RESOURCE GOVERNOR RECONFIGURE

GO

Workload – by different Sessions

 

declare @i int

declare @s varchar(100)

set @i = 10

while @i > 0

begin

select @s = @@version;

end

Using Cap_CPU_Percent Parameter – New to SQL 2012 to configure Hard Cap on CPU

CPU_CAP_PERCENT & AFFINITY SCHEDULER settings are available only through Scripts as of now- Planned to come in GUI from SP1

 

ALTER RESOURCE POOL [Pool_Sales_fin_app]

WITH (CAP_CPU_PERCENT=30)

ALTER RESOURCE GOVERNOR RECONFIGURE;

GO

ALTER RESOURCE POOL [Pool_Reporting]

WITH (CAP_CPU_PERCENT=40)

ALTER RESOURCE GOVERNOR RECONFIGURE;

GO

Using Affinity Parameter to dedicate a scheduler to a single pool

 

ALTER RESOURCE POOL Pool_Sales_fin_app

WITH (AFFINITY SCHEDULER = (0))

GO

ALTER RESOURCE POOL Pool_reporting

WITH (AFFINITY SCHEDULER = (1))

GO

ALTER RESOURCE GOVERNOR RECONFIGURE

Go

Internal Tables\DMVs for Resource Governor

–Metadata Tables

 

SELECT * FROM sys.resource_governor_workload_groups

SELECT * FROM sys.resource_governor_resource_pools

SELECT * FROM sys.resource_governor_configuration

DMV’s

 

SELECT * FROM sys.dm_resource_governor_workload_groups

SELECT * FROM sys.dm_resource_governor_resource_pools

SELECT * FROM sys.dm_resource_governor_configuration

Select * from sys.dm_resource_governor_resource_pool_affinity — – New DMV in SQL 2012 for Affinity setting & Schedulers mask

Script to check which scheduler is being used by which Resource Group

select

r.session_id,

CONVERT(NCHAR(20), wg.name) as group_name,

t.scheduler_id,

r.status

from sys.dm_exec_requests r

join sys.dm_os_tasks t on r.task_address = t.task_address

join sys.dm_resource_governor_workload_groups wg on r.group_id = wg.group_id

where

r.session_id > 50

Clean-Up

—Clean Up Script

ALTER RESOURCE GOVERNOR disable;

–Drop Logins and associated Users

USE [simple]

GO

DROP USER [Sales_app]

DROP USER [Reporting_app]

DROP USER [Fin_App]

GO

USE [master]

GO

DROP LOGIN [Sales_app]

DROP LOGIN [Reporting_app]

DROP LOGIN [Fin_App]

GO

–drop workload

USE [master]

GO

DROP WORKLOAD GROUP [Reporting_Group]

DROP WORKLOAD GROUP [Sales_Group]

DROP WORKLOAD GROUP [Fin_Group]

go

–drop pools

USE [master]

GO

DROP RESOURCE POOL [Pool_Sales_fin_app]

DROP RESOURCE POOL [Pool_Reporting]

GO

—drop classifier function

ALTER RESOURCE GOVERNOR

WITH (CLASSIFIER_FUNCTION = Null)

GO

DROP FUNCTION DBO.CLASSIFIER_1

 

Reference: Boris Baryshnikov’s blog post & Whitepapers for Resource Governor 2008 & 2012.

Regards

Sarabpreet Anand

Free SQL Server Webcast | Tap is the only Solution Ver.2 | 08-Nov-2012

Tap is the only solution – Ver. 2

Scarcity is the fundamental economic problem of having humans who have unlimited wants and needs in a world of limited resources. Alternatively, scarcity implies that not all of society’s goals can be pursued at the same time.
Just like Water the Server Resources are also getting scarce and we should find a way to put a Tap on these resources to optimally utilize them as and when needed. In this session we’ll see a way to control & manage server resources to get desired performance and targets.(Level: 100 – 300)

Register: http://ssgwebcast08nov2012.eventbrite.com/

Note: You will get the LiveMeeting details a few hours before the event. Please make sure that your laptop/PC is equipped with LiveMeeting client.

image

How to choose a profession?

Hi Friends,

Choosing a career is not a rocket science but the decision should be yours & unaffected by all other forces.

I don’t know about rest of the world but here in India almost every other student chooses a career without even knowing what is actually expected out of them in the given profession and what kind of life they’ll be living.

One chooses a course\technology mainly based on three reasons and unfortunately all these reasons are affected by others and they jump into conclusion and decide the career on vague parameters.

 

  1. The career counselor is suggesting to Opt Technology X so go for it.
  2. Family friend or elder cousin is already working on Technology Y from last n number of years, he is well settled and earn a lot , So decision taken I (or sometimes family decides that my son or daughter) will choose this as a career.
  3. I did a course in Technology Z but currently there is no opening for the same or I was unable to crack an interview and landed to a job which needs technology A so from now onwards Technology A becomes focus.

Now if you analyze the reasons carefully none of them was decided properly by the candidate and this is what leads to unsatisfied professionals.

Deciding a career is very crucial, it’s not just about the monetary factor, but it’s about:

  • Satisfaction
  • Desire to excel at something
  • Suitability
  • Life you want
  • And Much MORE.

So, How to choose a right career? Let’s say you are a young aspirant who knows SQL basics and want to Opt it as your career, now the question arises whether I should go for SQL Server or not, is it good for me?

 

Choose a Field

Knowing the basics doesn’t qualifies for opting it as your profession, you might not have seen the other side of the coin (so called Big Picture), you should look for more useful and meaningful info about the same. SQL Server is a Technology which is very vast and there are multiple tracks to choose from, like: (Not in any specific order)

  1.  DBA
  2.  Developer
  3.  BI Expert
  4.  Consultant
  5.  Trainer
  6.  Etc

Strength – Interest Now, you should first identify your strengths and interest, they go hand in hand.For Example: If you are good in creating logics and love to code, Development is the best match for you.

There could be a possibility that you are good at creating Excel dashboard reports and PowerPoint presentations but you don’t like doing it, you do it just to get good marks and since you know it more than anyone in the classroom that doesn’t mean you should opt it as your profession.

Then comes Desire\wish What are your desires to achieve in life, what type of life\work you wish to do – and this all should be realistic.

If you are good in SQL and you like to troubleshoot things but you don’t see yourself extending your shifts almost every other day & you hate 24*7 shifts then DBA as a career is a BIG NO-NO for you.

Other things: You may also want to know pros and cons of these tracks, Pressure involved, criticality, 24*7 Shifts (generally a must for DBA) or General Shift(9-6 Job).

Ask yourself, what kind of profession you are looking.

You might not take it seriously right now, but trust me it’s not that easy to change your profession or track. So decide very carefully and stick to it.

Thanks,
Sarabpreet Singh
@Sarab_SQLGeek

SQL Server Day | Gurgaon | 8-Sept-2012 + Hiring Drive for Microsoft India

SQLServerGeeks.com cordially invites you to SQL Server Day, the quarterly dose of SQL Server knowledge & learning. And after multiple successful events in Bangalore, Kolkata, Mumbai & Delhi NCR, we are coming to Gurgaon, once again! This time SQL Server Day event has been customized for SQL Server Developers. We bring you 3 rock star speakers and 3 intriguing sessions related to SQL Server Development.

And Don’t forget to bring your CV and meet the Microsoft India Recruitment Team Smile

Details

Session & Speaker details

Venue & Contact details

Date & Time

Welcome Note (15 mins) by Amit Bansal(MVP-SQL Server)

Tips & Tricks of Transact SQL
by Sarabpreet Singh Anand, (Vice President, SQLServerGeeks.com)

Session by Microsoft IDC Recruitment Team

Optimizing Transactional and Procedural code by Amit Bansal (Director, Peopleware India, MVP-SQL Server)

SQL Server Myth-buster for Developers by Ahmad Osama

Closing Note (15 mins)

Microsoft Corporation (I) Pvt. Ltd.
DLF Phase 3, Cyber Greens,9th Floor, Tower A, DLF Cyber City, Sector 25A (Near Shankar Chowk)

Note: Refreshments will be served

 

September 8, 2012 (Saturday)
1.30 pm to 5.30 pm

Note: The event will start sharp at 2.00 pm. Please come by 1.30 pm for registration. (Due to security policies, registration takes time. Please come with a valid photo ID)


clip_image001[4]

You can register @ http://sqlserverdaygurgaon8sep2012.eventbrite.com/

8-sep-2012-t-sql-tips-sessions-gurgaon

See you at the event.

SQL Server Day | Bangalore | 28-July-2012

SQLServerGeeks.com cordially invites you to SQL Server Day, the monthly dose of SQL Server knowledge & learning. And this time we are coming again to Bangalore. We bring you 2 rock star speakers and 2 intriguing sessions. This is yet another opportunity to learn maximum from SQLServerGeeks.com across India. Sessions are spread across Business Intelligence, Developer & DBA tracks, so there is something for everyone to come and learn. Event will be hosted in Microsoft premises in Golf Link, Bangalore so that your experience is fabulous. Register Fast and block your seat. It is absolutely free.

You can register @ http://sqlserverdaybangalore28july2012.eventbrite.com/

Also forward this email to your friends and colleagues to spread awareness.

image

Here are the session Details:

————————————————————–

Session 1:Uncover Report manager by Sudhir Rawat (Microsoft)

Session Details: Report Manager – Most of us might heard/worked with this. But do I know the features it provides to improve reporting environment for my customer. Join in the story telling session and find out the features provided by report manager. This talk covers how report server behind the scene works and explaining about the scenario where you need them.

Session 2: Tap is the only solution by Sarabpreet Singh Anand (Vice President SQLServergeeks.com)

Session Details: Scarcity is the fundamental economic problem of having humans who have unlimited wants and needs in a world of limited resources. Alternatively, scarcity implies that not all of society’s goals can be pursued at the same time.
Just like Water the Server Resources are also getting scarce and we should find a way to put a Tap on these resources to optimally utilize them as and when needed. In This session we’ll see a way to control server resources to get desired performance and targets.

————————————————————–

Where are my system databases?

Hi Friends,

In last post, we saw that we can very easily use powershell to connect to SQL Server instance and see all the available databases (http://www.sarabpreet.com/?p=288), but by default powershell maintains security and hide all system level databases. Now the question arises how to list\view system databases in powershell.

Well to do this, all you have to do is use an extra switch called FORCE, this forces powershell to show all content.

let’s see how to use this.

If we use the  normal get-childitem cmdlet it will just display the user databases like the below screenshot.

default behaviour

Now let’s use the force switch, Like: get-childitem –force, this will change the default behavior and show all Databases including System Databases.

with force switch

Enjoy!

Happy Learning.

Thanks,
Sarabpreet Singh

How to Connect to SQL Server using Powershell

Hi Friends,

In this blog I’ll show you how to connect to SQL Server using Powershell Step-by-Step. There are basically two ways to connect to SQL Server,

A. Using sqlps utility from Powershell.exe

B. Using SSMS context menu option

 

Let’s review both:

1. Go to command prompt\cmd and type Powershell.exe & press enter.

clip_image001

This will open the Powershell console for you,

2. Now you can execute SQLPS to start SQLPS utility and load SQL specific snappins. You can also start SQLPS directly from your command prompt by writing sqlps.exe. To know more about the SQLPS utility and Snappins visit: http://www.sarabpreet.com/?p=203

clip_image002

3. Notice the change of drive letter from PS to PS SQLSERVER, so that means Powershell has a SQLServer Drive which is being used by SQLPS utility to connect to SQL Server. (More on this in future posts)

4. Now as you know we can browse all drive contents with a command dir which is an alias for Get-ChildItem So we can do the same in SQLSERVER drive also, since the behavior will remain same.

Note: I am using cd, dir which are alias to set-location & get-childitem respectively. This is just to make it easy to understand, going forward in the future posts we’ll be using Set-location cmdlet only.

5. Issue a dir command and you’ll get all objects you can access. For this post we’ll just concentrate on SQL which is there to connect to SQL Server Database Engine.

6. Now you can execute a cd command to go in SQL folder.

clip_image004

7. Again do a dir to get name of the Server. Once you get the Server name set-location to the server so that you can see all available SQL instances on the Server.

8. Finally we can see all available SQL instances on the server, On this machine I’ve got two SQL Server Instances installed one is DEFAULT and the other is a Named instance, named as PROD02.

clip_image006

9. Now let’s change the location by using either cd or set-location to connect to SQL Server Instance, here in this example I am connecting to PROD02 SQL Instance.

10. Once you are connected to SQL Instance you can see all contents (or I should say all objects under SQL instance) in the same hierarchy.

clip_image008

11. To connect to a database, all I need to do is just set-location to Databases folder and all available databases will be exposed as an object\folder. You can see all databases by the same old command dir. Smile

clip_image010

Wait a min, Where are my System Databases? (http://www.sarabpreet.com/?p=294)

Using SSMS

You can directly connect to this DB in Powershell if you access Powershell by the context menu of SSMS (SQL Server Management Studio).

1. Open SSMS and connect to the SQL Server Instance you wish to connect.

2. Now Right click on the DB you want to connect and choose Start Powershell.

clip_image011

Notice that the SQL Server automatically connected you to the DB you’ve chosen in SSMS.

clip_image012

Happy Learning!

Thanks,
Sarabpreet Singh

Quotes in Powershell

In General Quotes are considered to be there just to let interpreter know that the value enclosed within quotes should be treated as a single string. But in reality there are many other uses and types of quotes supported by Powershell. The two types of Quotes are Single Quotes and Double Quotes.

Let’s say we need to pass a value to a parameter which contains special characters, for this purpose you can use any of these it doesn’t make any difference, both will let you do the same thing.

clip_image002[5]

In the above example, I used the quotes to let interpreter treat both words as a single string, a common example with File Paths and both quotes worked.

Back Quote Character ( ` ) aka Back tick

This is usually the upper leftmost key below escape. This comes to rescue the situations where you want to quote a single character.

clip_image004[5]

Variable Expansion

Double Quote has a unique use\feature which is not there with single quote, if the string enclosed in double quotes contains a variable reference starting with a “$”, it will be replaced or expanded by the string representation of the value stored in the variable. It will be more clear by the following example:

clip_image006[5]

As you can see in the example above when we enclosed the variable $a in single quotes the Write-Host Command treated it just as a simple string but when we enclosed the variable $a in double quotes both commands or I should say cmdlets expanded the value which was there in the variable and used it while executing the cmdlet.

How to Suppress Expansion?

Now let’s say we want to suppress expansion at one place for a single variable but not for rest of the variables in the output to achieve this we need to again use the back quote or back tick. Let’s see how to do this.

clip_image008[5]

In the above example see at the first occurrence of $a and $b I specifically used the back tick so that the interpreter doesn’t expand & replace the variable values.

New Line Character ( `n )

In case you want to move a part of output to a new line you can use this special sequence. You can achieve this with a back tick and n. you can place this in either single or double quotes. Please refer the example below:

clip_image010[5]

Horizontal Tab ( `t )

If you want to get a horizontal tab in your output you can use the Horizontal Tab special Sequence. Please refer the example below:

clip_image012[5]

Thanks,
Sarabpreet Singh Anand