Transcript SQL Server Stored Procedures

SQL Server Stored
Procedures
Architecture & Performance
Victor Isakov
MCT, CTT, MSCE, MCDBA
[email protected]
Victor Isakov
([email protected])
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
© Victor Isakov
[email protected]
Agenda
Stored Procedure Architecture
Stored Procedure Optimization
Managing Query Plan Performance
Undocumented Microsoft Stored Procedures
© Victor Isakov
[email protected]
Agenda
Stored Procedure Architecture
Stored Procedure Optimization
Managing the Query Plan for
Performance.
Undocumented Microsoft Stored
Procedures
© Victor Isakov
[email protected]
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
© Victor Isakov
[email protected]
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
© Victor Isakov
[email protected]
Stored Procedure Architecture
Stored Procedure
Query
Unauthorized
Security
Changes
SELECT logic
Business Rules
Constraints
Compiled
Execution Plan
SQL Server
© Victor Isakov
[email protected]
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
© Victor Isakov
[email protected]
Agenda
Stored Procedure Architecture
Stored Procedure Optimization
Managing the Query Plan for
Performance.
Undocumented Microsoft Stored
Procedures
© Victor Isakov
[email protected]
Stored Procedure Optimization
CREATE PROCEDURE…
Parse TSQL Syntax
Resolve References
Save in Database
Cache (RAM)
EXEC @RC=MyProc…
Resolve references
Optimize
Compile
Execute
© Victor Isakov
[email protected]
Compilation in Detail
© Victor Isakov
[email protected]
Stored Procedure Optimization
RAM Cache
EXEC @RC=MyProc…
SQL Server
1st instance compiled and QP loaded
2nd instance shares loaded QP (new EC)
1st instance finishes
3rd instance shares first QP (& 1st EC)
© Victor Isakov
Data Pages
[email protected]
Compilation / Execution in Detail
© Victor Isakov
[email protected]
Stored Procedures Optimization
Cache (RAM)
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
© Victor Isakov
Data Pages
[email protected]
Processing T-SQL in Detail
© Victor Isakov
[email protected]
Stored Procedure Optimization
Cache (RAM)
SPs compiled on first use
Query plan cached in RAM
Subsequent calls generally
use cached plan
Recompiled if….
Data Pages
© Victor Isakov
[email protected]
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
© Victor Isakov
[email protected]
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
© Victor Isakov
[email protected]
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
< 500 rows
Changes >= 500
Changes >= 500 + (20% of Cardinality)
Temporary
< 6 rows
Changes >= 6
Changes >= 500
Variable
© Victor Isakov
Change in Cardinality does not affect Automatic Recompilation
[email protected]
Profiler Trap SP:Recompile
Code Reason
1
2
3
4
5
6
Schema, bindings, or permissions changed
between compile or execute
Statistics changed
Object not found at compile time, deferred
check to run time
Set option changed in batch
Temp table schema, binding, or permission
changed
Remote rowset schema, binding, or permission
changed
© Victor Isakov
[email protected]
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
© Victor Isakov
[email protected]
Agenda
Stored Procedure Architecture
Stored Procedures Optimization
Managing Query Plan Performance
Undocumented Microsoft Stored
Procedures
© Victor Isakov
[email protected]
Query Optimization
All input parameters
IF
Whether used or not
Suitable indexes
Data statistics
All logic in the procedure
IF
Whether or not the code is
executed
Query Complexity
© Victor Isakov
[email protected]
Query Optimization in Detail
© Victor Isakov
[email protected]
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
© Victor Isakov
[email protected]
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
© Victor Isakov
[email protected]
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
© Victor Isakov
[email protected]
Inserting QP into Cache
© Victor Isakov
[email protected]
Retrieving QP From Cache
© Victor Isakov
[email protected]
Lazywriter Cache
Processing Flow
© Victor Isakov
[email protected]
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
© Victor Isakov
[email protected]
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
© Victor Isakov
[email protected]
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”
© Victor Isakov
[email protected]
Managing Query Plan
Performance
© Victor Isakov
[email protected]
Managing Query Plan
Performance
© Victor Isakov
[email protected]
Managing Query Plan
Performance Strategy
Flush cache DBCC FREEPROCCACHE
Force recompile WITH RECOMPILE
Avoid “all-purpose” stored procedures
SET Options
© Victor Isakov
[email protected]
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!
© Victor Isakov
[email protected]
Performance
Don’t sweat the small stuff…
Construct Command object
Execute query
Client-side preparation
Parse, resolve, build QP
Execute query
Return resultsets
Server-side execution
Client-side processing
Client-side consumption
© Victor Isakov
[email protected]
Agenda
Stored Procedure Architecture.
Stored Procedures Optimization
Managing Query Plan Performance
Undocumented Microsoft Stored
Procedures
© Victor Isakov
[email protected]
Undocumented Microsoft
Stored Procedures
Stored Procedure Architecture.
Stored Procedures Optimization
Managing Query Plan Performance
Undocumented Microsoft Stored
Procedures
© Victor Isakov
[email protected]
Undocumented Microsoft
Stored Procedures
Seven for the DBA
sp_EnumErrorLogs
sp_ReadErrorLog
sp_TempDbSpace
sp_EnumOleDbDatasources
sp_MSForEachDb
sp_MSLoginMappings
sp_MSTableSpace
© Victor Isakov
[email protected]
sp_EnumErrorLogs
© Victor Isakov
[email protected]
sp_ReadErrorLog
© Victor Isakov
[email protected]
sp_TempDbSpace
© Victor Isakov
[email protected]
sp_EnumOleDbDataSources
© Victor Isakov
[email protected]
sp_MSForEachDB
© Victor Isakov
[email protected]
sp_MSLoginMappings
© Victor Isakov
[email protected]
sp_MSTableSpace
© Victor Isakov
[email protected]
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
© Victor Isakov
[email protected]
sp_GetTypeString
© Victor Isakov
[email protected]
sp_MSCheck_UId_Owns_Anything
© Victor Isakov
[email protected]
sp_MSGet_Qualified_Name
© Victor Isakov
[email protected]
sp_MSForEachTable
© Victor Isakov
[email protected]
sp_MSIs_PK_Col
© Victor Isakov
[email protected]
sp_MSTable_Has_Unique_Index
© Victor Isakov
[email protected]
sp_MSTableRefs
© Victor Isakov
[email protected]
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 to
execute “optimal” plans
“KISS” Principle
Slides are up on www.sqlserver.org.au
© Victor Isakov
[email protected]