1 / 30

SQL Server Stored Procedure Best Practices

Learn about best practices for naming, calling, and optimizing stored procedures in SQL Server. Covering common problems and recommendations for tuning.

morales
Download Presentation

SQL Server Stored Procedure Best Practices

An Image/Link below is provided (as is) to download presentation 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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Atlanta SQL Server Users GroupApril 10, 2006 Stored Procedure Best Practices Kevin Kline Director of Technology Quest Software

  2. Agenda • Overview • Polling Question • Discuss Best Practices • Review Common Problems • Cover a few tuning recommendations

  3. Bio • Started in IT in 1986. BS in MIS in 1989 from University of Alabama. • Microsoft SQL Server MVP • Author of 5 database books • 1 on Oracle, 2 on SQL Server, 2 on SQL. • Daily blogger on www.sqlmag.com; ATE on www.searchSQLserver.com • President of PASS (www.sqlpass.org) • Conference is next Nov 13 – 17 in Seattle • Over 130 sessions on SQL Server, BI & Dev • Worked for NASA, US Army, and Deloitte before Quest Software.

  4. Naming Procs • Do NOT use sp_xxx as a naming convention. • Causes additional searches and added I/O. • If a user stored procedure has same name as an sp_xxx stored procedure in MASTER, then the user procedure will NEVER be used.

  5. Calling Procs • Use stored procedure calls rather than embedded SQL • EXEC versus SP_EXECUTESQL • same behavior with regard to batches, the scope of names, and database context • EXEC compiles entire SQL at one time • SP_EXECUTE compiles and executes as an execution plan separate from the execution plan of the batch that called sp_executesql itself. • SP_EXECUTESQL executes a Transact-SQL statement or batch that can be reused many times, or that has been built dynamically, using a single execution plan. • Often better than EXEC.

  6. Common Problems • Un-necessary network communication • Execution Plans • Not Enough Sharing • Too Much Sharing • Inadequate plan • Recompilation • Other Delays

  7. Set Nocount On • No done_in_proc messages • <10 rows affected> • Extra network trip • Server setting via Trace 3640 • -T3640 • sp_configure ‘user options’, 512

  8. Create Stored Procedure Creation SQL Parsing Entries into sysobjects and syscomments tables

  9. Exec Stored Procedure Execution In Memory? Read from syscomments NO compile YES optimize Execute

  10. SP Plan Sharing Execution Context Query Plan Spid 10 Cooking Select * From dbo.titles Where type = ? Spid 17 Business Spid 23 Psychology

  11. Not Enough Plan Sharing • Set Options • Language used • Dateformat

  12. Option Hierarchy • Set Statement in code • OLEDB/ODBC Connection string • ODBC • Control Panel • SQLConfigDatasource • OLEDB/ODBC auto set of 7 • DB-Library/Embedded SQL for C-NOT • Database Level Settings (alter database) • Server Wide Settings (sp_configure)

  13. SIS + +

  14. Too Much Plan Sharing Memory getord tbl scan exec getord ‘%’ finduser index exec getord ‘05022%’ sp_1 sp_4

  15. Monitoring Plan Caching • DBCC FreeProcCache • DBCC FlushProcInDB(<dbid>) • DBCC DropCleanBuffers • Actual Query Plan • SQL Profiler • Syscacheobjects

  16. Too Many Recompiles Execution In Memory? Read from syscomments NO compile YES optimize ReComp Execute

  17. SP Recompiles • Because we request it • Previous plan aged out of memory • Interleaved DDL and DML • Schema changes to objects in sp • New index statistics • Cursor on temp table (SQL 7.0) • Sp_configure

  18. Requested Recompile • Create proc … with recompile as • Exec myproc … with recompile • sp_recompile titles

  19. SP Plan Aging Memory getord 16 14 12 16 13 15 finduser 7 4 7 5 6 sp_1 sp_4 3 2 2 1 3 1 0 2 0

  20. Interleaved DDL and DML create proc testDDLDML as create table testdml (DDL) insert into testdml (DML – RECOMPILE) alter table testdml (DDL) insert into testdml (DML – RECOMPILE) drop table testdml

  21. Schema Changes to Objects • Sysobjects.schema_ver • Column additions, deletions • Data type changes • Constraint additions, deletions • Rule/Default bindings • Query plan index dropped

  22. New Index Statistics • Auto_update statistics • Auto_create statistics • Update statistics

  23. Inner SP Recompiles • When it uses the outer SPs temp table • First time (if called multi times)

  24. Using Local Variables in Where Clause • Optimizer guesses percentage of rows returned

  25. Best Practices • Owner qualify all names (2-part) • Standardize user options, language • Minimize use of tempdb • Use table variable instead • Always refer to LOCAL temp tables • Help the optimizer cache plans • Avoid using local vars in where clause • Don’t interleave DDL and DML • TEST,TEST, TEST

  26. Other Delay – Compile Locks • KB Q263889 spid blocked waittype waittime lastwaittype waitresource ---- --------- -------- ------- -------------- -------------------------------------- 221 29 0x000e 2141 LCK_M_X TAB: 6:834102 [[COMPILE]] 228 29 0x000e 2235 LCK_M_X TAB: 6:834102 [[COMPILE]] 29 214 0x000e 3937 LCK_M_X TAB: 6:834102 [[COMPILE]] 13 214 0x000e 1094 LCK_M_X TAB: 6:834102 [[COMPILE]] 68 214 0x000e 1968 LCK_M_X TAB: 6:834102 [[COMPILE]] 214 0 0x0000 0 LCK_M_X TAB: 6:834102 [[COMPILE]]

  27. dbo.sptest select * from test Kev.test dbo.test Kev stuff dbo stuff (Kev) Exec sptest

  28. References • SQL Server Books Online • “Execution Plan Caching and Reuse” • “Analyzing Optimal Compiled Plan Caching” - Sajal Dam • Knowledge Base • Q243588, “INF: Troubleshooting Performance of Ad Hoc Queries” • Q243586, “INF: Troubleshooting Stored Procedure Recompilation” • Q263889, “INF: SQL Blocking Due to [COMPILE] locks” • Inside SQL Server 2000 – Kalen Delaney • Guru’s Guide to … - Ken Henderson

  29. Questions & Answers • Thank you! • Email questions to kevin.kline@quest.com.

More Related