DBA 322 Optimizing Stored Procedure Performance. Kimberly L. Tripp Solid Quality Learning – SolidQualityLearning.com Email: Kimberly@SolidQualityLearning.com SYS olutions, Inc. – SQLSkills.com Email: Kimberly@SQLSkills.com. Introduction. Kimberly L. Tripp, SQL Server MVP
Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.
Kimberly L. Tripp
Solid Quality Learning – SolidQualityLearning.comEmail: Kimberly@SolidQualityLearning.com
SYSolutions, Inc. – SQLSkills.comEmail: Kimberly@SQLSkills.com
* In-depth, high quality training around the world!
www.tsqlsolutions.com and www.sqlmag.com
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!
Cost of recompilation might be significantly less than the execution cost of a bad plan!
Faster Execution with a better plan
Saving plans for reuse is NOT always beneficial
Some plans should NEVER be savedWhen to 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
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)
Dynamic String Execution or Modularized CodeOptions for Recompilation
EXEC dbo.GetMemberInfo ‘Tripp’ WITH RECOMPILE
EXEC dbo.GetMemberInfo ‘T%’ WITH RECOMPILE
EXEC dbo.GetMemberInfo ‘%T%’ WITH RECOMPILE
NOTE: Consider forcing recompilation through another technique – you should not expect users will know when/why to use EXECUTE … WITH RECOMPILE once in production!
CREATE PROCEDURE GetMemberInfo
( @LastName varchar(30) )
SELECT * FROM Member WHERE LastName LIKE @LastName
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
Do not use a lot of conditional SQL Statement Blocks
Call separate stored procedures instead!
IF (expression operator expression)
SQL Statement Block1
SQL Statement Block2
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.
CREATE PROC dbo.procname
SELECT columnlist FROM dbo.tablename
See KB Article Q263889
See “SET Options that Affect Results” in the BOL
Event = SP:Recompile & Column = EventSubClass
Converse online with Microsoft Newsgroups, including Worldwide
Meet and learn with your peers
Kimberly L. Tripp
Principal Mentor, Solid Quality Learning Website: www.SolidQualityLearning.com
President, SYSolutions, Inc.Website: www.SQLSkills.com
The tools you need to put technology to work!
Microsoft® SQL Server™ 2000 High Availability: 0-7356-1920-4
Microsoft® SQL Server™ 2000 Administrator's Companion:0-7356-1051-7
This presentation is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.