Top Tuning Tools for SQL Server Kevin Kline

  • Slides: 9
Download presentation
Top Tuning Tools for SQL Server Kevin Kline & Aaron Bertrand SQL Sentry

Top Tuning Tools for SQL Server Kevin Kline & Aaron Bertrand SQL Sentry

About Us Kevin Kline @kekline Aaron Bertrand @Aaron. Bertrand Director, Engineering Services Senior Consultant

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

Agenda § SQL Sentry Plan Explorer o o o Free Features PRO Features Hairy

Agenda § SQL Sentry Plan Explorer o o o Free Features PRO Features Hairy Execution Plan contest winners § SQL Sentry Performance Advisor o Helping automate problem query detection § SQL Server 2014 Features o o Buffer Pool Extension Enhanced Cardinality Estimator § Bonus Tips o A couple of simple tips to see immediate query benefits 3 © SQLintersection. All rights reserved. http: //www. SQLintersection. com

SQL Sentry Plan Explorer § Plan Explorer download: o http: //sqlsentry. com/plan-explorer § Many

SQL Sentry Plan Explorer § Plan Explorer download: o http: //sqlsentry. com/plan-explorer § Many of these demos can be downloaded: o http: //sqlperformance. com/updated-plan-explorer-demo-kit § Plural. Sight Course by Jonathan Kehayias: o http: //bit. ly/PETraining 4 © SQLintersection. All rights reserved. http: //www. SQLintersection. com

SQL Sentry Performance Advisor § Best in class performance monitoring and alerting. v 8

SQL Sentry Performance Advisor § Best in class performance monitoring and alerting. v 8 adds: o o o Intelligent Alerting Custom Conditions Configurable Baselines § Low Overhead: o http: //downloads. sqlsentry. com/downloads/SQLSentry. Overhead. Analysis. doc § Performance Advisor download: o http: //sqlsentry. com/download-trial/trial § Demonstration Videos at: o http: //sqlsentry. tv 5 © SQLintersection. All rights reserved. http: //www. SQLintersection. com

SQL Server 2014 Features § Buffer Pool Extensions o o Extend buffer pool to

SQL Server 2014 Features § Buffer Pool Extensions o o 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 o o Much better estimation, leading to better query plans in most cases Demo 6 © SQLintersection. All rights reserved. http: //www. SQLintersection. com

Bonus Tips : Two Easy Performance Boosts § SET NOCOUNT ON o Turns off

Bonus Tips : Two Easy Performance Boosts § SET NOCOUNT ON o Turns off n row(s) affected / DONE_IN_PROC noise § Non-default cursor options o Demo 7 © SQLintersection. All rights reserved. http: //www. SQLintersection. com

Bonus Tips : Making “dev” like production § DBCC AUTOPILOT / SET AUTOPILOT ON

Bonus Tips : Making “dev” like production § DBCC AUTOPILOT / SET AUTOPILOT ON o Create hypothetical indexes in production § Stats-only (“clone”) database o http: //bit. ly/Stats. Only. DB § DBCC OPTIMIZER_WHATIF o o Fool SQL Server into believing your dev box is more or less powerful Demo 8 © SQLintersection. All rights reserved. http: //www. SQLintersection. com

THANK YOU! Contact: kkline@sqlsentry. com abertrand@sqlsentry. com 9 © SQLintersection. All rights reserved. http:

THANK YOU! Contact: kkline@sqlsentry. com abertrand@sqlsentry. com 9 © SQLintersection. All rights reserved. http: //www. SQLintersection. com