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.
-- Database Tuning Recommendations
SELECT reason, score,
script = JSON_VALUE(details, '$.implementationDetails.script'),
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')
CROSS APPLY OPENJSON (Details, '$.planForceDetails')
WITH ( [query_id] int '$.queryId',
[new plan_id] int '$.regressedPlanId',
[recommended plan_id] int '$.recommendedPlanId',
recommendedPlanCpuTimeAverage float ) as planForceDetails
-- SQL Server 2017
ALTER DATABASE AdventureWorks2014
SET COMPATIBILITY_LEVEL = 140
------ Statistics ON
ALTER DATABASE StatusON SET AUTO_CREATE_STATISTICS ON WITH NO_WAIT
ALTER DATABASE StatusON SET AUTO_UPDATE_STATISTICS ON WITH NO_WAIT