1 / 38

Optimizing SQL Performance with Modern Hardware

Explore SQL performance fundamentals and concepts, hardware advancements, potential pitfalls, and strategies for efficient query optimization in 2011. Learn about parameters, compile options, statistics, and execution plans to enhance performance.

gay
Télécharger la présentation

Optimizing SQL Performance with Modern Hardware

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 Performance 2011/12 Joe Chang, SolidQwww.qdpma.com http://sqlblog.com/blogs/joe_chang/default.aspx jchang6@yahoo.com

  2. 2011 • Hardware is Powerful & Cheap • CPU (cores), memory, and now IO too! • Quad-core since 2006, 1GHz since 2000 • Is Performance still a concern? • Yes, along with fundamentals • Modern Performance Strategy • Can handle minor inefficiencies • Identify and circumvent the really bad things

  3. Modern Hardware • 4-12 cores per processor socket • 16GB DIMM at less than $1K • SSD • Enterprise grade SSD still moderately expensive • Both SLC and MLC • Consumer SSD – really cheap, $3-4K per TB • Uneven performance characteristics over time • Desired IO performance: 1-2GB/s, 20-50K IOPS

  4. Hardware Baseline 2011 Entry Mid-range 2 Xeon 5600 6-core 48 – 192GB 6 x 8GB to 12 x 16GB SSD options 16+ SATA SSDs 4-5 PCI-E SSDs • 1 Xeon E3 quad-core • 16GB memory • 4 x 4GB unbuffered ECC • SSD options: • 2-4 SATA SSDs • 1-2 PCI-E SSDs

  5. Performance Fundamentals • Network round-trips • Owner qualified, case correct • Log write latencies • Sufficiently low to support transaction volume • Not necessarily separate data and log disks • Normalization – correct data trumps all! • Indexes – a few good ones, and not too many! • SQL – that the optimizer

  6. What can go wrong? • With immense hardware resources • And a great database engine • What can go wrong? • Following a fixed set of rules and procedures • Basic transactions processing should work well • If your process does something unanticipated • Some things can go horribly wrong

  7. Performance Concepts • Query Optimizer • Execution plan operators • Formula for component operation • Data distribution statistics • to estimate rows & pages, automatically updated • Rules when estimate not possible • Stored procedure compile rules • Parameters and variables

  8. Stored Procedure Basics

  9. Parameters and Variables • On compile • Parameter values used to for row estimate • Variables – assume unknown value • Consider effect of skewed distribution

  10. Parameter & Variable 6 rows for value 1 4 rows for value unknown Consider impact for skewed data distributions

  11. Stored Procedure Compile Options • WITH RECOMPILE • OPTIMIZE FOR • Plan Guide • Temp table • KEEP PLAN, KEEPFIXED PLAN

  12. Compile & Execute Time • Plan reuse desired when • Compile cost is high relative to execute cost • Recompile desired when • Execute cost is high relative to compile cost

  13. Statistics Basics

  14. Statistics • No statistics – table variables • Temp table – statistics auto recompute • 6 row modified, 500 rows, every 20% thereafter • Statistics sampling • Random page, how to handle skewed distribution? • Upper and lower bounds • Problems caused by incrementing columns • Propagation errors

  15. Statistics Recompute • Scenario: start with accurate statistics • Update column with new values • That did not previously exist • If fewer than 20% of rows updated • Auto-recompute is not triggered

  16. Sampling • Default sampling percentage is usually good • Caution: not a random row sample! • Random sampling of page • From nonclustered index if available • If there is correlation between pages & values • Then serious over estimation possible

  17. Out of range • Statistics sampling tries to identify lower and upper bound

  18. Bad Execution Plan Examples Not comprehensive

  19. Scenarios Not comprehensive • Or condition • Multiple optional search arguments • Skewed distributions • 1 business logic for Small and large data sets • Reports for 1 day, 1 week, 1 month, 1 year • Statistics related problems • Resulting in horrible execution plan

  20. When the Query Optimizer Does not understand you

  21. Simple OR Conditions

  22. Simple Index Seek

  23. OR Condition in Join

  24. Alternative – Union

  25. UNION and UNION ALL • UNION • Only distinct rows • Sort to eliminate duplicates • Can be expensive for high row counts • UNION ALL • All rows • No sort to eliminate duplicates

  26. Multiple Optional SARGs This was suppose to work, but does not

  27. Parameterized SQL

  28. Skew and Range Variation

  29. Statistics Out-of Range

  30. Statistics Out of Range (cont.)

  31. Table Variable No Statisticsassumes 1 row, 1page Why? No recompiles

  32. Loop Join – Scan Inner Source Estimate 1 row Really Bad News

  33. The Correct Plan Estimate 1 row Hash Join forcedwith hint

  34. Loop Join vs. Hash Join

  35. Alternative Plan with Index

  36. Temp Table versus CTE • Consider options • SELECT xxx INTO #Temp • FROM Sql Main Expression • WITH tmp AS (SELECT xxx FROM Sql) Main Expression

More Related