11g – Maintenance Tasks\Autotasks

In Oracle10g they were created as separate jobs and visible in DBA_SCHEDULER_JOBS.JOB_NAME.

This has changed in Oracle 11g. The related view is DBA_AUTOTASK_WINDOW_CLIENTS.

--Get Autotask Job Schedules
--retrieve Autotask History

More details 11g: Scheduler Maintenance Tasks or Autotasks [ID 756734.1]


On investigating memory pressure on a SQL Server, i started looking into the cached plans and whether memory was being utilised badly by the plan cache.

Here are some queries i used to see whether plans were being used once only.

-- How many plans are in the cache
SELECT COUNT(*) FROM sys.dm_exec_cached_plans;
-- How much space is being used by the plan cache
SELECT (SUM(size_in_bytes)/1024)/1024 as 'MB'  
FROM sys.dm_exec_cached_plans;
-- Returns a breakdown of the memory used by all compiled plans in the cache.
SELECT plan_handle, ecp.memory_object_address AS CompiledPlan_MemoryObject, 
    omo.memory_object_address, pages_allocated_count, type, page_size_in_bytes 
FROM sys.dm_exec_cached_plans AS ecp 
JOIN sys.dm_os_memory_objects AS omo 
    ON ecp.memory_object_address = omo.memory_object_address 
    OR ecp.memory_object_address = omo.parent_address
