1 / 24

All the Magic Knobs

All the Magic Knobs. Low Effort, High Return Tuning. Cindy Gross Microsoft Certified Master : SQL Server Dedicated Support Engineer (DSE) Microsoft Premier Field Engineering (PFE) Cindy.Gross@microsoft.com http://blogs.msdn.com/cindygross / @ SQLCindy. Why do we want magic?.

senwe
Download Presentation

All the Magic Knobs

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. All the Magic Knobs Low Effort, High Return Tuning Cindy Gross Microsoft Certified Master : SQL Server Dedicated Support Engineer (DSE) Microsoft Premier Field Engineering (PFE) Cindy.Gross@microsoft.com http://blogs.msdn.com/cindygross/ @SQLCindy

  2. Why do we want magic? • Have a good foundation – the magic knobs • Minimum effort, maximum return • Tuning individual queries is resource intensive DBA-319-C| All the Magic Knobs

  3. When you can’t tune individual queries…. • Many consolidated databases • Non-Tier 1 databases • Databases you don’t understand or don’t control • 3rd party products • Standards/Starting Points DBA-319-C| All the Magic Knobs

  4. Where is the Magic? • Instance and database settings • Hardware • Windows configuration • User rights • Object settings that don’t require code changes • Maintenance DBA-319-C| All the Magic Knobs

  5. What is our goal? Low effort, high impact choices DBA-319-C| All the Magic Knobs

  6. Make it easy • Run the SQL Best Practices Analyzer • Run System Center Advisor • Do what they recommend DBA-319-C| All the Magic Knobs

  7. Power Savings • Power option set to High Performance • Bios and Windows levels • Uneven or oddly high CPU • Can set back to balanced off hours DBA-319-C| All the Magic Knobs

  8. IO • Fastest IO path(s) possible • Disk partition alignment • 64k block size • Understand degree of isolation from other apps • Understand tradeoff of many spindles vs. isolation • Bottleneck = - load, +capacity, or ignore DBA-319-C| All the Magic Knobs

  9. Virtual Machines • Set VM memory to avoid ballooning • No over-committing • Know where the VM does/can reside • Evaluate tradeoff of disk types DBA-319-C| All the Magic Knobs

  10. Outside SQL Server • Instant File Initialization on (small security risk) • Windows patches applied • Latest Windows storport, storage/SAN drivers, firmware, NIC drivers, MPIO • Exclude SQL Server from filter drivers • Reduce other apps on server • Throw hardware at the problem VDBA-319-C| All the Magic Knobs

  11. Optimize for ad hoc workloads • sp_configure setting in SQL Server 2008 and higher • Reduces size of plan cache • Caches only a stub for single use plans • SELECT '===TotalSizeOfStubs', COUNT(*) AS NumOfPlanStubs, SUM(size_in_bytes) AS BytesOfAllPlanStubs FROM sys.dm_exec_cached_plans • WHERE cacheobjtype = 'Compiled Plan Stub'; • SELECT '===TotalSizeOfCompiledPlansUsedOnce', COUNT(*) AS NumOfCompiledPlans, SUM(size_in_bytes) AS BytesOfAllCompiledPlans FROM sys.dm_exec_cached_plans • WHERE cacheobjtype= 'Compiled Plan' AND usecounts = 1; DBA-319-C| All the Magic Knobs

  12. Compression • New in SQL Server 2008 – some editions • Backup • Data – row or page • Uses more CPU, less memory and IO • Transparent to the application DBA-319-C| All the Magic Knobs

  13. Memory • Lock Pages in Memory (LPIM) • Max Server Memory set to allow other apps/services enough memory • Pagefile size generally irrelevant for SQL, though may be needed for other apps or kernel dumps DBA-319-C| All the Magic Knobs

  14. Database Sizing • Pre-size data and log files • Autogrow is a failsafe only • Set autogrow increments big enough to happen once but small enough to be fast • Alert on Autogrow occurrences • Avoid shrink unless you will never grow again or are cleaning up VLFs • Avoid Autoshrink DBA-319-C| All the Magic Knobs

  15. VLFs • Virtual Log Files (VLFs) – boundaries within a physical log file • View with DBCC LOGINFO • “Too many” = slow restore, online/start, transactional replication, mirroring, CDC • Shrink once, pre-size DBA-319-C| All the Magic Knobs

  16. Tempdb • Multiple data files of the same size, one log file • Enough data files (start with 8) to avoid contention, not so many to cause problems. • Presize for peak periods of next X months, re-evaluate • Fast IO subsystem • Change file size if you add new features that use tempdb • Monitor for approaching full, change in activity/size • Performance tune user databases and applications • Limit use of versioning or temp objects • -T1117 for even file growth DBA-319-C| All the Magic Knobs

  17. Maintenance • Rebuild Indexes • Reorg Indexes • Update Statistics (but not after rebuild) • Auto update/create statistics • Async update stats • Backup frequency DBA-319-C| All the Magic Knobs

  18. What magic tricks are in your hat? • Power Savings = High Performance • Smart Virtualization • Enough Hardware • Control other apps, filter drivers • Optimize for ad hoc workloads = ON • Compression = ON • Set LPIM + Max Server Memory • Pre-size files, avoid shrink and autogrow • Fast Tempdb • Proper Maintenance DBA-319-C| All the Magic Knobs

  19. Audience Tips and Questions • Share your tips • Questions? Cindy Gross Dedicated Support Engineer (DSE) Microsoft Premier Field Engineering (PFE) Cindy.Gross@microsoft.com http://blogs.msdn.com/cindygross/ DBA-319-C| All the Magic Knobs

  20. Hands-on Labs Get experienced through self-paced & instructor-led labs on our cloud based lab platform - bring your laptop or use HP provided hardware Microsoft SQL Server Clinic Work through your technical issues with SQL Server CSS & get architectural guidance from SQLCAT Expert Pods Meet Microsoft SQL Server Engineering team members & SQL MVPs Microsoft Product Pavilion Talk with Microsoft SQL Server & BI experts to learn about the next version of SQL Server and check out the new Database Consolidation Appliance Room611 ExpoHall 6th Floor Lobby Room 618-620 Session Code | Session Title

  21. Microsoft Certified Master

  22. DBA-319-C| All the Magic Knobs Microsoft CSS @ PASS 2011 Breakout Sessions Pre-Con • Winning with Kerberos • Mon 10/10 8:30am-4:30pm • Adam Saxton • Inside TempDB • DBA-500-HD • Wed 10/12 1:30-4:30pm • Bob Ward All the Magic Knobs DBA-319-C Thurs 10/13 3:00-4:15pm Cindy Gross “Denali” Performance Dashboard Reports DBA-215-C Fri 10/14 10:15-11:30pm Keith Elmore Boris Baryshnikov • Getting your Mind Wrapped Around • SQL Azure • AZ-301-C • Fri 10/14 1:00-2:15pm • Evan Basalik • Room 611 • 10/12 – 10/14 • After Keynote • Until 6:00pm…ish

  23. Complete the Evaluation Form to Win! Sponsored by Dell • Win a Dell Mini Netbook – every day – just for submitting your completed form. Each session evaluation form represents a chance to win. • Pick up your evaluation form: • In each presentation room • Online on the PASS Summit website • Drop off your completed form: • Near the exit of each presentation room • At the Registration desk • Online on the PASS Summit website Session Code | Session Title

  24. Thank you for attending this session and the 2011 PASS Summit in Seattle

More Related