In one of our PROD Servers...  The Total Physical RAM is 128GB The Total RAM I assigned for SQL Server is 122GB.  When I checked the SQL Server there were no Open Transactions, No Blockings....But still the Memory Consumption is higher and it show 99% in Task Manager and performace has been slowed down.  Should we increase the Memory or I don't understand if OS or SQL Server does not have enough Memory. Please advice if we need to allot some enough Memory to OS too...
359
I'm trying to come up with a sane way to understand if the max server memory (mb) setting is appropriate (either should be lower, or higher, or stay the way it is).  I am aware that max server memory (mb) should always be low enough to leave room for the operating system itself, etc.    The environment I'm looking at has several hundred servers; I need a reliable formula I can use to determine if the present size of the buffer pool is appropriate since RAM is costed per GB allocated to each server.  The entire environment is virtualized, and "physical" RAM allocated to a VM can easily be changed up or down.  I have a particular SQL Server Instance I'm looking at now with a PLE of 1,100,052 seconds, which equates to 12.7 days (the amount of time the server has been up).  The server has a max server memory setting of 2560MB (2.5GB), of which only 1380MB (1.3GB) is actually committed.    I've read several items including one by Jonathan Keheyias () and another by Paul Randal (), and several others.  Jonathan advocates monitoring for a PLE below 300 per 4GB of buffer pool as being too low.  For the SQL Server Instance above, 300 * (2.5 / 4) = 187 results in a really really low target PLE below 300.  This instance has 290GB of SQL Server data (not including log files), and is only used for integration testing.  Assuming the last 12 days is representative of typical usage for this server, I'd say the max server memory (mb) setting could be lowered.  At the other end of the scale, I have another integration-test server with a PLE of 294, that has a max server memory (mb) setting of only 1GB.  This server has only 224MB of SQL Server data not including logs, and is running some BizFlow databases.  This server might benefit from a higher max server memory (mb) setting.  I'm thinking a good starting place for targets that might be assigned too much memory could include looking at:  SELECT      RamMB = physical_memory_in_bytes / 1048576     , BufferPoolCommittedMB = bpool_committed * 8192E0 / 1048576     , BufferPoolCommitTargetMB = bpool_commit_target * 8192E0 / 1048576     , PercentOfDesiredSizeMB = CONVERT(INT,(CONVERT(DECIMAL(18,2),bpool_committed)                              / bpool_commit_target) * 100) FROM sys.dm_os_sys_info;   If BufferPoolCommitTargetMB / BufferPoolCommittedMB is greater than 1, the server is not using the entire buffer pool.  If the machine in question also has a PLE of greater than "x" then it might be a good candidate for a decrease in max server memory (mb).  Since the Buffer Manager:Lazy writes/sec performance counter tracks the number of times SQLOS has written pages out to disk between checkpoints due to memory pressure, this might be another good thing to look at.  DECLARE @WaitTime DATETIME; SET @WaitTime = '00:00:15'; DECLARE @NumSeconds INT; SET @NumSeconds = DATEDIFF(SECOND, 0, @WaitTime); DECLARE @LazyWrites1 BIGINT; DECLARE @LazyWrites2 BIGINT;  SELECT @LazyWrites1 = cntr_value  FROM sys.dm_os_performance_counters dopc WHERE (         dopc.counter_name LIKE 'Lazy writes/sec%' COLLATE SQL_Latin1_General_CP1_CI_AS     )     AND dopc.object_name = 'MSSQL$' + CONVERT(VARCHAR(255),                SERVERPROPERTY('InstanceName')) + ':Buffer Manager';  WAITFOR DELAY @WaitTime;  SELECT @LazyWrites2 = cntr_value  FROM sys.dm_os_performance_counters dopc WHERE (         dopc.counter_name LIKE 'Lazy writes/sec%' COLLATE SQL_Latin1_General_CP1_CI_AS     )     AND dopc.object_name = 'MSSQL$' + CONVERT(VARCHAR(255),                SERVERPROPERTY('InstanceName')) + ':Buffer Manager';  SELECT LazyWritesPerSecond = (@LazyWrites2 - @LazyWrites1) / @NumSeconds;   The above code assumes the server is under load during the 15 seconds it takes to run, otherwise it will report 0; which might be a misleading false-negative.  Should I also look at PAGELATCHIO_* wait stats or some other wait type as an indicator of memory pressure, or lack thereof?   My question is, how can I reliably determine a "good" target value for PLE and max server memory (mb)?
35687
I am requesting a new server from our Server team. Which is better for SQL Server performance: 2 CPUs with 8 cores per CPU or 1 CPU with 16 cores?
62440
My girlfriend bought a laptop last week. It's a core 2 duo with 4 GB  We installed vista 64bit, and one of the first things we did was right click on "My computer" to see gthe properties. Immediately we noticed something strange about her RAM, the line said: Installed memory (RAM): 4,00 GB (3,68 GB usable)  I told her not to worry, thinking it must be something about the laptop hardware (considering her vista installation came from the same DVD as mine, and I never noticed anything like that on my 4 GB desktop). One hour ago, it got worse. We looked at Properties again, and it now says: Installed memory (RAM): 4,00 GB (2,98 GB usable)  What does that mean? Are those 1,02 GB missing or being used by the system?  EDIT: There is a possibility that the sytem information is wrong. I just noticed that it reports an intel T6500 processor, when it's actually a T6400.  How can I find out how much RAM is really available to the system?  EDIT2: Checking the resource monitors, it says 1003 MB are reserved for the hardware. Is that good or bad? Thanks
21696
I am using a linux server which has 128GB of memory and 24 cores. I use top to see how much it is used. Its output is pasted at the end of the post. Here are two questions:  (1) I see that each of the running processes occupies a very small percentage of memory (%MEM no more than 0.2%, and most just 0.0%), but how the total memory is almost used as in the fourth line of output ("Mem:  130766620k total, 130161072k used,   605548k free,   919300k buffers")? The sum of used percentage of memory over all processes seems unlikely to achieve almost 100%, doesn't it?  (2) how to understand the load average on the first line ("load average: 14.04, 14.02, 14.00")?   Thanks and regards!  Edit:  Thanks!  I also really like to hear some rough numbers based on used percentage of memory to determine if a server is heavily loaded, since I once became the one who cramed the server without understanding the current load.  Is swap regarded as almost the same as memory? For example, when memory and swap are almost of same size, if the memory is almost running out but the swap is still largely free, may I just view it as if the used percentage of memory + swap is still not high and run other new processes?  How would you consider together CPU or memory (or memory + swap) usage? Do you become worried if either of them reaches too high or both?  Output of top：  $ top    top - 12:45:33 up 19 days, 23:11, 18 users,  load average: 14.04, 14.02, 14.00 Tasks: 484 total,  12 running, 472 sleeping,   0 stopped,   0 zombie Cpu(s): 36.7%us, 19.7%sy,  0.0%ni, 43.6%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st Mem:  130766620k total, 130161072k used,   605548k free,   919300k buffers Swap: 63111312k total,   500556k used, 62610756k free, 124437752k cached    PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND  6529 sanchez   18  -2 1075m 219m  13m S  100  0.2  13760:23 MATLAB 13210 timothy   18  -2 48336  37m 1216 R  100  0.0   3:56.75 absurdity 13888 timothy   18  -2 48336  37m 1204 R  100  0.0   2:04.89 absurdity 14542 timothy   18  -2 48336  37m 1196 R  100  0.0   1:08.34 absurdity 14544 timothy   18  -2  2888 2076  400 R  100  0.0   1:06.14 gatherData  6183 sanchez   18  -2 1133m 195m  13m S  100  0.2  13676:04 MATLAB  6795 sanchez   18  -2 1079m 210m  13m S  100  0.2  13734:26 MATLAB 10178 timothy   18  -2 48336  37m 1204 R  100  0.0  11:33.93 absurdity  12438 timothy   18  -2 48336  37m 1216 R  100  0.0   5:38.17 absurdity 13661 timothy   18  -2 48336  37m 1216 R  100  0.0   2:44.13 absurdity 14098 timothy   18  -2 48336  37m 1204 R  100  0.0   1:58.31 absurdity 14335 timothy   18  -2 48336  37m 1196 R  100  0.0   1:08.93 absurdity 14765 timothy   18  -2 48336  37m 1196 R   99  0.0   0:32.57 absurdity 13445 timothy   18  -2 48336  37m 1216 R   99  0.0   3:01.37 absurdity 28990 root      20   0     0    0    0 S    2  0.0  65:50.21 pdflush 12141 tim       18  -2 19380 1660 1024 R    1  0.0   0:04.04 top  1240 root      15  -5     0    0    0 S    0  0.0  16:07.11 kjournald  9019 root      20   0  296m 4460 2616 S    0  0.0  82:19.51 kdm_greet     1 root      20   0  4028  728  592 S    0  0.0   0:03.11 init     2 root      15  -5     0    0    0 S    0  0.0   0:00.00 kthreadd     3 root      RT  -5     0    0    0 S    0  0.0   0:01.01 migration/0     4 root      15  -5     0    0    0 S    0  0.0   0:08.13 ksoftirqd/0     5 root      RT  -5     0    0    0 S    0  0.0   0:00.00 watchdog/0     6 root      RT  -5     0    0    0 S    0  0.0  17:27.31 migration/1     7 root      15  -5     0    0    0 S    0  0.0   0:01.21 ksoftirqd/1     8 root      RT  -5     0    0    0 S    0  0.0   0:00.00 watchdog/1     9 root      RT  -5     0    0    0 S    0  0.0  10:02.56 migration/2    10 root      15  -5     0    0    0 S    0  0.0   0:00.34 ksoftirqd/2    11 root      RT  -5     0    0    0 S    0  0.0   0:00.00 watchdog/2    12 root      RT  -5     0    0    0 S    0  0.0   4:29.53 migration/3    13 root      15  -5     0    0    0 S    0  0.0   0:00.34 ksoftirqd/3
13245
What are the correct settings for min-memory and max-memory in this use case?  The server has 8GB ram, dual Intel Xeon processors, running Windows Server 2008 R2 / Sql Server 2008 Standard Edition. It's running several databases ranging from 30GB - 5GB in size.  Originally the memory usage was set to the default settings (min=0 max=2,147,483,647). On these settings most of the memory usage was taken up by sqlservr.exe and the server would eventually need to be restarted every day or two. It would run normally at first but within a day start to timeout on simple operations like looking up a record using the primary key.   I have changed min=4,096 and max=6,144. This results in only 1.4GB memory usage. However now all four cpus are running at 50-60% cpu usage constantly. Tasks are taking roughly 1/3rd longer to execute, although the server is much more stable.
63444
I have received the email below that states that an alert has been fired and     a significant part of SQL Server process memory has been paged out   should I raise the Minimum server memory to 1740 too?     Microsoft SQL Server 2005 - 9.00.5000.00 (X64)    Dec 10 2010 10:38:40      Copyright (c) 1988-2005 Microsoft Corporation   Standard Edition   (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)     There are other applications, even monitoring applications running on that server.   You can have a glimpse of those applications by looking at the picture below.    We received an alert at 12:15:37 PM on the wrsleic1 default instance that "a significant part of SQL Server process memory has been paged out."  We've investigated the alert and found that SQL Server previously reported this condition last year on 13/04/2015. SQL Server is currently allocated a maximum memory of 1.7 GB while the server has 4 GB physical memory available to it (and a dynamically sized page file that is currently 5 GB).  At the time of the alert SQL Server had it's maximum memory reserved while not necessarily actively using the cached data. Therefore when the server detected memory pressures from an external process (potentially IIS but we can't determine this retrospectively), SQL Server trimmed its working set to accommodate the requirement and is currently utilising approximately 900 MB.  The OS is responsible for memory management and SQL Server is designed to respond to memory release requests as it did at the time of the alert. Therefore if there was no perceivable impact to performance on the application that uses the hosted databases on this instance then we don't recommend any further action.   Lock Pages in Memory   The lock pages in memory has not been enabled for any sql server account.    I was going to change this, however, I decided to leave this server as it is because:  1) it is running on standard edition sql server 2005 SP2 and       Standard Edition Up until SQL Server 2005 SP3 Cumulative Update   Package 4 and SQL Server 2008 SP1 Cumulative Update Package 2 'Lock   Pages in Memory' was not supported in SQL Server Standard Edition.   2) after enabling it - needs a reboot      Question:         “Weather we require Lock Pages in Memory in SQL   Server 64-Bit”?      Answer:            YES   3) We should (hope so) migrate this server to sql server 2014 soon    and a reminder:
118607
We're about to install a memory intensive application and will purchase a server for it.  The server will have at least 4 CPUs, with multiple cores. While the application is memory intensive, the consultants we're speaking with said that 32GB should be sufficient.  They also said that if I add more memory than that, the server will slow down because of the "extra wiring" to connect all of the memory.  I'm not planning on putting VMs on the hardware so I don't need extra memory to partition for virtual machines.  However I am concerned that 32GB may not be enough.  Does the consultant's assertion that more memory than I need will slow the machine down correct?
58119
Is it possible to put the entire operating system on a ram disk? If I have 16 GB of RAM and it is an Intel Xeon. I don't have an SSD and my hard drive is killing my boot time. So can I just allocate some of my ram to work as if it were an SSD and install windows and vital software on it, to gain super fast speed. Or is it more trouble then its worth and would be better off just buying an SSD?
6002
We currently have several shared SQL Server servers and instances in our organization. They are going with a different architecture where business units will get their own SQL Server instances. We have SQL Server 2008R2.  In the past, databases were put on servers in a more or less random fashion -- business use and disk space were the main drivers.  I've been tasked with coming up with a plan for allocating our 50+ databases across 3 servers (one instance per server). My criteria for divvying them up is as follows:   Business critical db's should be spread across the servers. i.e. avoid lumping a bunch of business critical db's all one server.   For the remainder of the non-mission critical db's, consider: a. Disk space usage b. I/O usage -- memory, CPU, etc.     1 and 2a are easy for me to figure out. I know how to get data file sizes.   My question is with that last bit - 2b. I do not know how relevant this is, what specific stats to look at, or how to get them.   Frankly, I'm not 100% sure how to phrase this question! I think I basically want to know is, "How 'busy' is this db -- however you define 'busy'?" Or maybe, "How does this db impact the performance of the server?"  I mean, disk space is important -- but it could (in theory) have a ton of rows or binary objects in there that are not written very often, so it has little impact on the performance of the server. I think I would want to know how much impact a db within a date range.  Is there a way to get these kind of general stats? I'm not looking for expensive queries, detailed user stats, or query optimization here. I'm aware of the SSMS reports, but they seem to be more real-time. The end goal is to divide up our current databases into evenly balanced buckets, and I would think performance is one way to do that.  Thoughts? Wisdom? Any other ideas on how to divvy up the db's?  Thanks! Tom
103032
I am looking for a solution to [dramatically] improve an enterprise database performance on a system that needs to support thousands of transactions (inserts &amp; large selects) per second. Besides the database/queries optimization, we are also looking for hardware improvements, too.  I need someone with experience in database administration that used SSD drives to store databases. What RAID configuration did you use? RAID1 looks like a good choice maybe combined with another redundancy-focused configuration, also SLC technology would be preferred for SSD drives I suppose.  The questions are: what type of SSD drives are you using (make, model)? How are they organized in RAIDs, what improvements did you achieve and how many (major) issues did you encounter? How much are you worried about the reliability of current SSD drives? Would you recommend using them for database (meaning critical) storage?  Also, what was the approximative price (at the time you implemented the solution, of course) of the implemented solution?  Maybe this post can also provide hystorical data about how SSD+database relationship has evolved over the time.  PS: If important, we are currently using SQL Server 2008 R2. Database size: over 400GB at this time, but my question is not only related to my specifics.  Thanks.  An interesting article found here:
91001
What I understand about 32-bit OS is, the address is expressed in 32 bits, so at most the OS could use 232 = 4G memory space -- I assume the unit is bytes, so 4GB.  Does this mean if any machine with a 32-bit OS (be it Windows or Unix) has more than 4GB total of RAM + page file on hard disk, for example 8GB RAM and 20GB page file, its memory will never be "used up"?  By "used up" I mean that increasing RAM or page file won't help the performance; of course, it's always possible an application will keep requesting memory from the OS but failing.  Similarly, if this 32-bit OS machine has 2GB RAM and 2GB page file, increasing the page file size won't help the performance. Is this true?
123154
With SQL Server 2005, you could look at the Task Manager and, at least, get a cursory look at how much memory is allocated to SQL Server.  With SQL Server 2008, the Working Set or Commit Size never really goes above 500 MB, even though the SQLServer:Memory Manager/Total Server Memory (KB) perf counter states 16,732,760.  Is there a setting where it will actually show the server memory in the Task Manager?  Or is it a result of them changing how memory is used in SQL Server
6126
Today we experienced a degradation in performance on our production sql server.  Durring the time this occurred we logged several "The query processor could not start the necessary thread resources for parallel query execution" errors.  The reading that I've done suggests that this has to do with how many CPUs to use when executing a complex query.  However when I checked during the outage our CPU Utilization was only at 7%.  Is there something else this could be referring too that I haven't come across yet?  Is this a likely culprit of the performance degradation or am I chasing a red herring?  My sp_configure values for this are as follows:  name                                minimum maximum config_value run_value cost threshold for parallelism      0       32767   5            5
35452
I thought that Windows 8 was supposed to be better with memory than Windows 7. So far, that is not my experience.  When I boot up my brand new laptop (Samsung NP550P5C-S03CA Intel Core i5 3210M 8GB 1TB ) , the Task Manager Performance tab tells me that I have:  8.0 Gb of DDR3 available  of this:  3.7GB is in use, leaving 4.2GB available  3.5/25.9 GB is committed and 1.4GB is cached.  162Mb paged pool, 2.4GB non-paged.  I then go to the Details tab, and I add up the Memory used by all the processes. It comes to about 300Mb. That is 0.3GB.  So what is using up the other 3.4GB of memory???  I then load up a bunch of programs that I use concurrently - notably Firefox (with about 30 tabs), Chrome, Thunderbird, Notepad++, Filezilla and my memory consumption goes up to 4.4GB. That seems about right.  I then work on it for a number of days, hibernating in-between, and I start to get notices that I am running low of memory. The performance tab says I am using 90% or more of my memory. The committed has gone up to 31GB. But if I add up the memory usage listed in the Details tab, it comes to only about 1800MB (I notice that Firefox has gone up to about 1300MB)  So, again, where has all my memory gone?  If I then close all the programs, it doesn't help. The details tab shows little running, yet the Performance tab shows I am still out of memory?  What is using up all my memory?  The only thing that clears it, is to do a restart. Which brings us back to the top of the loop, with the same initial question - what is using up over 4GB of my memory?  I never had these problems with windows 7.  As a sidenote, Win 8 also seems to be very unstable. Frequently getting programs not responding and crashing, including Windows Explorer, which seems to have to reload itself frequently.  Oh, and my wifi connection keeps dying and then I have to run the troubleshooter to get it all running again (my wife has that same problem on her brand new Acer).  So, what gives? How do I get my new laptop to work as fast as my 4 year old Sony Vaio with Win 7 and lower specs?  Anyone got any ideas? Or is this normal for windows 8?
19359
I thought Mavericks was suppose to utilize RAM memory better, thus run smoother.  Running ML on Mac mini 2012, with 16GB RAM...always running smoothly even with over a dozen apps running.  Now, after installing Mavericks, my Mac is SLOW, eating up RAM like crazy! Looking over usage, of the 16GB of installed memory...on average it uses 10-14 GB RAM when barely running a few apps!!!   There is this kernel_task that is always running with over 1GB of RAM and most apps are not taking that much RAM, so what is using up all my RAM?! File cache? It shows very high in that new description, with about 1/3 of my total RAM
59832
I am asking myself which constellation is better: should I stock up my RAM up to 16GB (from 8GB), or should I buy a SSD with 128GB??   I really dont know :/  Cheers!  BTW: My macbook supports 16GB RAM, i already checked this ;)
77837
Our production server (2012, VM + SAN) has 32 GB of RAM, the database size is ~80GB. The application uses TempDB heavily - disk is hit ~100 MBps both reads &amp; writes.  Seeing tons of SQL compilations/sec .. 95% of all batch requests are compilations.  Ideally would like to increase the RAM to 64GB or 128GB, but need to 'prove' to team that it's required.  Buffer Cache Hit Ratio (BCHR) is 99.9%, but Page Life Expectancy (PLE) is only ~400. What's the explanation for this ? I though PLE &amp; BCHR had a linear relationship (i.e. they increase or decrease together)  On other VMs with larger databases and lot more RAM, both BCHR &amp; PLE are high.  Current wait stats and perf counters