DBA 322

Optimizing Stored Procedure Performance

Kimberly L. Tripp Solid Quality Learning SolidQualityLearning.com
Email: Kimberly@SolidQualityLearning.com

SYSolutions, Inc. SQLSkills.com

Email: Kimberly@SQLSkills.com

Introduction
Kimberly L. Tripp, SQL Server MVP Principal Mentor, Solid Quality Learning
* In-depth, high quality training around the world! www.SolidQualityLearning.com

Content Manager for www.SQLSkills.com Writer/Editor for TSQL Solutions/SQL Mag

www.tsqlsolutions.com and www.sqlmag.com

Consultant/Trainer/Speaker Coauthor for MSPress title: SQL Server 2000 High Availability Presenter/Technical Manager for SQL Server 2000 High Availability Overview DVD Very approachable. Please ask me questions!

Overview
Initial Processing - Review
Resolution Compilation/Optimization Execution/Recompilation

Recompilation Issues
When do you want to Recompile? Options for Recompilation? What to Recompile? Stored Procedure Best Practices
Naming Conventions Writing Solid Code Excessive Recompilations How? Detecting?

Processing of Stored Procedures

Creation
Parsing Resolution sysobjects Name, type, etc. syscomments Text of object syscolumns Parameter list sysdepends Object dependencies

Resolution*

Execution
(first time or recompile)

Optimization

Compilation

Compiled plan placed in unified cache

Resolution
When a stored procedure is created all objects referenced are resolved (checked to see whether or not they exist). The create will succeed even if the objects do not exist
Procedures called that do not exist generate error
Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'missingobjectname'. The stored procedure will still be created. Benefit: Recursion is allowed!

Tables, Views, Functions called that do not exist - do NOT generate error (unless in 6.5 compatibility mode)

Verify dependencies with sp_depends before dropping an object

Compilation/Optimization
Based on parameters supplied Future executions will reuse the plan Complete optimization of all code passed (more on this coming upmodular code!) Poor coding practices can cause excessive locking/blocking Excessive recompilations can cause poor performance

Execution/Recompilation
Upon Execution if a plan is not already in cache then a new plan is compiled and placed into cache What can cause a plan to become invalidated and/or fall out of cache:
Server restart Plan is aged out due to low use DBCC FREEPROCCACHE (sometime desired to force it)

Base Data within the tables - changes:

Same algorithm as AutoStats, see Q195565 INF: How SQL Server 7.0 and SQL Server 2000 Autostats Work

Recompilation Issues
RECOMPILATION = OPTIMIZATION OPTIMIZATION = RECOMPILATION When do you want to recompile? What options do you have Recompilation? How do you know you need to recompile? Do you want to recompile the entire procedure or only part of it? Can you test it?

When to recompile?
When the plan for a given statement within a procedure is not consistent in execution plan due to parameter and/or data changes Cost of recompilation might be significantly less than the execution cost of a bad plan! Why? Faster Execution with a better plan Saving plans for reuse is NOT always beneficial Some plans should NEVER be saved

Options for Recompilation

CREATE WITH RECOMPILE
When procedure returns widely varying results When the plan is not consistent

EXECUTE WITH RECOMPILE

For testing and to determine if CREATE WITH RECOMPILE is necessary

sp_recompile objname
Forces all plans with regard to that object to be invalidated (note: this does not force recompilation on views even though a view name is supported)

Statement Recompilation
Dynamic String Execution or Modularized Code

How do you know?

You Test!
Test optimization plans consistency using EXECUTE WITH RECOMPILE Choose what needs to be recompiled
Whole Procedure Portions of the procedure

Test final performance using the chosen strategy

Procedure Recompilation (CREATE with RECOMPILE) Statement Recompilation (Dynamic String Execution) Modularized Code (Sub procedures created with or without WITH RECOMPILE)

EXECUTE WITH RECOMPILE

Excellent for Testing Verify plans for a variety of test cases
EXEC dbo.GetMemberInfo Tripp WITH RECOMPILE EXEC dbo.GetMemberInfo T% WITH RECOMPILE EXEC dbo.GetMemberInfo %T% WITH RECOMPILE

Do the execution plans match? Are they consistent? Yes then create the procedure normally No Determine what should be recompiled

What Should be Recompiled?

Whole Procedure
CREATE with RECOMPILE
Procedure is recompiled for each execution

EXECUTE with RECOMPILE

Procedure is recompiled for that execution
NOTE: Consider forcing recompilation through another technique you should not expect users will know when/why to use EXECUTE WITH RECOMPILE once in production!

Statement(s) Recompilation
Limited number of statements cause excessive recompilation
Dynamic String Execution Modular Code

CREATE WITH RECOMPILE

