Tag Info

Hot answers tagged

25

Assuming Windows and SQL Server... There are two schools of thought. Leave 2-4 Gigs for Windows (depending on what is installed besides SQL Server). Leave 10% of your available memory free. As you get over 64 Gigs this gets to be a crazy large amount of memory to leave for the OS, which it probably won't need. Personally I'm in the first group. ...


21

The SQL Server development team work on the principle of least surprise - so SQL Server generally has new features disabled in the interests of maintaining behaviour as previous versions. Yes, optimize for adhoc workloads is great at reducing plan cache bloat - but always test it first!


17

Looking at the feature availability list at http://dev.mysql.com/doc/refman/5.1/en/memory-storage-engine.html two possible problems jump out: No transaction or FK support, meaning you will have to manage transactional integrity and referential integrity in your own code were needed (which could end up being a lot less efficient than letting the DB do this ...


17

If I have 8GB of RAM in a server and I run 4 instances of SQL Express, will the total memory limit used by SQL Server be 1GB or 4GB? Each instance can use up to 1GB of memory for the buffer pool. Each instance can use a bit more than 1GB in total because not all memory allocations go via the buffer pool. In your case, the maximum memory used by the ...


15

Assuming Linux, if you turn off swap and the kernel keeps killing your DB process because it's out of memory, that's a good indicator you're starving the OS for memory. Back off until that stops happening. A couple hundred megs is usually plenty.


14

You won't get a true picture of memory usage from Task Manager if the account the service is running under has the lock pages in memory privilege (edit: as per Mark Rasmussen's comment/link). To determine how much memory is being used you can look at: SQLServer:Memory Manager\Total Server Memory perfmon counter DMVs I can't recall if there is a DMV or ...


14

Standard Edition is limited to a 64 GB buffer pool. To use all the available memory of the server, you'll need to upgrade to either Enterprise or Datacenter Edition, or scale out to multiple instances of Standard Edition.


13

SQL Servers max memory setting defines the limits for buffer pool usage only. There will be variable but significant allocations required over and above that limit. Jonathan Kehayias's, Christian Bolton and John Samson have level 300/400 posts on the topic. Brent Ozar has an easier to read article that might be a better place to start. Also related: SQL ...


11

Reposting my answer to a similar question regarding SQL Server: In the SQL world, order is not an inherent property of a set of data. Thus, you get no guarantees from your RDBMS that your data will come back in a certain order -- or even in a consistent order -- unless you query your data with an ORDER BY clause. So, to answer your question, ...


11

As said Buffer pool and procedure cache are about the only things which are controlled by max server memory. There are lots of other things within SQL Server that can eat up memory beyond that limit. They include (but are not limited to): Database Mail SQLCLR Extended Stored Procedures The binaries them selves SQL Mail SSIS SSAS SSRS


11

SQL Server will consume as much memory as you will allow it. By default, that number would encompass 100% of your numerical memory on your machine. That's why you're seeing what you're seeing. If you give SQL Server 24 GB of memory, then SQL Server will do its best to use 24 GB of memory. Then you have SQL Server and the OS battling for resources, and ...


10

Stop/start the service, nothing else will release the memory back to the OS. Obviously not something you'd ever want to do with an operational server but perfectly reasonable for a local sandbox. With 3 different instances on my laptop, its the only viable way. Edit following @Nick's comment. As James noted, if it reserves more than the minimum you ...


10

There are no special settings for SQL Server which only uses physical memory normally Just do what MS say for Windows and that's it Oh, and buy more RAM anyway while we're one the subject... ;-)


10

You may be interessted in one of the mailing lists topics, it's answerd by Tom Lane (core dev): [..] But my opinion is that people who think they are smarter than an LRU caching algorithm are typically mistaken. If the table is all that heavily used, it will stay in memory just fine. If it's not sufficiently heavily used to stay in memory ...


10

The only way to prove is not a memory issue is by prooving is something else. And this requires you to identify the root cause of your performance problems. I recommend you follow a methodology like Waits and Queues. The SQLCAT team has also published a Troubleshooting Flowchart poster that you can follow. As a general side comment: if someone is offering ...


