Geek Sync | SQL Server 2017 - Let's Learn Automatic Tuning

Every new release of SQL Server brings a load of new features that an administrator can add to their arsenal of efficiency. SQL Server 2016 and 2017 are perfect examples of releases with many new and beneficial features.

Join IDERA and Pinal Dave on Wednesday, June 20 at 11 AM CT as he covers the new features in SQL Server 2016 / 2017. Pinal will walk through adaptive query plans, batch mode adaptive join, new cardinality estimate for optimal performance, adaptive query processing, and more. This is an interactive Geek Sync you will not want to miss!

You can view the Geek Sync recording here. You can view Pinal's slides here.

You can view Pinal's scripts below.

automatictuning.sql
-- Database Tuning Recommendations
SELECT reason, score,
 script = JSON_VALUE(details, '$.implementationDetails.script'),
 planForceDetails.[query_id],
 planForceDetails.[new plan_id],
 planForceDetails.[recommended plan_id],
 estimated_gain = (regressedPlanExecutionCount+recommendedPlanExecutionCount)*(regressedPlanCpuTimeAverage-recommendedPlanCpuTimeAverage)/1000000,
  JSON_VALUE(state, '$.currentValue') state,
  JSON_VALUE(state, '$.reason') state_transition_reason,
 error_prone = IIF(regressedPlanErrorCount>recommendedPlanErrorCount, 'YES','NO')
 FROM sys.dm_db_tuning_recommendations
     CROSS APPLY OPENJSON (Details, '$.planForceDetails')
                 WITH ( [query_id] int '$.queryId',
                        [new plan_id] int '$.regressedPlanId',
                        [recommended plan_id] int '$.recommendedPlanId',
                        regressedPlanErrorCount int,
                        recommendedPlanErrorCount int,
                        regressedPlanExecutionCount int,
                        regressedPlanCpuTimeAverage float,
                        recommendedPlanExecutionCount int,
                        recommendedPlanCpuTimeAverage float ) as planForceDetails
GO

compatlevel.sql
-- SQL Server 2017
ALTER DATABASE AdventureWorks2014 
SET COMPATIBILITY_LEVEL = 140
GO

stats.sql
------ Statistics ON
USE [master]
GO
ALTER DATABASE StatusON SET AUTO_CREATE_STATISTICS ON WITH NO_WAIT
GO
ALTER DATABASE StatusON SET AUTO_UPDATE_STATISTICS ON WITH NO_WAIT
GO