Entity Framework ou comment réconcilier les développeurs et DBA sur l'accès aux données

1. Entity Frameworkou comment réconcilier les développeurs et DBA sur l'accès aux données Matthieu Mezil Christian Robert Senior Developer Consultant mmezil@access-it.fr Senior Database Consultant cro@evolusys.org EvoluSys SA Access It IDF

2. About Matthieu Matthieu Mezil – mmezil@access-it.fr Work on .NET Server since 1.0 (2002) C# MVP (2008 – 2009)Data Platform Development MVP (since 2010) INETA Speaker MCT Specialized on Entity Framework, C#, T4, Architecture. My French blog : blogs.codes-sources.com/matthieu My English blog : msmvps.com/blogs/matthieu

3. Access It IdF in brief… • Consulting & training on Microsoft technologies only! • 20+ consultants/experts, including11 MVP • 2 domains of expertise: • Everythingabout .NET & Visual Studio! • MS Application Platform: SharePoint & SQL Server / BI .NET Plateforme Applicative Framework et langages .Net, AZURE, Silverlight, WPF/Surface, VS/TFS, Windows Phone 7,… SQL Server (SSIS, SQL, SSAS, SSRS), PowerPivot, SharePoint & on-line, Office 365,…

4. About Christian Christian Robert - cro@evolusys.org Have worked with SQL Server since 7.0 SQL Server MVP since 2007Microsoft Certified Master on SQL Server 2008 Specialized on SQL Server Relational Engine, around Performance Tuning, Security, TSQL Source Management and Powerpivot. 13 years in the database industry . Worked on Large deployment of SQL Server. Projects for : CICR, ricardo.ch, … My blog : blogs.codes-sources.com/christian Swiss SQL Server User Group : www.sqlpass.ch

5. EvoluSys… the data experts • Swiss company founded in 2006 • Consulting • Performance review • Security audit of the database server • Architecture choice • DataWarehouse conception • Reporting & Dashboard needs • Highly skilled and certified trainers • More info on : www.evolusys.org

6. Special price for Techdays attendees Promo code : TRAINTDGVA Trainingmore info on : formation@evolusys.ch

7. IntroducingEntity Framework

8. DB relational conception != Entities object conception. EF runtime does the mapping job for you • EF abstracts the DB schema and DB provider. Your code works with SQL Server, Oracle, etc • LINQ To Entities simplifies the querying

9. Démo EntityFramework pour développeur & DBA

11. EF advantages

12. EF advantages’ for developers • The mapping complexity is done by EF. You can very easily have your entities object conception without taking care of the DB constraints • LINQ To Entities are better: • Compiled => no repo mistake • Intellisense • Better readability • Typed result • Written on C# or VB.NET

13. EF advantages’ for developers (continued) • SQL written by developers is often not the best one • SQL generated by EF is pretty good… when you understand what you do!

14. EF advantages’ for DBAs • Possibility to change the database without any impact on the application • As all the queries are fully qualified it reduced a lot the risk to have broken queries on small schema changes • When the mapping is outside of the application more changes are even possible • Security • SQL generated is safe and risk of code injection is very low

15. EF advantages’ for DBAs (continued) • Source code management • LINQ queries are part of the application code and also part of the source controller • Dependencies • No risk of objects not found at execution time, dependencies are enforced at compile time • Works for all SQL-based database servers • Who provides an EF provider

16. What’s possible with EF ? • Horizontal and Vertical table Split • Take a table, put 2 columns on a new table and 3 others columns in an other table • Split the content of a table in 2 parts and map your Entity to one of them • Convert a complex LINQ to EF query to a Stored Procedure • Use of Stored Procedures for INSERT / UPDATE / DELETE operations

17. Démo Entity Framework pour développeur & DBA

18. Not (yet) available with EF • Recursive CTEs • PIVOT • Ranking functions • Support for CLR UDTs (spatial, hierarchyID) • Temp tables • Filestream • Query hints (except in plan guides) • FullText queries • TVF (Table Value Function) • BULK INSERT support

19. Troubleshooting EFon server side

20. Dynamic SQL considerations • EF is a SQL Generator so most of issues seen with that kind of tools remains • Who is generating that code ? • Difficult to cluster queries by types • Almost no possibilities to change the query content • Side effects • Security enforcement (not possible to trust code) • Number of queries to consider can be huge

21. Performance considerations • The behavior of EF queries are the exact same as Stored Procedures • Stored Procedures are compiled at first execution using the parameters values provided, plan is stored in cache. On second execution the plan is reused from cache • EF uses sp_executesql (as standard ADO.Net queries) and provide parameters… The behavior is the same, instead of the object_id (to find the cache entry of the plan) SQL Server use the query hash • The query engine is dynamic by essence, this allows execution plan to execute and adapt on the real data

22. SQL Profiler isyourfriend • Allow you to intercept query : • Text • Reads / Writes • CPU • Execution Time • Attention, when you copy and paste a query from SQL Profiler to SSMS the behavior change • Management Studio use slightly different connection parameters, so the query will generate a new plan and new reads, writes and CPU time!

23. UsefullDynamic Management Views • Querying the Plan cache with Management Views (DMVs): • sys.dm_exec_cached_plans • sys.dm_exec_plan_attributes • sys.dm_exec_query_stats • Some Management Functions (DMFs): • sys.dm_exec_sql_text • sys.dm_exec_query_plan • Since SQL Server 2008 the query and plan hash help you to find similar queries with « changing » plans ! • Contrary to the SQL Profiler, DMVs only show you queries that are in the Plan Cache

24. Plan cache • As describe before the behavior of EF vs SP are similar but : • EF uses parametrized queries so the cache can be larger • The cache entry is based on the query hash (exact text content including comments) • Use the server option : optimizefor ad hoc workloads’ • The query plan iskept in cache onlyat 2ndexecution • If using different users to connect to the server, some other cache can suffer of contention • Really rare since SQL 2005 SP2

25. Plan guides • Now, you know your problematic queries… • But it’s dynamic… no way to update them :( • Since SQL Server 2005 • Possibility to identify a pattern of queries… • … and apply them Hints • … and provides to the engine alternates plans (but engine is not obliged to use them) • Plan guides are the ultimate’s solution to change the query’s behavior on the server !

26. Plan guide for Recompile • This query’s execution time change a lot from time to time • Execution plan don’t change but parameters change • When is queries is expected to have different behavior with different parameters it should be good to always recompile that query • Create a plan guide for that query pattern • Dynamically add ‘OPTION (RECOMPILE)’

27. Plan guide with OPTIMIZE FOR • This query is fast the morning, and suddenly become slow for a certain time • Execution plan changes on statistics update and the next parameter provided is not always perfect for the compilation • Remember that the statistics are recomputed every 20% of data changes on a column, so the recompilation can occur very frequently • Create a plan guide to force the engine to compile the query based on a specific values or statistics of the columns • Dynamically add ‘OPTION (OPTIMIZE FOR UNKNOWN)’ • Dynamically add ‘OPTION (OPTIMIZE @p1 FOR N‘myvalue’)

28. Démo Entity Framework pour développeur & DBA

29. Conclusion • Entity Framework makes developer job easier • Entity Framework improves development flexibility • Don’t forget that there is a database server behind your queries • Watch your queries and plans • Optimization is a not a one shot task… It’s a day to day work !

30. Q&A

31. © 2011 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.