T sql performance guidelines for better db stress powers

Shehap EL-Nagar I am MVP,MCTS , MCITP SQL Server, I am DB consultant and Architect for lots of Banking, Telecom ,Ministries and governmental organizations all over Gulf ,also he has deep knowledge about T-SQL performance , HW Performance issues, Data Warehousing solutions , SQL Server Replication, Clustering solutions and Database Designs for different kinds of systems ... The founder of the biggest SQL Server community all over the middle east http://sqlserver-performance-tuning.net/ , you can watch its success memories at http://www.youtube.com/user/ShehapElNagar Moderator and author at http://www.sql-server-performance.com , , the 1st SQL Server Author at MSDN Arabia http://msdn.microsoft.com/ar-sa/library/jj149119.aspx , Speaker at SQL Saturday Events worldwide , local events at Saudi Arabia , many online events , more than 90 video tutorials and also many private sessions for .net developers and Database Administrators And also influent participator at Microsoft Forums of SQL Server at http://social.technet.microsoft.com. More about him , you can find him on MVP Microsoft site http://mvp.microsoft.com/en-us/mvp/Shehap%20El-Nagar5000188 . You can contact him at the below contacts :Mail :idgdirector@yahoo.com ….Cellular phone :00966560700733

Agenda and Overview: First :How to Start Performance Analysis..? • • How to Capture Expensive Queries..? How to analyze them to find out performance bottlenecks..? Second :T-SQL Optimization by checking and replacing poor T-SQL Practices 1-The practice of Temp Tables and table variables Practice 2-The practice of Union Command 3-The practice of MTVF 4-The practice of Scalar Function 5-The Practice of Views 6-Fast Performance means Third: Index Tuning by checking and replacing all poor Indexes (Next Session)

• How to Capture Expensive Queries..? Microsoft /Third party tools SQL Server • APM (Application Performance Management) • Alerts-Based Performance monitors • • • • SQL Profiler / SQL Tracer Activity Monitor / SQL Server Agent Alerts UCP (Utility Control Point ) of 2008 R2 Dynamic Management Views (DMVs)

Microsoft /Third party tools 1- APM (Application Performance Management) : • • • • • • • Dyntrace /Gomez by Compuware Quest software tools Redgate DLD (SQL Deadlock Detector) SQL Trace Analyzer of SQL Server Solutions.com RML SQLIOSim (SQL IO Simulator ) (Exists per SQL Server 2008 Installation directory) 2- Alerts–Based Performance tools : • SCOM (System Centralized Operation Manager) • Application Manager /Operation Manager

• SQL Server Techniques /Tools 1- SQL Profiler / SQL Tracer: • • • High impact on DB Server Standstill cases if profiler is capturing frequent events on DB Server SQL Tracer is better coz it can save 70 % of Network bandwidth 2- Activity Monitor of SQL Server 2008 / SQL Server Agent Alerts 3-UCP (Utility Control Point of SQL Server 2008 R2) which can monitor: • SQL Server 2008 • SQL Server 2008 R2 • SQL Server 2012 4- Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs)

• How to Analyze Expensive Queries..? • Database Engine Tuning Advisor (Fundamental Way) Query-Level • Query Execution Plans (Advanced Way)

1- Update Statistics/ Rebuild indexes Update statistics Online Index Rebuild Find out Poor Execution Elements 2- Display Actual/Estimated Query Execution Plan Identify Relevant tables Check how they are used within Query (Joins/Where /Select / Scalar functions/Order By/Group By) 3- T-SQL Optimization 4- Index Tuning Alter these Elements by Address all poor T-SQL practices Display Again Query Execution plan to tune finally indexes Query Execution Plan (Advanced Way) Main Poor Query execution Elements : •Table Scan •Index Scan •TVF Scan •Key Lookup/bookmark lookup •RID lookup •Hash Match •High IO /CPU cost of indexes

