Tuesday, March 24, 2015

What causes Multi-Page allocations?

In SQL Server versions before SQL 2012 single page allocations and multi-Page allocations are handled by different components, the Single Page Allocator (which is responsible for Buffer Pool allocations and governed by 'max server memory') and the Multi-Page allocator (MPA) which handles allocations of greater than an 8K page. If there are many multi-page allocations this can affect how much memory needs to be reserved outside 'max server memory' which may in turn involve setting the -g memory_to_reserve startup parameter.

So what kinds of query result in MPA activity? Here are few scenarios

1. A workload that has stored procedures with a large # of parameters (say > 100, > 500), and then invoked via large ad hoc batches, where each SP has different parameters will result in a plan being cached for this “exec proc” batch. This plan will result in MPA.

clip_image001

2. Another workload would be large adhoc batches of the form:

clip_image002

In SQL 2012 all page allocations are handled by an "Any size page allocator" and included in 'max server memory'. The buffer pool effectively becomes a client of the any size page allocator, which in turn relies on the memory manager.

SQL Maintenance Plan error “Could not generate mail report.An exception occurred while executing a Transact-SQL statement or batch.No global profile is configured. Specify a profile name in the @profile_name parameter.”

Some body have this error : Could not generate mail report.An exception occurred while executing a Transact-SQL statement or batch.No global profile is configured. Specify a profile name in the @profile_name parameter. When i execute my maintenance plan.

 

Resolution

The issue is related to the default email profile, you have to set a default email profile in order for the maintenance plan send the email alert.

 

Please Follow These steps to Resolve this Problem

1. Go to Database mail

2. Right Click  Click on Configure Database mail.

3. wizard will click next

4.select Manage Profile security

5. Click Next

6. Beside Profile name  by default there will be no , Please make it yes

7. Finish