Common SQL Server myths – Series II – SQL Memory

Not so long ago, I had written a blog post for SQL Server myths related to Setup & Service startup myths for T-SQL Tuesday which was hosted by Sankar Reddy (Blog | Twitter). The round-up of the T-SQL Tuesday posts had some awesome content where renowned SQL Community members debunked common SQL Misconceptions. Read the round-up here.

I found that that this was quite a compelling topic for a series and decided to do a Part II for SQL Myths around memory related misconceptions. SQL Memory by itself is a murky topic and there are quite a few urban legends that are so well imbibed in people’s memories that we have to battle against them to get users to see light at the end of the tunnel. So, here goes another round of debunking some more misconceptions.

Thumbs downAWE needs to be enabled on 64-bit instances for SQL Server
I think everyone who has posted an answer for this question on MSDN forums has probably gone tired of saying the same thing over and over again. This configuration option is required for 32-bit instances of SQL Server only to enable the instance to benefit from the Address Windowing Extension. AWE option on 64-bit instances has no meaning. This is not required and basically does nothing. Please refer the following blog post more details:

Thumbs downSystem.OutOfMemoryException encountered while executing a query from SSMS is not a Database Engine Out-of-Memory condition
I have seen multiple suggestions on forums for reducing Max Server Memory. This error is a .NET exception telling the user that an OOM condition occurred. The database engine is not a managed application. It was coded in native language and hence, wouldn’t throw a .NET OOM error. I have written about this in an earlier blog post: System.OutOfMemoryException for Management Studio

Thumbs downWindows Task Manager will tell me SQL Server’s memory usage
Another urban legend that CSS deals with day in and day out. The “Monitoring Memory Usage” topic in SQL Server Books Online talks about how to monitor SQL Server memory usage. None of the tools you typically use to inspect application memory use (Task Manager, Perfmon/Sysmon, etc.) show the amount of AWE memory used by individual processes. There’s no indication of the amount of AWE memory used by each process, nor is this memory included in the working set size reported for a given process. Reference:

Thumbs downMax Server Memory will limit the SQL Server memory
This is true for only the SQL Server Buffer Pool. Max Server Memory setting is used to control the size of the Buffer Pool only. For a 64-bit instance, the non-BPool region can grow to a maximum of Total Physical Memory – (Max Server Memory + Memory used by Windows & other applications). I had blogged about this in the past in my post: Non Bpool region on 64bit SQL instances may be larger than you think

Thumbs downSQL Server eats up all the available memory on the box
SQL Server is a server application. Ideally, you should have a dedicated server for a SQL instance that has a high number of database transactions. If that is not the case, then the Max Server Memory needs to be capped after taking into account the memory requirements for Windows, SQL Server and the other applications on the box. SQL Server (especially 64-bit) is designed to not back off it’s memory footprint once it grows unless it is asked to because of a low memory notification received from the Operating System. The thumb rule of leaving just 2GB for the OS and other applications may not be enough when you have File Copies, SSRS, SSIS and SQL Database Engine running on the same instance. SQL Server 2005 and above is designed to react to low memory notifications sent out by Windows and scale back it’s memory usage as required.

Thumbs downVirtual Address Space – Isn’t that an excuse?
Virtual Address Space defines the amount of memory that can be addressed by any application and the limiting factor is always the processor architecture: 32-bit or 64-bit. 32-bit Windows can reference upto 4GB (232) and 64-bit can reference a maximum of 8TB (264) depending on the Windows edition. The addressing limitations on a 64-bit instance is limited to the availability of your Physical RAM which is an exact opposite of the story of a 32-bit server. With Physical Address Extension enabled on the Windows servers, Windows can use more than 4GB of memory and SQL Server (or any other Windows application if coded correctly) can take advantage of this by using AWE APIs. If you are falling prey to VAS limitations, then you either have a large VAS consumer or your workload is not the most optimal workload for 32-bit architecture! If you are facing a Virtual Address crunch on your SQL instance, then follow the steps mentioned in the post to work towards determining the root cause of the problem: Who is eating up the Virtual Address space on your SQL Server

