SQL Server Stored Procedures Architecture

1
SQL Server Stored ProceduresArchitecture
Performance

• Victor Isakov
• MCT, CTT, MSCE, MCDBA
• sql_at_informare.com.au

2
Victor Isakov (sql_at_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
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
Stored Procedure Optimization
CREATE PROCEDURE
Parse TSQL Syntax
Resolve References
Save in Database
EXEC _at_RCMyProc
Resolve references
Optimize
Compile
Execute
11
Compilation in Detail
12
Stored Procedure Optimization
RAM Cache
EXEC _at_RCMyProc
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
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 its recognized
• Auto-Parameterization

Data Pages
15
Processing T-SQL in Detail
16
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

Table Type Empty Condition Threshold When Empty Threshold When Not Empty
Permanent lt 500 rows Changes gt 500 Changes gt 500 (20 of Cardinality)
Temporary lt 6 rows Changes gt 6 Changes gt 500
Variable Change in Cardinality does not affect Automatic Recompilation Change in Cardinality does not affect Automatic Recompilation Change in Cardinality does not affect Automatic Recompilation
20
Profiler Trap SPRecompile
Code Reason
1 Schema, bindings, or permissions changed between compile or execute
2 Statistics changed
3 Object not found at compile time, deferred check to run time
4 Set option changed in batch
5 Temp table schema, binding, or permission changed
6 Remote rowset schema, binding, or permission changed
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
Managing Query Plan Performance

• 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

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

• Dont 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
51
sp_MSCheck_UId_Owns_Anything
52
sp_MSGet_Qualified_Name
53
sp_MSForEachTable
54
sp_MSIs_PK_Col
55
sp_MSTable_Has_Unique_Index
56
sp_MSTableRefs
57
Summary

• Audience
• Who cares, let us go home! Please!
• Victor
• But I need to put up something
• Understand how SQL Server works
• Helps you help SQL Server toexecute optimal
  plans
• KISS Principle
• Slides are up on www.sqlserver.org.au