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!


16

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


14

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


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.


11

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

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


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

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

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

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


9

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


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

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

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


8

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


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

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


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

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


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

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


7

I'm guessing the 'memory left' output you posted is from Christian Bolton's VAS usage analysis script. Is that correct? If so, you have only 17-18 MB available in the memtoleave area, which is likely to cause a problem at some point and could cause all or most of the error messages that you pasted. This query from Jonathan Kehayias here will give you an ...


7

Can I have this 32 bit installation use more memory using locked pages? In the past, my answer would have been (a heavily qualified) "yes". Using AWE (address windowing extensions) would enable up to 64GB memory. Unfortunately (from your point of view, perhaps) 32-bit AWE was removed from the database engine starting with SQL Server 2012. In previous ...


6

Memory usage in linux in general and for Postgres in particular is a pretty complex subject, a good starting point is Bruce Momjians blog covering the subject and the usage of smem. It is well worth following the links in Chris Seibenmanns blog on the subject.


6

You could NEVER, EVER trust Task Manager to tell you how much memory SQL Server is using. 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 sys.dm_os_performance_counters WHERE counter_name = 'Total Server Memory (KB)'; You could save that ...


6

I'm not sure I believe there was ever an actual issue. Just because max server memory was set to 2 petabytes doesn't mean it would ever use that much. This is the functional equivalent of setting it to infinity, and just means you trust SQL Server to use as much memory as it needs. Setting it to 500 MB seems artificially low to me. While your database is ...


6

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



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