Thumbs downI can change –g to whatever I want!
Absolutely not! The hard coded upper limit for –g is 1024GB. Whenever you increase your MemToLeave on a 32-bit machine, you can decreasing the SQL Serve Buffer Pool memory proportionately. Enabling this for a 64-bit instance is bit of a moot-point. So increasing –g is just a workaround and sometimes it might lead you to face BPool pressure on the server. I had documented the layout of the buffer pool for a 32-bit instance in an earlier blog post: SQL Server Memory Basics

Thumbs downI am Smart! I will enable /3GB and then increase –g. All problems solved!
Well, I would say you are too smart for your own good. For systems with above 16GB of RAM, this can prove to be counter-productive. This will only cause a lack of available System PTEs on the Windows server and create more problems than it actually solves. From “Using AWE” topic in SQL Server Books Online:

If there is more than 16 GB of physical memory available on a computer, the operating system requires 2 GB of virtual address space for system purposes and therefore can support only a 2 GB user mode virtual address space. For the operating system to use the memory range above 16 GB, be sure that the /3gb parameter is not in the Boot.ini file. If it is, the operating system cannot use any physical memory above 16 GB.

Thumbs downLock Pages in Memory is the next best thing after sliced bread!
How many times have we heard this: “Enable LPIM for the SQL service account and everything will be alright”! Everything will be alright only when you have taken into consideration the memory requirements for the SQL Server instance(s) on the box, other applications and Windows and then capped the Max Server Memory. Without that enabling LPIM for the SQL Service account is as good riding a bike on the freeway without a helmet. It’s all smooth sailing till disaster strikes!

Hope the above points help clear out some urban legends regarding SQL Server Memory concepts and next time during a coffee break or a water cooler discussion, the above points help you convince a *misguided SQL Server expert*!! Winking smile

Wiki: SQL Database Engine Startup Failures

The startup failures for SQL Server can be classified into two types:

1. Service startup failures for stand-alone instances

2. Services startup failures for clustered instances

I shall document a road-map for a step-by-step troubleshooter for handling SQL Server startup failures for both stand-alone and clustered instances. By no means, is this the only way to troubleshoot startup failures but for a novice SQL Server user (hopefully, you don’t attempt the steps below for the first time on a production instance), this would be a good starting point in determining the root cause of the startup failures.

Services startup failures for stand-alone instances

If you are using SQL Server 2000, then try and start the SQL instance from the Services Manager and check if the service starts up. If you are using SQL Server 2005 or above, then try and start the service using SQL Server Configuration Manager.SQL Server Startup Paramaters

If the service fails to start, locate the SQL Server Errorlog location and check if any errors were observed in the SQL Server Errorlog. This can be done by looking up the –e startup parameter value for the SQL Server instance in question. If you are using SQL Server 2005 and above, then right-click on the Service Name in Configuration Manager –> Properties –> Advanced Tab (Refer Pic for more details). If you want to act smart in front of your co-worker Smile with tongue out, then you can use a VBScript (or Powershell) to fetch this information. Here is an example for obtaining the Startup Parameters using WMI and VBScript.

Verify that the files pointed to by the –d (master data file location), –e (Errorlog location) and –l (master log file location) actually exist and have the correct file paths. Starting from SQL Server 2005, the Errorlog will have the startup parameters printed in it when the SQL instance starts up.

Once you have located the current ERRORLOG (file with the same name will be present by the same name in the LOG folder), check for any errors present in the SQL Server Errorlog. An example of an error would be:

2010-11-20 07:50:58.250 Server Error: 17113, Severity: 16, State: 1.
2010-11-20 07:50:58.250 Server Error 2(The system cannot find the file specified.) occurred while opening file ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf’ to obtain configuration information at startup. An invalid startup option might have caused the error. Verify your startup options, and correct or remove them if necessary.

The above error message clearly indicates that the master.mdf file was not found in the location that it was expected to be found. If your system database files are not consistent, the SQL Server instance will not startup.

Note: If you see that the timestamp in the SQL Server Errorlog file doesn’t reflect the current system time when you attempted to start the SQL instance, then it is highly likely that the path to the SQL Errorlog is incorrect. If that is true, then the SQL Server instance will fail to start and a pretty unhelpful error message will be shown to you. It is always one of the two:

“The service failed to respond in a timely fashion”


“The service failed to start”

