I put SQL Server 2012 Standard on a virtual machine (VMWare) which has roughly 98GB of Memory.  Now I checked the query below how much memory is taken by SQL Server.  Microsoft SQL Server 2012 - 11.0.5058.0 (X64)    And see that he ate already 81GB of the memory and the max Memory is set to ~90GB.  Using:  select (physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB,       (locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,  (total_virtual_address_space_kb/1024 ) VAS from sys. dm_os_process_memory   SQL Server 2012 Standard should support only 64GB of memory.  Why does my SQL Server eat more than he can? Or is the info incorrect?  Any idea? Is that related to a virtual machine using virtual memory?   I don't know if balloon driver is activated on VM Host...  If you need any more info I will add it.
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
15964
I would like your input on this. I have a sql server 2008r2 Ent. Ed. 64bit with 16 cores and 64GB RAM. There is one instance of SQL server patched fully as of 20111014.  The max ram is set to 60000MB. Amount of free ram is 0 according to task manager after a few days online.  If I change the max ram to below 53GB it will after a few days to stabilize and have some free ram.   It is the sql process that does allocate the ram according to task manager. How do i come to terms with what the problem really is? It goes without saying that i did alot of testing already but havn't solved this to my liking yet. and ohh we do not get the typical memory starvation lagging when the available ram is down to 0 free.  Update 1:  Inspired by another Q/A related to RAM on this page  . I used these two to see what the RAM is being used for.  SELECT TOP ( 10 )         [type] AS [Memory Clerk Type] ,         SUM(single_pages_kb) AS [SPA Mem, Kb] FROM    sys.dm_os_memory_clerks GROUP BY [type] ORDER BY SUM(single_pages_kb) DESC OPTION  ( RECOMPILE ) ;  SELECT  DB_NAME(database_id) AS [Database Name] ,         COUNT(*) * 8 / 1024.0 AS [Cached Size (MB)] FROM    sys.dm_os_buffer_descriptors --WHERE   database_id &gt; 4 -- system databases --        AND database_id &lt;&gt; 32767 -- ResourceDB GROUP BY DB_NAME(database_id) ORDER BY [Cached Size (MB)] DESC OPTION  ( RECOMPILE ) ;   The amount used shown by these are  first select 7948432    Kb second one 44030,57812  MB that is a total of about 52GB used by sql server... so where did the rest of my RAM go? :-) Task manager show right now cached 363, available 401, free 40 and sqlservr.exe has Memory private set 64 459 656. Max Ram set to 60000MB as before.
29384
As soon as I start SQL Server with no application querying, in the task manager it starts to consume memory very fast till it consumes around 15GB. How can I resolve this issue?
37570
So I am running on Windows 7 (64-bit) Home Premium with 12 GB RAM (Intel Core i7 920).  I have restricted the Page File to 400MB since it was eating up a lot of space om my SSD (that is 80GB).   After that I sometimes get the "low memory"-warning, like this, except for the behold-comment:   When I get those warning I have usually a lot of RAM available; when I check Resource Manager I have over 2GB "Free" and over 2GB "Available" - usually more than that.  The diskspace on all my drives have over 10GB free.  So the question is - why does Windows complain? It actually restarted by computer (hard boot) when it happened, and as I said - lots or RAM available.
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?
82270
I'm running a Dell Studio XPS, with quad core processor, two threads per core, with 12GB of installed RAM. According to Windows, only 7.99GB of that RAM is usable.  The really weird part is, up until two weeks ago I only had 8GB of RAM and then only 3.99GB was usable. So 4GB of RAM is consistently being taken.  I've checked in every screen, under every option in my BIOS and there doesn't seem to be a memory option there at all (that's a damn Dell for you). I've read that a lot of times this can occur because of the video card stealing some RAM for itself.   Is there some way I can check this, other than the BIOS? I have onboard graphics, not a separate card.  I'm running Windows 7 64-bit.
121095
I have a table with a varchar(max) column that stores text ~4-10KB in size. Data is just being added - never modified, it is a log. My problem is that a table grows too large in size - I would like to keep about 1000000 rows in this table.   I am limited to SQL Server Express and hit the limit on database size. Compression is not available in SQL Server Express and I afraid that Filestream may not be appropriate and the number of files would be too big.   What is the best solution in this case?
102099
First thank you for your time. We have a SQL Server database that was assigned to be replicated. the original size of the database was 5 GB for the datafile, not the log file. Now after the replication the data file is 4 times bigger around 19 GB. Any pointers to see what could happened will be appreciated. I know sql as developer and some little management but nothing about replication. The performance of the database has been damaged very bad so I am trying to see what need to be done to improve it. I do apologize for my English as well.
29444
I just upgraded my Windows 7 machine from 12GB to 24GB RAM - both for running more VMs and for future proofing.  My C drive is an SSD with 129GB formatted size.   I was surprised to find out that the SSD only has 68GB free (most of my files are on D: to G:). Researching I found 24GB of my precious C: SSD are taken up by the virtual memory pagefile in the root.  Do I need such large amounts of virtual memory when I have 24GB or RAM? I bought this size of memory so I'd not have to go to disk.
114705
I am trying to determine the size in bytes of the contents in a VARBINARY(MAX) field in SQL Server 2005, using SQL. As I doubt there is native support for this, could it be done using CLR integration? Any ideas would be greatly appreciated.
97902
I have an installation of SQL Server 2012 Std Ed on Windows 2012 R2 running in a default instance, and an app that wants to connect to it using a named instance (can't be changed)  E.g.   ServerName - mySQLServer Alias required by app - mySQLServer\CrazyApp   I've tried to create an alias as follows:     Alias Name - .\crazyapp Port No - 1433 Protocol - TCP/IP Server .   I've:   checked TCP/IP protocol is enabled have started SQL browser (not needed, I believe) restarted SQL (several times) tried with the machine name instead of "." tried with no port number instead of 1433. banged head against wall   ...all to no avail.  Is it possible to do this, if so, would some kind person tell me what I'm doing wrong.  Thanks!
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
81167
I have installed monitoring software on a few SQL Server instances in the environment. I am trying to find bottlenecks and fix some performance issues. I want to find out if some servers need more memory.  I am interested in one counter: page life expectancy. It looks different on every machine. Why does it change often on some instances and what does it mean?  Please take a look at the data from the last week gathered on a few different machines. What can you say about each instance?   Heavily used production instance (1):   Moderately used production istance (2)   Rarely used test instance (3)    Heavily used production instance (4)   Moderately used test instance (5)   Heavily used data warehouse (6)   EDIT: Im adding the output of      SELECT @@VERSION for all of these servers:  Instance 1: Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)  Jun 17 2011 00:54:03 Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 &lt;X64&gt; (Build 7601: Service Pack 1) (Hypervisor)   Instance 2: Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)  Oct 19 2012 13:38:57  Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 &lt;X64&gt; (Build 7601: Service Pack 1) (Hypervisor)   Instance 3: Microsoft SQL Server 2012 - 11.0.5058.0 (X64)  May 14 2014 18:34:29      Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows NT 6.1 &lt;X64&gt; (Build 7601: Service Pack 1) (Hypervisor)  Instance 4: Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30  Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 &lt;X64&gt; (Build 7601: Service Pack 1) (Hypervisor)   Instance 5: Microsoft SQL Server 2012 - 11.0.5058.0 (X64)  May 14 2014 18:34:29  Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows NT 6.1 &lt;X64&gt; (Build 7601: Service Pack 1) (Hypervisor)  Instance 6: Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)  Apr 2 2010 15:48:46  Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 &lt;X64&gt; (Build 7601: Service Pack 1) (Hypervisor)   I also ran the following query on the machines:  SELECT DISTINCT memory_node_id FROM sys.dm_os_memory_clerks   and it returned 2 or 3 rows for each server:  Instance 1: 0; 64; 1 Instance 2: 0; 64 Instance 3: 0; 64 Instance 4: 0; 64 Instance 5: 0; 64 Instance 6: 0; 64; 1   What does it mean? Do these servers run NUMA?
88544
I get error from System Center Operations Manager (SCOM).  How to solve this error?      SQL DB 2012 Engine Stolen Server Memory is too high.   I run query and get this counters:  ╔═══════════════════════════════╦═════════════╗ ║ Stolen Server Memory (MB)     ║ 7354.773437 ║ ║ Lock Memory (MB)              ║ 106.195312  ║ ║ Free Memory (MB)              ║ 64.632812   ║ ║ Connection Memory (MB)        ║ 24.203125   ║ ║ Log Pool Memory (MB)          ║ 14.085937   ║ ║ Optimizer Memory (MB)         ║ 2.351562    ║ ║ Granted Workspace Memory (MB) ║ 1.296875    ║ ║ Cursor memory usage           ║ 0.000000    ║ ║ Cursor memory usage           ║ 0.000000    ║ ║ Cursor memory usage           ║ 0.000000    ║ ╚═══════════════════════════════╩═════════════╝
119820
I have a memory optimised table in SQL Server 2016, with file durability enabled. The table is only 100MB. SQL Server is constantly creating new files on disk, which start off at a few hundred MB, but then grow constantly until the drive runs out of space then they clear down and the process starts again. The files are growing at about 900MB an hour. Running the SQL below shows that all the space is in Precreated Data - so it's not even actually storing large amounts of data, just allocating space to use later.  SELECT state_desc, file_type_desc, COUNT(*) AS [count],    SUM(file_size_in_bytes) / 1024 / 1024 AS [on-disk size MB]    FROM sys.dm_db_xtp_checkpoint_files   GROUP BY state, state_desc, file_type, file_type_desc   ORDER BY state, file_type     File growth on the file group is set to unlimited, so I'm assuming I can set a value on this and stop it growing, but I can't find anything to say why it's allocating so much space in the first place. The data in the table does change very rapidly (a lot of inserts and deletes as the data is constantly processed, but it the data that isn't Precreated never gets over 4GB.  Is there a setting in SQL Server that controls this file precreation? Is it safe to set a limit on the autogrowth for the file group?
48689
I have accidentally updated over 3,000 records in one of my tables hosted on Azure. This was not a transaction and I did not have any back ups for this table.  I've read around and found ApexSQL Log but when I connect I try to connect I see "SQL server Azure is not supported"  I understand that this tool examines logs to reproduce the data, is there a manual technique to do is, or an alternative compatible tool?  I also found this  but after running it on Azure I see 'Invalid object name 'fn_dblog'  Really grateful for you support.  Thanks.
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.