Tag Archives: SQL 2012

Memory Configuration Changes | SQL Server 2012

Hi Friends,

There are a few changes in SQL Server 2012 in Memory Manger, and a DBA should be aware of such changes before upgrading or installation of SQL 2012 Instance on production.

In Earlier versions of SQL Server (before SQL 2012) SQL Server was also consuming memory outside of the limit defined in Max Server Memory. Those who don’t know, in earlier versions SQL Server was allocating memory mainly in two ways Single Page and Multi Page. Single page allocation was consuming memory from the limit defined in Min & Max Memory Setting; the main consumer of this type of memory is Buffer Pool. Multi-Page allocation was capable to consume memory out of the limit which is S0_Called available for OS.

SQL Server 2012 has a new page allocator that manages both single-page and multi-page allocations (less than 8 KB and greater than 8 KB allocation requests). Therefore, there is no separate categorization that is called “Multi-Page allocations” in SQL Server 2012.

 

Changes to “max & min server memory (MB)”

In earlier versions of SQL Server (2005, 2008, 2008 R2), the following configuration options determined the limits of physical memory that the buffer pool consumed. Notice that we are talking about physical memory in this case, physical memory that is committed by the SQL Server database engine process:

  • max server memory (MB)
  • min server memory (MB)

This configuration option typically included only memory allocations that were less than or equal to 8 KB in the SQL Server process. These allocations were also referred to as “single_page_allocations” because the SQL Server memory manager used a page size of 8 KB. This configuration did not include the following memory allocation requests:

  • Multi-Page allocations from SQL Server: These are allocations that request more than 8 KB.
  • CLR allocations: These allocations include the SQL CLR heaps and its global allocations that are created during CLR initialization.
  • Memory allocations for thread stacks in the SQL Server process.
  • Memory allocation requests made directly to Windows: These include Windows heap usage and direct virtual allocations made by modules that are loaded into the SQL Server process. Examples of such memory allocation requests include allocations from extended stored procedure DLLs, objects that are created by using Automation procedures (sp_OA calls), and allocations from linked server providers.

 

Starting with SQL Server 2012, Multi-Page allocations and CLR allocations are also included in memory limits that are controlled by max server memory (MB) and min server memory (MB).

Carefully review your current max server memory (MB) and min server memory (MB) values after you upgrade to SQL Server 2012. You should review such values because SQL Server 2012 now includes and accounts for more memory allocations compared to earlier versions. These changes apply to both 32-bit and 64-bit versions of SQL Server 2012.

The following table indicates whether a specific type of memory allocation is controlled by the max server memory (MB) and min server memory (MB) configuration options.

 

Type of memory allocation SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 SQL Server 2012
Single-page allocations Yes Yes, consolidated into “any size” page allocations
Multi-page allocations No Yes, consolidated into “any size” page allocations
CLR allocations No Yes
Thread stacks memory No No
Direct allocations from Windows No No

 

Changes to “memory_to_reserve”

Because SQL Server 2012 has the new “any size” page allocator that handles allocations greater than 8 KB, the memory_to_reserve value does not include the multi-page allocations. Except for this change, everything else remains the same with this configuration option.

AWE Feature is not available in SQL Server 2012

http://www.sarabpreet.com/2016/02/sql-server-awe-enabled-option-deprecated/1509

Even though the “awe enabled” feature is not available in 32-bit SQL Server 2012, you can still use the “locked pages” feature by assigning the “lock pages in memory” user right for the SQL Server startup account.

Carefully review the memory requirements of the existing instance of SQL Server before you upgrade to SQL Server 2012.

Do let us know your thoughts on this by leaving a comment.

Regards

Sarabpreet Anand

SQL Server AWE Enabled Option | Deprecated

Like we already know, Windows-based applications can use Windows AWE (Address Windowing Extensions) APIs to allocate and to map physical memory into the process address space. AWE allow 32-bit operating systems to access large amounts of memory. Memory that is allocated by using this method is never paged out by the operating system, provided “Lock Pages In Memory” user right (LPIM) has been granted to the application Service account.

 

Lock Pages in memory is by default given to Local system Service account.