The most common root causes for service startup failures are:

  1. Access denied on the folders for the SQL Server Service Account on the DATA folder which contains the SQL Server system databases or due to some other process (like anti-virus holding a lock on the SQL database files)
  2. Insufficient permissions granted to the SQL Server Service account.
  3. File not found errors due to either accidental deletion of files or disk failures.
  4. System database files having inconsistencies prevent SQL Server startup.
  5. Password of the service account was changed but not updated on the server that has the SQL instance installed.
  6. Startup parameters have incorrect file path locations.
  7. The SQL Server service is disabled in Service Control Manager.

Find out if the SQL Server service account has all the necessary permissions required to start the SQL Server service. This would cover the ACLs, Security Privileges for the Windows Account, and Windows Registry permissions. Refer the Books Online article which documents all the permissions required by a SQL Server service account.

Even if that doesn’t help, then check the Windows System and Application Event logs and check for any errors related to the SQL Server instance or service that is currently failing to start.

Note: Make sure that you are not falling prey to a Desktop Heap exhaustion issue.

Still stuck!! Wow!! Aren’t we in a soup. No errors in the Errorlog (very very rare case scenario) or in the Windows Event Logs.

Then you need to try and start the service using command prompt in console mode. There is yet another Books Online article which explains how to start the SQL Server instance using sqlservr.exe located in the BINN folder.

Example for Default instance:

sqlservr.exe –c –m -T3608

Example for Named instance:

sqlservr.exe –c –m –s <instance name> -T3608

Check if the command prompt window spews out any helpful error message which will point you to the root cause of the issue. The Trace Flag 3608 prevents any database startup apart from the master database. This will help you determine if the SQL instance is starting up correctly and the problem might lie with the other system databases.

During my course of troubleshooting, I have also encountered some pretty weird startup failures. Of the top of my head, here are a few which I could find links to:

By Balmukund Lakhani (Blog)

The famous (infuriating) TDSSNICLIENT client initialization failures during startup. The SQL Protocols team has documented a bunch of them here:

The case of the obnoxious locks value:

In case you have any more interesting startup failures that you find blog posts about, feel free to leave a comment for this post.

Startup Failures for Clustered Instances in another blog post.

Versions that this road-map applies to:

SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2

Additional references:
T-SQL Tuesday: Common SQL Server myths – Series I–Setup and Services

Removing primary transaction log file

Recently I was discussing about removing transaction log files on the #sqlhelp hashtag on Twitter. The question was whether we could remove an existing transaction log file after adding a new one. During the course of the discussion, I learnt that the file that was being removed was the primary transaction log file of the database.

When you attempt to remove the primary log file of a database from Management Studio or using ALTER DATABASE command, you will receive the following error:

Drop failed for LogFile ‘dbLogTest_log’.  (Microsoft.SqlServer.Smo)

The primary data or log file cannot be removed from a database. (Microsoft SQL Server, Error: 5020)

This is by-design. We do not allow the removal of the primary transaction log file of user databases. This was first introduced in SQL Server 2000.

Tibor (Blog), a SQL Server MVP, has already blogged about how to remove transaction log files using T-SQL commands here. So, I am not going to duplicate the post here again by providing the same T-SQL commands.

How to get File Space used information

There are multiple ways to get this done. Starting from SQL Server 2005, you can get this information using the system logs to get the similar kind of information using system catalogs.

This can be achieved using sys.master_files and FileProperty function. The query below will give you the same information as you see in the Object Explorer window when you do the following: Right Click database name –> Tasks –> Shrink –> Files.

EXEC sp_MSforeachdb 'SELECT DB_NAME() as database_name,
name as [File Name],
physical_name as [Physical Name],
size/128.0 as [Total Size in MB],
size/128.0 - CAST (FILEPROPERTY(name,''SpaceUsed'') as int)/128.0 AS [Available Space in MB]. [file_id]
FROM sys.database_files;'

[Blog Update] SQLServerFAQ posts for October

Here are my posts on the MSDN blog SQLServerFAQ for the month of October.

A guide to troubleshooting tempdb growth due to Version Store usage:

Troubleshooting tempdb growth due to Version Store usage

An email chain with Jonathan (Blog | Twitter) resulted in this blog post:

Troubleshooting specific Login Failed error messages

A much needed VDI troubleshooting tool called SQL Server Backup Simulator:

SQL Server Backup Simulator