10

IMO you are making what is probably a pretty common mistake when it comes to web pages which is to assume that the answer to performance problems due to initial result size on MySQL is to jump to NoSQL solutions often with little understanding of what the tradeoffs are or how to use them appropriately and effectively. I would be surprised if a well-tuned db ...


9

You could NEVER, EVER trust Task Manager to tell you how much memory SQL Server is using (maybe you are remembering a 32-bit system with a very small amount of memory). Stop using Task Manager for this, period. Use the performance counter - you can also query the performance counter using DMVs: SELECT object_name, cntr_value FROM ...


9

This is one of the most controversial topics I have ever dealt with over the years as a MySQL DBA and in the DBA StackExchange. To put it mildly, there is simply no other way to shrink ibdata1. With innodb_file_per_table disabled, every time you run OPTIMIZE TABLE on an InnoDB table, ibdata1 grows rapidly. Data that are dropped using DROP TABLE and DROP ...


9

You can find whats stored in the buffer pool (data cache) using below query : From here : select count(*)as cached_pages_count, obj.name as objectname, ind.name as indexname, obj.index_id as indexid from sys.dm_os_buffer_descriptors as bd inner join ( select object_id as objectid, ...


8

There are plenty of cases not to use the memory storage engine - and when InnoDB will be faster. You just need to think about concurrency and not trivial single threaded tests. If you have a large enough buffer pool, then InnoDB will become entirely memory resident for read operations as well. Databases have caches. They warm themselves up! Also - do ...


8

mysqltuner rarely provides any useful information. It uses mostly irrelevant statistics about "hit rates" and puts arbitrary limits on what is an acceptable number of widgets are acceptable. If you are not facing a performance problem, then you don't actually need to solve any of the problems that it presents to you. That being said, here's a little ...


8

To address your developers concern only (i.e. demonstrate memory isn't the problem), you could start by proving that the query isn't waiting on memory grants and that page life expectancy is at a comfortable figure. Memory grants (from Glen Berry): -- Shows the memory required by both running (non-null grant_time) -- and waiting queries (null grant_time) ...


8

I would suggest collecting performance metrics on this server, so you can eliminate the guesswork from troubleshooting these types of problems. See this article for a more complete guide if you don't know where to start with this. In particular, I would check the performance counters Memory\Available MBytes and Paging File(_Total)\% Usage because you said ...


8

This is an awesome post. To answer your final question, I'd speculate that your answer is "yes". That said, I probably would have pursued soft numa before resorting to the trace flags. I think you are right about the numa node allocation and that's could be at the root of your problem. Via soft numa, you could scale out the requests, depending on your ...


7

You can leverage Amazon's experience running thousands of customers' database servers here: On Amazon Relational Database Service, they set MySQL's innodb buffer pool to 3/4 of the system's memory, regardless of how much memory that is. Add in up to a couple megs per connection for various query buffers, and they're likely leaving 10-20% of the memory to the ...


7

The order of the rows in the absence of ORDER BY clause may be: different between any two storage engines; if you use the same storage engine, it might be different between any two versions of the same storage engine; Example here, scroll down to "Ordering of Rows". if the storage engine version is the same, but MySQL version is different, it might be ...


7

Consider using pgtune to help you tune the PostgreSQL configuration. From PgFoundry: pgtune takes the wimpy default postgresql.conf and expands the database server to be as powerful as the hardware it's being deployed on PostgreSQL's default configuration is very conservative and that tool is meant to help with this exact situation. The ...


7

There's a recent article from our own Brent Ozar that treats this case, when Task Manager doesn't show correctly the memory eaten by SQLServer and its additional services. You can find it here: A Sysadmin’s Guide to Microsoft SQL Server Memory. Quote: "Why Isn’t SQLServer.exe Using Much Memory? When you remote desktop into a server and look at Task ...



Only top voted, non community-wiki answers of a minimum length are eligible