SQL Server Tutorial: A SQL Server Experiment and its Findings
SQL server is not an ordinary application. Usually Windows
applications use all the memory they need. If there is not enough
memory on a server, some memory pages will be written into a page file.
SQL server application is different. It is aware of the memory
conditions on a server. When it is configured to adjust memory
automatically (Dynamically-configured memory), it can 'breathe',
growing in size when there is a lot of free memory available, and
reducing in size when 'pressure' from other program increases.
Let's check how it works. Experiments are conducted on a small computer with only 512Mb of physical memory.
Point 1. At first SQL server is not started, and about 50% of
physical memory is free. Windows XP and system processes occupy 244 Mb.
Point 2. SQL server is just started, and it occupies only 38Mb.
Point 3. Now I execute several OLAP/reporting queries. These queries
read a lot of data. It takes 25 seconds to execute them the very first
time. SQL server process grew and now it occupies 347Mb. There is no
free physical memory left at this moment: SQL server is aware of the
memory conditions and does not occupy more memory than this computer
Point 4. I execute these queries again. SQL server reads almost
everything from a cache, so, now it takes only 3 seconds to execute all
queries. Not bad.
Point 5. I start Adobe Photoshop and I load 25 big pictures into it.
After loading all these JPG files, Adobe occupies 226 Mb. SQL server
sacrifices its cache, freeing memory to Adobe Photoshop.
Point 6. I execute the same queries again. What a surprise. It takes 68 seconds !!! Much longer than the very first time!
Point 7. I execute my queries again, now it takes 18 seconds. SQL
server size stabilizes around 153Mb. It is not enough to put all data I
read into cache so when Adobe is running, SQL server performance is
Point 8. Finally, I close Adobe Photoshop and execute my queries
again. SQL server occupies all memory, freed by Adobe and returns to
its normal size.
This behavior looks very logical, except for point 5. SQL server
frees up memory when there is not enough memory and allocates memory
when there is unused one, but why is the performance so dramatically
degraded immediately after a start of another memory-consuming program?
The diagram below will help explain it.
On this chart we display the size of SQL server process, reported by
task manager, and internal cache size, reported by SQL server (for
example, by DBCC MEMUSAGE, don't forget to multiply the number by 8,
because 1 page = 8K).
Of course, OS.mem normally is greater than cache, because SQL server
consists of some code, some data plus cache. This is why the red line
is above the blue one. Everywhere, except point 5. What does this mean?
When Adobe Photoshop is started and is taking up more memory, there are 2 things happening at the same time:
- Operating system is trying to free up physical memory for Adobe,
moving SQL server into the pagefile.sys. It can move any page: code
page or data page.
- SQL server sacrifices a cache, adjusting its memory to new conditions
Obviously, the first process is faster then the second one. So, at
point 5, some of SQL server cache and code are actually on the disk, in
pagefile.sys. The very first access to SQL server, even with a very
simple query, like 'select 1', generates a lot of page faults. What is
worse, the pattern for such reads is random, 'scattered read', which is
less effective then a sequential read, normally used by a database
What happens when 2 SQL server instances coexist on the same server?
Both instances are 'memory-aware', they allocate memory based on the
balance of the workload. In some cases, it can do more harm than good.
I continue the previous experiment by running a second instance on
the same server. On the second instance, let's call it 'instance B', I
run query, which normally requires about 170Mb memory to be cached in
full. The first execution takes 13 seconds; it reads all pages from
disk. Interesting, but the second execution takes about the same time.
And the third one, I continue the experiment:
After each 3-5 executions instances A and B rebalance memory, adding
4Mb portions to instance B. Execution time slowly improves. Only after
155 executions (!!!), finally, instance B allocated all memory it
Why did it take so long? The answer is that instance A executed a
lot of physical reads before, and was considered more 'active' under a
'heavy load' in comparison with instance B. That is why instance B
tried to keep itself so small for so long.
What can we learn from these experiments?
- SQL server instances should be used only when there is no other
choice. For example, when you need different collations of master
database, or when you have incompatible security models. It is not a good way to balance a workload. Two databases on the same instance would be balanced much better, then 2 databases on separate instances. It is not recommended to install multiple instances on the production (unless it is used for a cluster, of you have a clear understanding of what you are doing)
- Golden rule is: SQL server should never be in a pagefile on a
production. Check that Page Fault counter is low for SQL server process.
- If you have more than 2Gb of memory (4Mb on a cluster), always use
AWE memory (this is not applicable to 64 bit systems, of course). AWE
memory is never paged out.
- If you don't use AWE memory, you can use (2Gb-SQL server code size) =
about 1.7Gb. If you have enough memory on a dedicated server, you can
leave memory in an automatic mode.
- However, if you have other processes on this server, it is
recommended to use a fixed memory with an option 'Reserve Physical
memory for SQL server'
- The worst combination is a SQL server with dynamic memory allocation coexisting with a periodic memory-consuming process.
- SQL server is not considered to be a real-time system. However,
giving it a smaller, but guaranteed amount of memory makes it response
time more predictable. Dynamically-configured memory might allow SQL
server to allocate more memory, so it would work faster sometimes, but
it might cause unexpected delays, when it takes seconds for SQL server
to 'wake up' (operating system will be reading process pages from a
pagefile) in order to reply to a very simple request.