Use when the procedure returns drastically different results based on input parameters. May not be the only or even the best option How do you know?
CREATE PROCEDURE GetMemberInfo ( @LastName varchar(30) ) AS SELECT * FROM Member WHERE LastName LIKE @LastName go EXEC GetMemberInfo 'Tripp' -- index+bookmark EXEC GetMemberInfo 'T%' -- plan already exists (s/b a table scan) EXEC GetMemberInfo '%T%' -- definitely should use a table scan

Statement Recompilation
What if only a small number of statements need to be recompiled? The SQL Statement is not likely safe (i.e. it will not be saved and parameterized) Dynamic String Execution! Amazingly Flexible Permission Requirements Potentially Dangerous Advanced Examples
Complex large strings Changing database context Output parameters

Modular Code The Better Solution!

IF (expression operator expression) SQL Statement Block1 ELSE SQL Statement Block2
Do not use a lot of conditional SQL Statement Blocks Call separate stored procedures instead!

Solution?

Scenario 1 upon first execution Parameters are passed such that the ELSE condition executes BOTH Block1 and Block2 are optimized with the input parameters Scenario 2 upon first execution Parameters are passed such that the IF condition executes ONLY Block1 is optimized. Block2 will be optimized when a parameter which forces the ELSE condition is passed.
See ModularProcedures.sql

sp_recompile
Can be used to periodically and directly force recompilation of a procedure (or trigger) Can be used on tables and views to indirectly force the recompilation of all procedures and triggers that reference the specified table or view Does not actually recompile the procedures Instead it invalidates plans for next execution SQL Server invalidates plans as data changes Never really negative especially if you run it at night as part of batch processing after index rebuilds or statistics updates with FULLSCAN

Stored Procedure Best Practices

Naming Conventions
Owner Qualify Do not use sp_

Modifying Procedures Write Solid Code

Writing Better Queries/Better Search Arguments Changing Session Settings Interleaving DML/DDL Temp Table Usage Modular Code

Detecting Excessive Recompilations

Naming Conventions
Owner Qualify to Eliminate Ambiguity
On execution
EXEC dbo.procname

On creation
CREATE PROC dbo.procname AS SELECT columnlist FROM dbo.tablename EXEC dbo.procname

Minimize Blocking initial cache lookup by owner will fail. It will not cause a recompile but excessive lookups can cause significant blocking and cache misses. Do not use sp_ in stored procedure names causes cache misses on lookup as well because SQL Server looks in master first!
See KB Article Q263889

Modifying Procedures
DROP and RECREATE
Loses the dependency chain stored in sysdepends Loses the permissions already granted Invalidates all plans

ALTER PROC
Loses the dependency chain stored in sysdepends Retains the permissions Invalidates all plans

To retain the dependency chain you must also ALTER all procedures that depend on the procedure being altered.

Changing SESSION Settings

Certain Session Settings can be set within a stored procedure some can be desired:
SET NOCOUNT ON SET QUOTED_IDENTIFIER OFF (not recommended except for backward compatibility and upgrades)

Some Session Settings will cause EVERY execution to force a recompile:

ANSI_DEFAULTS ANSI_NULLS (tip: do not use WHERE col = null, use col IS NULL) ANSI_PADDING ANSI_WARNINGS CONCAT_NULL_YIELDS_NULL (tip: use the ISNULL function to concatenate strings)

Recommendation: DO NOT Change these session settings in the client or the server! See SET Options that Affect Results in the BOL

Interleaving DML/DDL Statements

Objects that dont exist at procedure first execution cannot be optimized until statement execution Upon execution of a DDL statement the procedure gets recompiled to recompile the plans for the DML But wait not all of the objects are createdso later executions of DDL force recompilation AGAIN Dont interleave DDL and DML separate it All DDL at the beginning of the proc, all DML later!

Data Manipulation
Derived Tables
Nested Subquery in FROM clause May optimize better than temp tables/variables

Views
Another option rewrite existing temp table code to use views instead (simple rewrite) May optimize better than temp tables/variables

Temp Tables
Should be considered

Table Variables
Limitations might not affect you Might be the most optimal

Temp Table Usage

Temp Table can create excessive recompilations for procedures. Consider creating permanent tables (with indexes) and manipulating data there. Consider dropping and re-creating or rebuilding indexes as part of the procedure instead! Try not to create tables conditionally (IF create ELSE create) Use Profiler to see if there are significant recompiles Use KEEP PLAN on SELECT statements if data changes more than 6 times but the plan should not change

Table Variable Usage

Scope is limited to the local procedure\transaction Does not cause excessive recompiles due to local only access
No re-resolution on CREATE/ALTER Temp Tables need re-resolution for nested procedures

Only Key Indexes can be created

Definition of Table allows PRIMARY KEY/UNIQUE constraint indexes Use TEMP TABLES if large volumes of data will be manipulated create the right indexes for access

