Oct 122010
 

T-SQL Tuesday LogoThis Month’s T-SQL Tuesday topic comes from Sankar Reddy (blog | twitter) and he’s asking for SQL Server myths and misconceptions to be de-bunked. I’ve got 2 myths I’d like to set the record straight about. They’re rather closely related, and concern the “Lock Pages in Memory” setting.

What’s it do?

Without getting into an entire lesson on how operating systems manage memory, I’ll try to shrink this down into about a 15 second blurb for benefit of those who might not already know. Every computer has a finite amount of memory (RAM). Regardless of how much memory you have, all of the processes running on your computer combined will probably allocate more memory than is physically available. Since not every process needs all it’s memory at any given second, operating systems employ a memory management scheme knowing as “paging”, which means that inactive data stored in RAM will be written to disk in a file called the “page file”. This typically happens unbeknownst to the application whose data has just been paged. When the application needs this data again, it is read back from the page file into memory and the process continues. The downside of all this, of course, is that reading and writing to/from disk takes time, and is slower than if the data had just stayed in memory to begin with.

“Lock Pages in Memory” (LPM) allows some processes to skirt around this by declaring their allocated portions of memory as “locked”, therefore preventing the operating system from paging it to disk. This can result in significant performance gains for high-demand applications such as SQL Server. To grant a process the rights to utilize LPM, check out Microsoft’s instructions.

The Myths

Now that I’m done with the setup, let’s get to the myths. Myth the first is that LPM is only available in SQL Server Enterprise Edition. This once was true, but is no longer the case. Around April of 2009 it was announced that users of SQL 2005 and 2008 Standard Edition are also able to join the party and utilize LPM. To take advantage of this, systems must be upgraded to at least:

SQL Server 2005 Cumulative Update 4 for Service Pack 3
or
SQL Server 2008 Cumulative Update 2 for Service Pack 1

So now let’s say you’re running Standard Edition of a version listed above (or later) and you have the Lock Pages In Memory security setting enabled for the SQL Server Service account, just like you’d do for Enterprise Edition. You’re now good to go, right? Not exactly, but it’s commonly thought that it should work at this point, hence the second myth. Just because you’re using Standard Edition, you’ll also need to set Trace Flag 845 to take advantage of LPM.

Please be forewarned that “Lock Pages In Memory” can actually decrease performance in certain cases. It is not a silver bullet, and should be tested thoroughly in a development environment before deploying to production. Side effects may include nausea, vomiting, and constipation. Do not use Lock Pages In Memory if you are pregnant or thinking about becoming pregnant. If pages in memory remain locked for more than four hours, you should seek immediate medical attention.

To set a Trace Flag on startup, open SQL Server Configuration Manager and select “SQL Server Services” from the list on the left. Then select the desired SQL Server service from the list on the right. Right-click on it, select “properties”, and choose the “Advanced” tab. In this case, you will want to make a change to the “Startup Parameters” value, by adding ";-T845" to the end of it. It should look something like the screenshot below. Once it does, click OK. The trace flag won’t take effect until the SQL Server service is restarted.
Startup Parameters Screenshot

Once the service is restarted, look in the SQL Error Log. Shortly after startup you should see an entry that reads “Using locked pages for buffer pool.” The presence of this entry means that Lock Pages In Memory is active.

What about AWE?

Typically when Lock Pages in Memory is mentioned in conversation, Address Windowing Extensions (AWE) isn’t too far behind. AWE isn’t unique to SQL Server, it’s actually part of Windows – an API that allows 32-bit processes to map memory outside of their virtual address space. Did AWE in Standard Edition get the same treatment as LPM? It depends on your version.

For 32-bit, yes – AWE and Lock Pages in Memory (LPM) go hand-in-hand. Processes can’t utilize AWE without the ability to lock pages – it would be a lot more complicated otherwise. If you try enabling AWE without LPM, you’ll see error 5845, which tells you that AWE requires the ability to lock pages, which the current process doesn’t have. Conversely, pages will only be locked if they’re above the 32-bit process 4GB threshold, so granting the ability to use LPM without having AWE enabled will do nothing at all. In order to take advantage of your newly-minted right to lock pages in Standard Edition, Microsoft *had* to give you AWE, or it would be completely pointless.

If you’re using 64-bit SQL Server, you don’t need to care. AWE only applies to 32-bit processes, so the AWE setting is totally ignored in 64-bit versions. It’s not necessary anyway, as 64-bit process have enough address bits to go around. In fact, the feature is deprecated as of SQL Server 2008 R2. This makes sense, as it was announced over three years ago that Windows Server 2008 R2 would be the last Windows Server OS available for 32-bit architectures.

