MSSQLWIKI

Karthick P.K on SQL Server

Archive for the ‘Memory’ Category

False warning “A significant part of sql server process memory has been paged out”

Posted by Karthick P.K on June 13, 2013

In A significant part of SQL Server process memory has been paged out we discussed about SQL Server working set trim warning, when they can occur and how to troubleshoot them.

I the same blog I mentioned SQL Server will log “A significant part of sql server process memory has been paged out” warning when working set reaches 50% or below of the overall committed memory by SQL Server memory manager. In this blog I will try to cover when this warning could be a false warning and how to identify it.

Let us recollect what is committed memory and different states of committed bytes in windows.

Committed: Total memory that is allocated by process (allocated bytes can be in RAM or Page file)

Committed working set: Committed memory which is currently in RAM

Committed Paged : Committed memory which is currently page file

Committed Mapped : Committed mapped to page file.

Committed untouched: committed memory which is never accessed (When a page is committed in windows it will never become working set unless accessed).

Let us understand what Committed untouched is. Download Memoryallocator exe from This link and Keep committing memory using the same exe.

You will notice that the committed memory of the Memoryallocator process increases, but the physical memory usage (RAM usage or Working set) (or) Page file usage will not increase at all. Only the committed memory of the process and committed memory of overall system increases.

Why?

When a page is committed in windows it will not become part of working set or page file unless it is accessed.

Similarly when SQL Server estimates the memory requirements of different clerks and allocates them during startup or on need. These allocated memory is part of committed memory but will not have a page in RAM or Page file unless accessed for the first time.

So during this condition SQL Server’s working set can go far below the committed bytes and once working set reaches 50% or below of overall committed bytes then ““A significant part of sql server process memory has been paged out” warning is logged in SQL Server errorlog.

How do you identify if this warnings are false warnings?

We can identify if these warnings are false using the SQL Server memory dump or using the Perfmon counters.

Let us stimulate a false warning situation using the below backup query and see how to identify if the warning is false.

Run the below query in your test system.

Note: If you do not get the warning message increase the buffer count in below query. If you get “There is insufficient system memory in resource pool” then reduce the buffer count.

WARNING: dumptrigger  and below trace flag’s are undocumented and should be used only in test environments with caution  (or) under Microsoft Support supervision. There is no guarantee that they will work in future versions of SQL Server.

DBCC TRACEON(8026,-1) --Trace flag –T8026 tells dump trigger to remove the trigger after the first dump has been triggered.
go
DBCC DUMPTRIGGER('SET',17890)
go
BACKUP DATABASE MSDB TO DISK = N'msdb.BAK' WITH NOFORMAT, INIT,NAME = N'msdb', SKIP, NOREWIND, NOUNLOAD, STATS = 1 ,BUFFERCOUNT = 10000,BLOCKSIZE = 65536 ,MAXTRANSFERSIZE=2097152

Once you run the above backup command you will see   error: 17890 “A significant part of sql server process memory has been paged out” and a mini memory dump will be created in Errrorlog  folder along with SQLDump00nn.txt

Using memory dump

Open the SQLDump00nn.txt and review the memory section in SQLDump00nn.txt. This will give you the system memory information when the error occurred.

Snippet from my SQLDump00nn.txt.

{

Memory                              

MemoryLoad = 26%                    

Total Physical = 131067 MB          

Available Physical = 96691 MB       

Total Page File = 393201 MB         

Available Page File = 334217 MB     

Total Virtual = 8388607 MB          

Available Virtual = 8166328 MB      

**Dump thread – spid = 0, EC = 0x00000020F19C2B90                                                               

***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL11.RBS\MSSQL\LOG\SQLDump0048.txt    

}

In the above output  “Available Physical  is  96,691 MB” which indicates there is no physical memory pressure when SQL Server raised  17890 warning so widows is not trimming the working set and obviously we can come to conclusion that this instance of warning is false warning.

Note: Above method may not work well in earlier versions of windows in which working set of all processes are hard trimmed when there memory pressure in the system.

Using perfmon counters

In below perfmon graph I have collected three counters

1. Process\SQLServr\Working set (highlighted Black)

2. Process\SQLServr\Private bytes (Committed memory. Green line)

3. Memory\AvailableMbytes  (Red line)

I you review your SQL Server error log you would notice 17890 warning at the same time when Private bytes (Green line) spiked.

How to conclude that the warning is printed because of “untouched committed pages”  by SQL Server.

In general when a page is committed and accessed it will be part of working set as long as there is enough available memory on the system. If you look at below graph you will notice that the private bytes (committed) is increasing but the working set is not increasing at same phase though there is adequate available memory in the system. This can happen only when the pages are committed and not accessed (If you look at below graph carefully committed memory increased and dropped with in 10 seconds so when you configure Perfmon choose sample rate every 1 second else perfmon might miss the data and you will find some thing like this happened). 

clip_image002

 

Related posts

Troubleshooting steps for all SQL Server Memory errors

Trouble shooting working set trim “A significant part of SQL Server process memory has been paged out”

SQL Server lock pages in memory should I use it?

SQL Server memory leak

What is new in SQL Server 2012 Memory

How to set max server memory and min server memory

If you liked this post, do like us on Facebook at https://www.facebook.com/mssqlwiki and join our Facebook group

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

Disclaimer:

The views expressed on this website/blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights

 

Posted in Memory, SQL Server Engine, SQL Server memory | Tagged: , , , , | 5 Comments »

Max server memory – Do I need to configure?

Posted by Karthick P.K on April 22, 2013

Do I need to configure Max server memory and min server memory?  What is the right value for  this configuration and how to determine it?

There are many debates around this and above questions are raised by many SQL Server DBA’s frequently in many forums. If you ask me , “It depends” on various factors.

Before we choose to configure or leave this value to default it is very important to understand how SQL Server grow and shrink its memory usage based on the available memory in operating system even when Max server memory is not configured or defaulted.

How SQL Server grow and shrink its memory usage based on the available memory in operating system even when Maximum server memory is not configured or defaulted?

 SQL Server memory management is designed to dynamically adjust its memory usage based on the amount of available memory on the system. SQL Server will keep allocating memory based on its need as long as there is memory available I.e. as long as MEMPHYSICAL_HIGH (HighMemoryResourceNotification )notification is signaled in widows  and will scale down its usage when there is MEMPHYSICAL_LOW (LowMemoryResourceNotification) signaled in windows. When available memory is between the low memory and high memory SQL Server will try to maintain the memory usage stable( RESOURCE_MEM_STEADY) with some exceptions.

You can download the ResourceNotificationHighandLow.exe from This link to see memory notifications from windows.

The default level of available memory that signals a LowMemoryResourceNotification event is approximately 32 MB per 4 GB, to a maximum of 64 MB. (By default, the threshold is 64mb on most systems).

The default level that signals a high-memory-resource notification event is three times the default low-memory value (By default, the threshold is 64*3=192 MB on most systems).

Key points:

1. Once the available memory on the system goes below 192 MB HighMemoryResourceNotification (MEMPHYSICAL_HIGH) signal is revoked by windows and SQL Server will not grow its Bpool.

2. Once the available memory on the system goes below 64 MB LowMemoryResourceNotification (MEMPHYSICAL_low) is signaled by windows and SQL Server will shrink its Bpool (reduce its memory usage).

3. When the available memory in the system is between 192Mb and 64 Mb (I.e between LowMemoryThreshold and HighMemoryThreshold) SQL Server will not grow or shrink its usage (With some exceptions which we will see in a while)

Note: So unless there is an crazy application in the system that keeps allocating and releasing memory in Zigzag fashion making  windows trigger HighMemoryResourceNotification and LowMemoryResourceNotification one after the other SQL Server will not grow and shrink its memory usage in Loop continuously. If there are such application in system then even configuring max server memory may not help.

The default Low memory threshold 64MB may not be ideal for all systems.  Ex: Let as assume an application is requesting 150MB of memory suddenly when the available memory is 190 MB and the grant is successful. Available memory will now drop to 40 MB making windows signal the LowMemoryResourceNotification. SQL Server will start responding to the LowMemoryResourceNotification from windows but at the same time windows working set manager will also start trimming the working set of all the processes. Which will bring down the overall performance of the system.

We can increase the LowMemoryThreshold value by making the following registry changes If LowMemoryThreshold set to higher value  OS will notify applications such as SQL on low memory conditions much earlier and SQL Server can respond to memory pressure much early before the system starves for memory and before windows working set manger starts trimming the working set of all the processes.

In Regedit -> go to

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\SessionManager\MemoryManagement

Right click on the right pane,

Select New -> select click DWORD Value -> enter LowMemoryThreshold

Double Click LowMemoryThreshold -> value (choose decimal) -> 512

System Reboot is required to take effect.

In the above example I have set the LowMemoryThreshold to 512 MB hence the MEMPHYSICAL_LOW notification will be signaled as soon as the available memory drops to 512MB and HighMemoryResourceNotification (MEMPHYSICAL_HIGH) will be in signaled state till the available memory is  1536MB (LowMemoryThreshold *3).

