Search results matching tag 'Memory Management'

The Myth Around 32-bit SQL Server Instances on 64-bit Operating Systems, and AWE

Argenis — Sun, 30 Dec 2012 23:36:00 GMT

(I’m taking a break from my DBA Best Practices blog series to talk about this today – the series continues after the Holidays!)

I love to be proved wrong. Really, I do.

Recently we had a discussion in an distribution list where somebody asked whether a SQL Server 32-bit instance could address more than 4Gb of RAM when running on top of a 64-bit OS. One of the really smart guys in Microsoft SQL Server Support, Karthick P.K. [Blog|Blog|Twitter] replied “sure, just add more RAM and enable AWE – SQL will use that memory”. I was much convinced that this was incorrect, so I jumped in and said that AWE does nothing under those circumstances. After all, that is what I had read in the past in many different articles. I even had some SQL Server MVPs who have been around the block forever agreeing with me.

But the good news is that I was wrong.

AWE in fact does nothing on 64-bit instances of SQL Server. But on WOW64 - Windows on Windows 64-bit, the subsystem of Windows x64 that allows you to run 32-bit processes and instances of SQL Server, AWE does allow you to address memory above 4Gb. Let me show you.

Side note: AWE is now deprecated - removed in SQL Server 2012. The last version of SQL Server that supports AWE is SQL Server 2008 R2. Because of this, a SQL Server 2012 x86 instance won’t be able to enjoy over 4Gb of memory – even if running on WOW64.

I setup a vanilla VM in my lab with a 64-bit OS with 8Gb of memory. MSINFO32 looks like this on the VM:

I installed a 32-bit SQL Server 2008 R2 SP2 instance on the VM. @@VERSION looked like this:

I did not enable AWE. I did grant the “Perform Volume Maintenance Tasks” and “Lock Pages in Memory” privileges using secpol.msc (not pictured!). I wanted to see what memory consumption would look like without AWE enabled.

This screenshot shows the permissions granted to the SQL Server Service Account (NETWORK SERVICE, in this case) – used “whoami /priv”, a quick and dirty way to get the privileges of the service account using xp_cmdshell. Ugh, xp_cmdshell. It’s okay, it’s just a test.

I went ahead and created a test database with just one big table – big enough to use all the buffer pool memory on the instance when a size-of-data operation is executed on it. I did set Max Server Memory to 6Gb.

Here’s the CREATE table script:

CREATE TABLE [dbo].[testTable](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [wideColumn] [nvarchar](4000) NULL,
        PRIMARY KEY CLUSTERED 
        (
            [id] ASC
        )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[testTable] ADD DEFAULT (replicate(N'A',(4000))) FOR [wideColumn]
GO

Next, I went ahead and inserted 1M rows on the table:

INSERT INTO [dbo].[testTable] DEFAULT VALUES
GO 1000000

If you do the math very quickly, you will agree with me that this not so little table will be more than 6Gb in size. Let’s check anyway:

Cool! The table is large enough for the purpose of this test. But how does memory utilization look like after that 1M row insert?

That’s less than 4Gb. Next I turned on AWE, and restarted the instance. Then I queried the ERRORLOG to confirm that AWE was enabled:

Okay, since I had just restarted the instance my buffer pool was gone – needed to force a size of data operation on the large table. Rebuilding the clustered index did just fine (I know, that’s probably overkill). Then I checked sys.dm_os_process_memory again:

And there we go! A lot more than 4Gb being used now that AWE is in play. I had set Max Server Memory to 6Gb, as I detailed earlier.

It looks like there’s quite a lot of incorrect documentation regarding this, so I wanted to clear out any confusion – and learn something new along the way. Don’t you love SQL Server?

Happy Holidays and Happy New Year!

-Argenis

P.S.: If you are running IA64 (Itanium) - then this doesn't apply to you. AWE does not work at all on IA64.

Did You Know: Good Stuff!

Kalen Delaney — Wed, 30 Jun 2010 21:33:00 GMT

I know, I promised a technical post after my online seminar today, and I'm still planning on that. There weren't all that many really deep questions, just a lot of basic understanding questions, plus questions about topics I'll be covering in my Plan Caching and Recompilation Seminar in August. There was one really interesting question about a particular behavior that I am researching, so that might turn into a post. Also, it's always fun to see people react to using GO with a number, if they've never seen it before, as I discussed here:

http://sqlblog.com/blogs/kalen_delaney/archive/2007/07/22/did-you-know-run-a-batch-multiple-times.aspx

I don't do a lot of pointers to other bloggers, but this one I just can't resist. I found two excellent posts by Jay Choe, of the SQL Server Engine team at Microsoft, explaining about Memory Grants:

http://blogs.msdn.com/b/sqlqueryprocessing/archive/2010/02/16/understanding-sql-server-memory-grant.aspx

http://blogs.msdn.com/b/sqlqueryprocessing/archive/2010/03/11/mystery-of-memory-fraction-in-showplan-xml.aspx

These should keep your brain busy until my next technical post …

Have fun!

~Kalen

Did You Know? What PreCon would I take if I were attending TechEd?

Kalen Delaney — Tue, 18 May 2010 00:27:00 GMT

TechEd starts in 3 weeks, and I'm not going to make it this year. I had very much wanted to visit New Orleans post-Katrina and see the recovery for myself. I attended a couple of TechEd's there many years ago, but my primary reason for visiting that fabulous city was because my daughter went to school there. She graduated from Tulane University in 1999, but it just so happened that every time TechEd was there, it was after school was over for the year, so I never got to combine my conference trip with a family visit.

If you're going to the conference, and you're trying to decide what PreCon session to sign up for, I've got a suggestion for you.

You might be aware that I have a 5-day SQL Server Internals course, that is based on the material in my book(s). For the last few years, there has only been one other person besides me, in the entire world, whom I have authorized to teach my course. I'm proud to announce that just last week, the second authorized trainer presented my course for the first time. Maciej (how do you pronounce this?) Pilecki taught the course in Warsaw and will be teaching it again in Germany this November.

I just found out today that Maciej will be giving a full day session at TechEd called :

Secrets of the SQLOS:
Leveraging Microsoft SQL Server Internal Operating System for Improved Scalability and Performance

So this session goes beyond what I cover in my class, down into the interaction between SQL Server and the operating system, and the topic is one of Maciej's specialties. If you've never heard Maciej talk about SQL Server memory management (or even if you have!), you won't regret taking this precon.

It's not too late to sign up!

Have fun!

~Kalen

Geek City: Q & A on my Plan Cache Sizing article in SQL Server Magazine

Kalen Delaney — Sun, 16 Dec 2007 03:00:00 GMT

My December article in SQL Server Magazine, called Managing Plan Cache Size, expands on the information I talked about in a post last month on the limits of plan cache.

A reader of the article ask some questions on the SQL Server Magazine web site, and I have decided to use my blog to post the answers.

Q: Could you give me an illustration how to calculate target memory please? Let say there is a 64 bit system (64 bit hardware, windows 2003 64 bit enterprise edition, SQL Server 2005 Enterprise edition 64 bit, memory 30 GB), max server memory (in sp_configure) is between 16 (min value) - 30 GB (max value)).

