What is the difference between ad hoc and prepared query in sql server plan cache? -
i’m trying understand plan cache content of sql server.
so questions are:
1. difference between ad hoc , prepared plans?
2.what should know when trying optimize sql server plan cache?
what difference between ad hoc , prepared plans?
adhoc query:
select * t1 prepared query:
queries substitute place holders in place of actual values called prepared statements.
some examples:
select * t1 id=@id one more example taken wikipedia:
command.commandtext = "select * users username = @username , room = @room"; command.parameters.addwithvalue("@username", username); command.parameters.addwithvalue("@room", room); what should know when trying optimize sql server plan cache?
there whitepapers written how optimize plan cache.so try keep little..
normally when query executed against sql ,sql compiles plan , stores in plan cache .this plan cache memory taken buffer pool , different versions have different restrictions on how amount of memory used
you know memory precious resource , no amount of hardware enough if have leaks..
assume submit queries once or twice , tend submit queries lot.sql store plan of queries in plan cache bloats plancache bad
there different dmvs in digging through plan cache..
query find different type of objects in plan cache :
select objtype,count(*) countt,sum(size_in_bytes)*1024.0 memoryinkb sys.dm_exec_cached_plans group objtype adhoc,prepared queries bloating plancache , used once:
select q.query_hash, q.number_of_entries, t.text sample_query, p.query_plan sample_plan (select top 20 query_hash, count(*) number_of_entries, min(sql_handle) sample_sql_handle, min(plan_handle) sample_plan_handle sys.dm_exec_query_stats group query_hash having count(*) > 1 order count(*) desc) q cross apply sys.dm_exec_sql_text(q.sample_sql_handle) t cross apply sys.dm_exec_query_plan(q.sample_plan_handle) p to delete statements bloating plan cache:
declare @mb decimal(19,3) , @count bigint , @strmb nvarchar(20) select @mb = sum(cast((case when usecounts = 1 , objtype in ('adhoc', 'prepared') size_in_bytes else 0 end) decimal(12,2)))/1024/1024 , @count = sum(case when usecounts = 1 , objtype in ('adhoc', 'prepared') 1 else 0 end) , @strmb = convert(nvarchar(20), @mb) sys.dm_exec_cached_plans if @mb > 10 begin dbcc freesystemcache('sql plans') raiserror ('%s mb allocated single-use plan cache. single-use plans have been cleared.', 10, 1, @strmb) end else begin raiserror ('only %s mb allocated single-use plan cache – no need clear cache now.', 10, 1, @strmb) — note: warning message , not actual error. end go the above should give idea on start ,below must read topics , references :
1.http://www.sqlskills.com/blogs/kimberly/category/plan-cache/
3.https://technet.microsoft.com/en-us/library/dd672789(v=sql.100).aspx
4.must read article sqlcat on issues customer faced while using prepare statements
in referenced articles above,kimberely suggests enable optimize adhoc workloads option,but suggest test first.here interesting thread on dba.se
Comments
Post a Comment