Changing the Max Server Memory setting

Of late I have encountered a number of servers with no Max Server Memory setting, in fact all around me I see SQL Server 2008R2 servers which are straight out the box installs.

A couple of these machines have had memory challenging - I was looking at one today, a virtual, with 30Gb of RAM allocated, 100% adhoc queries (and no "optimize for adhoc workloads" but duly sorted that) where SQL is reporting as using 29.7Gb of RAM and 805mb of ram outside of the buffer cache, needless to say paging was quite heavy as SQL alone was using 30.5Gb of 30Gb.

However, a slight catch, the Max Server Memory setting is dynamic and can simply be changed but very recent experience has shown me that regardless of whether the max setting is above or below current usage for certain the procedure cache is dumped, I haven't checked buffer cache but I suspect that the setting change is a blunt instrument so be warned.

Null storage

I was trying to figure out how much space storage of nulls takes. There are any number of articles online (SQL Server 2012 and prior versions) saying that nulls take no space except for the 2 byte row overhead for having any number of nullable columns.

However, I am convinced I read somewhere that null columns only take no space when they are not subsequently followed by non null data.

So I set up a small experiment that is easy to repeat :

       
 create table fred (
      name varchar(100) not null,
      name2 varchar(100) null,
      name3 varchar(100) not null
)
create table fred2 (
      name varchar(100) not null,
      name3 varchar(100) not null,
      name2 varchar(100) null
)

set nocount on

declare @i int
set @i = 0
while @i < 50000 begin
  set @i=@i+1
  insert into fred values ('dklfsdklfsdlhfsdfhsdfjsdjk',null,'jkadlfhklsdfsdfjksdjkfjksd')
  insert into fred2 values ('dklfsdklfsdlhfsdfhsdfjsdjk','jkadlfhklsdfsdfjksdjkfjksd',null)
  if @i%1000 = 0 select @i -- display progress occassionally
end
 
exec sp_spaceused 'fred'
exec sp_spaceused 'fred2'
 
drop table fred
drop table fred2
       
 

I had a couple of false starts when running this so I received the output below from sp_spaceused where I had more rows that specified in the code but the principle remains the same :

So to summarise what I've done, both tables have 3 fields of varchar(100), the table "fred" has the middle field nullable and the table "fred2" has the final field nullable. Both are filled with identical data and the results show that fred2 with the trailing null colum has taken less space.

This demonstrates that storage of nulls last is most space efficient which on a larger table could translate to real returns on the amount of data being stored on disk and in the buffer cache.

I have a document called "Writing Efficient SQL" which is aimed at in-house developers and I have updated that to recommend all not null fields in a table come first, followed by fixed length nullable fields (fixed length fields are still a fixed length whether null or not it seems) followed by nullable variable length fields in order the the most likely to be not null first to give the greatest chance of storage efficiency.

This does mean that fields are not in a natural order, indeed, it might be easier to simply keep to natural order for all except the variable length nullable fields but generally ordering of fields is not relevant to presentation to the user so shouldn't really make any usability differences.

Memory issues

I've a SQL script that produces cumulative waits (sys.dm_os_wait_stats), key items for review (same source & gives % of all waits to each type but ignore "benign" wait types) and then reproduces the same information but as a delta so current waits can be seen and finally looks at current activities with waits so it is possible to see what is hurting (via sys.dm_os_waiting_tasks and sys.dm_exec_sessions / sys.dm_exec_requests).

This gives me a broad summary of what's going on an is a good indicator of where to look when there are problems, what's more it generates a good viewpoint without taking an age to run which is always good news as the only boxes this needs to be executed against are those with problems.

What I also have is an accumulated list of wait types and meanings, I'm not sure where I picked up the initial list but as time has gone on I've added to and clarified meanings. One area I have been looking at lately is memory grants because I had a server which continually has outstanding memory grants - within the last 12 months we've gone from 16Gb to 40Gb and I think the machine would still like more. The core database suffers a range of problems include the wrong fields / no fields being indexed, uniqueidentifiers, non unique primary keys and a failure to agree an archive policy prior to implementation but that's a little out of scope here. What I do know is that the machine is now largely stable but could do with more memory (EDIT:10 days on and performance problems have persisted and more RAM would definitely be good).

During the course of investigating I have collected further information about the RESOURCE_SEMAPHORE wait type that appears to link wait types to memory shortages which ties in with the grants outstanding issue.

In this article MSSQL Tips : Resource_Semource waits the requested_memory_kb column from sys.dm_exec_query_memory_grants is highlighted showing how much memory has been requested and then talks about looking at sys.dm_exec_query_memory_grants and sys.dm_exec_sql_text(sql_handle) to look at the largest memory requests.

Having been really rather instructive the article then fizzles out and concludes the answer is to look at indexes or lack thereof.

That is good starting point and all to often basic indexes are the fix but indexes can be a minefield, vendors are often very sensitive to making changes  and if they discover changes have been made without agreement then blame all future problems on that one things - I had a vendor once offer to accept the idea of creating two indexes (which I had tested and transformed their system performance) but having received the creation statements from me wanted to charge £800 to allow us to run the statements (I would have been the one running them in the production instance). Further, changing indexes on a mature system can often lead to unexpected effects that are difficult to predict without testing the application in greater depth.

