1 / 21

Plan Guides

Plan Guides. Jeremy Lowell Data Realized Jeremy@DataRealized.com. Agenda . Who is it for? Why do they exist? What is it? Query processing Query Plans Plan Guides Demo. Overview. Plan Guides Why? Increased complexity in database environments today Linq to SQL

essien
Download Presentation

Plan Guides

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Plan Guides Jeremy Lowell Data Realized Jeremy@DataRealized.com

  2. Agenda • Who is it for? • Why do they exist? • What is it? • Query processing • Query Plans • Plan Guides • Demo

  3. Overview • Plan Guides • Why? • Increased complexity in database environments today • Linq to SQL • C.O.T.S. implementations • Commercial-off-the-shelf • Proactive vs. Reactive • Who? • Database Administrators • Database & BI Developers

  4. Plan Guides • What are they? • Plan Guides can be used to optimize the performance of queries when you cannot or do not want to change the text of the query directly. • Plan guides can be useful when a small subset of queries in a database application deployed from a third-party vendor are not performing as expected. • Plan guides influence optimization of queries by attaching query hints or a fixed query plan to them. -- Microsoft

  5. Query Plans • Query Processing

  6. Query Plans • Creation • One note of importance:: • SQL Server uses the optimal access plan, but query optimization is limited by what is possible. • Meaning… Even a great optimizer can’t always make poor code run efficiently. • Optimizer • Evaluates • Keys • Indexes • Clustered • Non-clustered • Cardinality (selectivity) • Quantity of data • Statistics • Etc… • Generates Query Plan • Execution context (parameter variables) • Stores Query Plan in Cache • This is where it gets FUN! • Known as Procedure Cache, Statement Cache, Plan Cache etc…

  7. Query Plans • Cache • Hard to “see” the cache in memory on a server… • dbcc proccache num proc buffs num proc buffs used num proc buffs active proc cache size proc cache used proc cache active -------------------- -------------------- --------------------- -------------------- -------------------- -------------------- 510917 230 230 18844 28 28 • num proc buffs • Total number of pages used by all entries in the procedure cache. • num proc buffs used • Total number of pages used by all entries that are currently being used. • num proc buffs active • For backward compatibility only. Total number of pages used by all entries that are currently being used. • proc cache size • Total number of entries in the procedure cache. • proc cache used • Total number of entries that are currently being used. • proc cache active • For backward compatibility only. Total number of entries that are currently being used.

  8. Query Plans • Cache • DMV’s • SELECT cacheobjtype,objtype, sum(refcounts) as TotalRefcounts, sum(usecounts) as TotalUseCounts from sys.dm_Exec_Cached_plans group by cacheobjtype,objtype cacheobjtype objtype TotalRefcounts TotalUseCounts -------------------------------------------------- -------------------- -------------- -------------- Parse Tree View 242 48049 Compiled Plan Proc 2044 9140155 Compiled Plan Trigger 75 149753 Extended Proc Proc 25 320641 Compiled Plan Adhoc 31907 294193 Parse Tree UsrTab 3 5782 Compiled Plan Prepared 3696 1951629 Parse Tree Check 13 213 • Refcounts • Number of cache objects that are referencing this cache object. • UseCounts • Number of times this object has been used since it has existed in cache • Cacheobjtype • Type of object in cache • Ojbtype • Type of object in cache

  9. Query Plans • Cache • DMV’s • Return attributes for a specific plan • SELECT plan_handle, refcounts, usecounts, size_in_bytes, cacheobjtype, objtype FROM sys.dm_exec_cached_plans; GO SELECT attribute, value, is_cache_key FROM sys.dm_exec_plan_attributes(<plan_handle>); • Set options • SELECT plan_handle, pvt.set_options, pvt.sql_handle FROM ( SELECT plan_handle, epa.attribute, epa.value FROM sys.dm_exec_cached_plans OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa WHERE cacheobjtype = 'Compiled Plan' ) AS ecpa PIVOT (MAX(ecpa.value) FOR ecpa.attribute IN ("set_options", "sql_handle")) AS pvt; • Optimizer info • select * from sys.dm_exec_query_optimizer_info • Memory breakdown of all cached complied plans • 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’ order by page_size_in_bytes desc

  10. Query Plans • Cache • DMV’s SELECT st.text, cp.usecounts, cp.size_in_bytes, cp.cacheobjtype, cp.objtype from sys.dm_exec_cached_plans cp cross apply sys.dm_exec_sql_text(cp.plan_handle) st order by cp.usecounts desc Sql_text usecounts size_in_bytes cacheobjtype objtype ---------------- ------------------ ------------------------ ------------------------ ------------ create procedure [dbo]… 358968 450560 Compiled Plan Proc CREATE PROC [dbo]… 308201 245760 Compiled Plan Proc CREATE PROC [dbo]… 290058 114688 Compiled Plan Proc CREATE PROCEDU [db 280376 565248 Compiled Plan Proc CREATE FUNC [dbo]… 280253 155648 Compiled Plan Proc (@P1 ntext,@P2 nvarch… 274484 40960 Compiled Plan Prepared (@p0 int)SELECT [t0].[… 233054 98304 Compiled Plan Prepared • sys.dm_exec_sql_text(cp.plan_handle) • Imagine the possibilities!!

  11. Query Plans • Cache • Top 10 …. • Physical Reads select top 10 substring(st.text, (qs.statement_start_offset/2) + 1, ((case statement_end_offset when -1 then datalength(st.text) else qs.statement_end_offset end - qs.statement_start_offset)/2) + 1) as statement_text, * from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.sql_handle) st where execution_count > 1 and dbid = 5 order by total_physical_reads desc • Max Physical Reads • order by max_physical_reads desc • Max Logical Reads • Order by max_logical_reads desc • Max Logical Writes • Order by max_logical_writes desc • Total Elapsed time • Max Elapsed time • Etc….

  12. Query Plans • Cache • Things to look for : • Determine if the adhoc or dynamic statements are generating plans that are re-used, or in this case, not being re-used. select Count (*), refcounts, usecounts, objtype, left(text,50) from sys.dm_Exec_cached_plans cross apply sys.dm_exec_sql_text(plan_handle) where cacheobjtype = 'Compiled Plan’ and objtype = 'Adhoc' and usecounts = 1 group by refcounts, usecounts, objtype, left(text,50) order by 1 desc Count Refcount Usecount objtype sql – first 50 characters…. 1670 2 1 Adhoc Select DISTINCT TOP 100 W………….. 1603 2 1 Adhoc SELECT TOP 1000 W………. 1538 2 1 Adhoc select distinct top 100 v.W……… • Means that there are 4811 plans for three nearly identical statements • Significant memory required for caching plans • Finite amount of cache for plans

  13. Query Plans • Cache • Life of… • Execution cache is re-cycled based on age, usage and statistics they are dependent upon • Engine will not always choose “best” plan; rather it will find a balance between the best plan and one quick to create and easy to maintain. • The algorithms to match new SQL statements to existing, unused execution plans in the cache require that all object references be fully qualified. For example, the first of these SELECT statements is not matched with an existing plan, and the second is matched: • SELECT * FROM Contact • SELECT * FROM Person.Contact • Execution plan is marked for de-allocation from cache when the following is true: • The memory manager requires memory and all available memory is currently being used. • The age field for the object is 0. • The object is not currently referenced by a connection. • http://msdn.microsoft.com/en-us/library/ms181055.aspx

  14. Query Plans • Cache • Recompile • Use Profiler to determine reason: BOL has great explanations. • Changes made to a table or view referenced by the query (ALTER TABLE and ALTER VIEW).Changes to any indexes used by the execution plan.Updates on statistics used by the execution plan, generated either explicitly from a statement, such as UPDATE STATISTICS, or generated automatically. • Dropping an index used by the execution plan. • An explicit call to sp_recompile. • Large numbers of changes to keys (generated by INSERT or DELETE statements from other users that modify a table referenced by the query). • For tables with triggers, if the number of rows in the inserted or deleted tables grows significantly. • Executing a stored procedure using the WITH RECOMPILE option.

  15. Query Plans • Cache • Parameterization • Simple (Default) • SQL 2k – known as Auto-Parameterization • Enables the engine to better match queries and their plans • SQL Server parameterizes a relatively small set of queries • Example : • SELECT * FROM AdventureWorks2008.Production.Product WHERE ProductSubcategoryID=1; • This query will be parameterized by the engine at compile time. • SELECT * FROM AdventureWorks.Production.Product WHERE ProductSubcategoryID = 4; • This query will use the same plan. • More complex statements may not be parameterized. • Statements which reference more than one table. • Delete or update with a from clause • Many more … • Forced

  16. Query Plans • Cache • Parameterization • Forced Parameterization • Forces all statements to be parameterized. • Any literal value will be parameterized with many exceptions. http://msdn.microsoft.com/en-us/library/ms175037.aspx • Xquery, Cursors, ANSI_PADDING or ANSI_NULLS set to OFF. • The TOP, TABLESAMPLE, HAVING, GROUP BY, ORDER BY, OUTPUT...INTO, or FOR XML clauses of a query; Statements that reference variables, ETC…. • Alter Database <name> SET parameterization FORCED / SIMPLE • Decreases compilations – increases concurrency • Quite a few “does this”, “does not do this” set’s of rules. • Ensure that you test it on your unique situation and reference the link above.

  17. Plan Guides • Creation • Context • OBJECT Plan Guide • Stored proc’s, UDF’s, Triggers (dml), CTE’s, etc… • SQL Plan Guide • t-sql statements (dynamic, ad-hoc, prepared etc…) • Template Plan Guide • Standalone queries that parameterize to a specified form. • These can OVERRIDE the parameterization setting. • The PARAMETERIZATION database option is SET to FORCED, but there are queries you want compiled according to the rules of simple parameterization. • The PARAMETERIZATION database option is SET to SIMPLE (the default setting), but you want forced parameterization to be attempted on a class of queries. (http://msdn.microsoft.com/en-us/library/ms190417.aspx)

  18. Plan Guides • Demo • Create an object plan guide • Disable the Plan Guide • Enable the Plan Guide • Drop the Plan Guide • View the Plan Guides • Create a SQL plan guide • Create a Template plan guide • Create a SQL plan guide using XML_SHOWPLAN • Create a Plan guide against an existing query plan.

  19. Plan Guides • Profiler • Capture event • SQL:BatchStarting • Extract event data to a file ** Open in Notepad • Must be exact spacing • Exception is to escape ‘ ‘s • Demo … • Validation • Profiler • Performance category >> Plan_Guide_Successful & Plan_Guide_Unsuccessful • Perfmon • Guided Plan Executions/sec & MisGuided Plan Executions/sec

  20. Questions

  21. Your Feedback is Important Please fill out a session evaluation form and either put them in the basket near the exit or drop them off at the conference registration desk. Thank you! Contact: Jeremy@DataRealized.com Updated Slides – Page 1 of the schedule guide

More Related