SQL Server – Determine SQL Drivers Used by Application

In SQL Server 2008 R2 and earlier, the following allocations come from the MemToLeave (MTL) area:

• Linked servers
• XML documents
• Extended stored procedures
• sp_OACreate calls
• Query plans that exceed 8KB
• SQLCLR
• Backups that use a large value for MAXTRANSFERSIZE
• Using a network packet size over 8192 bytes

Information extracted from this article by Microsoft support engineer Amit Banerjee.

SQL Server 2012 uses the Any Size Page Allocator.

Further information is included in this Microsoft Knowledge Base article.

Multi-page allocations (MPA) also come from the MTL area, before SQL Server 2012. Whether an allocation made by SQL Server is made via the MPA or single page allocator (SPA) depends purely on the size of the allocation request. If it is 8KB or less, it is allocated via SPA and stolen from the buffer pool. If it is 8KB or more, MPA is used.

I know data page is always 8K so is there a situation where data/index page make use of MPA?

No, data and index pages are always cached in the buffer pool.

Is there a way to confirm that memory allocation for the execution plan is using multi page allocation?

Cached plans have a complex structure. Individual allocations within that structure are generally 8KB allocations, but certain queries (generally those that contain long IN lists or many parameters to a stored procedure call) can require an MPA allocation. These are comparatively rare. It is possible to query SQL Server DMVs to determine the split between SPA and MPA for a cached plan.

Audit events are actually not available through Extended Events. You would need to use SQL Server Audit, which more or less works on top of the Extended Events engine (at least from what I understand).

Steps to go with:

1. Create a Server Audit
2. Create a Server Audit Specification
3. Then go back and enable them.

It is fairly straight forward in using the UI via SSMS to create them but for purposes of this post, this would be the T-SQL to do the same thing:

USE [master]
GO

CREATE SERVER AUDIT [cmdshell-20150603-124954]
TO FILE 
(   FILEPATH = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL12\MSSQL\Log\' )
WHERE object_name = 'xp_cmdshell';
GO

CREATE SERVER AUDIT SPECIFICATION [Audit-cmdshell]
FOR SERVER AUDIT [cmdshell-20150603-124954]
ADD (SCHEMA_OBJECT_ACCESS_GROUP);
GO

Then when you go back and view the Audit Collection it you would see something similar to this:

In addition with regard to the audit being turned off you can create a seperate server audit that will specifically log when any SQL Server Audit is disabled or started. This will at most give you who stopped it.