SQL Server Stored Procedures Architecture & Performance

1. SQL Server Stored ProceduresArchitecture & Performance Victor Isakov MCT, CTT, MSCE, MCDBA sql@informare.com.au

2. Victor Isakov (sql@informare.com.au) • Victor Isakov has been involved with SQL Server since Version 4.21a, enjoying a reputation as one of the best "SQL Gurus" in the country • He has taught the Administration, Development, Data Warehousing and Performance Tuning courses since 1995 in the Australasia-Pacific region and provided consultancy services to companies like the University of NSW, Department of Education and Training, Westpac and the United Nations • He specialises as a consultant in Optimising, Troubleshooting and Architecting Database Solutions based on Microsoft technology

3. Agenda • Stored Procedure Architecture • Stored Procedure Optimization • Managing Query Plan Performance • Undocumented Microsoft Stored Procedures

4. Agenda • Stored Procedure Architecture • Stored Procedure Optimization • Managing the Query Plan for Performance. • Undocumented Microsoft Stored Procedures

5. Stored Procedure Architecture • SPs are SQL Server programs • Traditionally written in Transact-SQL • In Yukon will include VB.NET, C# • Provide • Query Management • Data Modifications • Data / Referential Integrity • Maintenance Tasks • Protect Data • Provide Gateway to Database Objects

6. Stored Procedure Architecture • Stored in Database • Protected like other DB objects • Logic moved to server • Increased Developer Productivity • Reusable Code • More structured 3-Tier solutions • Eliminate needless query plan construction • Cached query plans can improve performance

7. SQL Server Stored Procedure Architecture Stored Procedure Query SELECT logic Changes Security Business Rules Unauthorized Constraints Compiled Execution Plan

8. Execution Plan in Detail • Query Plan • Re-Entrant, Read-Only Structure • Shared • Separate Parallel / Serial Plans • Execution Context • User Context • Parameter Values, etc • Not Shared • Can be Re-Used

9. Agenda • Stored Procedure Architecture • Stored Procedure Optimization • Managing the Query Plan for Performance. • Undocumented Microsoft Stored Procedures

10. Cache (RAM) Stored Procedure Optimization CREATE PROCEDURE… Parse TSQL Syntax Resolve References Save in Database EXEC @RC=MyProc… Resolve references Optimize Compile Execute

11. Compilation in Detail

12. Stored Procedure Optimization SQL Server RAM Cache EXEC @RC=MyProc… P P 1st instance compiled and QP loaded 2nd instance shares loaded QP (new EC) 1st instance finishes 3rd instance shares first QP (& 1st EC) Data Pages

13. Compilation / Execution in Detail

14. Cache (RAM) Stored Procedures Optimization • Ad-hoc Queries • Compiled when first used • EP potentially cached • Subsequent references • Query Optimizer compares query with Query-Templates • Conservative • Use cached plan if it’s recognized • Auto-Parameterization Data Pages

15. Processing T-SQL in Detail

16. Cache (RAM) Stored Procedure Optimization • SPs compiled on first use • Query plan cached in RAM • Subsequent calls generally use cached plan • Recompiled if…. Data Pages

17. Recompilation • WITH RECOMPILE in CREATE PROCEDURE or EXECUTE statement • Schema changes to referenced objects • Table referenced by stored procedure • Adding / dropping constraints, defaults, or rules • sysobjects.schema_ver • Restoring the database or any objects procedure references

18. Recompilation • Running sp_recompile on table • SET Options • May affect query result • ANSI_DEFAULTS • ANSI_NULLS • ANSI_PADDING • ANSI_WARNINGS • CONCAT_NULL_YIELDS_NULL • Certain operations on temporary tables

19. Recompilation • Interleaving DDL and DML in procedure • Server activity ages plan out of cache

20. Profiler Trap SP:Recompile

21. Avoiding Recompilation • Use sp_executesql • Preferred Method • Calling Stored Procedure unaffected • Use Sub-Procedures • Only smaller sub-procedure recompiled • Use KEEP PLAN Option • SET Options

