Transcript Presentation - SQLPerformance.com

Top Tuning Tools for SQL Server
Kevin Kline & Aaron Bertrand
SQL Sentry
About Us
Kevin Kline
@kekline
Aaron Bertrand
@AaronBertrand
Director, Engineering Services
Senior Consultant
Microsoft MVP since 2003
Author, SQL in a Nutshell and 11 more
http://KevinEKline.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



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
3
© SQLintersection. All rights reserved.
http://www.SQLintersection.com
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
4
© SQLintersection. All rights reserved.
http://www.SQLintersection.com
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
5
© SQLintersection. All rights reserved.
http://www.SQLintersection.com
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
6
© SQLintersection. All rights reserved.
http://www.SQLintersection.com
Bonus Tips : Two Easy Performance Boosts
 SET NOCOUNT ON

Turns off n row(s) affected / DONE_IN_PROC noise
 Non-default cursor options

Demo
7
© SQLintersection. All rights reserved.
http://www.SQLintersection.com
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
8
© SQLintersection. All rights reserved.
http://www.SQLintersection.com
THANK YOU!
Contact:
[email protected]
[email protected]
9
© SQLintersection. All rights reserved.
http://www.SQLintersection.com