SQL Server supports dynamic allocation of AWE memory on Windows Server. The SQL Server 64-bit version also uses “locked pages” to prevent the process working set (committed memory) from being paged out or trimmed by the operating system. When you enable “locked pages,” it is very important to set an appropriate value for “max server memory” and for “min server memory” configuration options for each instance of SQL Server to avoid system-wide problems.

During startup, AWE reserves only a small portion of AWE-mapped memory. As additional AWE-mapped memory is required, the operating system dynamically allocates it to SQL Server. Similarly, if fewer resources are required, SQL Server can return AWE-mapped memory to the operating system for use by other processes or applications.

AWE Feature is not available in SQL Server 2012.

Even though the “awe enabled” feature is not available in 32-bit SQL Server 2012, you can still use the “locked pages” feature by assigning the “lock pages in memory” user right for the SQL Server startup account.

 

Note:

The virtual address space that is reserved for these allocations is determined by the memory_to_reserve configuration option. The default value that SQL Server uses is 256 MB. To override the default value, use the SQL Server -g startup parameter. This part of the virtual address space is also known as “Memory-To-Leave” or “non-Buffer Pool region.”

Because SQL Server 2012 has the new “any size” page allocator that handles allocations greater than 8 KB, thememory_to_reserve value does not include the multi-page allocations. Except for this change, everything else remains the same with this configuration option.

 

Regards

Sarabpreet Anand

Understanding, Analysing and Troubleshooting Deadlocks – Part1

A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock.

In simple language, Deadlock is a situation when two or more processes are waiting to acquire lock on resource(s) which are already blocked by opposite participant process, hence results in a never ending locking situation. A deadlock will never resolve itself and the processes will keep on waiting forever.

That’s the reason SQLServer has Deadlock monitor\detector, It keep on checking such situation and if a deadlock is found it identifies one process as victim and kill that process so that the lock(s) can be released and the other process can acquire the required locks & complete the job. Deadlock monitor is an internal process which runs every 5 second by default and if your system is experiencing frequent deadlocks the time interval between different checks can be changed internally by SQL Engine and it can go as low as 100 MS.

Now you must be wondering which process will be identified as victim and on what basis?Deadlock Detector does not select a process randomly, there are three rules\conditions:

  1. A process cannot be identified as victim and killed if the deadlock priority is set to High.
  2. Log Generation: the process with least amount of log generation during the transaction will be identified as victim.
  3. It also checks whether the process is a user process or a system process.

Generally a deadlock occurs when different transactions try to access same set of objects\resources in different order. For Example:

  • User A acquires a share lock on Table1.
  • UserB acquires a share lock on Table2.
  • UserA now requests an exclusive lock on Table2, and is blocked until UserB finishes and releases the share lock it has on Table2.
  • UserB now requests an exclusive lock on Table1, and is blocked until UserA finishes and releases the share lock it has on Table1.
  • UserA cannot complete until UserB completes, but UserB is blocked by UserA.
  • UserA has a dependency on UserB, and UserB closes the circle by having a dependency on UserA.

 

Don’t get confused between Deadlock with Blocking – in blocking the resources wait in a queue and the queue gets cleared once the task completes hence the first transaction releases the held locks so that second transaction can acquire lock but in deadlock it becomes a cyclic lock.

Once a deadlock occurred on a transaction\Application SQL Server throws an ugly 1205 Error with the following message: Transaction (Process ID 54) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

clip_image002

If you are new to SQLServer or you don’t have any prior experience with Deadlocks you may go crazy after reading the error, since the message tells that an error has occurred but still suggests\prompts you to rerun your transaction without asking you to fix something.

The reason is that once the deadlock victim was killed the other transaction got a chance to acquire the required locks and complete the transaction, so if you rerun the transaction after a small delay probably you won’t get another deadlock and this time your transaction can complete.

In next Parts we’ll see how to get more info about Deadlock that just occurred in your environment (yes you can actually get more info), how to get same Deadlock Graph (XML Information as well as a GUI Representation) using Profiler  and how to interpret it.

Happy Learning 🙂

Thanks,
Sarabpreet Singh Anand
Subscribe now to get latest Tips\blog posts in your Inbox

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