SQL 2012 Memory and Sizing Considerations when setting up Max Memory

SQL Memory and Sizing Considerations

I am getting info about this article from https://support.microsoft.com/en-us/kb/2663912

I had recently interviewed for Microsoft SQL PFE position and failed miserably when they asked me about Max Memory (that is why I am not working there yet)

1. What does it do?
2. When you configure it, does SQL Engine utilize all of it

After answering the questions, the technical interviewers were nice enough to explain why I was wrong. I decided to learn more on it and found the article in the above link. 

One thing to remember before we get started that MAX MEMORY behavior was changed starting SQL 2012 and beyond. All versions including SQL 2008 R2 and prior handled memory differently.

Summary:

MS SQL Engine manages automatic and dynamic memory requirements based on current memory needs of the internal SQL components and workload. There are options to fine tune specific behaviors to meet needs of SQL. One the option we are discussing is MIN MEMORY and MAX MEMORY.

Info:

With SQL 2012, new changes were introduced to include specific types of memory allocations. Let me explain some of these terms

Types of Memory Allocations:

1. Single-page Allocations: Memory allocations requests less than or equal to 8K are referred to Single-page Allocations used by SQL Server processes. 
2. Multi-page Allocations: These are the allocations that request more than 8K pages. What could cause them, one of the examples I found was an example of workload that has stored procedures with large number of parameters and then invoked via large ad-hoc batches. You can read more about it here
3. CLR allocations: These allocations include the SQL CLR heaps and its global allocations that are created during CLR initialization.
4. Thread Stacks Memory: Memory used for thread stacks within SQL Server Process (Max worker thread * thread stack size). Thread stack size is 512K in 32 bit SQL Server, 904K in WOW mode, and 2 MB in 64 Bit.  
5. Direct Allocations from Windows: Direct windows allocations made by Non-SQL Server DLL’s ([These include windows heap usage and direct virtual allocations made by modules loaded into SQL Server process. Examples: allocations from extended stored procedure DLL’s, objects created using OLE Automation procedures (sp_OA calls), allocations from linked server providers loaded in SQL Server process)

SQL 2005, SQL 2008, SQL 2008 R2:

Type of memory allocation	Supported
Single-page allocations	YES
Multi-page allocations	NO
CLR allocations	NO
Thread stacks memory	NO
Direct allocations from Windows	NO

SQL 2012, SQL 2014, SQL 2016:

Starting with SQL 2012, Single-page and Multi-page memory allocation are included in Any Size Page Allocator.  You can read more about it here

Type of memory allocation	Supported
Single-page allocations	Yes - As part of Any size Page Allocator
Multi-page allocations	Yes - As part of Any size Page Allocator
CLR allocations	YES
Thread stacks memory	NO
Direct allocations from Windows	NO

Hopefully this will help you understand Max and Min memory for SQL server. If you like to learn more in detail, I have included links within this article.

Thanks!

How to convert INT to Date and Date to INT - SQL

How to convert INT to Date and Date to INT

 I recently switched from DBA to Database Analyst working on Data Warehouse on SQL 2012.

One of the few challenges I run into is to convert date to INT for matchup in SSIS packages. I am writing this blog so I can reference it whenever I am in doubt.

Just remember that INT and DATE Functions cannot be converted directly. They need to converted o VARCHAR or CHAR.

Here is the info for reference

https://msdn.microsoft.com/en-us/library/ms187928.aspx



Converting DATE to INT

Declare @DateConvert DATE = '12/10/2104'

SELECT CAST(CONVERT(VARCHAR(8), @DateConvert, 112) AS INT) 

Converting INT to DATE

Declare @INTConvert INT = 20141012

SELECT CAST(CONVERT(VARCHAR(8), @INTConvert, 112) AS DATE) 

In the statements above

• VARCHAR or CHAR can be used. I like using varchar but that my personal preference
• 112 - That is the data type format for ISO Standard. If you prefer to use US or other format, reference the Microsoft article and it should help you.

Hopefully this will be helpful.

Have a great day