Population
Does not support INSERT EXEC Does not support SELECT INTO

Temp Table vs. Table Variables

Temp Table PROs
Can create useful nonclustered non-unique indexes to improve join performance Can access from other nested procedures Can populate with INSERT EXEC or SELECT INTO

CONs
Potential for excessive recompiles due to resolution

Table Variable Table

PROs
Local only no excessive recompiles

CONs
Cannot create additional nonclustered indexes Not flexible on population

Detecting SP Recompilation
Event = SP:Recompile & Column = EventSubClass

Local Schema, bindings or permissions changed between compile and execute or executions
Shouldnt happen often. If it does isolate where/how changes occur and batch/schedule for off hours

Statistics changed

Thresholds for statistics of the different types of tables vary. Empty Tables (Permanent >= 500, Temp >= 6, Table Variables = No threshold) Tables with Data (Perm/Temp >= 500 + 20% cardinality, Table Variables = No threshold) If consistent plan then eliminate recompiles from changes in statistics by using (KEEPFIXED PLAN) optimizer hint in SELECT

3 4 5 6

Object not found at compile time, deferred check at run-time

If the objects on which the procedure are based are permanent objects consider recreating

Set option changed in batch

Best Coding practice: Consistency in client session settings. Consistency in development environment. Only use SET options when connection is started and when procedure is created.

Temp table schema, binding or permission changed

Change coding practice for #temptable

Remote rowset schema, binding or permission changed.

Gets stats from remote server, may recompile. If youre going to another server often for a relatively small amount of static data you might consider periodically brining over a local copy?

Profiling SP Performance
Create New Trace (SQLProfilerTSQL_sps)
Replace SP:StmtStarting w/SP:StmtCompletion Better if you want to see a duration (starting events dont have a duration) Add Duration as a Column Value

If short term profiling for performance:

Add columns: Reads, Writes, Execution Plan

Always use Filters

Database Name (only the db you want) Exclude system IDs (checkbox on filter dialog)

Review
Initial Processing - Review
Resolution Compilation/Optimization Execution/Recompilation

Recompilation Issues
When do you want to Recompile? Options for Recompilation? What to Recompile?

Stored Procedure Best Practices

Naming Conventions Writing Solid Code Excessive Recompilations How? Detecting?

Other Sessions
DAT 335 SQL Server Tips and Tricks for DBAs and Developers Tuesday, 1 July 2003, 15:15-16:30 DBA 324 Designing for Performance: Structures, Partitioning, Views and Constraints Wednesday, 2 July 2003, 08:30-09:45 DBA 328 Designing for Performance: Optimization with Indexes Wednesday, 2 July 2003, 16:45-18:00 DBA 322 Optimizing Stored Procedure Performance in SQL Server 2000 Thursday, 3 July 2003, 08:30-09:45

Articles
Articles in TSQLSolutions at www.tsqlsolutions.com (FREE, just register)
All About Raiserror, InstantDoc ID#22980 Saving Production Data from Production DBAs, InstantDoc ID#22073

Articles in SQL Server Magazine, Sept 2002:

Before Disaster Strikes, InstantDoc ID#25915 Log Backups Paused for Good Reason, InstantDoc ID#26032 Restoring After Isolated Disk Failure, InstantDoc #26067 Filegroup Usage for VLDBs, InstantDoc ID#26031

Search www.sqlmag.com and www.tsqlsolutions.com for additional articles

Resources
Whitepaper: Query Recompilation in SQL Server 2000 http://msdn.microsoft.com/library/default.asp? url=/nhp/Default.asp?contentid=28000409

Community Resources
Community Resources
http://www.microsoft.com/communities/default.mspx

Most Valuable Professional (MVP)

http://www.mvp.support.microsoft.com/

Newsgroups
Converse online with Microsoft Newsgroups, including Worldwide http://www.microsoft.com/communities/newsgroups/default.mspx

User Groups
Meet and learn with your peers http://www.microsoft.com/communities/usergroups/default.mspx

Ask The Experts

Get Your Questions Answered

I will be available in the ATE area after most of my sessions!

Thank You!
Kimberly L. Tripp
Principal Mentor, Solid Quality Learning
Website: www.SolidQualityLearning.com Email: Kimberly@SolidQualityLearning.com

President, SYSolutions, Inc.

Website: www.SQLSkills.com Email: Kimberly@SQLSkills.com

Suggested Reading And Resources

The tools you need to put technology to work!
TITLE
Microsoft SQL Server 2000 High Availability: 0-7356-1920-4

Available

7/9/03

Microsoft SQL Server 2000 Administrator's Companion:07356-1051-7

Today

Microsoft Press books are 20% off at the TechEd Bookstore Also buy any TWO Microsoft Press books and get a FREE T-Shirt

evaluations

 2003 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.