1 / 18

Ayoka, L.L.C. 202 E. Border Street, Ste 334 Arlington, TX 76010 817.210.4042 ayokasystems

SQL Server Best Practices. By Steve Chang. Ayoka, L.L.C. 202 E. Border Street, Ste 334 Arlington, TX 76010 817.210.4042 www.ayokasystems.com. Temporary Tables. Local temporary table On disk, in tempdb Visible only to the current scope (like a stored procedure) Global temporary table

havyn
Download Presentation

Ayoka, L.L.C. 202 E. Border Street, Ste 334 Arlington, TX 76010 817.210.4042 ayokasystems

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. SQL Server Best Practices By Steve Chang Ayoka, L.L.C. 202 E. Border Street, Ste 334 Arlington, TX 76010 817.210.4042 www.ayokasystems.com

  2. Temporary Tables • Local temporary table • On disk, in tempdb • Visible only to the current scope (like a stored procedure) • Global temporary table • On disk, in tempdb • Visible to all sessions

  3. Local Temporary Table • CREATE TABLE #people (     id INT,     name VARCHAR(32) ) • SELECT id, name INTO #people FROM employees • DROP TABLE #people

  4. Global Temporary Table • CREATE TABLE ##people (     id INT,     name VARCHAR(32) ) • SELECT id, name INTO ##people FROM employees • DROP TABLE ##people

  5. Table Variables • In memory • Performs slightly better than local temporary table • Automatically cleared when the procedure or function goes out of scope • In user-defined function, only allow table variables

  6. Table Variables Syntax • DECLARE @people TABLE (     id INT,     name VARCHAR(32) )

  7. Limitations of Table Variables • Cannot truncate a table variable • Table variables cannot be altered • Cannot explicitly add an index to a table variable • Cannot drop a table variable when it is no longer necessary • …

  8. Conclusion • Rarely use ##table • Choose between #table and @table • Depend on performance and reasonable load testing • Small data set result, use @table • Need index, use #table • In most situations, #table makes more sense • Avoid cursor • Use #table before large tables’ join. • Do not use the SELECT INTO statement to create #table

  9. Code Examples • ex_tmp_table.sql • ex_no_tmp_table.sql

  10. Microsoft SQL Server Management Studio Tools • Actual Execution Plan • Client Statistics • Database Engine Tuning Advisor

  11. Actual Execution Plan (1) • On SQL Server Management Studio’s tool bar

  12. Actual Execution Plan (2) • According to the result, determine which sql statements are the bottlenecks

  13. Client Statistics (1) • On SQL Server Management Studio’s tool bar

  14. Client Statistics (2) • Review the performance

  15. Database Engine Tuning Advisor (1) • On SQL Server Management Studio’s tool bar

  16. Database Engine Tuning Advisor (2) • Start Analysis

  17. Database Engine Tuning Advisor (3) • Create recommended indexes

  18. Database Engine Tuning Advisor (4) • ex_advisor.sql

More Related