Other Thoughts

  • My “quick and dirty” lesson on paging barely scratches the surface – there’s really a lot more to it. If you’re curious and want to learn more, the Wikipedia article on paging is a great place to start
  • Lock Pages In Memory only applies to the SQL Server Buffer Pool, which is only one area of memory used by the database. Because LPM (and therefore AWE) can only benefit the buffer pool, it’s perfectly possible to have a server with a large quantity of memory returning “out of memory” errors, not because there’s not enough available memory, but because there’s not enough available memory in the right place.
Jul 062010
 

One of the nifty features available in SQL Server 2008 is the “Optimize for Ad hoc Workloads” option, which is at the instance level.  If your workload involves a high number of ad hoc queries that are used only once, your plan cache (also known as the “procedure cache”) may benefit from enabling this feature.  (There are, of course, other solutions to this issue as well, a primary one being to use less ad-hoc SQL and more stored procedures, but that may not always be possible.)

What it does

SQL Server has an upper limit on the size of its plan cache, which is dependent on the amount of memory installed.  In systems with high ad hoc workloads, a disproportionate amount of this cache may be going towards query plans that are only executed once.  Enabling “Optimize for Ad hoc Workloads” prohibits the caching of plans that are only used once, freeing up the cache for other objects that might be utilized more often.

If you’re curious, in 64-bit SQL Server 2008 (and also 2005 SP2 and later) the maximum size for the plan cache is calculated as follows:
(75% of server memory from 0-4 GB) + (10% of server memory from 4-64 GB) + (5% of server memory beyond 64 GB).

So an x64 server with 32GB of RAM would have a maximum plan cache size of (0.75*4) + (0.1*28) = 5.8GB.

32-bit servers would have a maximum 3GB plan cache size, as only 4GB of memory is considered.  Any additional memory allocated via AWE is accessible only to the buffer cache and not the plan cache.

How It Works

At a very high level, the SQL Query Optimizer takes in a batch for execution and tries to locate an appropriate query plan in the plan cache.  If it’s unable to find one, it compiles a plan and stores it in cache so that the next time it’s called, the cost of compiling such a plan is not incurred.  The downside to this is that if the batch (and hence the plan) is only used once, that plan will take up space in memory that could be better allocated towards something else.

When “Optimize for Ad hoc Workloads” is enabled, an extra step is added to the above process.  The first time a plan is created for a batch, it isn’t stored in the cache.  Instead, a hash of the plan is stored, which takes up much less space.  If a plan is used that already has a hash stored, then the full plan is added to the cache since it has now been used twice and is likely to be called again in the future.

What To Look For

The dm.exec_cached_plans DMV allows us to peek into the plan cache and see what’s taking up space.  A few simple queries with it can provide a bunch of information.  Try running these three statements together:

SELECT SUM(cast(size_in_bytes AS BIGINT))/1024/1024 AS 'Size (MB)'
FROM sys.dm_exec_cached_plans;

SELECT objtype AS 'Type',
COUNT(*) AS '# Plans',
SUM(CAST(size_in_bytes AS BIGINT))/1024/1024 AS 'Size (MB)',
AVG(usecounts) AS 'Avg uses'
FROM sys.dm_exec_cached_plans
GROUP BY objtype;

SELECT SUM(cast(size_in_bytes AS BIGINT))/1024/1024 AS 'Size (MB)'
FROM sys.dm_exec_cached_plans
WHERE objtype = 'Adhoc' AND usecounts = 1;

The first result set will tell you the total size of the procedure cache.  The second one breaks that number down by object type.  The third tells the size of the procedure cache being taken up by Ad hoc query plans that have only been used once.  Here’s some results I recently retrieved:

Result Sets

As you can see, 3125 MB of the 3330 MB plan cache is going towards Ad hoc query plans which are used a relatively low number of times compared to other object types.  Also 2240 MB of the 3125 MB goes towards plans that have only been used once.  This machine is a good candidate for enabling the optimization.

How To Enable It

Enabling this optimization is pretty straightforward, just run the following script.  Again, this option is at the instance level, so it will be enabled for all databases on that instance.

sp_configure 'show advanced options',1;
RECONFIGURE;
GO

sp_configure 'optimize for ad hoc workloads',1;
RECONFIGURE;
GO

DBCC FREESYSTEMCACHE('SQL Plans');

You can also check the “Advanced” section of the Server Properties menu and change it from there.

Advanced Config Screenshot

Either way, you’ll want to run

DBCC FREESYSTEMCACHE(‘SQL Plans’)

afterwards, as it will clear the procedure cache for ad hoc queries.  “Optimize for Ad hoc Workloads” only applies to plans not already in the cache, so you won’t see any changes until you purge those pre-existing entries.

Further Reading