Sqlserver-dba.com

SQL server OLE Automation memory pressure

Focus on the following areas:

1)OLE Automation usage that is causing VAS pressure\fragmentation

2)Excessive Linked Server Usage

3)Custom Extended Stored Procedure

4)3rd Party Backup Application (such as  Redgate SQL Backup) using to large of a Network Packet Size.

5)Your applications are connecting with a Network Packet Size exceeding 8KB and going into VAS/Multi-page allocations. 

6)Adhoc (non-parameterized) workload leading to  plan cache expansion

7)SQL CLR usage that is causing VAS Pressure\fragmentation

 

Potential approaches for the above are:

1,2,3,7)Try pushing up the VAS reservation by using the -g option.Also for linked server , plan to run out of process

4)Tune the applications to minimise VAS pressure - work with the vendor , who may have knowledge

5)Use sys.dm_exec_connections and report on network packet size greater than 4096 - Further details below

6)Consider FORCED parametisation - this will disguise the problem , consider returning to developers for development using parameterized queries

 

 

Make the relevant change - monitor the server performance for a few days and check if the issue appears again

 

 

Query to see if you have an application that is connecting with a network packet size of 8KB which requires multi-page allocations from VAS. 

If you do, change the connection string to include (Packet Size=4096)

 

select s.session_id, s.host_name, s.host_process_id, s.program_name, c.net_packet_size

from sys.dm_exec_connections c

join sys.dm_exec_sessions s on c.session_id = s.session_id

where net_packet_size >= 8000

Ref:Jack Vamvas (http://www.sqlserver-dba.com)

 

  


Author: Jack Vamvas (http://www.sqlserver-dba.com)

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact