테스트 상에서는 큰 문제가 보이지 않고 효과도 입증이 되었지만... 실 서비스에서는 어떤 Side Effect가 있을지 ...
확실히 테스트시에는 효과가 좋습니다.
좀 여유가 있는 시스템에 적용 후 추이를 봐가면 부하가 큰 시스템에 순차적 적용하면 좋을 듯 합니다.
출처 : http://www.sqlskills.com/blogs/kimberly/post/procedure-cache-and-optimizing-for-adhoc-workloads.aspx
Plan cache and optimizing for adhoc workloads
I mentioned that servers receiving a lot of dynamic constructs (typically those generated by client SQL generaters/ORM/LINQ, etc.) can start to consume too much plan cache and have problems with "single-use plans" in my last post titled: Statement execution and why you should use stored procedures. I also mentioned that SQL Server 2008 has an option/feature specifically to help reduce the bloat on the plan cache by only storing a query plan hash the first time a plan is created.
First - let's check to see how your plan cache is currently allocated:
(note: updated to decimal(18,2) as a few of you had overflow errors due to high use counts!)
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
go
This statement will show you how much of your cache is allocated to single use plans... and, I'd love to hear what your numbers are... this query works in 2005 and 2008; however, the primary solution I'm describing here (optimize for adhoc workloads) will only work in SQL Server 2008.
If you have a lot of your cache going to plans that are only executed once, then it's time to clean up the cache and take better advantage of it with plans that are more consistent and more stable. Ideally, this means using more stored procedures and writing these stored procedures effectively for better performance (I'm currently in the midst of doing this in my Optimizing Procedural Code category). However, if you absolutely must use a lot of adhoc SQL, then you should consider upgrading and turning on this new option. It's an advanced configuration option so you won't see it until you "show advanced options" and it's set using sp_configure. There have been some other really good posts out there on how to use this and what this is so I'm just going to bring together some great resources for you to read. The most important post to read (and it's especially important for those on you on versions of SQL Server 2000 or SQL Server 2005 RTM/SP1 [er... why aren't you on SP2?]) is that plan cache can get out of control. 2005 SP2 and 2008 reduce the total size but there no upper limit (which is again - a GREAT reason for the addition of "optimize for adhoc workloads"). Kalen Delaney talks about how things really work in her SQL Server 2005 SP2 post titled: Did You Know? SP2 does NOT limit the amount of plan cache you can have (key word there is LIMIT). Again, SQL Server doesn't set an upper limit but it does [drastically] reduce the total size that's possible (as of SP2). As for even more details on plan caching, recompilation and SQL Server 2008's better cache control - check out Greg Low's (blog|twitter) whitepaper titled: Plan Caching in SQL Server 2008. Taken STRAIGHT from the first section of the whitepaper:
SQL Server 2008 and SQL Server 2005 SP2
* 75% of visible target memory from 0-4GB + 10% of visible target memory from 4Gb-64GB + 5% of visible target memory > 64GB
SQL Server 2005 RTM and SQL Server 2005 SP1
* 75% of visible target memory from 0-8GB + 50% of visible target memory from 8Gb-64GB + 25% of visible target memory > 64GB
SQL Server 2000
* SQL Server 2000 4GB upper cap on the plan cache
Finally, lots of additional posts on this topic will give you even more details:
- Bob Beauchemin (blog) did a simple post on this while it was still in RC0 here: Performance features in SQL Server 2008 RC0 - Optimize for Adhoc Workloads.
- Jose Barreto (blog|twitter) did a simple post on this and pulls together a few nice resources as well here: SQL Server 2008 Optimize for Ad Hoc Workloads.
- Kalen Delaney (blog) did a post on how SQL Server 2008's metadata returns only 17 characters when "Compiled Plan Stub" is actually 18... and, how to correctly see it here: Geek City: Reducing Cache Bloat and a Metadata Bug. Note: this bug has been fixed in SQL Server 2008 SP1.
- Kalen Delaney wrote an article on SQL Server Magazine (note: this article requires a SQL Server Magazine subscription) titled: Managing Plan Cache Size and after the article, she posted a Q&A on the questions she received from the article in a blog post titled: Geek City: Q & A on my Plan Cache Sizing article in SQL Server Magazine.
- Books online topics:
- Glenn [Alan] Berry (blog|twitter) has a post in his DMV A Day series on looking at the plan cache and seeing which statements are causing you grief. Once you see how much cache is being wasted with your single use plans (with the above query) - it's time to start looking and seeing what they are. His blog post is titled: A DMV A Day - Day 16.
However, I'm still really interested in seeing your numbers from the query above - let me know!
NEW/ADDITIONAL REQUEST: Let me know your max server memory setting as well as the total memory available on the box?
Thanks for reading!
kt
Read more: http://www.sqlskills.com/blogs/kimberly/post/procedure-cache-and-optimizing-for-adhoc-workloads.aspx#ixzz1l73uvfAb
출처 : http://blogs.msdn.com/b/joesack/archive/2011/03/28/measuring-impact-of-optimize-for-ad-hoc-workloads-via-cachestore-sqlcp.aspx
Measuring impact of “optimize for ad hoc workloads” via CACHESTORE_SQLCP
The cache store “CACHESTORE_SQLCP” represents cached ad-hoc query plans, server-side cursors and prepared statements. One way to gather memory allocation values for this specific cache store is by using the following query:
SELECT single_pages_kb, multi_pages_kb
FROM sys.dm_os_memory_clerks
WHERE type = 'CACHESTORE_SQLCP'
You can also see allocations to CACHESTORE_SQLCP via DBCC MEMORYSTATUS (example output below for one node):
CACHESTORE_SQLCP (node 0),KB
VM Reserved,0
VM Committed,0
Locked Pages Allocated,0
SM Reserved,0
SM Committed,0
SinglePage Allocator,12728
MultiPage Allocator,1968
There has already been much written about cache bloat due to ad hoc query plans and I won’t rehash it here (Kimberly Tripp has a few great posts on this topic – including one post that lists several posts from different authors on the subject). There are multiple ways to reduce this bloat (for example - move to using parameterized queries, force parameterization). One of the easier methods includes enabling the “optimize for ad hoc workloads” option.
The purpose of this post is to connect the dots between CACHESTORE_SQLCP and enabling the “optimize for ad hoc workloads" option (note that you can also use sys.dm_exec_cached_plans to see the impact on size_in_bytes by plan and reference the cacheobjtype of Compiled Plan versus Compiled Plan Stub). The following demonstration simply shows the impact of executing several ad hoc queries and measuring the significant allocation differences in CACHESTORE_SQLCP. In my testing – single_pages_kb was equal to 13,072 without plan stubs and then 760 after enabling the “optimize for…” option. This below demo is intended to be stepped through statement by statement:
-- Tested on 10.50.1765
-- This demo assumes optimize for ad hoc workloads is off
-- (And yes please only run this demo on a test environment)
USE [master]
GO
EXEC sp_configure 'show advanced options',1
RECONFIGURE
GO
EXEC sp_configure 'optimize for ad hoc workloads',0
RECONFIGURE
GO
EXEC sp_configure 'show advanced options',0
RECONFIGURE
GO
CREATE DATABASE [QueryBloat];
GO
USE [QueryBloat];
GO
CREATE TABLE dbo.Bloat (col01 uniqueidentifier);
GO
INSERT dbo.Bloat
VALUES (NEWID())
GO 500
-- Clear out adhoc queries, prior to baseline
DBCC FREESYSTEMCACHE('SQL Plans')
-- Take baseline
SELECT single_pages_kb
FROM sys.dm_os_memory_clerks
WHERE type = 'CACHESTORE_SQLCP'
-- In my case, I saw single_pages_kb = 120
-- Now let's make some bloat
DECLARE @NEWID varchar(36)
DECLARE curBloat CURSOR FOR
SELECT col01
FROM dbo.Bloat
ORDER BY col01
OPEN curBloat
FETCH NEXT FROM curBloat
INTO @NEWID;
EXEC ('SELECT col01 FROM dbo.Bloat WHERE col01 = ' + '''' +@NEWID + '''')
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM curBloat
INTO @NEWID;
EXEC ('SELECT col01 FROM dbo.Bloat WHERE col01 = ' + '''' +@NEWID + '''')
END
CLOSE curBloat;
DEALLOCATE curBloat;
-- Checking again, I see single_pages_kb = 13,072
SELECT single_pages_kb
FROM sys.dm_os_memory_clerks
WHERE type = 'CACHESTORE_SQLCP'
-- Now let's enable "optimize for ad hoc workloads"
EXEC sp_configure 'show advanced options',1
RECONFIGURE
GO
EXEC sp_configure 'optimize for ad hoc workloads',1
RECONFIGURE
GO
EXEC sp_configure 'show advanced options',0
RECONFIGURE
GO
-- Clear out adhoc queries for our second test
DBCC FREESYSTEMCACHE('SQL Plans')
-- Take baseline - I see single_pages_kb = 120
SELECT single_pages_kb
FROM sys.dm_os_memory_clerks
WHERE type = 'CACHESTORE_SQLCP'
-- Make some bloat again
DECLARE @NEWID varchar(36)
DECLARE curBloat CURSOR FOR
SELECT col01
FROM dbo.Bloat
ORDER BY col01
OPEN curBloat
FETCH NEXT FROM curBloat
INTO @NEWID;
EXEC ('SELECT col01 FROM dbo.Bloat WHERE col01 = ' + '''' +@NEWID + '''')
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM curBloat
INTO @NEWID;
EXEC ('SELECT col01 FROM dbo.Bloat WHERE col01 = ' + '''' +@NEWID + '''')
END
CLOSE curBloat;
DEALLOCATE curBloat;
-- Measuring impact - single_pages_kb = 760 (versus 13,072)
SELECT single_pages_kb
FROM sys.dm_os_memory_clerks
WHERE type = 'CACHESTORE_SQLCP'
'Brain Trainning > DataBase' 카테고리의 다른 글
[MSSQL] Replication 복제 오류 (0) | 2012.02.05 |
---|---|
[MSSQL] Microsoft iSCSI Software Target 사용하기 (0) | 2012.02.02 |
[MSSQL] Storage Top 10 Best Practices (펌) (0) | 2012.01.28 |
[MSSQL] The effect of TCP Chimney offload on viewing network traffic (펌) (0) | 2012.01.24 |
[MSSQL] TCP Chimney Offload - Windows 2003 (0) | 2012.01.24 |