Tag Info

New answers tagged

0

I would focus on core points When we check high physical memory usage in the task manager it shows nearly 90 to 95 % full but I have checked all system processes which are taking nearly 2-3GB RAM memory. You are doing mistake which probably many newbies do when referring to memory utilized by SQL Server. SQL Server would never show you correct memory ...


0

You also can use below query to see what are the top memory consumers in your sql system ,plus also check are there any other applications present in Box other than sql. select (sum(pages_kb)*128)/1024 as 'sizein_mb',type as 'clerkttype' from sys.dm_os_memory_clerks group by type order by (sum(pages_kb)*128)/1024 desc Note,sql wont release memory ...


0

You should be sure to set a hard limit on the amount of memory that SQL Server is allowed to use. As others have mentioned, SQL Server will gradually use more and more memory when it is available. It's much easier to service read requests when SQL Server can read from high speed RAM instead of reading from slow spinning disks. If you open SSMS you can ...


2

AWE is not a "process", it is an API used for memory allocation. Quick googling shows that SQL Server may be using that API to allocate its bufferpool pages. It must be a quirk of RAMmap to show this under the AWE category. I think it is pretty normal for a database server to show high memory utilization ratios, otherwise unused memory would be simply ...


2

To answer your original question in title: Starting with 12c, one can easily limit both SGA and PGA. SGA could be limited in earlier releases also by sga_max_size or other parameters from which the database calculated sga_max_size. The problematic part was usually the PGA, with no simple limit value just some workarounds, but that can be now easily ...


4

SQL Server is configured to work this way. It assumes that if you need the memory once, you'll need it again. It's expensive to constantly give this memory back to the operating system and ask for it back again, so why give any of it back to the operating system? The easiest solution for now is to just restart the SQL Server service - this will return you ...


6

First thing is to change the max memory setting on your instance using either the GUI or sp_configure. EXEC sp_configure 'max server memory (MB)', 1024 Then go into SQL Server Configuration Manager and stop and re-start the instance. You don't actually have to restart your laptop. That will fix it so that your instance won't use much more than 1gb. I'm ...


1

The problem was that when the database was installed, conflicting other parameters were set. So I needed to export those from the "spfile" to a "pfile", back them up, edit them, test them and import from the pfile to the spfile: SQL> create pfile='some/file/path' from spfile; File created. Then I set the conflicting parameters to zero: ... ...


8

Would you still manually limit max RAM for MSSQL eventhough the sole purpose of that server is to serve MSSQL only? That is bit debatable topic considering you have SQL Server 2012 here. If you would have asked same question about SQL Server 2005/2008, which was on Windows Server 2003/2008, I would strongly suggest to set a max server memory limit due ...


2

SQL is NUMA aware so you can find NUMA info using a query. You can also use this query to see how many NUMA nodes you have and which CPU and cores are assigned to which NUMA: SELECT parent_node_id, scheduler_id, cpu_id FROM sys.dm_os_schedulers WITH (NOLOCK) WHERE [status] = N'VISIBLE ONLINE'; Or just how many NUMA: select COUNT(distinct Parent_node_id) ...


-1

Yes, you should limit the max memory for SQL Server even though the server has only one purpose. Otherwise SQL Server may start using memory Windows itself needs to operate well. It is usual to leave 1GB -> 20% of the total RAM to Windows, depending on your needs. The NUMA configuration of the server is displayed in Task Manager by selecting a process, ...


1

Thanks for all the help. I thought I would send an updated version of the above query that we found helpful. -- Search plan cache for Memory Grant issues IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp -- Collect more info about the plan here if required, eg usecounts, objtype etc, SELECT IDENTITY( INT, 1, 1 ) rowId, query_plan, db = ...


1

You are running into a known bug - There is a fix for it but update to SP2 rather than installing the hotfix. The errorlog contains SQL Server is terminating a system or background task HADR Task due to errors in starting up the task (setup state 1). and you are using availability groups. The memory leak is in the MEMORYCLERK_SQLLOGPOOL which is used for ...


0

CPU > 80% -- 80% of one core Memory > 90% -- good Any Swap -- that relates to what the previous answer discussed Disk IO > 90% Disk Full > 90% -- 90% minus the size of the largest table -- this allows room for ALTER and other maintenance slow log growing too fast etc For basic memory allocation, see my blog. Summary "If using just InnoDB, set ...


6

SQL SERVER 2005 SP3 Your best bet first is to upgrade it to a supported SP - First Download SP4 then Download CU3 --> Build NO: 9.00.5266. Support for SQL server 2005 is ending next year (2016/04/12) even for the latest SP and CU - so better upgrade to a 2012/2014. You can use my script to help you suggest a good MAXDOP value. Can anyone explain ...



Top 50 recent answers are included