After making the above change SQL Server will grow its Bpool memory till the available memory in the system is greater than 1536 MB and as soon as the available memory drops below 1536MB HighMemoryResourceNotification signal will be revoked by windows causing SQL Server to maintain steady state and will not grow its memory usage further but that  doesn’t  mean SQL Server will wait for the LowMemoryResourceNotification notification to scale down its memory usage after the  HighMemoryResourceNotification notification is revoked. SQL Server will always try to keep the available physical memory in the system high (I.e. SQL Server will try to keep the available memory in system to  HighMemoryThreshold (LowMemoryThreshold  * 3 ).

What if I have multiple instances of SQL Server on same server and how they load balance the memory among themselves?

SQL Server will try to balance to balance its memory usage with other instances of SQL Server running on the same box .  As I mentioned earlier SQL Server will try to maintain the available memory on the system to High memory threshold. SQL Server Lazy writer checks If there is disk reads performed in last 10 seconds and if there is no reads for last 10 seconds then SQL Server will reduce its memory usage until HighMemoryResourceNotification is signaled by OS.

Let us see this with an example :

Let us assume there are 2 SQL Server instances running on server with 32 GB of RAM and Lowmemorythreshold is set to 512MB on the system (so HighMemoryThreshold is 1536 (Lowmemorythreshold *3)).

1.            When the OS starts  HighMemoryResourceNotification  is set to on because there is adequate available memory on the server.

2.            SQL Server instance 1 starts first and it will consume memory till the HighMemoryResourceNotification resource notification is revoked ( HighMemoryResourceNotification  will be revoked when available memory drops below 1536 MB).

2.            Now the 2nd SQL Server instance is started and it finds High memory resource notification is revoked so it will not increase its memory usage. 

3.            Lazy writer thread of 1st instance checks if there is any disk reads performed by 1st Instance  in last 10 seconds , If there is no disk reads then first instance will scale down its usage until HighMemoryResourceNotification is signaled by OS (HighMemoryResourceNotification  will be signaled again when available memory becomes 1536 MB).

4.            2nd Instance which is hungry for memory sees the High memory resource notification and starts growing its usage till the high memory notification is revoked.  Once the high memory notification is revoked 2nd instance will stop growing.

5.            1st  instance finds the high memory notification is revoked and will again check if there are any disk reads in last 10 seconds and if there are no reads then It will further scale down till there is high memory resource notification.

6.            Once the high memory is signaled 2nd instance will start growing again.

7.            Over a time each instance will very well balance their memory requirements among themselves. ( I.e. if there is read performed from disk with in last 10 seconds we assume there is additional memory requirement for the instance so it will not scale down while on other hand if there is no reads for more than 10 seconds and if the memory available is below the high memory threshold instance it will scale down to give memory for other instance)  

8.            Instance with higher memory requirement will be consuming more memory than the instance with low memory requirement in some time. This way both the instances will balance their memory requirements with each other.

Note:

1. Above logic may not fit well if the total Physical memory on the system is very low compared with the memory requirements of multiple SQL Server instances running on the system because if you start the second instance while the first SQL Server is running with full memory utilization but still performing lot of reads I.e. RESOURCE_MEM_STEADY and still lot of reads , second instance may take long time or may not scale up its memory usage soon. In such case you can cap the max server memory but the performance of SQL Server will be very poor because of memory contention.

2. Also be cautious when you increase the value of LowMemoryThreshold beyond 512 MB.  Increasing this threshold  increases the range of memory that is available where neither the LowMemoryResourceNotification or HighMemoryResourceNotification object is signaled ( RESOURCE_MEM_STEADY).  So when you have multiple instance , if you start the second instance while the first SQL Server is running with full memory utilization and with lot of reads  I.e. RESOURCE_MEM_STEADY + reads continuously , second instance may take time scale up its memory usage soon and chances of getting Lowmemorythreshold is low because of wider range  of  RESOURCE_MEM_STEADY

FAQ:

1. What will happen when MTL allocations increases?

Available memory in system drops when the MTL consumption increases. If the MEMPHYSICAL_HIGH is set then there will not be any effect to bPool. If MTL consumption increases drastically it might cause available memory to drop further causing windows to trigger LowMemoryResourceNotification.

If LowMemoryThreshold is siganled SQL Server will scale down its bPool usage.

2. Will windows working set manager starts trimming the working set of all processes as soon as the LowMemoryResourceNotification is signaled?

No.

3. What are the other effects of changing LowMemoryThreshold?

There might be other application and drivers which is also using memory notification from windows to grow and shrink memory usage. They will also shrink and grow when there is notification from windows.

4. Why would I need to CAP my SQL Server memory when we have a great dynamic mechanism in SQL Server to grow and shrink its memory usage?

You can leave the max server memory as default  If your operating system is Windows 2008 or above and if you have all the fixes in This link and This link and if you do not have any faulty drivers or applications which will request large amount of memory suddenly and if you are not using large pages memory model else I would suggest capping the Max server memory

If you have decided to configure the Max server memory remember it will not control the overall memory used by SQL Server. There are significant changes in memory allocations controlled by Max server Memory between SQL Server2012 and earlier versions. Let us understand what allocations it controls in SQL Server 2012 and earlier versions of SQL Server

What is controlled by SQL Server Max Server Memory (Extract from SQLServer2012 Memory) ?

SQL Server memory is internally divided in to two regions known as BPOOL and NonBPool (aka MTL or MTR) More details about BPOOL and MTL can be found in This blog.

In earlier versions of SQL Server (Till 2008 R2) “Max Server Memory” controlled the Maximum physical memory Single page allocator (BPOOL)  can consume in SQL Server user address space.

Only the single page allocator was part of BPOOL and Max server memory controlled only BPOOL, so the following allocations came outside BPOOL (Max server memory)

1.Multi-Page allocations from SQL Server [These are allocations which request more > 8 KB and required contiguous memory]

2.CLR allocations [These include the SQL CLR heaps and its global allocations created during startup]

3.Memory used for thread stacks within SQL Server process (Max worker threads * thread stack size). Thread stack size is 512K in 32 bit SQL Server, 904 K in WOW mode and 2 MB in 64-Bit 

4.Direct windows allocations made by Non-SQL Server dll’s ([These include windows heap usage and direct virtual allocations made by modules loaded into SQL Server process. Examples: allocations from extended stored procedure dll’s, objects created using OLE Automation procedures (sp_OA calls), allocations from linked server providers loaded in sqlserver process)

SQL Server 2012 memory manager has now clubbed single page allocator and multipage allocator together  as any-size page allocator . As a result, the any-size page allocator now manages allocations categorized in the past as single page and Multi-Page allocations.

1. "max server memory" now controls and includes “Multi pages allocations”.

2. In earlier versions of SQL Server CLR allocated memory was outside BPOOL (Max server memory)   . SQL Server 2012 includes SQL CLR allocated memory in "max server memory".

SQL Server 2012 "max server memory" configuration does not include only the following allocations:

1. Memory allocations for thread stacks within SQL Server process

2. Memory allocation requests made directly to Windows [Ex: Allocations (Heap, Virtualalloc calls ) from 3rd party Dll’s loaded in SQL Server process , objects created using OLE Automation procedures (sp_oa) etc]

Hope you got clarity on allocations controlled by  Max server memory , Let us see how to set it.

How to set correct value for SQL Server Max server memory?

There is no magic formula for this. Estimate the memory required by other applications running on same server, Operating system, Drivers , SQL Server Non- bPool allocations, jobs, anti virus etc.. Make sure you have acceptable available physical memory even when the system is under heavy load.

1. Consider the operating system memory requirement.

     Approximately 1 GB (Would increase if it is DC, cluster etc.)

2. Consider the memory requirements by other applications/processes running on the server.

   You have to derive it based on applications/processes/AV’s running on the system and their memory requirements. (Perfmon Process-> Private bytes and Working set can help)

3. Consider the memory requirements of the drivers/firmwares.

    You have to derive it based on memory requirements by drivers installed on the system. (RAMMAP can help)

4. Consider the NonbPool (aka MTL or MTR) memory requirements by SQL Server.

select  sum(multi_pages_kb)/1024 as multi_pages_mb from sys.dm_os_memory_clerks

(You can skip above query if your SQL Server version is 2012)

+

Max worker threads * 2MB

+

Memory for direct Windows allocations approximately 0 to 300 MB in most of the cases but you may have to increase it if there are many 3 party components loaded in SQL Server process (Including linked server dll’s, 3rd party backup dll’s etc.)

+

If you are using CLR extensively add some additional memory for CLR.   

5. Consider the memory requirement by jobs (Including replication agents, Log shipping etc. )  and packages that will run on the server.

You have to derive (May vary from few Mb’s to GB’s)

6. Consider SSAS and RS memory requirements.

You have to derive

7. Make sure there is good enough free space for operating system.

Approximately (100 MB for each GB till 4G) + (50 MB for each additional GB till 12GB) + (25 MB for each additional GB till your RAM size)

8. Other memory requirements.

If you have any other memory requirement specific to your environment.

Once you have calculated a reasonable value for all the above memory requirements  take the sum of all the above requirements and deduct it with total physical memory to derive an ideal value for your max server memory.

Max server memory=  Total physical memory  – (1+2+3+4+5+6+7+8)

If you still see LowMemoryResourceNotification  or working set below 100% frequently then use This exe which will print the memory information of all the processes and system wide memory information (Global memory status) when the operating system signals  low memory notification. Once you get the output from the exe when there is LowMemoryResourceNotification  review requirements of each process and tweak Max server Memory accordingly.

Important: Make sure you have this fix if you are on windows2003 http://support.microsoft.com/kb/938486

What about Min server memory and should I configure it?

I mentioned earlier that when LowMemoryResourceNotification comes from Windows or HighMemoryResourceNotification is revoked+No reads for 10 seconds , SQL Server scales down its memory usage.

How much it scales down?

Until “Minimum server memory”  is reached (If there is continuous memory pressure on the system).

What happens when you set Max server memory and min server memory to same value?

SQL Server will never scale down its memory usage even when there is memory pressure system wide (Lowphysicalmemory notification  set at system level). Note: This setting does not affect OS from paging.

What are the affects?

When there is LowMemoryResourceNotification  If LPM is not enabled SQL Server’s working set (Bpool + Non bPool )will be paged. If LPM is enabled system will starve for memory  and non-bpool will be paged.

If you do not want SQL Server to scale down its usage when there is LowMemoryResourceNotification  in windows configure Min server memory and Max server memory to same value (Bad choice). 

If you want to limit  “how much SQL Server wants to scale down“ you can configure this value.

If you liked this post, do like us on Facebook at https://www.facebook.com/mssqlwiki and join our Facebook group

 

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

Disclaimer:

The views expressed on this website/blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights

 

Posted in Memory, Performance, SQL Server Engine, SQL Server memory | Tagged: , , , , , | 36 Comments »

SQL Server lock pages in memory

Posted by Karthick P.K on March 26, 2013

Lock pages in memory is again a black box for many SQL Server DBA’s. Configuration choice to enable lock pages in memory depends on various aspects.

Before we get in to internals we will recollect some of the basics of SQL Server lock pages in memory in 64-Bit SQL Servers.

What is Locked pages in memory in windows?

Its user right for windows account and can be enabled by using secpol.msc or gpedit.msc

Why do we need this privilege for SQL Server startup account?

There are 3 different memory models in 64-bit SQL Server. They are conventional ,locked pages and large pages memory model.

Locked pages memory model: In lock pages memory mode SQL Server uses allocateuserphysicalpages and mapuserphysicalpages function to allocate memory. Caller token of this function should have LPIM privilege else the function call would fail, hence you need LPIM for startup account of SQL Server  to use lock pages memory mode.

Large pages memory model: In large pages memory model I.e When you use TF834 in enterprise edition on systems with physical memory  >8GB  SQL Server uses large pages memory model. In this memory model SQL Server uses vitualalloc API with MEM_LARGE_PAGES allocation type. For using MEM_LARGE_PAGES in virtualalloc caller token must have LPIM privilege.

Memory allocated using AWE allocator API’s (or) Virtualalloc function with MEM_LARGE_PAGES are not part of Process working set ,hence cannot be paged out and not visible in private bytes or working set in task manger and Perfmon. process. Private bytes (or) Perfmon.process. working set.

What is the Advantage of using Lockedpages or Largepages? SQL Server working set (BPOOL) cannot be paged by windows  even when there is system wide memory pressure.

Disadvantage: Operating system will starve for memory when there is system wide memory pressure. OS has to completely rely on SQL Server to respond to low memory notification and scale down its memory usage . SQL Server may not respond fast enough to low memory condition at system level because OS is already starving for memory. LPIM prevents only the BPOOL from paging, Non-Bpool components can still be paged and we have critical portions of SQL Server memory like thread stack, SQL Server Images/DLL’s  in Non-Bpool which can still be paged by OS.

So many disadvantage…. But still why do we recommend LPIM in some places?

In earlier versions of windows 2003 (If This fix is not applied) when there is system wide memory pressure windows memory manger would trim one-quarter of working set of all the process. Imagine If SQL Server is using 200GB of RAM and there is system wide memory pressure, Windows memory manager would move 50 GB of SQL Server working set to page file and we would end with performance problems. If LPIM is enabled OS cannot trim. Imagine there is a faulty application/drivers in the server and it leaks memory  fast , It might consume all the memory in the server and windows memory manager might trim all of SQL Server working set.

Known issues in windows like the one in This and few in windows 2008 mentioned in  This  link can cause windows memory manager to trim the working set of SQL Server process suddenly. Windows has a background process which keeps  writing the contents of working set to page file, so when there is paging only the dirty pages  needs to be moved to the page file others are already backed by back ground process, So paging would be very fast and SQL Server working set would be moved to page file in seconds before SQL Server responds to low memory resource notification from OS causing negative performance. In systems with large amount of memory (Ex: 1 TB )we might get non yielding scheduler situations when allocating memory in conventional memory model. LPIM is only option is this case. LPIM can be used in servers in which it might take long time to identify the cause of the working set trim. It is always suggested to identify the cause of TRIM before choosing LPIM in first place. You can use the steps mentioned in This link to troubleshoot working set trims.

Note:

1. Local system account has LPIM privilege by default, so if you are using local system as startup account of SQL Server then SQL Server might be using lock pages memory model by default with out your knowledge.

2. In earlier versions of SQL Server (Till 2008R2) you need TF845 with fix in KB970070 in standard and BI edition to make use of lock pages memory model.

 

If you liked this post do like us on Facebook at https://www.facebook.com/mssqlwiki and join our Facebook group MSSQLWIKI

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

Disclaimer
The views expressed on this website/blog are mine alone and do not reflect the views of my company. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Posted in Memory, SQL Server Engine, SQL Server memory | Tagged: , , | 7 Comments »

SQL Server -g

Posted by Karthick P.K on March 5, 2013

I decide to write this quick blog after seeing a lot of confusion around sqlserver -g switch.  sql server -g switch is nop in 64-Bit sqlserver and it is used in 32-bit sqlserver to increase the size of MTL(AKA MemToLeave).

The default value of -g switch is 256 MB. I.e. if you do not specify value for sqlserver -g switch it is defaulted to 256 MB.

Initialization of sqlserver memory during the startup of SQL Server is as follows.

1. Calculate the size of MemtoLeave and reserve it using the algorithm below

MTL (Memory to Leave)= (Stack size * max worker threads) + Additional space (By default 256 MB and can be controlled by -g).

Stack size =512 KB per thread for 32 Bit SQL Server and 904 KB for 32Bit SQL Server running on 64-Bit systems.

I.e = (256 *512 KB) + 256MB =384MB

-g switch is used to increase the additional space from 256 to any desired value.

2. Calculate the size of BPOOL using below algorithm.

SQL Server Buffer Pool is minimum of “Physical RAM “ or “user mode memory(2GB or 3GB) – MTL- BUF structures”

BPool = Minimum (Physical memory, User address space – MTL) – BUF structures

Buf structures are arrays maintained by sqlserver to track the status of each buffer in BPOOL . SQL Server makes maximum of 32 allocation requests to the OS to reserve bpool pages.

SQL Server maintains contiguous array to track status information associated with each buffer (8 KB page) in the BPool. In addition SQL Server maintains a second array to track the committed and reserved bitmap pages.

This bit can be 0 or 1 . 1 indicates buffer is committed and 0 indicated page is reserved.

Size of Buf structure is approximately 16 MB when AWE is not enabled and when AWE is enabled buf structures use additional 8MB per each GB of RAM in the system.

3. Release the MTL region which is reserved initially. We reserve MTL at startup and releases it after BPOOL is reserved to ensure MemToLeave region are contiguous.

More details about the SQL Server memory architecture in https://mssqlwiki.com/sqlwiki/sql-performance/basics-of-sql-server-memory-architecture/

If you are in SQL Server 2012 read https://mssqlwiki.com/2012/10/21/sql-server-2012-memory-2/

 

Related posts:

Troubleshooting SQL Server Memory

A significant part of SQL Server process memory has been paged out

 

If you liked this post, do like us on Facebook at https://www.facebook.com/mssqlwiki and join our Facebook group

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

Disclaimer:

The views expressed on this website/blog are mine alone and do not reflect the views of my company. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Posted in Memory, SQL Server Engine, SQL Server memory | Tagged: , , , , | 14 Comments »

SQL Server fails to start with error "Failed allocate pages: FAIL_PAGE_ALLOCATION 1" During startup

Posted by Karthick P.K on January 6, 2013

SQL Server fails to start and If you look at the SQL Server Error log you will find "Failed allocate pages: FAIL_PAGE_ALLOCATION" and SQL Server generating exception dump. Similar to the SQL Server error log below.

 

Note: This blog is applicable when you out get of memory error during startup (or) with event ID: 2019 in system event log. For general troubleshooting of SQL Server out of memory errors follow steps in Troubleshooting SQLServer Memory

{

2013-01-02 12:31:20.91 Server      Microsoft SQL Server 2008 R2 (SP1) – 10.50.2500.0 (Intel X86)

                Jun 17 2011 00:57:23

                Copyright (c) Microsoft Corporation

                Enterprise Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)

2013-01-02 12:31:20.91 Server      (c) Microsoft Corporation.

2013-01-02 12:31:20.91 Server      All rights reserved.

2013-01-02 12:31:20.91 Server      Server process ID is 1583.

2013-01-02 12:31:20.91 Server      Authentication mode is MIXED.

2013-01-02 12:31:20.91 Server      Logging SQL Server messages in file ‘C:\Microsoft SQL Server\MSSQL10_50.MSSQLWIKIServer\MSSQL\Log\ERRORLOG’.

2013-01-02 12:31:20.91 Server      This instance of SQL Server last reported using a process ID of 9240 at 1/3/2013 7:31:20 PM (local) 1/4/2013 12:31:20 AM (UTC). This is an informational message only; no user action is required.

2013-01-02 12:31:20.91 Server      Registry startup parameters:

                 -d C:\Microsoft SQL Server\MSSQL10_50.MSSQLWIKIServer\MSSQL\DATA\master.mdf

                -e C:\Microsoft SQL Server\MSSQL10_50.MSSQLWIKIServer\MSSQL\Log\ERRORLOG

                -l C:\Microsoft SQL Server\MSSQL10_50.MSSQLWIKIServer\MSSQL\DATA\mastlog.ldf

2013-01-02 12:31:20.92 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.

2013-01-02 12:31:20.92 Server      Detected 24 CPUs. This is an informational message; no user action is required.

2013-01-02 12:31:20.94 Server      Address Windowing Extensions is enabled. This is an informational message only; no user action is required.

2013-01-02 12:31:27.33 Server       Failed allocate pages: FAIL_PAGE_ALLOCATION 1

2013-01-02 12:31:27.33 Server     

Memory Manager                                   KB

—————————————- ———-

VM Reserved                                 1534584

VM Committed                                  51576

AWE Allocated                                     0

Reserved Memory                                1024

Reserved Memory In Use                            0

2013-01-02 12:31:27.33 Server      Error: 17311, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.

2013-01-02 12:31:27.33 Server      Using ‘dbghelp.dll’ version ‘4.0.5’

2013-01-02 12:31:27.34 Server      **Dump thread – spid = 0, EC = 0x00000000

2013-01-02 12:31:27.34 Server      ***Stack Dump being sent to C:\Microsoft SQL Server\MSSQL10_50.MSSQLWIKIServer\MSSQL\LOG\SQLDump0008.txt

2013-01-02 12:31:27.34 Server      * *******************************************************************************

2013-01-02 12:31:27.34 Server      *

2013-01-02 12:31:27.34 Server      * BEGIN STACK DUMP:

2013-01-02 12:31:27.34 Server      *   01/03/13 19:31:27 spid 4344

2013-01-02 12:31:27.34 Server      *

2013-01-02 12:31:27.34 Server      * ex_handle_except encountered exception C0000005 – Server terminating

 

}

 

 

Why would SQL Server fail with out of memory error (FAIL_PAGE_ALLOCATION)during the startup? Only possible reason that I could think of is Paged or NonPaged pool is empty.

How to prove if my Paged / NonPaged pool is empty?  Look at the system event log for the Event ID: 2019

 

You will find error in system event log similar to one you see below.

{

Event Type:        Error

Event Source:    Srv

Event Category:                None

Event ID:              2019

Date:                     2013-01-02

Time:                     12:31:00 PM

User:                     N/A

Computer:          MSSQLWIKIServer

Description:

The server was unable to allocate from the system nonpaged pool because the pool was empty.

}

 

Above error indicates nonpaged pool is empty, When Nonpaged pool is empty every application would fail. How to identify who is consuming Nonpaged pool?

 

Use poolmon.exe from windows support tools. (Steps are documented in This KB).

If you r OS is windows 2003 or above you can simple run the exe from command prompt and identify who is consuming (Leaking J) space in Paged / NonPaged pool.

Below is sample output of poolmon.exe which I collected from my test system

 

clip_image002[4]

 

Memory consumption by each tag is printed in above output. After finding the tag which is leaking the memory (Highest bytes)identify the Driver which is using the tag by using  find command or strings utility from sysinternals (search for TAG in drivers folder %Systemroot%\System32\Drivers). Once you identify the driver, check if there are any known issue with the driver or you may have to contact the vendor of the driver to identify why the driver is consuming large amount of pooled /Non-pooled memory.

 

If you liked this post, do like us on Facebook at https://www.facebook.com/mssqlwiki , join our Facebook group MSSQLWIKI and post your SQL Server questions to get answered by experts.

 

 

Thank you,

Karthick P.K |

 My Facebook Page |My Site| Blog space| Twitter

 

Disclaimer

The views expressed on this website/blog are mine alone and do not reflect the views of my company. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Posted in Configuration, Memory, SQL Server Engine, SQL Server memory, Startup failures | Tagged: , , , , | 16 Comments »

Debugging memory Leaks using Debug diagnostic tool.

Posted by Karthick P.K on December 6, 2012

In my previous post (SQL Server memory leak ) I explained how to identify the modules  which are leaking the memory using ‘!heap’ commands.  Sometimes we may not be able to find the cause by displaying the memory using ‘!d’ commands to find the patterns or using search memory commands (s).

In such scenarios  we can use  Debug Diagnostic Tool or UMDH to track memory leaks.   This blog will explain how to identify the memory leaks using Debug diagnostics tools.

 

Download and install Debug Diagnostic Tool from http://www.microsoft.com/en-us/download/details.aspx?id=26798

 

1. Go to ToolsàOptions ->Preferences àSelect Record call stacks immediately when monitoring the leaks.

 

clip_image002

 

2. Go to the rules tab and select add rule.

 

3. Choose Native (non .Net) memory leak and handle leak.

 

4. Select the SQL Server or any process which has to be tracked for memory leak.

 

5. Click next and leave the default options (you can choose auto-unload Leak track when rule is completed or deactivated).

 

6. Click next and Activate the rule now.

 

7. Leaktrack.dll would have loaded to the process for which we are tracking the allocations.

 

8.  Now you can wait for the leak to happen again.

 

{

–If you are learning how to troubleshoot SQL Server memory leak follow the steps which we followed in previous post (https://mssqlwiki.com/2012/12/04/sql-server-memory-leak/)to leak the memory.

 

–Download HeapLeak.dll from this link.

–Create  an extended stored procedure in SQL Server

sp_addextendedproc ‘HeapLeak’,‘C:\HeapLeakdll\HeapLeak.dll’

–Let us execute this Extended SP 30 times and leak memory.

exec HeapLeak

go 30

}

 

9. Once you suspect memory is leaked. Go to the rules and take a full user dump by right clicking the Leak rule.

 

clip_image004

 

 

10. After the dump is captured , go to the advanced analysis tab, Add data files and select the dump which we generated.

 

11. Go to ToolsàOptions ->set the symbol path for analysis. Default Microsoft symbol path is below.

 

srv*c:\Websymbols*http://msdl.microsoft.com/download/symbols;c:\Release

 

Important: Replace c:\Release with symbol path of dll’s which you have loaded in SQL Server (optional)

 

 

11. In the available analysis script select memory pressure analyzers (memory analysis.asp).

 

12. Click start analysis.

 

clip_image006

 

 

13. Analysis might take a while depending on time it takes to load the symbols. Once the analysis is completed it would generate and open a HTML report.

This HTML report is  stored in C:\Program Files\DebugDiag\Reports\ by default and can be used for later reference.

 

I have attached a sample report which I collected when leaking memory using heapleak.dll in This link. You can use it for reference.

 

 

Report generated by debug diagnostic tool memory pressure analyzer will have the analysis summary and below Table Of Contents

 

sqlservr.exe__…………dmp

   Virtual Memory Analysis Report

   Heap Analysis Report

   Leak Analysis Report

   Outstanding allocation summary

    Detailed module report (Memory)

    Detailed module report (Handles)

 

 

 

14.  Analysis summary is  good portion in the report to start with and would give the module which is leaking the memory. Look at the below report.

 

clip_image008

 

 

15. Report has clearly indicated HeapLeak.dll has 255 MB of outstanding allocations. In heapleak.dll “Sub“ is the function which allocated this memory at offset 23.

 

 

16. Look at the virtual memory summary. It gives complete picture about memory distribution in the virtual address space. In the below summary memory reserved is 1.57 GB which is normal in 32-Bit SQL Server, but native heaps is 272.94 MB which is not normal.

Look at the heap summary there are 50 heaps.

 

 

 

 

 

clip_image010

 

clip_image012

 

17. Now look at the Outstanding allocation summary. It gives top 10 modules by allocation count and allocation size. In below summary HeapLeak has 26,182 allocations with size of 255.6 MB.

Note: In this report it is HeapLeak but in real time it might be any module which is leaking the memory

.

 

clip_image014

 

18. You can  also look at detailed module report(Memory).  It gives the memory allocation from each module along with function  and source line which allocated the memory (If you set the symbols for all the modules loaded).

 

clip_image016

 

 

By now we are sure that sub function in HeapLeak.dll has allocated 255 MB in line number 14 and has not released. The report also gives you the callstack samples that show the code path when functions was doing allocations. Refer This sample HTML report file. 

 

If you liked this post, do like us on Facebook at https://www.facebook.com/mssqlwiki , join our Facebook group https://www.facebook.com/mssqlwiki#!/groups/454762937884205/ and post your SQL Server questions to get answered by experts.

Related posts:

Basics of SQL Server Memory Architecture

SQL Server 2012 Memory

Troubleshooting SQL Server Memory

A significant part of SQL Server process memory has been paged out

 

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

 

Posted in Debugging, Memory, Performance, SQL Server memory | Tagged: , , , , , | 5 Comments »

SQL Server memory leak

Posted by Karthick P.K on December 4, 2012

What is memory leak?

When a process allocates memory it is supposed to de-allocate it and release it back to OS. If it misses to de-allocate the memory due to flaw in code it is called as leak and It can cause memory pressure both to the operating system and application.

 

Myth about SQL Server memory leak

SQL Server memory management is designed to dynamically grow and shrink its memory based on the amount of available memory on the system and Max server memory setting in SQLServer.

Many times system admins look at the memory usage of SQLServer and assume SQLServer is leaking memory if they find SQL Server memory usage is high.

This is incorrect SQL Server is server based application and its memory manager is designed in such a way that it will keep growing its memory usage on need (Exception large pages) and will not scale down its usage unless there is low memory notification from Windows. We can control the memory usage of SQL Server using Max server memory setting in SQLServer. This setting limits the Bpool usage of SQL Server and doesn’t control the overall memory usage of SQLServer. There are portions of SQLServer memory that is allocated outside BPOOL (aks: MTL or MTR) we do not have a way to control how much memory SQL Server can use outside bpool, but non bool memory usage will be normally low and can be easily estimated by studying the components running in SQL Server.

Ex: If you want to set SQLServer to use only 10GB RAM on server. Consider how much memory SQL Server might need outside Bpool and set the “max server memory” setting accordingly. In this case if you estimate SQL Server will use 1.5GB outside Bpool then set the Max server memory to 8.5GB.

What can cause SQL Server Memory leak?

SQL Server code has a logic to allocate memory but doesn’t de-allocate it. If any of the components in SQL Server is causing a memory leak in SQL Server it can be identified easily using the DMV’s like sys.dm_os_memory_allocation,sys.dm_os_memory_clerks and sys.dm_os_memory_objects etc., but most of the memory leaks in SQL Server is caused by 3rd party Dll’s which are loaded in SQL Server process.

 

Note: All the memory allocations by Non SQL server Dll’s loaded in SQL Server will happens in “Mem to Leave”(outside the Bpool) and they are called as direct windows allocations (DWA) 

 

When there is out of memory conditions in SQL Server and if you suspect there is a memory leak.First thing to determine is who is consuming the memory. If SQL Server is not using majority of the memory in MemToLeave and still you get Mem to leave errors probably there is a leak and it caused by some DLL’s loaded in

SQL Server. Refer Section 1 (MTL error) in https://mssqlwiki.com/sqlwiki/sql-performance/troubleshooting-sql-server-memory/

 

Below query can be used to determine actual memory consumption by SQL Server in MTL.

select sum(multi_pages_kb)  from sys.dm_os_memory_clerks

 

If the memory consumption by SQL Server is very low and still if you see SQL Server memory errors like few below then focus on Leaks.

 

Ex:

SQL Server 2000

                WARNING: Failed to reserve contiguous memory of Size= 65536.

                WARNING: Clearing procedure cache to free contiguous memory.

                Error: 17802 “Could not create server event thread.”

                SQL Server could not spawn process_loginread thread.

SQL Server 2005/2008

                Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE 122880

 

 

 

How to identify and troubleshoot the memory leak?

 

There are multiple ways in windows to identify who is leaking memory in process. We will discuss how to identify the memory leak using  

 

1. Windows debugger 2. Debug diagnostics tools for windows and 3. UMDH in this blog.

 

Let us create a sample DLL to load in SQL server process to leak memory and see how to use the tools I mentioned above to troubleshoot the leak. 

 

Download HeapLeak.dll from This link and install Microsoft Visual C++ 2010 Redistributable Package from this links 32-Bit or 64-Bit to make this DLL work.

 

–Create  an extended stored procedure in SQL Server

exec sp_addextendedproc  'HeapLeak','C:\HeapLeakdll\HeapLeak.dll'

–Let us execute this Extended SP 30 times and leak memory.

exec HeapLeak

go 30

 

 

We will also enable below trace flags in SQL Server to automatically generate filter dump when there  is out of memory errors and see how to identify who is leaking.

 

 

dbcc traceon (2551,-1) — 2551 is used to enable filter dump.

go

dbcc traceon (8004,-1) –8004 is used to take memory dump on first occurrence of OOM condition

go

–Note: Both the trace flags listed above are un-documented, So use it at your own risk and there is no guarantee that this trace flags will work in future versions of SQL Server

 

 

Once we enable the trace flag . We have to cause out memory error in SQL Server to generate OOM memory dump. We have leaked around 300 MB of memory from MTL by executing above extended SP 30 times.

 

Let use execute below script which create XML handles. Memory for xml handles is allocated from MTL we will get out of memory errors very soon because extended stored procedure which we executed has already leaked the memory.

(Do not run below XML script directly with out executing HeapLeak  Below script will cause OOM error because of handle created for each execution, but it is accounted as SQL Server allocation so will not help us to understand the  how to debug leaks caused by 3rd party DLL’s)

 

Note: 1. SQL Server memory dump will be generated in SQL Server error log folder. 
2. Size of MTL is 256 MB + Max worker threads *.5  in 32-Bit SQL Server.  So approximately 384 MB unless modified using –g switch.

 

DECLARE @idoc int
 
DECLARE @doc varchar(1000)
 
SET @doc ='<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
     <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
      <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
   </Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
   <Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
   <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
   </Order>           
</Customer>
</ROOT>'
 
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
 
go 10000

We will receive below error after few executions.

Msg 6624, Level 16, State 12, Procedure sp_xml_preparedocument, Line 1

XML document could not be created because server memory is low.

To analyze the dump download and Install Windows Debugger from http://msdl.microsoft.com/download/symbols/debuggers/dbg_x86_6.11.1.404.msi

 

Step 1 (Load the memory dump file to debugger):

 

Open Windbg .  Choose File menu –> select Open crash dump –>Select the Dump file (SQLDump000#.mdmp)

 

Note : You will find SQLDump000#.mdmp in your SQL Server error log when you get the Exception or assertion.

 

Step 2 (Set the symbol path to Microsoft symbols server):

 

on command window type

 

.sympath srv*c:\Websymbols*http://msdl.microsoft.com/download/symbols;

 

Step 3 (Load the symbols from Microsoft symbols server):

 

Type .reload /f and hit enter. This will force debugger to immediately load all the symbols.

 

 

Step 4 (check if symbols are loaded):

 

Verify if symbols are loaded for  SQL Server by using the debugger command lmvm

 

:028> lmvm sqlservr

start    end        module name

01000000 02ba8000   sqlservr   (pdb symbols)          c:\websymbols\sqlservr.pdb\93AACB610C614E1EBAB0FFB42031691D2\sqlservr.pdb

    Loaded symbol image file: sqlservr.exe

    Mapped memory image file: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe

    Image path: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe

    Image name: sqlservr.exe

    Timestamp:        Fri Oct 14 15:35:29 2005 (434F82E9)

    CheckSum:         01B73B9B

    ImageSize:        01BA8000

    File version:     2005.90.1399.0

    Product version:  9.0.1399.0

    File flags:       0 (Mask 3F)

    File OS:          40000 NT Base

    File type:        1.0 App

    File date:        00000000.00000000

    Translations:     0409.04e4

    CompanyName:      Microsoft Corporation

    ProductName:      Microsoft SQL Server

    InternalName:     SQLSERVR

    OriginalFilename: SQLSERVR.EXE

    ProductVersion:   9.00.1399.06

    FileVersion:      2005.090.1399.00

    FileDescription:  SQL Server Windows NT

    LegalCopyright:   © Microsoft Corp. All rights reserved.

    LegalTrademarks:  Microsoft® is a registered trademark of Microsoft Corporation. Windows(TM) is a trademark of Microsoft Corporation

    Comments:         NT INTEL X86

 

Step 5 : (!address to display the memory information)

 

Use !address command to display the memory information of the process from dump.

 

 

0:028> !address -summary

 

——————– Usage SUMMARY ————————–

    TotSize (      KB)   Pct(Tots) Pct(Busy)   Usage

   686a7000 ( 1710748) : 81.58%    81.80%    : RegionUsageIsVAD

     579000 (    5604) : 00.27%    00.00%    : RegionUsageFree

    4239000 (   67812) : 03.23%    03.24%    : RegionUsageImage

     ea6000 (   15000) : 00.72%    00.72%    : RegionUsageStack

      1e000 (     120) : 00.01%    00.01%    : RegionUsageTeb

   122d0000 (  297792) : 14.20%    14.24%    : RegionUsageHeap

          0 (       0) : 00.00%    00.00%    : RegionUsagePageHeap

       1000 (       4) : 00.00%    00.00%    : RegionUsagePeb

       1000 (       4) : 00.00%    00.00%    : RegionUsageProcessParametrs

       1000 (       4) : 00.00%    00.00%    : RegionUsageEnvironmentBlock

       Tot: 7fff0000 (2097088 KB) Busy: 7fa77000 (2091484 KB)

 

——————– Type SUMMARY ————————–

    TotSize (      KB)   Pct(Tots)  Usage

     579000 (    5604) : 00.27%   : <free>

    4239000 (   67812) : 03.23%   : MEM_IMAGE

     5fc000 (    6128) : 00.29%   : MEM_MAPPED

   7b242000 ( 2017544) : 96.21%   : MEM_PRIVATE

 

——————– State SUMMARY ————————–

    TotSize (      KB)   Pct(Tots)  Usage

   1b7bd000 (  450292) : 21.47%   : MEM_COMMIT

     579000 (    5604) : 00.27%   : MEM_FREE

   642ba000 ( 1641192) : 78.26%   : MEM_RESERVE

 

Largest free region: Base 00000000 – Size 00010000 (64 KB)

 

 

Look at the RegionUsageHeap it is around 297792 KB and largest free region is just 64KB. We know SQL Server doesn’t use Heap’s extensively so normally the heap allocated by SQL Server will not go beyond few MB. In this case it is consuming around 290 MB and so other components which use MTL can easily fail.

Let us try to understand why the Heap is around 297792 KB and try to identify if there is  a pattern.

 

Step 6: (Let us use !heap –s to display summary information about the heap)

 

 

0:028> !heap -s

LFH Key                   : 0x672ddb11

  Heap     Flags   Reserv  Commit  Virt   Free  List   UCR  Virt  Lock  Fast

                    (k)     (k)    (k)     (k) length      blocks cont. heap

—————————————————————————–

000d0000 00000002    1024    896    896      6     1     1    0      0   L 

001d0000 00008000      64     12     12     10     1     1    0      0     

002c0000 00001002    1088     96     96      2     1     1    0      0   L 

002e0000 00001002      64     52     52      3     2     1    0      0   L 

007c0000 00001002      64     64     64     56     1     0    0      0   L 

00d10000 00001002     256     24     24      8     1     1    0      0   L 

340b0000 00001002      64     28     28      1     0     1    0      0   L 

340c0000 00041002     256     12     12      4     1     1    0      0   L 

342a0000 00000002    1024     24     24      3     1     1    0      0   L 

34440000 00001002      64     48     48     40     2     1    0      0   L 

61cd0000 00011002     256     12     12      4     1     1    0      0   L 

61d10000 00001002      64     16     16      7     1     1    0      0   L 

61d20000 00001002      64     12     12      4     1     1    0      0   L 

62a90000 00001002    1024   1024   1024   1016     2     0    0      0   L 

62b90000 00001002    1024   1024   1024   1016     2     0    0      0   L 

62c90000 00001002     256     40     40      7     1     1    0      0   LFH

00770000 00001002      64     16     16      2     2     1    0      0   L 

63820000 00001002      64     24     24      3     1     1    0      0   L 

63830000 00001001   10240  10240  10240    160    21     0    0    bad     

64230000 00001001   10240  10240  10240    160    21     0    0    bad     

64c30000 00001001   10240  10240  10240    160    21     0    0    bad     

65630000 00001001   10240  10240  10240    160    21     0    0    bad     

66030000 00001001   10240  10240  10240    160    21     0    0    bad     

66a30000 00001001   10240  10240  10240    160    21     0    0    bad     

67430000 00001001   10240  10240  10240    160    21     0    0    bad     

68130000 00001001   10240  10240  10240    160    21     0    0    bad     

68b30000 00001001   10240  10240  10240    160    21     0    0    bad     

69530000 00001001   10240  10240  10240    160    21     0    0    bad     

69f30000 00001001   10240  10240  10240    160    21     0    0    bad     

6a930000 00001001   10240  10240  10240    160    21     0    0    bad     

6b330000 00001001   10240  10240  10240    160    21     0    0    bad     

6bd30000 00001001   10240  10240  10240    160    21     0    0    bad     

6c730000 00001001   10240  10240  10240    160    21     0    0    bad     

6d130000 00001001   10240  10240  10240    160    21     0    0    bad     

6db30000 00001001   10240  10240  10240    160    21     0    0    bad     

6e530000 00001001   10240  10240  10240    160    21     0    0    bad     

6ef30000 00001001   10240  10240  10240    160    21     0    0    bad     

6f930000 00001001   10240  10240  10240    160    21     0    0    bad     

70330000 00001001   10240  10240  10240    160    21     0    0    bad     

70d30000 00001001   10240  10240  10240    160    21     0    0    bad     

7a160000 00001001   10240  10240  10240    160    21     0    0    bad     

7ab60000 00001001   10240  10240  10240    160    21     0    0    bad     

7b560000 00001001   10240  10240  10240    160    21     0    0    bad     

7d0d0000 00001001   10240  10240  10240    160    21     0    0    bad     

7e030000 00001001   10240  10240  10240    160    21     0    0    bad     

7ea30000 00001001   10240  10240  10240    160    21     0    0    bad     

67f90000 00001003     256     16     16     14     1     1    0    bad     

71850000 00001003     256      4      4      2     1     1    0    bad     

71890000 00001003     256      4      4      2     1     1    0    bad     

67fd0000 00001002      64     16     16      4     1     1    0      0   L 

718d0000 00001003     256     40     40      3     1     1    0    bad     

71910000 00001003     256      4      4      2     1     1    0    bad     

71950000 00001003     256      4      4      2     1     1    0    bad     

71990000 00001003     256      4      4      2     1     1    0    bad     

67ff0000 00001002      64     16     16      4     1     1    0      0   L 

719d0000 00001003    1792   1352   1352      5     2     1    0    bad     

71a10000 00001003     256      4      4      2     1     1    0    bad     

71a50000 00001003     256      4      4      2     1     1    0    bad     

71a90000 00001002      64     16     16      1     0     1    0      0   L 

—————————————————————————–

 

 

If you look at the above out put you can clearly identify a pattern. There are multiple created and each of them is 10 MB. But how to identify who actually created them?

 

Step 7:

 

Let us pickup one of the heap which is 10 MB and display all the entries (allocations) with in this 10 MB heap using !heap with –h parameter

 

Heap I have picked is 63830000.

 

 

0:028> !heap -h 63830000

Index   Address  Name      Debugging options enabled

19:   63830000

    Segment at 63830000 to 64230000 (00a00000 bytes committed)

    Flags:                00001001

    ForceFlags:           00000001

    Granularity:          8 bytes

    Segment Reserve:      00100000

    Segment Commit:       00002000

    DeCommit Block Thres: 00000200

    DeCommit Total Thres: 00002000

    Total Free Size:      00005048

    Max. Allocation Size: 7ffdefff

    Lock Variable at:     00000000

    Next TagIndex:        0000

    Maximum TagIndex:     0000

    Tag Entries:          00000000

    PsuedoTag Entries:    00000000

    Virtual Alloc List:   63830050

    UCR FreeList:        63830588

    FreeList Usage:      00000000 00000000 00000000 00000000

    FreeList[ 00 ] at 63830178: 6422de88 . 638ad7e0      Unable to read nt!_HEAP_FREE_ENTRY structure at 638ad7e0

(1 block )

    Heap entries for Segment00 in Heap 63830000

        63830608: 00608 . 00040 [01] – busy (40)

        63830648: 00040 . 02808 [01] – busy (2800)

        641b6698: 02808 . 02808 [01] – busy (2800)

        ……………………………………

        ……………………………………

        ……………………………………

        ……………………………………

       

Step 8: (Let us pickup one of the heap entry (allocation) and try to identify what is in it)

 

 

0:028> db 641b6698

641b6698  01 05 01 05 93 01 08 00-49 61 6d 20 66 69 6c 69 ……..Iam fili

641b66a8  6e 67 20 74 68 65 20 68-65 61 70 20 66 6f 72 20  ng the heap for

641b66b8  64 65 6d 6f 20 61 74 20-4d 53 53 51 4c 57 49 4b  demo at MSSQLWIK

641b66c8  49 2e 43 4f 4d 00 00 00-00 00 00 00 00 00 00 00  I.COM………..

641b66d8  00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00  …………….

641b66e8  00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00  …………….

641b66f8  00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00  …………….

641b6708  00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00  …………….

 

0:028> db 63830648

63830648  01 05 08 00 89 01 08 00-49 61 6d 20 66 69 6c 69 ……..Iam fili

63830658  6e 67 20 74 68 65 20 68-65 61 70 20 66 6f 72 20  ng the heap for

63830668  64 65 6d 6f 20 61 74 20-4d 53 53 51 4c 57 49 4b  demo at MSSQLWIK

63830678  49 2e 43 4f 4d 00 00 00-00 00 00 00 00 00 00 00  I.COM………..

63830688  00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 …………….

63830698  00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 …………….

638306a8  00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 …………….

638306b8  00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 …………….

 

 

Similarly you can dump multiple heap allocations to identify a pattern.

 

Now if you look at the memory dumped you see a string which might help you to identify the DLL which created the heap. There is a pattern in above heaps. All the heap allocations have below string

“Iam filing the heap for demo at MSSQLWIKI.COM”

 

Note : You can use L Size to dump more memory using db or dc command’s example db 63830648 L1500

 

Step 9:

Let us open the DLL which we loaded in SQL Server for testing using notepad and see if there is string which matches the pattern

 

clip_image002

 

 

Yes there is which proves that this DLL’s has caused the leak. In real time you may have to play with different heap allocations to identify the pattern.

 

This is one way to find the leaks from the memory dump after the leak has actually happened. It may not be always easy to find a pattern and identify the modules who allocated the memory, In such scenarios  you may have to track the leak using the tools like debug diagnostic tool, UMDH etc.In the my next blog I will post how to track memory leak using Debug diagnostics tool.

Continued in Debugging memory Leaks using Debug diagnostic tool

 

If you liked this post, do like us on Facebook at https://www.facebook.com/mssqlwiki , join our Facebook group https://www.facebook.com/mssqlwiki#!/groups/454762937884205/ and post your SQL Server questions to get answered by experts.

Related posts:

Basics of SQL Server Memory Architecture

SQL Server 2012 Memory

Troubleshooting SQL Server Memory

A significant part of SQL Server process memory has been paged out

 

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

Posted in Debugging, Memory, Performance, SQL General, SQL Server Engine | Tagged: , , , , | 37 Comments »

SQL Server NUMA load distribution

Posted by Karthick P.K on November 22, 2012

When port affinity is not configured all the connection to SQL Server enters through single port and connections are tied to nodes in round robin basis.

 

We might end with Imbalance of Workload in NUMA systems under below conditions.

 

1. When a connection is tied (or) affinitized to a node, all the work from that connection will be completed on the same node (in which connection is directed)  if plans are serial.  We don’t consider the CPU load across the NUMA to pick up the node for serial plans, We use the same node in which connection is made for serial plan execution. Parallel query would use any NUMA node regardless of node this query came from. When all the queries execute from connections made to same node and if plans are also serial we might end up with overloading one Node while others are not fully used.

 

2. State of each nodes is internally maintained by SQL Server and updated every 2 seconds so there is  remote possibility that all parallel queries end with same node some times and cause spike in one node, while the other nodes are unused.

 

3. When there is imbalance between the number of online schedulers in each node (Ex: 16-CPU in Node1 and 4-CPU in Node2 ) and if all plans are serial (assume we have set Max DOP 1) We might end up with overloading the schedulers in node with least schedulers. while the schedulers on other node is underused, similarly when memory is shared across nodes we share it equally irrespective of number of schedulers on each node so in this case first 16 schedulers would have got  half of memory and 4 schedulers of second node would have got remaining half. So ensure you choose the CPU affinity carefully (Specially when you have installed SQL Server with limited processor license on system with larger number of CPU’s). 

 

 

Image 1:  sys.dm_os_schedulers (6 – CPU’S on node-0 and  1- CPU on node-1. Look at current task count)

 

clip_image002

 

Image 2 (Look at the current and pending task in node 0 and in node 1)

clip_image002[4]

 

If you liked this post, do like us on Facebook at https://www.facebook.com/mssqlwiki and join our Facebook group https://www.facebook.com/mssqlwiki#!/groups/454762937884205/

 

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

 

Disclaimer

The views expressed on this website/blog are mine alone and do not reflect the views of my company. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Posted in Debugging, Memory, Performance, SQL Server Engine, SQL Server memory | Tagged: , , , | 3 Comments »

SQL Server 2012 Memory

Posted by Karthick P.K on October 21, 2012

SQL Server 2012 has made many changes to the memory manager to govern the SQL Server memory consumption in efficient way compared with earlier versions. Important changes to SQL Server 2012 memory which every DBA should be aware of is documented in this blog. If you are not familiar with the SQL Server memory architecture of earlier versions I would recommend reading THIS ARTICLE  before you continue with changes in Denali memory manager.

clip_image001
Max Server Memory

In previous versions of SQL Server “Max Server Memory” controlled the Maximum physical memory Single page allocator (BPOOL)  can consume in SQL Server user address space.

Only the single page allocator was part of BPOOL and Max server memory controlled only BPOOL, so the following allocations came outside BPOOL (Max server memory)

1.Multi-Page allocations from SQL Server [These are allocations which request more > 8 KB and required contiguous memory]

2.CLR allocations [These include the SQL CLR heaps and its global allocations created during startup]

3.Memory used for thread stacks within SQL Server process (Max worker threads * thread stack size). Thread stack size is 512K in 32 bit SQL Server, 904 K in WOW mode and 2 MB in 64-Bit 

4.Direct windows allocations made by Non-SQL Server dll’s ([These include windows heap usage and direct virtual allocations made by modules loaded into SQL Server process. Examples: allocations from extended stored procedure dll’s, objects created using OLE Automation procedures (sp_OA calls), allocations from linked server providers loaded in sqlserver process)

 

SQL Server 2012 memory manager has now clubbed single page allocator and multipage allocator together  as any-size page allocator . As a result, the any-size page allocator now manages allocations categorized in the past as single page and Multi-Page allocations.

1. "max server memory" now controls and includes “Multi pages allocations”.

2. In earlier versions of SQL Server CLR allocated memory was outside BPOOL (Max server memory)   . SQL Server 2012 includes SQL CLR allocated memory in "max server memory".

SQL Server 2012 "max server memory" configuration does not include only the following allocations:

1. Memory allocations for thread stacks within SQL Server process

2. Memory allocation requests made directly to Windows [Ex: Allocations (Heap,Virtualalloc calls ) from 3rd party Dll’s loaded in SQL Server process , objects created using OLE Automation procedures (sp_oa) etc]

These changes allow DBA’s to configure and control SQL Server more accurately in accordance with the memory requirements and using resource governor.

-g startup parameter

We used the -g startup option to change the default value of a region in SQL Server user address space known as "Memory-To-Reserve". This region was also known as "memory-to-leave or MTL.  The "Memory-To-Reserve" (or) -g configuration option are relevant only for a 32-bit instance of SQL Server.

Multi pages allocation and CLR was part of Mem-to-reserve (-g)  in In previous SQL Server versions until SQL Server 2008 R2 , From Denali they are part of BPOOL (Controlled by Max server memory)  So you may have to remove –g if you have set it to give space for multipage allocator or CLR in earlier versions and migrating to Denali now.

 

AWE feature removed from SQL Server 2012

AWE feature was used in earlier versions of 32-Bit SQL Server to address more than 4GB of memory . This feature is now removed in Denali  refer:"AWE deprecation".  So if you need more memory then you may need to migrate to 64-Bit SQL server.

 

Locked pages in memory

Trace flag 845 is no more required to Lock Pages in memory. As long as the startup account of SQL Server has “Lock pages in memory” privilege Datacenter, Enterprise, standard and Business intelligence edition will use AWE allocator Api’s for memory allocations in BPOOL and this allocations will be locked.

 
Dynamic virtual address space management

In earlier versions of SQL Server 32-Bit we reserved Bpool at the startup and remaining addresses are left for MTL (Memory to reserve or Memory to leave) . In Denali virtual address space management is dynamic (we  don’t reserve at startup) , So it is possible for 3rd part components to use more memory than what is  configured in –g parameter.

 
SQLCLR loaded at startup

In earlier SQL Server versions, Common language runtime (CLR) functionality is initialized inside SQL Server process when the first SQL CLR procedure or function is invoked. SQL Server 2012 performs SQL CLR initialization at startup. The initialization is independent of the ‘clr enabled’ configuration option.

You will notice the following messages in the SQL Server error log during server startup:

2012-10-18 15:23:13.250 spid8s       Starting up database ‘master’.

2012-10-18 15:23:13.930 Server       CLR version v4.0.30319 loaded.

Total Physical memory and memory model used

Total physical memory available on the server and the memory model  used is logged in SQL Server error log

2012-10-18 15:23:06.690 Server       Detected 131067 MB of RAM. This is an informational message; no user action is required.

2012-10-18 15:23:06.700 Server       Using locked pages in the memory manager

2012-10-22 15:32:20.450 Server       Detected 131067 MB of RAM. This is an informational message; no user action is required.
2012-10-22 15:32:20.450 Server       Using conventional memory in the memory manager.

 

DMV and Performance counter changes

In earlier version of SQL Server most of the DMV’s used single_pages_kb and  multi_pages_kb to refer allocations by SQL Server with in BPOOL and outside BPOOL. Now they are represented together as  pages_kb. More details in THIS link

 

If you liked this post, do like us on Facebook at https://www.facebook.com/mssqlwiki and join our Facebook group

 

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

Disclaimer:

The views expressed on this website/blog are mine alone and do not reflect the views of my company. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

 

 

Posted in Memory, SQL General, SQL Server Engine, SQL Server memory | Tagged: , , , , , , , , , | 18 Comments »

What is RESOURCE_SEMAPHORE_QUERY_COMPILE?

Posted by Karthick P.K on October 12, 2012

What is RESOURCE_SEMAPHORE_QUERY_COMPILE?

Before we understand the RESOURCE_SEMAPHORE_QUERY_COMPILE let us see what is compile memory.

Compile memory:  When a query is compiled in SQL Server, the compilation process needs memory (for parsing, algeberaization and optimization) called compile memory. This memory doesn’t include the memory required to execute the query.

Challenges with Compile memory:  This memory used for Query compilations are usually expected to fit into SQL Server main-memory and to be relatively short-lived.  Like any other consumers of Bpool,  this is implemented by “stealing” pages from the Buffer Pool and hence it blocks other memory consumers from using that memory until a query compilation completes.

Even if the SQL Server has enough memory to service multiple simultaneous query compilation, allowing all of them to occur at the same time might lead to stealing a significant number of pages from the buffer pool, with consequent increased physical IO , poor performance of query execution and causing memory pressure within SQL Server. However on the other side, a throttling mechanism that is too restrictive could lead to a non-optimal usage of the system resources and decreased throughput for compile intensive workloads, So SQL Server came with more dynamic approach to solve the problem  which  is to better manage system resources, memory in particular. Such management should hence be based on and driven by the amount of memory used.

Let us see it with example:

Assume SQL  Server Max server memory is set to 1000MB and Currently data /index pages is consuming 800MB with in Max server memory (bpool) and 3 queries are reaching SQL Server for compilation, each of them requiring 300 MB for compilation.

If all three queries are compiled simultaneously  total compilation might take 900MB of memory causing all the data and index pages to be dropped from BPOOL causing  consequent increased physical IO and poor performance of query during execution(to bring data pages back to memory). On the other hand let us assume each of this 3 queries need only 2 MB of compilation memory, There is no reason for SQL Server to throttle the number of compilation.

To overcome above challenges SQL 2005+ throttles the number of concurrent compiles that can happen at  any time based on memory usage during the compile. SQL Server memory grant is controlled by a object called “Resource Semaphore” and has internal mechanism to detect how much memory has been used by each compile. There are three gateways (semaphores) called the small, medium, and big gateway. When a request is received for compilation SQL Server will start compilation. There is no limit on how many queries can be compiled simultaneously, but when memory usage for a query reaches the threshold for a given gateway it will then acquire that semaphore of next gateway before continuing. The semaphores (Queries which can be compiled concurrently) are set up to allow 4*schedulers count for the small gateway, 1*schedulers count for the medium gateway and 1 (per SQL instance) for the big gateway.

The small gateway has a fixed threshold for how much memory must be consumed before you enter it.  The medium and big gateways have dynamic thresholds that vary depending on how much memory is available, how much is stolen, etc.

If the semaphore can’t be acquired then you see this wait type (Query is waiting for memory grant to compile a query =  RESOURCE_SEMAPHORE_QUERY_COMPILE wait). This behavior lets SQL Server to allow only a few memory-intensive compilations occur at the same time. Additionally, this behavior maximizes throughput for smaller queries.

clip_image001[4]

How to identify RESOURCE_SEMAPHORE_QUERY_COMPILE waits?

 

To get an idea look at the sys.sysprocesses  table for sessions waiting on  RESOURCE_SEMAPHORE_QUERY_COMPILE

select sp.*, st.text from sys.sysprocesses sp CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) AS st

WHERE sp.lastwaittype LIKE ‘RESOURCE_SEMAPHORE_QUERY_COMPILE%’ ORDER BY sp.waittime DESC;

 

clip_image002[4]

 

 

 

There could be two possible reasons for RESOURCE_SEMAPHORE_QUERY_COMPILE waits

1.       Memory pressure within SQL Server caused by others using  lot of stolen memory or OS memory pressure

In this case you will see thresholds for medium and big gateways very low. In this situation you have to identify who is consuming most of stolen memory and  tune them to increase the available memory or add additional memory that can used by SQL server. When the available memory decrease, threshold for medium and big gateways would decrease significantly and increase the number of queries which have to enter medium /big gateways, So the number of parallel compiles will decrease increasing the overall wait time.

 

This DBCC memory status output is from system which has 48 processor and has excessive RESOURCE_SEMAPHORE_QUERY_COMPILE waits because of memory pressure.

Small Gateway (default)                  Value

—————————————- ———–

Configured Units                         192        // 190 number of units .  48  CPU’s * 4=192

Available Units                            109

Acquires                                         83

Waiters                                            0

Threshold Factor                         380000

Threshold                                       380000

(6 row(s) affected)

Medium Gateway (default)                 Value

—————————————- ———–

Configured Units                         48              //48 number of units. SO 48 CPU’s *1=48

Available Units                               0             //There is no available slots. All slots are busy.

Acquires                                           48

Waiters                                             34           //34 Queries are waiting

Threshold Factor                         12

Threshold                                    2204603    //Threshold is very low 2 MB (This value is in bytes)

(6 row(s) affected)

Big Gateway (default)                    Value

—————————————- ———–

Configured Units                         1            //1 per instance

Available Units                             0            //There is no available slots. All slots are busy.

Acquires                                          1

Waiters                                          47            // 47 Queries are waiting

Threshold Factor                         8

Threshold                                3306905     //Threshold is very low 3 MB (This value is in bytes)

 

 

2.       There is huge amount of available memory but the available units in gateway is exhausted. This situation normally occurs when we have many queries that have high compile time and use lot of memory for compilation.

Compile time and Compile memory can be captures using the show plan XML for query compile event in profiler.

Below is extract from show plan XML for query compile event in profiler. This query has used approximately 150 MB of compile memory and the threshold for Big gateway is around 143 MB . So only one query which needs more than 143 MB can compile at a time , This can cause contention when there are multiple queries waiting for compile/recompile. Also an important factor to notice in this XML plan is compile time is ~139 times the CPU. So likely this query waited for most of the time for resource semaphore.

Big Gateway (default)                    Value

—————————————- ———–

Configured Units                         1

Available Units                             0

Acquires                                         1

Waiters                                          47

Threshold Factor                         8

Threshold                                149640500

 

         <QueryPlan CachedPlanSize="312" CompileTime="139847" CompileCPU="1002" CompileMemory="152320">

 

How to fix RESOURCE_SEMAPHORE_QUERY_COMPILE waits?

1.       Add additional memory to SQL Server.

2.       Reduce the number of compile and recompiles happening in SQL Server.

3.       Threshold for the gateways are dynamic (except for small gateway) and therefore memory pressure occurs from other sources (Internal to SQL Server or system wide) reduces the  amount of memory available for compiles and Queries are throttled to    higher gateways sooner. Make sure SQL Server is not starving for memory.

4.       Identify the queries which are consuming large compile memory and tune them (CompileMemory  in show plan XML query compile can be used).

5.       RESOURCE_SEMAPHORE_QUERY_COMPILE waits are very common in 32-bit SQL Server because of virtual address space limitation, so migrate to 64-Bit SQL Server.

 

If you liked this post, do like us on Facebook at https://www.facebook.com/mssqlwiki and join our Facebook group https://www.facebook.com/mssqlwiki#!/groups/454762937884205/

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

Posted in Memory, Performance, SQL Query, SQL Server Engine | Tagged: , , , , , | 14 Comments »

A significant part of SQL Server process memory has been paged out

Posted by Karthick P.K on June 27, 2012

When you get “A significant part of SQL Server process memory has been paged out. This may result in performance degradation.This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 2007640, committed (KB): 4594040, memory utilization: 43%.” message in SQL Server error log

Pay attention to Working set (KB), committed (KB) and memory utilization:% (Percentage of SQL Server memory in RAM) in above warning message. Above warning message is logged in SQL Server error log when working set reaches 50% or  below of the overall committed memory by SQL Server memory manager.

 

What is working set: Memory allocated by the process which is currently in RAM.

 

Committed: Total memory that is allocated by process (allocated bytes can be in RAM or Page file)

 

Working Set trimming:  Windows is moving the allocated bytes of the process from physical RAM to page file because of memory pressure. Memory pressure is most commonly caused by applications or windows components that are requesting more memory causing OS to start trimming working set of other processes to satisfy these new requests.

 

Before we step in to troubleshooting working set trimming warnings, here are few basics about how SQL Server memory management is designed to dynamically adjust the committed memory based on the amount of available memory on the system.

SQL Server uses CreateMemoryResourceNotification to create a memory resource notification object  and SQL Server Resource monitor threads calls QueryMemoryResourceNotification every time it runs to identify if there is any notification. If a low memory notification comes from Windows, SQL Server scales down its memory usage and when Windows sends the high memory notification, SQL Server Server can grow its memory usage target. Low memory notification is signaled by windows when the available physical memory drops approximately below 32 MB per 4 GB, to a maximum of 64 MB. The default level that signals a high-memory-resource notification event is three times the default low-memory value. As soon as the SQL Server resource monitor threads finds low-memory-resource notification it scales down SQL Server memory usage.

 

Why do I see “A significant part of sql server process memory has been paged out. This may result in performance degradation.” By SQL Server In spite of having above mechanism to detect the system level memory pressure and scale SQL Server memory?

 

There are couple of situations where SQL Server Process working set might be paged out by Windows despite these memory resource notification mechanism.

1.If windows is not sending the correct notifications to all listening processes at the right moment and thresholds

2.If SQL Server is not responding fast enough to the low memory resource notification from Windows

3.When low physical memory notification is received by SQL Server it will scale down its memory usage by trimming internal caches. This effect is applied for 5 seconds and then paused for 1 minute. This is to avoid any hungry/faulty application to consume all the memory making SQL Server to  scale its usage continuously. If low memory conditions still exist after 1 minute pause, the effect is applied again. So if there is physical memory pressure even after SQL Server scaling its usage for 5 seconds windows will still page out SQL Server’s working set.

4.Conditions in Windows where working sets of all processes are trimmed

5.Windows might decide to trim a certain percentage of working set of various or specific processes

 

 

We can also increase the LowMemoryThreshold value so the OS will notify applications such as SQL on low memory conditions much earlier and SQL Server can respond to memory pressure much early before the system is starving for memory.

 

How to set the LowMemoryThreshold value (in MB)?

 

In Regedit -> go to

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\SessionManager\MemoryManagement

Right click on the right pane,

Select New -> select click DWORD Value -> enter LowMemoryThreshold

Double Click LowMemoryThreshold -> value (choose decimal) -> 512

System Reboot is required to take effect.

 

Default values as per MSDN:

“The default level of available memory that signals a low-memory-resource notification event is approximately 32 MB per 4 GB, to a maximum of 64 MB. The default level that signals a high-memory-resource notification event is three times the default low-memory value.”

 

 

We can use the below query to extract information about the condition of OS memory and SQL memory using a query like the following. Looking at this query, you will be able to easily determine the various indicators that would have triggered the Windows to page various processes including SQL Server. Use the following query to obtain the memory notification-related information from the XML data of the ring buffer

SELECT CONVERT (varchar(30), GETDATE(), 121) as [RunTime],
dateadd (ms, (rbf.[timestamp] - tme.ms_ticks), GETDATE()) as [Notification_Time],
cast(record as xml).value('(//Record/ResourceMonitor/Notification)[1]', 'varchar(30)') AS [Notification_type],
cast(record as xml).value('(//Record/MemoryRecord/MemoryUtilization)[1]', 'bigint') AS [MemoryUtilization %],
cast(record as xml).value('(//Record/MemoryNode/@id)[1]', 'bigint') AS [Node Id],
cast(record as xml).value('(//Record/ResourceMonitor/IndicatorsProcess)[1]', 'int') AS [Process_Indicator],
cast(record as xml).value('(//Record/ResourceMonitor/IndicatorsSystem)[1]', 'int') AS [System_Indicator],
cast(record as xml).value('(//Record/ResourceMonitor/Effect/@type)[1]', 'varchar(30)') AS [type],
cast(record as xml).value('(//Record/ResourceMonitor/Effect/@state)[1]', 'varchar(30)') AS [state],
cast(record as xml).value('(//Record/ResourceMonitor/Effect/@reversed)[1]', 'int') AS [reserved],
cast(record as xml).value('(//Record/ResourceMonitor/Effect)[1]', 'bigint') AS [Effect],

cast(record as xml).value('(//Record/ResourceMonitor/Effect[2]/@type)[1]', 'varchar(30)') AS [type],
cast(record as xml).value('(//Record/ResourceMonitor/Effect[2]/@state)[1]', 'varchar(30)') AS [state],
cast(record as xml).value('(//Record/ResourceMonitor/Effect[2]/@reversed)[1]', 'int') AS [reserved],
cast(record as xml).value('(//Record/ResourceMonitor/Effect)[2]', 'bigint') AS [Effect],

cast(record as xml).value('(//Record/ResourceMonitor/Effect[3]/@type)[1]', 'varchar(30)') AS [type],
cast(record as xml).value('(//Record/ResourceMonitor/Effect[3]/@state)[1]', 'varchar(30)') AS [state],
cast(record as xml).value('(//Record/ResourceMonitor/Effect[3]/@reversed)[1]', 'int') AS [reserved],
cast(record as xml).value('(//Record/ResourceMonitor/Effect)[3]', 'bigint') AS [Effect],

cast(record as xml).value('(//Record/MemoryNode/ReservedMemory)[1]', 'bigint') AS [SQL_ReservedMemory_KB],
cast(record as xml).value('(//Record/MemoryNode/CommittedMemory)[1]', 'bigint') AS [SQL_CommittedMemory_KB],
cast(record as xml).value('(//Record/MemoryNode/AWEMemory)[1]', 'bigint') AS [SQL_AWEMemory],
cast(record as xml).value('(//Record/MemoryNode/SinglePagesMemory)[1]', 'bigint') AS [SinglePagesMemory],
cast(record as xml).value('(//Record/MemoryNode/MultiplePagesMemory)[1]', 'bigint') AS [MultiplePagesMemory],
cast(record as xml).value('(//Record/MemoryRecord/TotalPhysicalMemory)[1]', 'bigint') AS [TotalPhysicalMemory_KB],
cast(record as xml).value('(//Record/MemoryRecord/AvailablePhysicalMemory)[1]', 'bigint') AS [AvailablePhysicalMemory_KB],
cast(record as xml).value('(//Record/MemoryRecord/TotalPageFile)[1]', 'bigint') AS [TotalPageFile_KB],
cast(record as xml).value('(//Record/MemoryRecord/AvailablePageFile)[1]', 'bigint') AS [AvailablePageFile_KB],
cast(record as xml).value('(//Record/MemoryRecord/TotalVirtualAddressSpace)[1]', 'bigint') AS [TotalVirtualAddressSpace_KB],
cast(record as xml).value('(//Record/MemoryRecord/AvailableVirtualAddressSpace)[1]', 'bigint') AS [AvailableVirtualAddressSpace_KB],
cast(record as xml).value('(//Record/@id)[1]', 'bigint') AS [Record Id],
cast(record as xml).value('(//Record/@type)[1]', 'varchar(30)') AS [Type],
cast(record as xml).value('(//Record/@time)[1]', 'bigint') AS [Record Time],
tme.ms_ticks as [Current Time]
FROM sys.dm_os_ring_buffers rbf
cross join sys.dm_os_sys_info tme
where rbf.ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR' --and cast(record as xml).value('(//Record/ResourceMonitor/Notification)[1]', 'varchar(30)') = 'RESOURCE_MEMPHYSICAL_LOW'
ORDER BY rbf.timestamp ASC

We can use below query to to check the health of SQL Server including SQL Server working set information in past

SELECT  CONVERT (varchar(30), GETDATE(), 121) as runtime, DATEADD (ms, a.[Record Time] - sys.ms_ticks, GETDATE()) AS Notification_time,    a.* , sys.ms_ticks AS [Current Time]
FROM   (SELECT x.value('(//Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [ProcessUtilization],
x.value('(//Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS [SystemIdle %],
x.value('(//Record/SchedulerMonitorEvent/SystemHealth/UserModeTime) [1]', 'bigint') AS [UserModeTime],
x.value('(//Record/SchedulerMonitorEvent/SystemHealth/KernelModeTime) [1]', 'bigint') AS [KernelModeTime],
x.value('(//Record/SchedulerMonitorEvent/SystemHealth/PageFaults) [1]', 'bigint') AS [PageFaults],
x.value('(//Record/SchedulerMonitorEvent/SystemHealth/WorkingSetDelta) [1]', 'bigint')/1024 AS [WorkingSetDelta],
x.value('(//Record/SchedulerMonitorEvent/SystemHealth/MemoryUtilization) [1]', 'bigint') AS [MemoryUtilization (%workingset)],
x.value('(//Record/@time)[1]', 'bigint') AS [Record Time]  FROM (SELECT CAST (record as xml) FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_SCHEDULER_MONITOR') AS R(x)) a  CROSS JOIN sys.dm_os_sys_info sys ORDER BY DATEADD (ms, a.[Record Time] - sys.ms_ticks, GETDATE())
  • Process Utilization shows the percentage of overall used system CPU time that was consumed by sqlservr.exe. Process Utilization is calculated as sql_process_cpu_time/total_system_cpu_time for the current time interval.
  • SystemIdle is the percentage of time that the system’s CPU’s have been idle.
  • Page Faults value is the number of hard + soft page faults that have occurred since the last snapshot.
  • Working Set is the change in the working set size  in KB, since the last snapshot.
  • Memory Utilization is working set/committed memory which is another way of saying that the number is the percentage of the process’ committed memory that is in RAM.  The farther below 100% that this number falls, the larger the percentage of SQL memory that has been trimmed and moved to the page file.

Common Side Effects of Working set Trimming

1.  When OS starts trimming the working set of SQL Server we would see drastic performance drop, increased I/O,non-yielding Resource Monitor / scheduler dumps etc..

2.  IS-alive check failures resulting in SQL Server resource failure.

3. Resource monitor thread can start  Garbage collector  if SQLCLR is enabled on this instance of SQL. When Garbage collector  is kicked off during memory pressure all other threads in the process are suspended. So if Garbage collector is taking a long time reosurce monitor thread appears stuck and hence the non-yielding errors and dumps are generated. (Refer http://support.microsoft.com/kb/2504603)

How to troubleshoot?

1.  Capture perfmon counters (Process: Private bytes and Working set ) to determine which applications / windows component are requesting memory and causing OS to start trimming the working set of processes including SQL Server.

2.  Use This exe which will print the memory information of all the processes and system wide memory information (Global memory status) when the operating system signals  low memory notification.

 

3.  Cap the SQL Server MAX Server Memory after considering the memory required by other applications, Operating system, Drivers , SQL Server Non- Bpool allocations etc. Make sure you have adequate available physical memory even when the system is under heavy load.

 

4.  We can consider using the Lock pages in memory privilege. Remember it protects only the BPool from paging and Non-Bpool allocations can still be paged out.

If you liked this post, do like us on Facebook at https://www.facebook.com/mssqlwiki and join our Facebook group  https://www.facebook.com/mssqlwiki#!/groups/454762937884205/

 

https://mssqlwiki.com/sqlwiki/sql-performance/basics-of-sql-server-memory-architecture/

https://mssqlwiki.com/sqlwiki/sql-performance/windows-2008-and-windows-2008-r2-known-issues-related-to-working-set-memory/

https://mssqlwiki.com/2012/05/18/sql-server-performance-degraded-in-32-bit-sql-server-after-i-adding-additional-ram/

https://mssqlwiki.com/sqlwiki/sql-performance/troubleshooting-sql-server-memory/

https://mssqlwiki.com/sqlwiki/sql-performance/io-bottlenecks/

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

Posted in Memory, Performance, SQL Server Engine, SQL Server memory | Tagged: , , , , , , , , , , , , , , , | 24 Comments »

SQL Server performance degraded in 32-Bit SQL Server after adding additional RAM.

Posted by Karthick P.K on May 18, 2012

 

Do you know that adding additional RAM can affect the performance of SQL Server Sometimes?

I am not going to write how Optimizer can some times choose suboptimal plans when we have large amount of memory on the system but We will see how the memory which can be used by other memory clerks (aks: stolen memory) can shrink when we have large physical memory and AWE enabled.

 

If you notice  performance of 32-Bit SQL Server degraded after you added additional RAM or if you see SQL Server memory errors like ones below after adding RAM then it could be because of Large BUF structures which reduced the size of Bpool.

 

Errors:

SQL Server 2005/2008

 

Buffer Pool errors:

    BPool::Map: no remappable address found.

 

Either BPool or MemToLeave errors:

    Error: 17803 “Insufficient memory available..”

    Buffer Distribution:  Stolen=7901 Free=0 Procedures=1 Inram=201842 Dirty=0 Kept=572…

 

Extract from SQL Server memory design

{

SQL Server "User address space" is broken into two regions: MemToLeave and Buffer Pool

 

Size of MemToLeave (MTL) and Buffer Pool (BPool) is determined by SQL Server during start up as below.

 

MTL (Memory to Leave)= (Stack size * max worker threads) + Additional space to load Dll’s.

 

Stack size =512 KB per thread for 32 Bit SQL Server (904K under WOW)

 

I.e. = (256 *512 KB) + 256MB =384MB

 

Additional space to load Dll’s= 256 MB from SQLServer2000. This space is used to store COM objects, Extended stored procedure, Linked server in SQL Server process

 

Note: Additional space to load Dll’s can be modified using -g startup parameter.

 

on any machine with less than 4 processors the Maximum worker Thread’s is always 256 by default (unless we change the value using SP_configure)

 

SQL Server Buffer Pool is minimum of “Physical RAM “ or “user mode memory(2GB or 3GB) – MTL-  BUF structures”

 

BPool = Minimum (Physical memory, User address space – MTL) – BUF structures

 

}

 

 

When AWE is enabled in 32-Bit SQL Server M_pbuf (part of BUF structures) which is mentioned earlier is calculated and allocated for entire physical memory on the system . Regardless of “MAX Server Memory”   This is to adjust Max server memory without restarting SQL Server.

 

SQL Server requires 8MB to create M_pbuf for every 1GB of RAM available on the server.

 

Machine with 64 GB RAM can consume 64 (RAM) *8MB (M_pbuf for each GB) =512 MB just for the BUF array alone.

 

So the amount of BPOOL available for SQL Server is adversely affected.

 

Going back to the previous formula for BPOOL. Size of Bpool for 32-Bit SQL Server with AWE enabled and 64 GB of RAM would be.

 

BPool = Minimum (Physical memory, User address space – MTL) – BUF structures

 

BPool= Minimum (64GB, (2GB-384MB)) – BUF structures (512+ MB)

 

Bpool would approximately become 1GB.  Since size BPOOL become very small we might end up with memory errors.

 

Note:  In 32-Bit SQL Server Only data pages an index pages can be placed in AWE memory. So the memory available for other SQL Server memory objects is still limited to BPOOL and MTL.  

 

How to resolve this issue?

Remove few GB of RAM from server J if you can convince your management that removing RAM will improve performance.

(Or)

There is a startup trace flag TF 836 which you can use to indicate that BUF’s need to be allocated only for the configured max server memory setting. Enable this Trace Flag (836) and Reduce the “MAX Server Memory” of SQL Server.

(Or)

Enable /3GB. This will increase the Size of SQL Server BPOOL by 1GB providing relief to SQL Server BPOOL pressure.

Note: When the physical RAM in the system exceeds 16 GB and the /3GB switch is used, the operating system will ignore the additional RAM until the /3GB switch is removed.

 

 

 

Related posts

Troubleshooting steps for all SQL Server Memory errors

Trouble shooting working set trim “A significant part of SQL Server process memory has been paged out”

SQL Server lock pages in memory should I use it?

SQL Server memory leak

What is new in SQL Server 2012 Memory

How to set max server memory and min server memory

 

If you liked this post do like us on Facebook at https://www.facebook.com/mssqlwiki and join our Facebook group MSSQLWIKI

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

Disclaimer
The views expressed on this website/blog are mine alone and do not reflect the views of my company. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Posted in Memory, Performance, SQL General, SQL Server Engine | Tagged: , , , , , , , | 6 Comments »

QueryMemoryResourceNotification & CreateMemoryResourceNotification (How SQL Server identifies low system memory on the system and respond to low system memory?)

Posted by Karthick P.K on December 2, 2010

#include <windows.h> 
#include <string> 
#include <winbase.h> 
#include <iostream> 
using namespace std;
#include <psapi.h>
#pragma comment(lib,"psapi.lib")
#include <time.h>

DWORD dwLength;
DWORD dwMemoryLoad;
ULONG_PTR dwTotalPhys;
ULONG_PTR dwAvailPhys;
ULONG_PTR dwTotalPageFile;
ULONG_PTR dwAvailPageFile;
ULONG_PTR dwTotalVirtual;
ULONG_PTR dwAvailVirtual;
int *m_pBuf; 
MEMORY_RESOURCE_NOTIFICATION_TYPE Low;
MEMORY_RESOURCE_NOTIFICATION_TYPE High;
HANDLE LMHandle;
HANDLE HMHandle;
HANDLE THandle;
int ResourceState;
int x=0;
BOOL state=1;
char dateStr [9];
char timeStr [9];


BOOL SetPrivilege(
    HANDLE hToken,          // access token handle
    LPCTSTR lpszPrivilege,  // name of privilege to enable/disable
    BOOL bEnablePrivilege   // to enable or disable privilege
    ) 
{
    TOKEN_PRIVILEGES tp;
    LUID luid;

    if ( !LookupPrivilegeValue( 
        NULL,            // lookup privilege on local system
        lpszPrivilege,   // privilege to lookup 
        &luid ) )        // receives LUID of privilege
    {
        printf("LookupPrivilegeValue error: %u\n", GetLastError() ); 
        return FALSE; 
    }

    tp.PrivilegeCount = 1;
    tp.Privileges[0].Luid = luid;
    if (bEnablePrivilege)
        tp.Privileges[0].Attributes = SE_PRIVILEGE_ENABLED;
    else
        tp.Privileges[0].Attributes = 0;

    // Enable the privilege or disable all privileges.

    if ( !AdjustTokenPrivileges(
        hToken, 
        FALSE, 
        &tp, 
        sizeof(TOKEN_PRIVILEGES), 
        (PTOKEN_PRIVILEGES) NULL, 
        (PDWORD) NULL) )
    { 
        printf("AdjustTokenPrivileges error: %u\n", GetLastError() ); 
        return FALSE; 
    } 

    if (GetLastError() == ERROR_NOT_ALL_ASSIGNED)

    {
        printf("The token does not have the specified privilege. \n");
        return FALSE;
    } 

    return TRUE;
}



void processmemory()

{

    FILE * pFile;
    pFile = fopen ("MemoryStatus.txt","a");
    DWORD     PID[1024];
    DWORD pBytesReturned=NULL;
    BOOL S;
    //PID= new DWORD(SIZEOF(pBytesReturned));

    S= EnumProcesses(PID,sizeof(PID), &pBytesReturned);
    BOOL x;
    HANDLE TokenHandle;
    TokenHandle=NULL;
    fprintf(pFile,"Low Memory Notification received on  %s  %s  ", dateStr,timeStr);
    x= OpenProcessToken(GetCurrentProcess(),TOKEN_ADJUST_PRIVILEGES | TOKEN_QUERY,&TokenHandle);

    if (x==0)
    {

        fprintf (pFile,"Unable To OpenProcessToken For current Process Error:%d",GetLastError());

    }


    if(SetPrivilege(TokenHandle, SE_DEBUG_NAME, TRUE))
    {
        fprintf (pFile,"Success");
    }
    else
    {
        fprintf (pFile,"FAILURE");
    }            



    for(int i=0;i<=pBytesReturned/sizeof(DWORD);i++)
    {
        HANDLE H;    
        fprintf (pFile,"\nProcess:%d",PID[i]);

        H= OpenProcess(PROCESS_QUERY_INFORMATION |PROCESS_VM_READ |PROCESS_ALL_ACCESS,TRUE,PID[i]);


        if (H==0)
        {
            fprintf (pFile,"Unable To Get Process Name ");
            fprintf (pFile,"Error:%d",GetLastError());
        }
        else

        {
            char   Basename[MAX_PATH];

            BOOL A=0;

            A=GetModuleBaseName(H,NULL,(LPSTR) Basename,sizeof(Basename)/sizeof(TCHAR));
            if (A==0)
            {
                fprintf (pFile,"Error:%d",GetLastError());
                fprintf (pFile,"Unable To Get ModuleBaseName ");
            }
            else
            {
                fprintf (pFile,",Process Name:%s",Basename);
            }

            PROCESS_MEMORY_COUNTERS PMC;

            GetProcessMemoryInfo(H, &PMC,sizeof(PMC));


            fprintf (pFile,",PageFaultCount:%d",PMC.PageFaultCount);
            fprintf (pFile,",PeakWorkingSetSize:%d",PMC.PeakWorkingSetSize);
            fprintf (pFile,",WorkingSetSize:%d",PMC.WorkingSetSize);
            fprintf (pFile,",QuotaPeakPagedPoolUsage:%d",PMC.QuotaPeakPagedPoolUsage);
            fprintf (pFile,",QuotaPagedPoolUsage:%d",PMC.QuotaPagedPoolUsage);
            fprintf (pFile,",QuotaPeakNonPagedPoolUsage:%d",PMC.QuotaPeakNonPagedPoolUsage);
            fprintf (pFile,",QuotaNonPagedPoolUsage:%d",PMC.QuotaNonPagedPoolUsage);
            fprintf (pFile,",PagefileUsage:%d",PMC.PagefileUsage);
            fprintf (pFile,",PeakPagefileUsage:%d",PMC.PeakPagefileUsage);

        }





    }


    PERFORMANCE_INFORMATION Perfinfo;
    GetPerformanceInfo(&Perfinfo,sizeof(Perfinfo));
    SIZE_T CommitTotal=Perfinfo.CommitTotal;

    fprintf (pFile,"\n\n\nSYSTEM PERFORMANCE INFORMATION");
    fprintf (pFile,"\nCommitTotal=%d",Perfinfo.CommitTotal);
    fprintf (pFile,"\nCommitLimit=%d",Perfinfo.CommitLimit);
    fprintf (pFile,"\nCommitPeak=%d",Perfinfo.CommitPeak);
    fprintf (pFile,"\nPhysicalTotal=%d",Perfinfo.PhysicalTotal);
    fprintf (pFile,"\nPhysicalAvailable=%d",Perfinfo.PhysicalAvailable);
    fprintf (pFile,"\nSystemCache=%d",Perfinfo.SystemCache);
    fprintf (pFile,"\nKernelTotal=%d",Perfinfo.KernelTotal);
    fprintf (pFile,"\nKernelPaged=%d",Perfinfo.KernelPaged);
    fprintf (pFile,"\nKernelNonpaged=%d",Perfinfo.KernelNonpaged);
    fprintf (pFile,"\nPageSize=%d",Perfinfo.PageSize);
    fprintf (pFile,"\nHandleCount=%d",Perfinfo.HandleCount);
    fprintf (pFile,"\nProcessCount=%d",Perfinfo.ProcessCount);
    fprintf (pFile,"\nThreadCount=%d",Perfinfo.ThreadCount);




    OSVERSIONINFOEX   OSINFO;

    OSINFO.dwOSVersionInfoSize = sizeof(OSVERSIONINFOEX);
    GetVersionEx((LPOSVERSIONINFOA) &OSINFO);
    fprintf (pFile,"\n\n\nWINDOWS VERSION INFO");
    fprintf (pFile,"\ndwMajorVersion:%d",OSINFO.dwMajorVersion);
    fprintf (pFile,"\ndwMinorVersion:%d",OSINFO.dwMinorVersion);
    fprintf (pFile,"\ndwBuildNumber:%d",OSINFO.dwBuildNumber);
    fprintf (pFile,"\ndwPlatformId:%d",OSINFO.dwPlatformId);
    // fprintf (pFile,"\nszCSDVersion[128]:%s",OSINFO.szCSDVersion[128]);
    fprintf (pFile,"\nwServicePackMajor:%d",OSINFO.wServicePackMajor);
    fprintf (pFile,"\nwServicePackMinor:%d",OSINFO.wServicePackMinor);
    fprintf (pFile,"\nwSuiteMask:%d",OSINFO.wSuiteMask);
    fprintf (pFile,"\nwProductType:%d",OSINFO.wProductType);
    fprintf (pFile,"\nwReserved:%d",OSINFO.wReserved);

    fclose (pFile);

}


DWORD Lowmemorynotification()    
{
    LMHandle = CreateMemoryResourceNotification(Low);
    HMHandle = CreateMemoryResourceNotification(High);
    state=QueryMemoryResourceNotification(LMHandle, &ResourceState);
    if (state==1)
    {
        printf("QueryMemoryResourceNotification Created for Low Memory pressure");
        printf("\nYou will be signaled when there is low MemoryResourceNotification");
    }
loop:

    x=x+1;
    WaitForSingleObject( LMHandle,INFINITE);
    _strdate( dateStr);
    _strtime( timeStr );
    printf("%d",x);
    printf("Low Memory Notification received on  %s  %s  ", dateStr,timeStr);
    processmemory();
    printf("  Memory status is printed to Memorystatus.txt \n");
    Sleep(3000); 
    goto loop; 

    return 1;
};


void main()

{
    THandle=CreateThread( NULL, 8388608,(LPTHREAD_START_ROUTINE)Lowmemorynotification,NULL,0,NULL);

    //8388608=>Stack size in bytes which is 1 mb
    //    Lowmemorynotification();


    if (THandle==NULL)
    {
        printf("Create thread failed",GetLastError());
    }

    else
    {
        printf("\nSuccess");
        WaitForSingleObject(THandle,INFINITE);
    }

}

Thanks

Karthick P.K

Posted in Memory, Programming, SQL Server Engine | Tagged: , , , , , | 3 Comments »

SQL Server: Table Variables (VS) Temp Tables

Posted by Karthick P.K on January 15, 2009

Table Variables (VS) Temp Tables

SQLServer2005 caches temp tables and temp variables only under some conditions.
Scenarios where temp table/variable are not cached (see below) may cause performance degradation as compared to SQLServer2000.

Following are scenarios where temp table/variable are not cached:
1. select into #t
2. alter table #t
3. create index on #t
4. Global temp tables (##t)
5. Local temp tables on adhoc level (nest level 0)
6. table variables are also not cached for dynamic SQL.

What are some of the drawbacks of table variables?

These are some of the drawbacks as compared to temporary tables:

Table variables do not maintain statistics like temporary tables can. Statistics cannot be created on table variables through automatic creation or by using the CREATE STATISTICS statement.

Therefore, for complex queries on large tables, the lack of statistics may deter the optimizer to determine the best plan for a query, thus affecting the performance of that query.

The table definition cannot be changed after the initial DECLARE statement.
Tables variables cannot be used in a INSERT EXEC or SELECT INTO statement.
CHECK constraints, DEFAULT values, and computed columns in the table type declaration cannot call user-defined functions.
You cannot use the EXEC statement or the sp_executesql stored procedure to run a dynamic SQL Server query that refers a table variable, if the table variable was
created outside the EXEC statement or the sp_executesql stored procedure because table variables can be referenced in their local scope only, an EXEC statement and
a sp_executesql stored procedure would be outside the scope of the table variable.

However, you can create the table variable and perform all processing inside the EXEC statement or the sp_executesql stored procedure because then the table
variables local scope is in the EXEC statement or the sp_executesql stored procedure.

Are table variables memory-only structures that are assured better performance as compared to temporary or permanent tables, because temporary or permanent tables
are maintained in a database that resides on the physical disk and also logged?

A table variable is not a memory-only structure. Because a table variable might hold more data than can fit in memory, it has to have a place on disk to store
data. Table variables are created in the tempdb database similar to temporary tables. If memory is available, both table variables and temporary tables are
created and processed while in memory (data cache).

Do I have to use table variables instead of temporary tables?

The answer depends on these three factors:
The number of rows that are inserted to the table and are they joined with other tables.
The number of recompilations the query is saved from.
The type of queries and their dependency on indexes and statistics for performance.

 

Option recompile can help optimizer to estimate the number of rows table variable  refer http://blogs.msdn.com/b/psssql/archive/2010/08/24/query-performance-and-table-variables.aspx 

 

If you liked this post, do like us on Facebook at https://www.facebook.com/mssqlwiki and join our Facebook group https://www.facebook.com/mssqlwiki#!/groups/454762937884205/

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

Posted in Memory, Performance, SQL Query, SQL Server Engine | Tagged: , , , | 6 Comments »

 
%d bloggers like this: