1 / 9

Top Tuning Tools for SQL Server

Top Tuning Tools for SQL Server. Kevin Kline & Aaron Bertrand SQL Sentry. About Us. Kevin Kline @kekline Director, Engineering Services Microsoft MVP since 2003 Author, SQL in a Nutshell and 11 more http://KevinEKline.com http://SQLSentry.TV.

shawn
Download Presentation

Top Tuning Tools for SQL Server

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. Top Tuning Tools for SQL Server Kevin Kline & Aaron Bertrand SQL Sentry

  2. About Us Kevin Kline@kekline Director, Engineering Services Microsoft MVP since 2003 Author, SQL in a Nutshell and 11 more http://KevinEKline.com http://SQLSentry.TV Aaron Bertrand@AaronBertrand Senior Consultant Microsoft MVP since 1997 Author, MVP Deep Dives 1 & 2 http://sqlblog.com/ http://sqlperformance.com/ http://sqlsentry.com/

  3. Agenda • SQL Sentry Plan Explorer • Free Features • PRO Features • Hairy Execution Plan contest winners • SQL Sentry Performance Advisor • Helping automate problem query detection • SQL Server 2014 Features • Buffer Pool Extension • Enhanced Cardinality Estimator • Bonus Tips • A couple of simple tips to see immediate query benefits

  4. SQL Sentry Plan Explorer • Plan Explorer download: • http://sqlsentry.com/plan-explorer • Many of these demos can be downloaded: • http://sqlperformance.com/updated-plan-explorer-demo-kit • PluralSight Course by Jonathan Kehayias: • http://bit.ly/PETraining

  5. SQL Sentry Performance Advisor • Best in class performance monitoring and alerting. v8 adds: • Intelligent Alerting • Custom Conditions • Configurable Baselines • Low Overhead: • http://downloads.sqlsentry.com/downloads/SQLSentryOverheadAnalysis.doc • Performance Advisor download: • http://sqlsentry.com/download-trial/trial • Demonstration Videos at: • http://sqlsentry.tv

  6. SQL Server 2014 Features • Buffer Pool Extensions • Extend buffer pool to SSDs to act somewhat like memory • Great solution for large databases on servers with SSD but limited RAM • Enhanced Cardinality Estimator • Much better estimation, leading to better query plans in most cases • Demo

  7. Bonus Tips : Two Easy Performance Boosts • SET NOCOUNT ON • Turns off n row(s) affected / DONE_IN_PROC noise • Non-default cursor options • Demo

  8. Bonus Tips : Making “dev” like production • DBCC AUTOPILOT / SET AUTOPILOT ON • Create hypothetical indexes in production • Stats-only (“clone”) database • http://bit.ly/StatsOnlyDB • DBCC OPTIMIZER_WHATIF • Fool SQL Server into believing your dev box is more or less powerful • Demo

  9. THANK YOU!Contact:kkline@sqlsentry.comabertrand@sqlsentry.com

More Related