WHERE cacheobjtype = 'Compiled Plan';
-- Plans which have only been used once
SELECT * FROM sys.dm_exec_cached_plans 
WHERE objtype = 'ADHOC' and usecounts < 2;
-- This statement will show you how much of your cache is 
-- allocated to single use plans
SELECT objtype AS [CacheType]
        , count_big(*) AS [Total Plans]
        , sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs]
        , avg(usecounts) AS [Avg Use Count]
        , sum(cast((CASE WHEN usecounts = 1 
        THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024/1024 
                AS [Total MBs - USE Count 1]
        , sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) 
                AS [Total Plans - USE Count 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs - USE Count 1] DESC

The output from the above is useful in analysing how much space is being consumed in memory by plans which have only ever been used once.


The result set above shows a large quantity of all the plans in the cache have only been used once (11074) and are consuming 793 MB!

--Details for plans with usecount of 1
SELECT    bucketid, refcounts, usecounts, (size_in_bytes)/1024 as 'Size in KB', 
        cacheobjtype, objtype, [text] 
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE objtype = 'ADHOC' and usecounts < 2;

As the below graphic shows, a lot of the single use plans are the same, apart from the ID criteria in the WHERE clause. If this happens, then it suggests that there are a lot of SQL compilations\sec which means that SQL Server Is having to build execution plans on the fly – a relatively CPU Intensive. This usually means SQL is being compiled dynamically by the application which can be seen in sys.dm_exec_cached_plans. For example:


So we have thousands of compiled ad hocs queries which are technically the same with different ID’s. To maintain the same plan, paramatize the query:

DECLARE @id bigint
Set @ID = 813232101024130

UPDATE tbl SET sold=1, lastChecked=GetTimeStamp() WHERE ID=@ID

On Investigation, SQL compilations per second are around 34% of the Batch Requests per second. Microsoft’s SQL Customer Advisory Team’s Top SQL 2005 OLTP Performance Issues says that if Compilations/sec is more than 10% of Batch Requests/sec, you may be experiencing CPU pressure because SQL Server has to build execution plans. However, on this occasion i didn’t believe that the server was under CPU pressure (Based on other performance stats) and also because the actual batches per second were quite low.


Starting from SQL Server 2008, there is an option to optimize the cache for workloads which have a large percentage of Adhoc Compiled plans – or in other words, one use plans.

When this option is set to 1, the Database Engine stores a small compiled plan stub in the plan cache when a batch is compiled for the first time, instead of the full compiled plan. This helps to relieve memory pressure by not allowing the plan cache to become filled with compiled plans that are not reused.

Setting the optimize for ad hoc workloads to 1 affects only new plans; plans that are already in the plan cache are unaffected.

sp_configure 'optimize for ad hoc workloads', 1;


Does more or less the same as sys.dm_exec_cached_plans. It is a SQL 2000 system table which is included as a view in 2005/2008 for backward compatibility.

4 different DMV’s now do the job of SYSCACHEOBJECTS. These are:





Retrieving Server/Machine Name in T-SQL

Problem: Some automated backups run on a SQL Server 2008 instance which is sitting on a VM which is linked to a 2 node Physical Windows Cluster. We generate the backup location through UNC so the path can alter and we save the completed backup path to a backup_status table. This table is then used by non live servers to pick up the location of the latest backup so that they can automatically refresh.


Different options for selecting machine names

select HOST_NAME()

HOST_NAME will return the name of the workstation that you are logged in to. So if logged into the Virtual Server, then MYVMSERVER01 will be returned

select SERVERPROPERTY('ComputerNamePhysicalNetBIOS')

The above server property will return the NetBIOS name of the local computer on which the instance of SQL Server is currently running. For a clustered instance of SQL Server on a failover cluster, this value changes as the instance of SQL Server fails over to other nodes in the failover cluster.

For the example in the diagram, it will currently return MYSERVER01A when run on the MYVMSERVER01. If the SQL Cluster Fails over, then it will return MYSERVER01B.

select SERVERPROPERTY('MachineName')

MachineName returns the Windows computer name on which the server instance is running. For a clustered instance, an instance of SQL Server running on a virtual server on Microsoft Cluster Service, it returns the name of the virtual server.

For the exmaple in the diagram, It will retrun MYVMSERVER01 regardless of which physical server the VM is running off.

select SERVERPROPERTY('ServerName')

Will return the server and instance name. For the example in the diagram, it will return MYVMSERVER01\SQL01 regardless of where it is run from.

Querying SQL Agent Job History

Really useful SQL if you ever need to use Job history to build graphs etc of job durations.

select DISTINCT sysjobs.name, 
(sysjobhistory.run_duration/60) as 'Minutes',
CASE sysjobhistory.run_status 
WHEN 1 THEN 'Success'
WHEN 3 THEN 'Cancelled'
CASE LEN(sysjobhistory.run_time) 
            when NULL then cast (('N/A')as char (8))
            when 1 then cast(('00:00:0') + RIGHT(sysjobhistory.run_time,1) as char (8))
            when 2 then cast(('00:00:')
                + Left(sysjobhistory.run_time,2) as char(8))
            when 3 then cast('00:0' 
                + Left(right(sysjobhistory.run_time,3),1)  
                +':' + right(sysjobhistory.run_time,2) as char (8))
            when 4 then cast('00:'
                + Left(right(sysjobhistory.run_time,4),2)
                +':' + Left(right(sysjobhistory.run_time,2),4)as char (8))
            when 5 then cast('0' + Left(right(sysjobhistory.run_time,5),1) 
                +':' + Left(right(sysjobhistory.run_time,4),2)  
                +':' + right(sysjobhistory.run_time,2) as char (8))
            when 6 then cast(Left(right(sysjobhistory.run_time,6),2) 
                +':' + Left(right(sysjobhistory.run_time,4),2)  
                +':' + right(sysjobhistory.run_time,2) as char (8))
from sysjobs, sysjobhistory
where sysjobs.job_id = sysjobhistory.job_id
and sysjobs.name = 'job_name'
--and sysjobhistory.step_id = 3
order by run_date ASC, run_time ASC

The DELETE statement conflicted with the REFERENCE constraint "FK_subplan_job_id"

Issue: When trying to delete a Scheduler job which was created as part of a maintenance plan, we get the following error

TITLE: Microsoft SQL Server Management Studio ------------------------------ Drop failed for Job 'manual_db_backups.Subplan_1'. (Microsoft.SqlServer.Smo) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+Job&LinkId=20476 ------------------------------ ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ The DELETE statement conflicted with the REFERENCE constraint "FK_subplan_job_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'job_id'. The statement has been terminated. (Microsoft SQL Server, Error: 547) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=547&LinkId=20476

We were getting the error even after removing the maintenance plan from SSIS.

To Resolve I did the following:


-- View the Maintenance plan subplans 
select * from sysmaintplan_subplans

-- View the Maintenance plan logs 
select * from sysmaintplan_log

To Delete the subplan:


--Delete the Log history for the maintenance plan affected 
DELETE FROM sysmaintplan_log
WHERE subplan_id in 
  ( SELECT Subplan_ID from sysmaintplan_subplans
    -- change Subplan name where neccessary 
  WHERE subplan_name = 'Subplan_1' ) 

-- Delete the subplan 
DELETE FROM sysmaintplan_subplans
WHERE subplan_name = 'Subplan_1'