Contact  |  Site Map  |  Privacy      Call for more info
 
Latest articles

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 has.

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 degraded.

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 engine.

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 wanted.

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.
Disclaimer:

In many articles you will find the execution times of different queries. Please do not interpret these results scientifically or as official benchmarks. You will not even find the configuration of the computer used for these experiments because it is rather unimportant.

What is important is that query A is running 2 times slower then query B. On your computer, it could be different: 1.5 or even 3 times slower. Read the full disclaimer