1 –The Practice of Temp Tables

Impacts of Temp tables and Tables variables • Impact on I/O subsystem resources (SAN or local storage) • Standstill case due to much Page Latch waits. • Much Temp Contention of SGAM (Shared Global Allocation Mapping) , GAM (Global A , PFS (Page Free Space) • Impact on CPU utilization due to much Cxpacket waits resulted of insufficient indexing of those temp tables.

Healthy Cases of Temp Table usage 1. Small Volume (Records Number and size) of data entity to be inserted to Temp tables 2. Sufficient indexing on Temp Tables (clustered or non clustered)

Alternatives of Temp Tables 1. CTE expression 2. Sub queries. 3. Physical table (Schema Table) if relevant data entity are seldom changed. 4. Table Parameters (New 2008 features)

2 –The Practice of Union Commands

Impacts of Union Command • Impact on I/O subsystem resources (SAN or local storage) • Standstill case due to much Page Latch waits + Page I/O latch waits • TempDB contention as explained before

•Alternatives of Union • • Controlling no of unions commands according to parameters passed from end users. • Ordering Union Commands according to data volume of each select • Case When Commands • Dynamic T-SQL queries . •

Cases of MTVF Impact Usage of MTVF within Joins Usage of MTVF within Where conditions

How to overcome MTVF Impacts…? •Transferring all of MTVF (Multiline TVF) to ITVF (Inline TVF) as much as possible •Selecting the Output of MTVF into Temp Tables •Creating •Then •You Sufficient indexes on these Temp tables Replacing MTVF everywhere with These Temp tables should to test it along with your T-SQL queries to check IO cost of Temp tables

4- The Practice of Scalar functions

Alternatives : 1- Temp Tables as used before with MTVF 2- Persisted deterministic Computed Columns with Sufficient indexing for them 3- Creating Update Jobs on the appropriate columns to get values of scalar functions

How to get a Persisted deterministic Computed Columns…? •Not to use any aggregative functions of other records. •Not to use functions that call external system procedures •Not to use any functions of other fields of other tables. •To use much better System functions not UDF How to create index on them..? Precise Columns only can be put within key column part within index design Imprecise Columns like decimal ones can be added within include columns part

Impacts: •Much CPU Consumption & CXPacket waits particularly more for Huge data cases and Stressed workload cases •Dramatic performance degradation for the entire of DB Server due to CPU bottleneck

Alternatives: 1- Sub queries filtered with the appropriate conditions 2- CTEs (Common Table Expressions) 3- Indexed Views but this option has some restrictive limitations to enjoy with indexing feature such as : •Views should be based on a single table not multiple tables •No use for with (nolock) hint or such similar hints •No usage for any aggregative functions , Top or distinct commands •WITH SCHEMABINDING should be used •View should have unique clustered index.

Update Statistics & Index Rebuild: Update index statistics to help Query Analyzer selecting the best execution plan Index rebuild to remove index fragmentations to assure indexes are selected correctly Table Hints: Used to enforce Query execution plan to select specific indexes on certain tables to let it move with another different Route Map With (Index (Index_Name),Forceseek,nolock) With (forceseek (index_Name(Column1, Column2..))) Query Hints: Used to enforce the entire of query execution plan to move with another route path that might be better than the default one: Option ( Maxdop 4)……….Controllable CPU Consumption Option ( Hash match) ……Much Faster for Large volume of data Option ( fast N)…………….Faster Execution plan for the first (N) records Option (Keepfixed plan)…Save elapsed time of Re-estimating Execution plan

Q&A Post your questions at: http://www.sqlserver-performancetuning.net/forums/

Thank you ..See you again 1/23/2014

T sql performance guidelines for better db stress powers

T sql performance guidelines for better db stress powers

More Related Content

What's hot

Similar to T sql performance guidelines for better db stress powers

Recently uploaded

T sql performance guidelines for better db stress powers