Originally Posted by
Does the amount of memory used by SQL server correlate with speed (i.e. higher memory used means slower speed)

Yes it does correlate. No higher memory does not mean slower speed. The more memory SQL Server can attain, the more efficient and fast it will be. Your confusion is partly related to understanding the difference between RAM that is used by the OS for running applications, etc. and the RAM used by SQL Server itself.

All of your computers including a server if you were using one need RAM. As you already know, everything in memory goes away as soon as you reboot or log off (not lock only). So, the more RAM you have on your computers, the better. Of course, that depends on your needs. If your client has 8GBs and you tend to need 6GBs, that is a good amount. But, having 100GBs, isn't going to help any (although it can get a bit more complicated than that).

SQL Server must run on the OS. SQL server will take memory from the server. It will access the amount of free memory as soon as you start the server and dynamically from then on, freeing up memory at times and taking memory at times. But, mostly, it is just easiest to think of it as SQL will take as much memory as it can. Difference versions of SQL can use different amounts of memory. So SQL Server 2012 Standard can take all the memory an OS has. So, to avoid SQL server from taking so much memory, the OS can't run efficiently, you use Management Studio to set minimums and maximums. With Express, one of the reasons it is free, is because it is limited to 1GB of memory. 2005 has 1GB of memory and 4GBs of database space. 2012 Express has 1GB of memory and 10GBs of database space.

If you reboot your main computer with SQL Server, all of the memory it had taken is given back to the OS. (Remember, this is for each instance, e.g. AmazingCharts instance, but you may have your billing software instance, etc.)

It is easy to understand if you look at it just like the normal RAM situation. When you first click on Word, it takes anywhere from five to twenty seconds to load depending on your hard drive speed and other factors like SSD vs HDD. But, when you minimize it, it will pop right back up when you need it, because you are no longer getting it from the hard drive; it is already in RAM.

Same with SQL. The first time you query certain information, you must get it from the SQL Server database. But, SQL will remember that you queried that info and save it in RAM or its cache as a page. The next time you query this info it only needs to obtain it from the SQL cache and not the database. Now, every page that is saved takes up some space or memory. So, there more memory SQL has, the more queries (pages), it can save. So, it is to your advantage to have a lot of memory for SQL. You are limited, however to that 1GB of RAM. Once, SQL uses all of its memory, it will discard the oldest page. So you likely won't notice a lot of difference overall unless you are making tons of queries.

So, when SQL continues to run, and it continues to utilize more of the free memory in the OS, every time you look in Task Manager, it will have more memory showing. A lot of people refer to this as a memory leak, which it is not. This is a good thing. But, it is a good thing if you have a lot of memory on your computer. So, like NeuroDawg who is anxiously awaiting more memory, if you have 12GBs or so on your main computer, what's 1GB? Not a big deal. But, if you are using a 32-bit OS with 4GBs of RAM, which the server can only see as 3.2GBs or so of RAM, now it is a different story. Now you have the interesting issue of which RAM is more important. It is great if SQL can use a ton of RAM, but if in taking the full GB of RAM, it lowers the OS's amount to 200MBs, well that's an issue. SQL isn't going to be very efficient if the OS slows down to a crawl, which it will.

So, you look in Task Manager, and you see the amount of RAM taken up by SQL, and you think it is leaking all the memory. So, you reboot, and suddenly the OS now has access to all the memory again, and it works fine. Obviously, if the OS has say 3GBs of usable RAM and SQL takes up 1GB of that, then there are still two GBs left. But, that is being used by many other processes and apps on the computer. With the advent of 64-bit OS and extremely cheap RAM, if you put 8, 12, 24 GBs of RAM on the computer, then it isn't an issue.

The only other thing you may want to consider, which goes back to the user war of P2P vs Client/Server war of 2011, servers are optimized for background services while clients are optimized for Programs. A server machine with a server OS, can manage a lot of these things more efficiently than, say, Win 7. If you go to Computer > System Properties > Advanced system settings > System Properties > Advanced tab > Performance (Settings) > Advanced, you will see those options. Changing your main computer to Background services, will not likely help much, but you can try it.

Short answer: Higher memory used means being able to query data from the memory cache and not the database.



Bert
Pediatrics
Brewer, Maine