A: Target memory refers to the maximum physical memory that can be committed to the buffer pool and ideally is the lesser of the values you’ve configured for “max server memory” and the total amount of visible physical memory available to the OS. On a 64bit system, all your memory is visible, and your max server memory is the same as your physical memory, so target ideally will be 30GB. You can see the target memory value in the metadata view sys.dm_os_sys_info (where it's called bpool_commit_target), along with the value for visible memory (called bpool_visible).
However, there are other factors that can affect the target value. The BOL entry for sys.dm_os_sys_info defines bpool_commit_target as follows:
"Number of 8-KB buffers needed by the buffer pool. The target amount is calculated using a variety of inputs such as the current state of the system, including its load, the memory requested by current processes, the amount of memory installed on the computer, and configuration parameters."
The exact formula is not published. It's best to just use sys.dm_os_sys_info to see what your system's target memory value is.

Q: In your article, you give an example how to calculate plan-cache pressure limit, for example for SQL Server 2005 RTM and SP1, it calculates for on 28 target memory as follows : (.75 x 8 GB) + (.5 x 20 GB) = 6 GB. Then you add to 10 GB so pressure limit is 16 GB. Where does 10 GB come from?

A: You have rephrased the expression incorrectly. This is what the article says:
(.75 x 8 GB) + (.5 x 20 GB) = 6 GB + 10 GB = 16 GB
If you do the math, you’ll see that the first term in parentheses (.75 x 8 GB) is equal to 6 GB and the second term (.5 x 20 GB) is equal to 10 GB, so we add the 6 and the 10 together.

Q. You mention "plan-cache pressure limit". Does it mean that when , let's say pressure limit of 28 GB is 16 GB, 16 GB is reached then SQL Server couldn't remove plan cache to make it less than 16 GB, is my understanding right?

A: No, in the above example, we calculated the pressure limit to be 16 GB. That means when your SQL Server hits 16 GB of memory for plan cache, it will start removing plans from cache to keep cache from getting too big.

Q: You show how to remove a prepared query by creating a plan guide with recompile hint; is it possible to remove non-prepared query plan on the following sample:

EXEC sp_create_plan_guide @name = N'RemovePlan1', @stmt = N' SELECT * FROM Sales WHERE ContactID < 3', @type = N'SQL', @module_or_batch = NULL, @params = N', @hints = N'OPTION(RECOMPILE)';

and I issue query twice as below shown SELECT * FROM Sales WHERE ContactID < 3

When I check in syscacheobjects with the query:
SELECT usecounts as uses, sql FROM sys.syscacheobjects WHERE dbid = db_id('AdventureWorks');

It shows that the non-prepared query plan is reused with count equal to 2.

A: Althought it is possible to create a plan guide for an adhoc (non-prepared) query, your @stmt parameter must be absolutely exactly the same as the query you will run. In your plan guide creation, you have an extra space in front of the SELECT, and your @module_or_batch parameter is incorrect; it should be NULL. A correct plan guide can be used for adhoc queries, but it will NOT remove the adhoc plan from cache when you drop the plan guide, like a plan guide for a prepared query will. You will have to wait for the next version (SQL Server 2008) to get that behavior.

I hope this helps!

~Kalen