March 11, 2014 at 2:54 am
Hi,
I've migrated a database from Oracle 11.1.0.7 to SQL Server 2008R2, using the application editor's migration tool. Since it's in production, we have been experiencing performance issues.
First of all, we planned on a 4Gb ram (where 1Gb was enough for the Oracle version). We had to double it.
I set the "optimized for ad hoc workload", just in case, but it didn't have the expected effect.
End users are still complaining are it's been a whole month since the migration has been completed.
The main component steeling pages to the buffer pool is CACHESTORE_SQLCP, and, as you can see below, the most expensive type of objects in this cache is for Prepared plans.
-- TOP MEMORY CONSUMING COMPONENTS
SELECT TOP 3
/*@@SERVERNAME AS 'Server',
GETDATE() AS 'DateStamp',*/
LEFT([name], 20) AS [name],
LEFT([type], 20) AS [type],
SUM([single_pages_kb] + [multi_pages_kb])/1024 AS cache_mb,
SUM([entries_count]) AS No_Entries
FROM sys.dm_os_memory_cache_counters
--WHERE TYPE IN ('CACHESTORE_SQLCP','CACHESTORE_PHDR','CACHESTORE_OBJCP')
GROUP BY [type], [name]
ORDER BY cache_mb DESC
SQL PlansCACHESTORE_SQLCP259036208
Object PlansCACHESTORE_OBJCP158302
Bound TreesCACHESTORE_PHDR57682
-- single use plans
SELECT cp.objtype, sum(cp.size_in_bytes) / 1024 /1024 as [Total Mb]
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE cp.cacheobjtype = N'Compiled Plan'
AND cp.objtype IN(N'Adhoc', N'Prepared')
AND cp.usecounts = 1
group by cp.objtype
ORDER BY 2 DESC
OPTION (RECOMPILE);
Prepared823
Adhoc4
Am I in the wrong way in my seek for performance improvement ? Do you have any idea/query that will help ?
I have also attached to this topic an overview of performances and memory that I got with dbWarden tool.
Thanks in advance.
March 11, 2014 at 11:09 am
Is this 32 bit or 64 bit system ? What are your memory settings ?
How frequently do you do index rebuilds and update stats ? Do you have MAXDOP configured on the server ?
--
SQLBuddy
March 11, 2014 at 1:54 pm
Hi, it's a 64 bits system, Windows Server 2008 R2 SP1.
The memory settings are Min memory = 4Gb, Max Memory = 7Gb.
Index rebuild is done every day. The statistics are set to be taken synchronously. For index rebuild, I use MaintenanceSolution. I have considered using it also for statistics updates, but haven't taken the time yet to do so...
March 11, 2014 at 2:51 pm
jelias 69485 (3/11/2014)
Hi, it's a 64 bits system, Windows Server 2008 R2 SP1.The memory settings are Min memory = 4Gb, Max Memory = 7Gb.
Index rebuild is done every day. The statistics are set to be taken synchronously. For index rebuild, I use MaintenanceSolution. I have considered using it also for statistics updates, but haven't taken the time yet to do so...
It looks like lot of memory is being consumed by SQLPlans and that too by prepared plans majorly. Nothing much can be done unless you change them to SPs and this need application changes.
You can do these as a relief ..
1. Use Optimize for Adhoc Workloads option (which you already have )
2.You may benefit by periodically clearing the Adhoc plan cache. You can schedule a job to accomplish this.
USE master;
GO
DBCC FREESYSTEMCACHE('SQL Plans');
GO
This clears only SQLplans not other plans.
--
SQLBuddy
March 12, 2014 at 1:27 am
Ok, thank you for your analysis.
March 12, 2014 at 8:07 am
You are welcome, Jelias 🙂
--
SQLBuddy
September 8, 2015 at 5:49 pm
please clarify periodically?
September 8, 2015 at 11:13 pm
Hi,
the situation hasn't changed ... The values I posted a while ago are still the same.
I think that this simply requires application code rewrite... which is not really something we can do...
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply