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)
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |