What’s New in SQL Server 2019: 100% Demos

Want to learn more? Check out my recent SQL Server 2019 posts.

You haven’t installed SQL Server 2019 yet, and you don’t want to sit through a bunch of slides. You want to see a series of real-world demos focusing on the features of SQL Server 2019 that will make your users happier with as little work as possible on your part. You don’t have time to rewrite all your queries or learn new languages – you just want the database to go faster.

I’m Brent Ozar, and I’ll give you the whirlwind tour. I’ll focus on just the features that make the biggest bang for the buck, things you can implement right away and see a noticeable difference. I’ll show you what kinds of code will benefit the most so you can go back to the office and have an honest discussion with management about what to expect.

No slides here – not even an about-me slide. Let’s dive into some queries!

To follow along, download the Stack Overflow database – any size will work, but onstage, I use the mid-size (50GB) Stack Overflow 2013 database.

Demo Script

/* 

What's New in SQL Server 2019: 100% Demos

Brent Ozar - v1.9 - 2019-11-21

Download the latest version free: https://BrentOzar.com/go/whatsnew
Open source with the MIT License. For full details, see the end of this file.

Requirements:
* SQL Server 2019
* Stack Overflow database of any size: https://BrentOzar.com/go/querystack

This first RAISERROR is just to make sure you don't accidentally hit F5 and
run the entire script. You don't need to run this:
*/
RAISERROR(N'Oops! No, don''t just hit F5. Run these demos one at a time.', 20, 1) WITH LOG;
GO


/* Demo setup: */
USE StackOverflow2013;
GO
DropIndexes;
GO
CREATE INDEX IX_Reputation ON dbo.Users(Reputation);
CREATE INDEX IX_Location ON dbo.Users(Location);
CREATE INDEX IX_OwnerUserId ON dbo.Posts(OwnerUserId) INCLUDE (Score, Title);
CREATE INDEX IX_PostId ON dbo.Comments(PostId);
ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;
SET STATISTICS IO, TIME ON;
GO





ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 140; /* 2017 */
GO
DECLARE @CoolCars TABLE (Make VARCHAR(30), Model VARCHAR(30));

INSERT INTO @CoolCars (Make, Model)
  VALUES ('Porsche', '911');

SELECT * FROM @CoolCars;
GO




DECLARE @CoolCars TABLE (Make VARCHAR(30), Model VARCHAR(30));

INSERT INTO @CoolCars (Make, Model)
  VALUES ('Porsche', '911'),
         ('Audi', 'RS5'),
		 ('Dodge', 'Hellcat'),
		 ('Chevrolet', 'Corvette'),
		 ('BMW', 'M5');

SELECT * FROM @CoolCars;
GO


/* Possible solutions in the past: change the code to add recompile hint, temp tables 





Or...just switch to 2019 compatibility level:
*/
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 150; /* 2019 */
GO
DECLARE @CoolCars TABLE (Make VARCHAR(30), Model VARCHAR(30));

INSERT INTO @CoolCars (Make, Model)
  VALUES ('Porsche', '911'),
         ('Audi', 'RS5'),
		 ('Dodge', 'Hellcat'),
		 ('Chevrolet', 'Corvette'),
		 ('BMW', 'M5');

SELECT * FROM @CoolCars;
GO









/* You probably still find stored procedures with table variables: */
CREATE OR ALTER PROC dbo.usp_PostsByUserLocation @Location NVARCHAR(40) AS
BEGIN
	DECLARE @UserList TABLE (Id INT);
	INSERT INTO @UserList (Id)
	  SELECT Id FROM dbo.Users WHERE Location LIKE @Location

    SELECT TOP 1000 p.Score, p.Id, p.Title, p.Body, p.Tags, uC.DisplayName, c.Text
        FROM @UserList u
        JOIN dbo.Posts p ON p.OwnerUserId = u.Id AND p.PostTypeId = 1
		LEFT OUTER JOIN dbo.Comments c ON p.Id = c.PostId
		LEFT OUTER JOIN dbo.Users uC ON c.UserId = uC.Id
        ORDER BY p.Score DESC, c.CreationDate;
END
GO



ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 140; /* 2017 */
GO
EXEC usp_PostsByUserLocation @Location = 'United States%';
GO
/* 
SQL Server 2017:
* Underestimates rows in the table variable, 
* Which leads to single-threaded processing and a low memory grant
* Which leads to tempdb spills 
*/

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 150; /* 2019 */
GO
EXEC usp_PostsByUserLocation @Location = 'United States';
GO

/* 
SQL Server 2019 is quite a bit faster because:

* It accurately estimates rows for the table variable
* Memory grant is more accurate
* No tempdb spills


Before we go on, note:
* The memory grant: 
* The number of users: 
* The number of questions: 

Then try another location: 
*/
EXEC usp_PostsByUserLocation @Location = 'Boston, MA, USA';
GO


/* 
The good news: table variables get stats.

The bad news: now they're vulnerable to parameter sniffing.
*/







/*
SQL Server has a new tool to help: adaptive memory grants.
*/
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 140; /* 2017 */
GO
EXEC usp_PostsByUserLocation @Location = 'United States';
GO



/* And again: */
EXEC usp_PostsByUserLocation @Location = 'United States';
GO





ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 150; /* 2019 */
GO
EXEC usp_PostsByUserLocation @Location = 'United States';
GO
/* Try that a few times */



/* Try that a few times, then: */
EXEC usp_PostsByUserLocation @Location = 'Boston, MA, USA';
GO


/* Here comes our American friend again: */
EXEC usp_PostsByUserLocation @Location = 'United States';
GO


/* 
The good news: your memory grant will change, preventing spills and RESOURCE_SEMAPHORE.

The bad news:
* Your memory grant will drop, CAUSING spills
* It will flip back & forth, and then settle on a number which may not be good either
* This process restarts every time you rebuild an index, update stats, fail over, reboot

The great news:
* In many cases, it's still better than prior versions were!

*/







/* SQL Server 2019 has a new tool to help: the ability to see a query's last 
actual execution plan! It's turned on at the database level with this, which I
ran at the start of the demos:

ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;

In another window (to save the original actual plan), turn off actual plans: */
sp_BlitzCache;
GO

/* Or if you like working with the plan handles directly: */
SELECT * FROM sys.dm_exec_query_plan_stats(0x050005000AFEBC35F0BE92666B01000001000000000000000000000000000000000000000000000000000000);
GO
/* 
The good news: you can get the last "actual" plan for a query that ran recently.

The bad news:
* No wait stats
* No elapsed time
* No CPU metrics
* The memory grant is there, but it's wrong - it's for the NEXT execution

Still a fantastic starter for actual vs estimated rows, though.

It does have drawbacks that impact performance, so I'm going to turn it off for
the rest of the demos:
*/
ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = OFF;
GO






/* Next SQL Server 2019 feature: inlined user-defined functions, aka Froid. 

Warm up the cache:
*/
SELECT COUNT(*) FROM dbo.Votes;
GO

CREATE OR ALTER FUNCTION dbo.GetVotesCount ( @UserId INT )
RETURNS BIGINT
    WITH RETURNS NULL ON NULL INPUT
AS
    BEGIN
        DECLARE @Count BIGINT;
        SELECT  @Count = COUNT_BIG(*)
        FROM    dbo.Votes
        WHERE   UserId = @UserId;
        RETURN @Count;
    END;
GO

/* Use it in a stored procedure: */
CREATE OR ALTER PROC dbo.usp_UsersByReputation @Reputation INT AS
    SELECT TOP 10 u.DisplayName, u.Location, u.WebsiteUrl, u.AboutMe, u.Id
        FROM dbo.Users u
        WHERE Reputation = @Reputation
		  AND dbo.GetVotesCount(u.Id) = 0; /* BAD IDEA */
GO



/* Go back to SQL Server 2017 and run it with ACTUAL PLANS on: */
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 140;
GO
DBCC FREEPROCCACHE;
GO
EXEC usp_UsersByReputation @Reputation = 2;
GO


/* 
Things to note:
* It takes a little while on my laptop.
* Both the plan and stats IO hide the impact. 
* The plan appears to be limited to a single core.

To see the truth, run this in another window without actual plans:
*/
sp_BlitzCache;
GO





/* Switch to 2019 and run it with ACTUAL PLANS on: */
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 150;
GO
DBCC FREEPROCCACHE;
GO
EXEC usp_UsersByReputation @Reputation = 2;
GO



/* May need to get the estimated plan in another window while this runs. ;-) */




DBCC FREEPROCCACHE;
GO
/* Just get the estimated plan, and note no parallelism: */
EXEC usp_UsersByReputation @Reputation = 2;
GO


/* And yet this query goes parallel: */
SELECT COUNT(*) FROM dbo.Users;
GO


/* 
The good news: SQL Server 2019 automatically inlines some kinds of functions

The bad news:
* But that doesn't mean your query will go faster
* Because it also changes the shape of the plan
* It doesn't mean your query will go parallel either
* Test, test, test
* You can still do way better by getting rid of the function yourself:
*/
CREATE OR ALTER PROC dbo.usp_UsersByReputation @Reputation INT AS
    SELECT TOP 10 u.DisplayName, u.Location, u.WebsiteUrl, u.AboutMe, u.Id
        FROM dbo.Users u
		LEFT OUTER JOIN dbo.Votes v ON u.Id = v.UserId
        WHERE Reputation = @Reputation
		  AND v.Id IS NULL;
GO


EXEC usp_UsersByReputation @Reputation = 2;
GO


/* Inline scalar functions can even crash your SQL Server:
https://dba.stackexchange.com/questions/253499/
https://sql-sasquatch.blogspot.com/2019/11/sql-server-2019-udf-inlining-oom-in.html
*/



/* Fine, forget functions. 

Next feature: adaptive joins on rowstore tables.

Take a stored procedure - no functions this time:
*/
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 150; /* SQL Server 2019 */
GO
CREATE OR ALTER PROC dbo.usp_UsersByReputation @Reputation INT AS
    SELECT TOP 100000 u.Id, p.Title, p.Score
        FROM dbo.Users u
        JOIN dbo.Posts p ON p.OwnerUserId = u.Id
        WHERE u.Reputation = @Reputation
        ORDER BY p.Score DESC;
GO

/* And run it: */
EXEC usp_UsersByReputation @Reputation = 1;
GO



/* Check out that adaptive join:
* Adaptive threshold in tooltip
* Over threshold: do an index scan
* Under: do a seek



Try another reputation, and it chooses the seek: */
EXEC usp_UsersByReputation @Reputation = 2;
GO

/* Try the big one: */
EXEC usp_UsersByReputation @Reputation = 1;
GO




/* Check the actual plan.
* Adaptive joins need memory
* That memory is affected by adaptive grants
* We ran it for a tiny reputation (2), then a big one (1) - it got a tiny grant

Try running it again, and it spills way less:
*/
EXEC usp_UsersByReputation @Reputation = 1;
GO

/* May need to do that a few times to stabilize */


/* But as soon as someone runs it for a tiny value again... */
EXEC usp_UsersByReputation @Reputation = 2;
GO

/* And then 1 runs, he's screwed again. */
EXEC usp_UsersByReputation @Reputation = 1;
GO


/* Moral of the story: parameter sniffing just got a LOT harder. 

Plus, check this out:
*/
DBCC FREEPROCCACHE;
GO
EXEC usp_UsersByReputation @Reputation = 1 WITH RECOMPILE; /* Gets an adaptive join */
EXEC usp_UsersByReputation @Reputation = 2 WITH RECOMPILE; /* Index seek on Users, no adaptive join, single threaded */
EXEC usp_UsersByReputation @Reputation = 3 WITH RECOMPILE; /* Index seek on Users, no adaptive join, parallel */



/*
The good news: SQL Server 2019 has more execution plan options.

The bad news: parameter sniffing just got harder to troubleshoot and fix, not easier.
*/










/* Next feature: batch mode on rowstore. Run this twice just to get a cached baseline, and note CPU time: */
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 140;
GO
SELECT YEAR(v.CreationDate) AS CreationYear, MONTH(v.CreationDate) AS CreationMonth,
    COUNT(*) AS VotesCount,
    AVG(BountyAmount * 1.0) AS AvgBounty
  FROM dbo.Votes v
  GROUP BY YEAR(v.CreationDate), MONTH(v.CreationDate)
  ORDER BY YEAR(v.CreationDate), MONTH(v.CreationDate)
GO


/* Then try in 2019: */
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 150;
GO
SELECT YEAR(v.CreationDate) AS CreationYear, MONTH(v.CreationDate) AS CreationMonth,
    COUNT(*) AS VotesCount,
    AVG(BountyAmount * 1.0) AS AvgBounty
  FROM dbo.Votes v
  GROUP BY YEAR(v.CreationDate), MONTH(v.CreationDate)
  ORDER BY YEAR(v.CreationDate), MONTH(v.CreationDate)
GO



/*
The good news: reporting-style queries with grouping, aggregates, etc are getting way faster.

The bad news: again, more query plan choices - some of which can go wrong.
*/



/* To make that even faster, we can create indexes.
Resumable index creation */
CREATE NONCLUSTERED INDEX IX_CreationDate_Includes on dbo.Votes (CreationDate) INCLUDE (PostId, UserId, BountyAmount, VoteTypeId) 
  WITH (ONLINE=ON, RESUMABLE=ON, MAX_DURATION=1);
GO

/* In another window: */
ALTER INDEX IX_CreationDate_Includes ON dbo.Votes PAUSE;

SELECT * FROM sys.index_resumable_operations;


ALTER INDEX IX_CreationDate_Includes ON dbo.Votes ABORT;
ALTER INDEX IX_CreationDate_Includes ON dbo.Votes RESUME;


/* But you have to be careful, because that's not the only way to pause an index creation.
Watch what happens when I cancel my own query:
*/
CREATE NONCLUSTERED INDEX IX_CreationDate_Includes on dbo.Votes (CreationDate) INCLUDE (PostId, UserId, BountyAmount, VoteTypeId) 
  WITH (ONLINE=ON, RESUMABLE=ON, MAX_DURATION=1);
GO


/* And same thing if I kill it.

And if there are any pending operations on the table, you can't do others:
*/
CREATE INDEX IX_UserId ON dbo.Votes(UserId);

/*
So moral of the story:
* Don't use RESUMABLE = ON unless you really want it
* If you want it, don't cancel your query unless you clean up after yourself
* Watch the contents of sys.index_resumable_operations

And whatever you do, don't do this.
This makes all index creations escalate automatically: 
*/
ALTER DATABASE SCOPED CONFIGURATION 
    SET ELEVATE_ONLINE = WHEN_SUPPORTED;
ALTER DATABASE SCOPED CONFIGURATION 
    SET ELEVATE_RESUMABLE = WHEN_SUPPORTED;
GO


/* So now, even if I don't ask for resumable, and I cancel my query: */
CREATE NONCLUSTERED INDEX IX_CreationDate_Includes on dbo.Votes (CreationDate) INCLUDE (PostId, UserId, BountyAmount, VoteTypeId) 
--  WITH (ONLINE=ON, RESUMABLE=ON, MAX_DURATION=1);

/* If I cancel my query, it's left behind as resumable: */
SELECT * FROM sys.index_resumable_operations;
GO





/* 
Speaking of canceling queries - you know what sucks? Rollbacks and failovers.


I'm going to set up an index to slow down my updates: 
*/
CREATE INDEX IX_Reputation_LastAccessDate ON dbo.Users(Reputation, LastAccessDate);
GO


/* Run a transaction that does a lot of work: */
BEGIN TRAN
UPDATE dbo.Users
  SET Reputation = 1000000,
      LastAccessDate = GETDATE();
GO


/* Oops! Forgot our WHERE clause! Gotta roll back. */
ROLLBACK
GO


ALTER DATABASE StackOverflow2013 SET ACCELERATED_DATABASE_RECOVERY = ON;
SELECT * FROM sys.databases;

/* Run a transaction that does a lot of work: */
BEGIN TRAN
UPDATE dbo.Users
  SET Reputation = 1000000,
      LastAccessDate = GETDATE();
GO

/* How much space is being used? */
SELECT TOP 100 * FROM sys.dm_tran_persistent_version_store_stats
SELECT TOP 100 * FROM sys.dm_tran_top_version_generators
SELECT TOP 100 * FROM sys.dm_tran_version_store
SELECT TOP 100 * FROM sys.dm_tran_version_store_space_usage

/* Clustered index */
SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.Users'), 1, 0, 'DETAILED')

/* Nonclustered index */
SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.Users'), 2, 0, 'DETAILED')


/* The big payoff: */
ROLLBACK;
GO





/*
SQL Server 2019 will help most of this code run faster:

* Table variables
* Scalar user-defined functions
* Reports with grouping, aggregate functions on tables without columnstore indexes
* Rollbacks & failovers

And these DBA jobs are easier:

* Seeing the "actual" plan for a recently-slow query
* Index creations
* Dealing with questions about rollbacks

But these DBA jobs are harder:

* Testing ALL of your code before you go live
* Dealing with parameter sniffing and unpredictable performance

Next step to learn more: BrentOzar.com/go/whatsnew
*/


/* 
MIT License

Copyright (c) 2019 Brent Ozar Unlimited

Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE.

*/

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

414

415

416

417

418

419

420

421

422

423

424

425

426

427

428

429

430

431

432

433

434

435

436

437

438

439

440

441

442

443

444

445

446

447

448

449

450

451

452

453

454

455

456

457

458

459

460

461

462

463

464

465

466

467

468

469

470

471

472

473

474

475

476

477

478

479

480

481

482

483

484

485

486

487

488

489

490

491

492

493

494

495

496

497

498

499

500

501

502

503

504

505

506

507

508

509

510

511

512

513

514

515

516

517

518

519

520

521

522

523

524

525

526

527

528

529

530

531

532

533

534

535

536

537

538

539

540

541

542

543

544

545

546

547

548

549

550

551

552

553

554

555

556

557

558

559

560

561

562

563

564

565

566

567

568

569

570

571

572

573

574

575

576

577

578

579

580

581

582

583

584

585

586

587

588

589

590

591

592

593

594

595

596

597

598

599

600

601

602

603

604

605

606

607

608

609

610

611

612

613

614

615

616

617

618

619

620

621

622

623

624

625

626

627

628

629

630

631

632

633

634

635

636

637

638

639

640

What's New in SQL Server 2019: 100% Demos

Brent Ozar - v1.9 - 2019-11-21

Download the latest version free: https://BrentOzar.com/go/whatsnew

Open source with the MIT License. For full details, see the end of this file.

Requirements:

* SQL Server 2019

* Stack Overflow database of any size: https://BrentOzar.com/go/querystack

This first RAISERROR is just to make sure you don't accidentally hit F5 and

run the entire script. You don't need to run this:

RAISERROR(N'Oops! No, don''t just hit F5. Run these demos one at a time.', 20, 1) WITH LOG;

/* Demo setup: */

USE StackOverflow2013;

DropIndexes;

CREATE INDEX IX_Reputation ON dbo.Users(Reputation);

CREATE INDEX IX_Location ON dbo.Users(Location);

CREATE INDEX IX_OwnerUserId ON dbo.Posts(OwnerUserId) INCLUDE (Score, Title);

CREATE INDEX IX_PostId ON dbo.Comments(PostId);

ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;

SET STATISTICS IO, TIME ON;

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 140; /* 2017 */

DECLARE @CoolCars TABLE (Make VARCHAR(30), Model VARCHAR(30));

INSERT INTO @CoolCars (Make, Model)

VALUES ('Porsche', '911');

SELECT * FROM @CoolCars;

DECLARE @CoolCars TABLE (Make VARCHAR(30), Model VARCHAR(30));

INSERT INTO @CoolCars (Make, Model)

VALUES ('Porsche', '911'),

('Audi', 'RS5'),

('Dodge', 'Hellcat'),

('Chevrolet', 'Corvette'),

('BMW', 'M5');

SELECT * FROM @CoolCars;

/* Possible solutions in the past: change the code to add recompile hint, temp tables

Or...just switch to 2019 compatibility level:

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 150; /* 2019 */

DECLARE @CoolCars TABLE (Make VARCHAR(30), Model VARCHAR(30));

INSERT INTO @CoolCars (Make, Model)

VALUES ('Porsche', '911'),

('Audi', 'RS5'),

('Dodge', 'Hellcat'),

('Chevrolet', 'Corvette'),

('BMW', 'M5');

SELECT * FROM @CoolCars;

/* You probably still find stored procedures with table variables: */

CREATE OR ALTER PROC dbo.usp_PostsByUserLocation @Location NVARCHAR(40) AS

BEGIN

DECLARE @UserList TABLE (Id INT);

INSERT INTO @UserList (Id)

SELECT Id FROM dbo.Users WHERE Location LIKE @Location

SELECT TOP 1000 p.Score, p.Id, p.Title, p.Body, p.Tags, uC.DisplayName, c.Text

FROM @UserList u

JOIN dbo.Posts p ON p.OwnerUserId = u.Id AND p.PostTypeId = 1

LEFT OUTER JOIN dbo.Comments c ON p.Id = c.PostId

LEFT OUTER JOIN dbo.Users uC ON c.UserId = uC.Id

ORDER BY p.Score DESC, c.CreationDate;

END

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 140; /* 2017 */

EXEC usp_PostsByUserLocation @Location = 'United States%';

SQL Server 2017:

* Underestimates rows in the table variable,

* Which leads to single-threaded processing and a low memory grant

* Which leads to tempdb spills

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 150; /* 2019 */

EXEC usp_PostsByUserLocation @Location = 'United States';

SQL Server 2019 is quite a bit faster because:

* It accurately estimates rows for the table variable

* Memory grant is more accurate

* No tempdb spills

Before we go on, note:

* The memory grant:

* The number of users:

* The number of questions:

Then try another location:

EXEC usp_PostsByUserLocation @Location = 'Boston, MA, USA';

The good news: table variables get stats.

The bad news: now they're vulnerable to parameter sniffing.

SQL Server has a new tool to help: adaptive memory grants.

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 140; /* 2017 */

EXEC usp_PostsByUserLocation @Location = 'United States';

/* And again: */

EXEC usp_PostsByUserLocation @Location = 'United States';

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 150; /* 2019 */

EXEC usp_PostsByUserLocation @Location = 'United States';

/* Try that a few times */

/* Try that a few times, then: */

EXEC usp_PostsByUserLocation @Location = 'Boston, MA, USA';

/* Here comes our American friend again: */

EXEC usp_PostsByUserLocation @Location = 'United States';

The good news: your memory grant will change, preventing spills and RESOURCE_SEMAPHORE.

The bad news:

* Your memory grant will drop, CAUSING spills

* It will flip back & forth, and then settle on a number which may not be good either

* This process restarts every time you rebuild an index, update stats, fail over, reboot

The great news:

* In many cases, it's still better than prior versions were!

/* SQL Server 2019 has a new tool to help: the ability to see a query's last

actual execution plan! It's turned on at the database level with this, which I

ran at the start of the demos:

ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;

In another window (to save the original actual plan), turn off actual plans: */

sp_BlitzCache;

/* Or if you like working with the plan handles directly: */

SELECT * FROM sys.dm_exec_query_plan_stats(0x050005000AFEBC35F0BE92666B01000001000000000000000000000000000000000000000000000000000000);

The good news: you can get the last "actual" plan for a query that ran recently.

The bad news:

* No wait stats

* No elapsed time

* No CPU metrics

* The memory grant is there, but it's wrong - it's for the NEXT execution

Still a fantastic starter for actual vs estimated rows, though.

It does have drawbacks that impact performance, so I'm going to turn it off for

the rest of the demos:

ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = OFF;

/* Next SQL Server 2019 feature: inlined user-defined functions, aka Froid.

Warm up the cache:

SELECT COUNT(*) FROM dbo.Votes;

CREATE OR ALTER FUNCTION dbo.GetVotesCount ( @UserId INT )

RETURNS BIGINT

WITH RETURNS NULL ON NULL INPUT

BEGIN

DECLARE @Count BIGINT;

SELECT @Count = COUNT_BIG(*)

FROM dbo.Votes

WHERE UserId = @UserId;

RETURN @Count;

END;

/* Use it in a stored procedure: */

CREATE OR ALTER PROC dbo.usp_UsersByReputation @Reputation INT AS

SELECT TOP 10 u.DisplayName, u.Location, u.WebsiteUrl, u.AboutMe, u.Id

FROM dbo.Users u

WHERE Reputation = @Reputation

AND dbo.GetVotesCount(u.Id) = 0; /* BAD IDEA */

/* Go back to SQL Server 2017 and run it with ACTUAL PLANS on: */

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 140;

DBCC FREEPROCCACHE;

EXEC usp_UsersByReputation @Reputation = 2;

Things to note:

* It takes a little while on my laptop.

* Both the plan and stats IO hide the impact.

* The plan appears to be limited to a single core.

To see the truth, run this in another window without actual plans:

sp_BlitzCache;

/* Switch to 2019 and run it with ACTUAL PLANS on: */

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 150;

DBCC FREEPROCCACHE;

EXEC usp_UsersByReputation @Reputation = 2;

/* May need to get the estimated plan in another window while this runs. ;-) */

DBCC FREEPROCCACHE;

/* Just get the estimated plan, and note no parallelism: */

EXEC usp_UsersByReputation @Reputation = 2;

/* And yet this query goes parallel: */

SELECT COUNT(*) FROM dbo.Users;

The good news: SQL Server 2019 automatically inlines some kinds of functions

The bad news:

* But that doesn't mean your query will go faster

* Because it also changes the shape of the plan

* It doesn't mean your query will go parallel either

* Test, test, test

* You can still do way better by getting rid of the function yourself:

CREATE OR ALTER PROC dbo.usp_UsersByReputation @Reputation INT AS

SELECT TOP 10 u.DisplayName, u.Location, u.WebsiteUrl, u.AboutMe, u.Id

FROM dbo.Users u

LEFT OUTER JOIN dbo.Votes v ON u.Id = v.UserId

WHERE Reputation = @Reputation

AND v.Id IS NULL;

EXEC usp_UsersByReputation @Reputation = 2;

/* Inline scalar functions can even crash your SQL Server:

https://dba.stackexchange.com/questions/253499/

https://sql-sasquatch.blogspot.com/2019/11/sql-server-2019-udf-inlining-oom-in.html

/* Fine, forget functions.

Next feature: adaptive joins on rowstore tables.

Take a stored procedure - no functions this time:

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 150; /* SQL Server 2019 */

CREATE OR ALTER PROC dbo.usp_UsersByReputation @Reputation INT AS

SELECT TOP 100000 u.Id, p.Title, p.Score

FROM dbo.Users u

JOIN dbo.Posts p ON p.OwnerUserId = u.Id

WHERE u.Reputation = @Reputation

ORDER BY p.Score DESC;

/* And run it: */

EXEC usp_UsersByReputation @Reputation = 1;

/* Check out that adaptive join:

* Adaptive threshold in tooltip

* Over threshold: do an index scan

* Under: do a seek

Try another reputation, and it chooses the seek: */

EXEC usp_UsersByReputation @Reputation = 2;

/* Try the big one: */

EXEC usp_UsersByReputation @Reputation = 1;

/* Check the actual plan.

* Adaptive joins need memory

* That memory is affected by adaptive grants

* We ran it for a tiny reputation (2), then a big one (1) - it got a tiny grant

Try running it again, and it spills way less:

EXEC usp_UsersByReputation @Reputation = 1;

/* May need to do that a few times to stabilize */

/* But as soon as someone runs it for a tiny value again... */

EXEC usp_UsersByReputation @Reputation = 2;

/* And then 1 runs, he's screwed again. */

EXEC usp_UsersByReputation @Reputation = 1;

/* Moral of the story: parameter sniffing just got a LOT harder.

Plus, check this out:

DBCC FREEPROCCACHE;

EXEC usp_UsersByReputation @Reputation = 1 WITH RECOMPILE; /* Gets an adaptive join */

EXEC usp_UsersByReputation @Reputation = 2 WITH RECOMPILE; /* Index seek on Users, no adaptive join, single threaded */

EXEC usp_UsersByReputation @Reputation = 3 WITH RECOMPILE; /* Index seek on Users, no adaptive join, parallel */

The good news: SQL Server 2019 has more execution plan options.

The bad news: parameter sniffing just got harder to troubleshoot and fix, not easier.

/* Next feature: batch mode on rowstore. Run this twice just to get a cached baseline, and note CPU time: */

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 140;

SELECT YEAR(v.CreationDate) AS CreationYear, MONTH(v.CreationDate) AS CreationMonth,

COUNT(*) AS VotesCount,

AVG(BountyAmount * 1.0) AS AvgBounty

FROM dbo.Votes v

GROUP BY YEAR(v.CreationDate), MONTH(v.CreationDate)

ORDER BY YEAR(v.CreationDate), MONTH(v.CreationDate)

/* Then try in 2019: */

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 150;

SELECT YEAR(v.CreationDate) AS CreationYear, MONTH(v.CreationDate) AS CreationMonth,

COUNT(*) AS VotesCount,

AVG(BountyAmount * 1.0) AS AvgBounty

FROM dbo.Votes v

GROUP BY YEAR(v.CreationDate), MONTH(v.CreationDate)

ORDER BY YEAR(v.CreationDate), MONTH(v.CreationDate)

The good news: reporting-style queries with grouping, aggregates, etc are getting way faster.

The bad news: again, more query plan choices - some of which can go wrong.

/* To make that even faster, we can create indexes.

Resumable index creation */

CREATE NONCLUSTERED INDEX IX_CreationDate_Includes on dbo.Votes (CreationDate) INCLUDE (PostId, UserId, BountyAmount, VoteTypeId)