Looking further at the memory requested I would suggest that there is a case to consider other factors too, yes they will be equally contentious with vendors but adding indexes might be a lazy solution :

1. Are joins of the correct type (can that outer be replaced with an inner) and are all join criteria specified
2. Are functions being executed against fields in the where clause that might impact index usage (perhaps in the future, always worth checking as comparisons against variables might be alterable to modify the variable).
3. If the query loading tables and fields that add no value.
4. Are there varchar fields involved - the query optimiser will estimate that varchars take half their defined length for memory requirements usually - have a looked at "Estimated Row size" in the query plan - if your varchar fields are frequently using a lot less than or more than half their defined size the estimates of row size might be a long way out especially for longer fields. You can test this for yourself, turn on the actual query plan and try :
   
   select cast('hello' as varchar(30)) from
   
   Right click on the "compute scalar" item and have a look at the estimate row size (I see 29Bytes), now change the varchar(30) to text or varchar(max), nvarchar(30) or anything else and see what sizes you get. For varchar(max) I get an estimate of 4035B - in the table I picked I have 129 rows so the difference is (129 * 4035) - (129 * 29) - thats 504Kb difference on just 129 rows, it'll soon add up.
5. How many rows does the query plan report will be retrieved - I've just been looking at a query that said it was retrieving 233,131 rows which didn't match the 136 rows I could see in the result set. Looking further at the query and the context from Profiler I can see that the query is generating a parameter list for an SSRS report - still too many rows though but the "distinct" reveals why I see so few records. More examination clarifies that the number of records reported is because the developer wants to get "types" of something that are used and is achieving that by an inner join between the types table (225 rows) and the "items" table that has 15m rows - replacing the inner join with an exists reduces the impact somewhat - runtime reduced from 12 seconds to less than 1.

I'm not saying this is the be all and end all of query optimization, it's a massive subject and I am grateful the optimizer does a brilliant job most of the time, I am though trying to highlight other potential issues.

Lock Pages In Memory

There is a lot of discussion about lock pages in memory on the internet so I'm not going to repeat that discussion. What I have found on some of my 64 bit physical servers I have found lock pages in memory to provide a massive improvement in performance from the perspective of end users.

Symptomatically, prior to using lock pages in memory, we would see the memory use rising (in Task manager) and then at a point close to 100% the server would start paging at an enormous rate (attempting thousands of pages a second) until the memory availability reduced down to a level Windows was obviously happy with. Repeat until users scream.

During the heavy paging activity the system would become unresponsive when combined with attempts at normal activity and that period lastest as long is it took our hardware to sort itself out - more memory = more time.

Turning on lock pages in memory changes that behaviour so that Windows doesn't reach a tipping point and decide to retrieve all that RAM for it's own purposes - essentially Windows really does hand over management to the extent that in Task Manager the SQLServer.exe process no longer shows how much RAM is in use on that process (which is annoying as it was easy to see where all the memory was but of course if Windows isn't managing that memory any more why would it care).

So from the user perspective locking pages in memory for SQL server prevents stop and go performance changes due to paging activity.

Configuration is simple but requires a reboot, here's a sample request to a service provider to get the change approved I raised  :

XYZ server Configuration Change

Aim:

To turn on the “lock pages in memory” configuration to improve the performance of the XYZ SQL Server from the perspective of users - essentially this setting allows SQL Server to prevent the operating system from paging out buffer pool memory that SQL Server is actively using. This is the same change as carried out against AAA Server successfully last year.

Step 1:

The usual first step is to change SQL Server to set a maximum amount of memory that will be consumed – this will ensure we reserve some memory for operating system purposes. This setting is changed on the memory tab of the SQL Server properties and should allow 2 -3 Gb to remain the Operating system + any out of buffer pool RAM that SQL requires, in this instance 3Gb is sufficient.

Step 2:

Change the machine policy to enable the “lock pages in memory” settings. This change must be made to both nodes of of a cluster. Therefore, this step consists of :

1)             Identify the current passive node.

2)             Make the following change to the current passive node as follows:

a.     Click Start, click Run, type gpedit.msc, and then click OK.
Note The Group Policy dialog box appears.

b.    Expand Computer Configuration, and then expand Windows Settings.

c.     Expand Security Settings, and then expand Local Policies.

d.    Click User Rights Assignment, and then double-click Lock pages in memory.

e.     In the Local Security Policy Setting dialog box, click Add User or Group.

f.     In the Select Users or Groups dialog box, add “”, “Administrators” and “<your cluster service account if a cluster>” , and then click OK.

g.    Close the Group Policy dialog box.

3)             Restart the node.

4)             Check that the change is in place.

5)             Fail over to the node just changed.

6)             Repeat the above changes on the new passive node.

7)             Restart the node.

8)             Fail back to the usual node.

The change should be carried out by maintenance staff who should confirm that it will not be overwritten by domain wide policy application.

Step 3:

Email DBAs who can confirm that the configuration change has taken effect, this should take the form of the message “Using Locked Pages For Buffer Pool” appearing in the SQL Server logs.

Assessment:

The main criteria of success is that the user experience has improved.

For further reference on these changes goto http://support.microsoft.com/kb/918483