may, 2019
Abstract
Abstract
With a few exceptions, SQL Server stores the execution plan for a query in the internal plan cache. In most cases, subsequent execution of a similar query will greatly benefit from this mechanism, but not in all cases. In this session, you will learn why and under what circumstances SQL Server caches execution plans. We will analyze the plan cache to identify performance issues, such as plan pollution, and how to avoid this. You’ll see the benefits and drawbacks of parameter sniffing for procedures and functions, and the differences between native compiled and interpreted stored procedures. Furthermore, we will analyze how the settings optimize for adhoc workloads and forced parameterization influence query execution and the re-use of cached plans.
Speaker for this event
-
Torsten Strauß
Torsten Strauß
MVP | MCSE | MCT
Torsten (MVP, MCSE, MCT) has been working with Microsoft SQL Server since the turn of the millennium and is specialized in performance optimization for large SQL Server environments. He has been with bp as a database engineer for over two decades and collaborates closely with Sarpedon Quality Lab as a Principal Microsoft SQL Server Consultant. Torsten is the founder of inside-sqlserver.com and has given more than 120 presentations on Microsoft SQL Server performance optimization at national and international conferences and user groups. Torsten has been recognized as a Microsoft Most Valuable Professional for Data Platform since 2018.
MVP | MCSE | MCT