WITH (ONLINE=ON, RESUMABLE=ON, MAX_DURATION=1);

/* In another window: */

ALTER INDEX IX_CreationDate_Includes ON dbo.Votes PAUSE;

SELECT * FROM sys.index_resumable_operations;

ALTER INDEX IX_CreationDate_Includes ON dbo.Votes ABORT;

ALTER INDEX IX_CreationDate_Includes ON dbo.Votes RESUME;

/* But you have to be careful, because that's not the only way to pause an index creation.

Watch what happens when I cancel my own query:

CREATE NONCLUSTERED INDEX IX_CreationDate_Includes on dbo.Votes (CreationDate) INCLUDE (PostId, UserId, BountyAmount, VoteTypeId)

WITH (ONLINE=ON, RESUMABLE=ON, MAX_DURATION=1);

/* And same thing if I kill it.

And if there are any pending operations on the table, you can't do others:

CREATE INDEX IX_UserId ON dbo.Votes(UserId);

So moral of the story:

* Don't use RESUMABLE = ON unless you really want it

* If you want it, don't cancel your query unless you clean up after yourself

* Watch the contents of sys.index_resumable_operations

And whatever you do, don't do this.

This makes all index creations escalate automatically:

ALTER DATABASE SCOPED CONFIGURATION

SET ELEVATE_ONLINE = WHEN_SUPPORTED;

ALTER DATABASE SCOPED CONFIGURATION

SET ELEVATE_RESUMABLE = WHEN_SUPPORTED;

/* So now, even if I don't ask for resumable, and I cancel my query: */

CREATE NONCLUSTERED INDEX IX_CreationDate_Includes on dbo.Votes (CreationDate) INCLUDE (PostId, UserId, BountyAmount, VoteTypeId)

-- WITH (ONLINE=ON, RESUMABLE=ON, MAX_DURATION=1);

/* If I cancel my query, it's left behind as resumable: */

SELECT * FROM sys.index_resumable_operations;

Speaking of canceling queries - you know what sucks? Rollbacks and failovers.

I'm going to set up an index to slow down my updates:

CREATE INDEX IX_Reputation_LastAccessDate ON dbo.Users(Reputation, LastAccessDate);

/* Run a transaction that does a lot of work: */

BEGIN TRAN

UPDATE dbo.Users

SET Reputation = 1000000,

LastAccessDate = GETDATE();

/* Oops! Forgot our WHERE clause! Gotta roll back. */

ROLLBACK

ALTER DATABASE StackOverflow2013 SET ACCELERATED_DATABASE_RECOVERY = ON;

SELECT * FROM sys.databases;

/* Run a transaction that does a lot of work: */

BEGIN TRAN

UPDATE dbo.Users

SET Reputation = 1000000,

LastAccessDate = GETDATE();

/* How much space is being used? */

SELECT TOP 100 * FROM sys.dm_tran_persistent_version_store_stats

SELECT TOP 100 * FROM sys.dm_tran_top_version_generators

SELECT TOP 100 * FROM sys.dm_tran_version_store

SELECT TOP 100 * FROM sys.dm_tran_version_store_space_usage

/* Clustered index */

SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.Users'), 1, 0, 'DETAILED')

/* Nonclustered index */

SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.Users'), 2, 0, 'DETAILED')

/* The big payoff: */

ROLLBACK;

SQL Server 2019 will help most of this code run faster:

* Table variables

* Scalar user-defined functions

* Reports with grouping, aggregate functions on tables without columnstore indexes

* Rollbacks & failovers

And these DBA jobs are easier:

* Seeing the "actual" plan for a recently-slow query

* Index creations

* Dealing with questions about rollbacks

But these DBA jobs are harder:

* Testing ALL of your code before you go live

* Dealing with parameter sniffing and unpredictable performance

Next step to learn more: BrentOzar.com/go/whatsnew

MIT License

Permission is hereby granted, free of charge, to any person obtaining a copy

of this software and associated documentation files (the "Software"), to deal

in the Software without restriction, including without limitation the rights

to use, copy, modify, merge, publish, distribute, sublicense, and/or sell

copies of the Software, and to permit persons to whom the Software is

furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all

copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR

IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,

FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE

AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER

LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,

OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE

SOFTWARE.

What’s New in SQL Server 2019: 100% Demos

Demo Script

Save your cart?