22. Agenda • Stored Procedure Architecture • Stored Procedures Optimization • Managing Query Plan Performance • Undocumented Microsoft Stored Procedures

23. Query Optimization • All input parameters • Whether used or not • Suitable indexes • Data statistics • All logic in the procedure • Whether or not the code is executed • Query Complexity IF IF

24. Query Optimization in Detail

25. Generated query plan based on parameters Provided by first query (arbitrary) Cached and reused for all subsequent use Regardless of suitability Some queries run normally, others do not Managing Query Plan Performance

26. Managing the Cache • Flushing the Cache • DBCC FREEPROCCACHE • DBCC DROPCLEANBUFFERS • Tip: Should CHECKPOINT first to ensure all data is flushed, otherwise “dirty” data pages will remain in the cache. • Restart SQL Server • Power-cycle system

27. SQL Server Cache • Least-Frequently-Used strategy • Least used objects flushed when cache fills • Unified cache stores data and procedure pages • Lazywriter does know the difference • Monitor cache with Perfmon

28. Inserting QP into Cache

29. Retrieving QP From Cache

30. Lazywriter Cache Processing Flow

31. Managing Query Plan Performance • Recompilation might help performance • When “optimized” query is not optimal • Cost of recompile insignificant compared to poorly running query • Test all parameter combinations • Check for consistent plans, performance • If query plan varies based on parameters • Recompile for each execution? • Best to redesign procedure

32. Managing Query Plan Performance • Recompiling on Demand • CREATE PROCEDURE … WITH RECOMPILE • Compiles QP each time stored procedure is executed • EXECUTE … WITH RECOMPILE • When parameters are not “typical” • sp_recompile • Forces all plans to be recompiled (very cheap) • Point to stored procedure, table… • Statement-based recompile • Dynamic string execution (dangerous, but powerful) • Smaller, more-focused procedures

33. Managing Query Plan Performance • Use Query Analyzer to view Query Plan • Execute query with a range of input parameters • Clear procedure and data cache • View IO Statistics • Enable “Show Execution Plan”

34. Managing Query Plan Performance

35. Managing Query Plan Performance

36. Managing Query Plan Performance Strategy • Flush cache DBCC FREEPROCCACHE • Force recompile WITH RECOMPILE • Avoid “all-purpose” stored procedures • SET Options

37. Managing Query Plan Performance Strategy • Re-engineer stored procedures • Break up larger, more complex procedures • Each sub-procedure gets its own query plan • Design procedures to work with “typical” parms • Build special case procedures • Use templates!

38. Performance • Don’t sweat the small stuff… Construct Command object Client-side preparation Execute query Parse, resolve, build QP Server-side execution Execute query Return resultsets Client-side processing Client-side consumption

39. Agenda • Stored Procedure Architecture. • Stored Procedures Optimization • Managing Query Plan Performance • Undocumented Microsoft Stored Procedures

40. Undocumented Microsoft Stored Procedures • Stored Procedure Architecture. • Stored Procedures Optimization • Managing Query Plan Performance • Undocumented Microsoft Stored Procedures

41. Undocumented Microsoft Stored Procedures • Seven for the DBA • sp_EnumErrorLogs • sp_ReadErrorLog • sp_TempDbSpace • sp_EnumOleDbDatasources • sp_MSForEachDb • sp_MSLoginMappings • sp_MSTableSpace

42. sp_EnumErrorLogs

43. sp_ReadErrorLog

44. sp_TempDbSpace

45. sp_EnumOleDbDataSources

46. sp_MSForEachDB

47. sp_MSLoginMappings

48. sp_MSTableSpace

49. Undocumented Microsoft Stored Procedures • Seven for the Developer • sp_GetTypeString • sp_MSCheck_UId_Owns_Anything • sp_MSGet_Qualified_Name • sp_MSForEachTable • sp_MSIs_PK_Col • sp_MSTable_Has_Unique_Index • sp_MSTableRefs

50. sp_GetTypeString