SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


What process is consuming my VAS Reservation


What process is consuming my VAS Reservation

Author
Message
sqlserverdba1316
sqlserverdba1316
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 35
I am encountering an error on one of our environments where further logins are not allowed anymore. Only way to resolve the problem is to restart the SQLServer Instance.

Different SQL Memory Changes were done on the instance from Enabling/Disabling AWE, changing min and max memory to adding -g to the startup option.

Environment is 32bit, SQL Server 2005 SP3. Memory on the server is 4GB.

After reading several articles, I started monitoring the VAS reservation. I used the query stated on this link http://sqlblogcasts.com/blogs/christian/archive/2008/01/07/sql-server-memtoleave-vas-and-64-bit.aspx

Currently AWE is enabled on the instance with max memory of 2.4GB. The -g512 was added to the startup parameter. However, it only prolonged the error being encountered. Usually, the error occurs(further logins not allowed) when Max Free Size goes down to 2MB.

From what I read, the resources/processes that is using the VAS are the ff:
1) Any OLE/COM components loaded in SQL Server
2) Extended Stored Procedures
3) SQL Mail components
4) Any prepared documents using sp_xml_preparedocument
5) Linked Server Providers
6) Large Plans stored in Procedure Cache
7) Very frequent Backups also may cause MTL area depletion.
8) SQL CLR

Are there any DMVs I could check to pinpoint which of these components is consuming the VAS? Or are there procedures to troubleshoot the problem?

Any suggestions, links and ideas are welcome :-)

Thanks In advance
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37818 Visits: 14411
From Understanding the VAS Reservation (aka MemToLeave) in SQL Server by Jonathan Kehayias this shows you the clerks using VAS in detail:

SELECT type, virtual_memory_committed_kb, multi_pages_kb 
FROM sys.dm_os_memory_clerks
WHERE virtual_memory_committed_kb > 0 OR multi_pages_kb > 0



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
sqlserverdba1316
sqlserverdba1316
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 35
Thanks for the reply.

Did run the query and got the these result:

type virtual_memory_committed_kb multi_pages_kb
MEMORYCLERK_SQLBUFFERPOOL 6848 1144
OBJECTSTORE_LOCK_MANAGER 4096 0
MEMORYCLERK_SQLSTORENG 1152 56
MEMORYCLERK_SQLUTILITIES 240 0
MEMORYCLERK_SOSNODE 0 6072
MEMORYCLERK_SOSNODE 0 2800
MEMORYCLERK_SQLGENERAL 0 2456
CACHESTORE_SQLCP 0 344
MEMORYCLERK_SQLSERVICEBROKER 0 192
CACHESTORE_OBJCP 0 112
MEMORYCLERK_SQLOPTIMIZER 0 88
MEMORYCLERK_HOST 0 64
MEMORYCLERK_SNI 0 16
MEMORYCLERK_SNI 0 16
CACHESTORE_STACKFRAMES 0 8
OBJECTSTORE_SNI_PACKET 0 8
OBJECTSTORE_SNI_PACKET 0 8
OBJECTSTORE_SNI_PACKET 0 8
OBJECTSTORE_SNI_PACKET 0 8
OBJECTSTORE_SNI_PACKET 0 8
OBJECTSTORE_SNI_PACKET 0 8
CACHESTORE_STACKFRAMES 0 8
OBJECTSTORE_SNI_PACKET 0 8
OBJECTSTORE_SNI_PACKET 0 8
OBJECTSTORE_SNI_PACKET 0 8
OBJECTSTORE_SNI_PACKET 0 8
OBJECTSTORE_SNI_PACKET 0 8
OBJECTSTORE_SNI_PACKET 0 8

From the information gathered, how could I actually pinpoint which processes is consuming VAS. Which clerks actually relates to dblinks, CLR, etc.

Thanks in advance.
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37818 Visits: 14411
Nothing is really standing out as holding onto a large amount of virtual memory at the moment. You can also have this issue if the VAS becomes fragmented and there no way to defragment it short of rebooting. What are you using to do backups? Are you running any third-party products or extended stored procedures on the server?

The best move really would be to move onto a 64-bit platform.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
sqlserverdba1316
sqlserverdba1316
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 35
Thanks again for the reply...

We don't have 3rd Party backup software as we are using Maintenance Plans for our backup. As with extended stored procedures also. Will have to verify this one. Is there any view to check if this is actually being used?

What we have are DBLinks to SQL server, Oracle and DB2 databases. I am suspecting this is eating up the VAS.
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37818 Visits: 14411
sqlserverdba1316 (1/20/2013)
Thanks again for the reply...

We don't have 3rd Party backup software as we are using Maintenance Plans for our backup. As with extended stored procedures also. Will have to verify this one. Is there any view to check if this is actually being used?


USE master;
SELECT * FROM sys.extended_procedures;



What we have are DBLinks to SQL server, Oracle and DB2 databases. I am suspecting this is eating up the VAS.

Use of Linked Servers can easily be the cause of something like what you're seeing. I have dealt with the problem you are seeing on 32-bit SQL 2005 SP4 and it was traced to a combination of third-party extended stored procedures (XSP) that were part of the backup software being used and Linked Server usage. I could show that after reboot an initial select from one of the Linked Servers on the instance (to a MySQL instance) would drop the largest VAS chunk avaialble significantly. To mitigate the issue the server was rebooted weekly every weekend. The final resolution was to upgrade to a 64-bit version of SQL Server.


edit: fix quote tags

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search