“But It Worked in Development!” – 3 Hard Performance Problems

Last Updated 5 years ago

You’ve been performance tuning queries and indexes for a few years, but lately, you’ve been running into problems you can’t explain. Could it be RESOURCE_SEMAPHORE, THREADPOOL, or lock escalation? These problems only pop up under heavy load or concurrency, so they’re very hard to detect in a development environment.

In a very fast-paced session, I’ll show these three performance problems pop up under load. I won’t be able to teach you how to fix them for good – not inside the span of 75 minutes – but at least you’ll be able to recognize the symptoms when they strike, and I’ll show you where to go to learn more.

You watch the below PASS Summit 2017 session with a very special guest: I brought Pinal Dave up onstage with me to be the audience member and ask questions as they came to him. Enjoy!

Here’s my resources, tools, and reference posts:

"But It Worked In Development!" – 3 Hard SQL Server Problems from Brent Ozar

Stack Overflow database – shared under Creative Commons licensing by the incredibly cool people of StackOverflow.com
SQLQueryStress – open source load test generator
First Responder Kit – open source tools including sp_BlitzCache, sp_BlitzWho, and sp_BlitzFirst that I used to identify poison waits and queries with high memory grants.
Dedicated Admin Connection (DAC) – a separate CPU scheduler & memory set aside for emergency troubleshooting. Learn how to use it before the emergency strikes.
sp_WhoIsActive – @AdamMachanic‘s Amazing Tool to check current activity on a server. Blazing fast, even when the server is blazing.

Open Source Demo Script

Here’s the complete annotated demo script. Of course remember – don’t do this in production!

/*

"But It Worked In Development!" - 3 Hard Performance Problems

Brent Ozar, 2018/02/17

Come on in and have a seat - yes, you're in the right session. (I guess.)
No, there's no slides here today - just demos.

About Me:
 * Microsoft Certified Master - really good at taking tests
 * American - really bad at understanding cricket
 * Created sp_Blitz, DBAreactions.com, SQLServerUpdates.com, GroupBy.org


Download this script, the database, and watch the session video:
https://www.brentozar.com/go/3hardproblems

MIT License

Copyright (c) 2018 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.
*/




/* 
   ****************************************************************************
   *** THE SETUP
   ****************************************************************************
*/


/*

My SQL Server VM is:
 * 4 cores, 8GB RAM
 * SQL Server 2017 CU3
 * Has the StackOverflow.com database
 * Focusing on the dbo.Users table - about 1GB in size


Let's make sure we're on the latest-and-greatest SQL 2017 compat level. I
distribute the Stack Overflow database in a SQL Server 2008 backup just so that
the most people possible can play around with it, but today, I'm using 2017.
Nothing in this demo requires 2017 - but I just want you to understand that the
behaviors I'm going to show you haven't been fixed or changed in 2017.
*/
ALTER DATABASE [StackOverflow] SET COMPATIBILITY_LEVEL = 140;
GO



/* 
This demo isn't about best practices, so I'm going to make a couple changes to
the export in order to cause some problems.

Let's make the DisplayName, Location, and WebsiteUrl fields ten times larger.
These schema changes will happen instantly - SQL Server doesn't have to rewrite
the data pages to pull this off.
*/
USE StackOverflow;
GO
ALTER TABLE dbo.Users
  ALTER COLUMN DisplayName NVARCHAR(400);
ALTER TABLE dbo.Users
  ALTER COLUMN Location NVARCHAR(1000);
ALTER TABLE dbo.Users
  ALTER COLUMN WebsiteUrl NVARCHAR(2000);
GO
/* 
Let's also create an index on DisplayName because our application frequently
looks up people by that:
*/
CREATE INDEX IX_DisplayName ON dbo.Users(DisplayName);
GO




/*
Open a web browser and check out the Stack Overflow users list:
https://stackoverflow.com/users?tab=Reputation&filter=all

Let's build a not-so-good query to reproduce that page. Run this:
*/
SELECT TOP 250 *
  FROM dbo.Users
  ORDER BY Reputation DESC;
GO


/*
It takes a second or two - not bad. That's good enough, right? Ship it.
*/










/* 
   ****************************************************************************
   *** PROBLEM 1: RESOURCE_SEMAPHORE
   ****************************************************************************
*/

/*
So now let's go live. Get SQLQueryStress, an open-source load test tool:

https://github.com/ErikEJ/SqlQueryStress

Open that, point it at your database server, and copy/paste in the query.
Run it for 100 iterations, 8 threads.

While it runs, let's look at SQL Server's wait stats and bottlenecks:
*/

sp_BlitzFirst @ExpertMode = 1;
GO





/*
One of your waits is probably RESOURCE_SEMAPHORE, a poison wait.

This means queries are waiting to get memory grants before they can even start.

Symptoms:
* RESOURCE_SEMAPHORE waits present
* Page life expectancy drops (sometimes without long-running-queries happening)
* SQLServer: Memory Manager – Memory Grants Pending counter goes > 0
* Queries take an unpredictably long time to start

To understand why, look at the query plan's memory grants. Run the query with
the actual execution plan turned on:
*/
SELECT TOP 250 *
  FROM dbo.Users
  ORDER BY Reputation DESC;
GO
/*
 * What is the query doing?
 * How much memory is it asking for to do that task?
 * How much data is SQL Server expecting to come back?
 * How big is the actual table?
*/
sp_BlitzIndex @TableName = 'Users';
GO


/*
In our case, this is a really big problem because we defined our data types to
be wildly oversized - remember how we grew 'em larger at the beginning? SQL
Server uses those data type sizes to estimate how much data is actually getting
stored. Erik Darling blogged about why big data type sizes are such a problem:
https://www.brentozar.com/archive/2017/02/memory-grants-data-size/

Ouch. That query isn't going to scale. If we stop SQLQueryStress and change its
parameters, like increase the number of threads or remove the wait time between
queries, it's going to get even worse. Even a handful of users can't scale here!

So how might we tune the query specifically trying to reduce memory grants?
*/

/* Before: */
SELECT TOP 250 *
  FROM dbo.Users
  ORDER BY Reputation DESC;
GO


/* Do we really need all those rows and columns? How about this: */
SELECT TOP 36 DisplayName, Location, Reputation, Id
  FROM dbo.Users
  ORDER BY Reputation DESC;
GO



/*
Run both of those queries, get their actual execution plans, and compare:
* The shape of the plan
* The costs
* The memory grants
* The logical reads

Sometimes, queries can look really similar, but have dramatically different
performance effects. Try running that new and improved query in SQLQueryStress
for 100 iterations, 8 threads.

Now, how do your wait stats look? (Turn off actual plans for this)
*/
sp_BlitzFirst @ExpertMode = 1;



/*
Consider:
* What's the memory grant of each query?
* Are queries waiting for that grant?
* Is the server faster?

It's still not a great query, and our server isn't in great shape when 8 of
these are running at the same time. CPU is maxed out at 100%. But it's sure
better than what we had before, right?

Right?
*/










/* 
   **************************
   **************************
      *******      *******
      *******      *******
      *******      *******
      *******      *******
      *******      *******
      *******      *******
      *******      *******
      *******      *******
   **************************
   **************************
     PROBLEM 2: THREADPOOL
*/


/*
It's coming up on my birthday. Time to update my age.
*/
BEGIN TRAN
UPDATE dbo.Users
  SET Age = Age + 1
  WHERE DisplayName = 'Brent Ozar';
--ROLLBACK
GO



/* Let's see what the leaderboard looks like now: */
SELECT TOP 36 DisplayName, Location, Reputation, Id
  FROM dbo.Users
  ORDER BY Reputation DESC;
GO



/*
My query can get through just fine - but only mine. If other people try to run
that same query in other sessions, they're going to get blocked because my
update transaction hasn't been committed.

Try it now - open another window, and run the select there.


Then, let's pretend this happens in production. Open SQLQueryStress, and run
the leaderboard query across 200 threads.

The server's still okay - you can run DMV queries:
*/
sp_WhoIsActive
GO



/*
Open another window of SQLQueryStress, run another 200 threads of the
leaderboard query. Is the server still responsive?
*/
sp_WhoIsActive
GO





/*
Keep throwing in more load - let's open yet another SQLQueryStress, and run yet
another 200 threads of the leaderboard. Then check your waits:
*/
sp_WhoIsActive
GO





/*
Dang - that's not good. Queries fail, even simple DMV queries, and you have a
hard time even connecting to SQL Server.

Symptoms:
* Apps, users can’t connect to the SQL Server
* Monitoring tools show gaps in history with no metrics
* CPU isn't high
* SQL Server error log doesn’t show anything unusual

Common causes:
* Query takes a lock, blocked queries use the rest of the worker threads
* Trying to protect 100s of databases with Always On AGs or database mirroring
  https://www.red-gate.com/simple-talk/sql/database-administration/alwayson-availability-groups-what-not-to-do-when-adding-databases/

Ways to fix it:

* Raise the max worker threads setting (usually a very bad idea)
* Commit your transactions faster
* Use the right isolation level for your app's needs
* Reduce unnecessary parallelism: set Cost Threshold and MAXDOP appropriately
* Tune queries to reduce parallelism requirements by doing less work

Stop all your SQLQueryStress threads, roll back your transactions:
*/
ROLLBACK
GO

/*
How could we tune this query to make it do less work, and maybe avoid the
blocking altogether?
*/
SELECT TOP 36 DisplayName, Location, Reputation, Id
  FROM dbo.Users
  ORDER BY Reputation DESC;




/*
Kinda interesting how we don't get an index recommendation, eh?
Let's make our own covering index:
*/
CREATE INDEX IX_Reputation ON dbo.Users(Reputation)
  INCLUDE (DisplayName, Location, Id);
GO


/* And then see how the execution plan looks: */
SELECT TOP 36 DisplayName, Location, Reputation, Id
  FROM dbo.Users
  ORDER BY Reputation DESC;
GO




/*
Now run our update again, taking out our locks:
*/
BEGIN TRAN
UPDATE dbo.Users
  SET Age = Age + 1
  WHERE DisplayName = 'Brent Ozar';
--ROLLBACK
GO



/* And run this in another window to see if it gets blocked: */
SELECT TOP 36 DisplayName, Location, Reputation, Id
  FROM dbo.Users
  ORDER BY Reputation DESC;
GO



/*
Ta-dah! The blocking is gone.

Now, we won't run into threadpool problems - because our query can actually
get through, without being blocked.

If you wanna see it, fire up SQLQueryStress, running 200 threads of the 
leaderboard. They will run, but of course now you have a new problem: 100% CPU. 
You can at least sustain a lot more load again, but of course it'll hurt.

When your server has been getting THREADPOOL waits:

 * Enable & use the Dedicated Admin Connection:
   https://www.mssqltips.com/sqlservertip/1801/enable-sql-server-dedicated-administrator-connection/ 
 * When it hits, train someone to log in and save sp_BlitzWho or 
   sp_WhoIsActive results to a table
 * Use sp_BlitzIndex to find “aggressive indexes” – indexes with long 
   lock waits, and tune those tables

*/



/* 
   ****************************************************************************
   *** PROBLEM TREE ***********************************************************
   ****************************************************************************
                             ,nm.
                             O8O8b              ,odb.
                       .d8b. `48P.      .dbo.   48b.4b.,._
               ,o.   _ 488P _..78OnO8b.,8O88O8b.d88888O88O8b.,db.
              '8O8bnd8O888888O8888O888O88b d888O8d8O888"8P'"48O88'
               `" 48O8P"'"88O8`8b__d8O888O88O8O88b`88O8bd8b._`887
             .db.d8888._,dO88b.888888888O' `8888O88888O8O8888b8O8b.
             8O888d888O888888O8888"488O888bnd888888O88P d8O88O888P
             `48O8888O8888P'"88O88b.`8888O8888O888b`88O888b"88O"8.._
           .d8d8888`48888O.,d8888O888O888888O8"_dO888O888O87b8' 4dO8Ob.
       ,db.88O8'd88b.888888O88O8888888P`78888888O88888O8P888O8b.d8848P
      jO888P 788O888888O888'488b.888O8888d888O88888O888' 888P.8887"'
      48P.O88888"'88P.d888b.88888O888888O88`"48O88888O8bd8P"8O88P
       `'"++8OP .d8O88888O88O8O888P 4O88888b8O8888b `88O888nO88b.
            `4888O8P+4888888"d88O88888O88O888b 4O888b8P"`48P"48OP
              "++"'   .d8bj888888888888+"`4888O888O88P'       "'
                      `488OP+88888888P'     `"'++"+"'
                        `'"  `+488888b.
                                `+488888b.
                                   `+488888b.
                                      `+88888b.
                                       d8888888
                                     .d8888888'
                                   .d88888888'
                                _.d8888888888b._
          888888888888888888888888888888888888888888888888888888888888
          888888888888888888888888888888888888888888888888888888888888
            ;888888888888888888888888888888888888888888888888888888'
             88888888888888888888888888888888888888888888888888888'
              `88888888888888888888888888888888888888888888888888'
               `888888888888888888888888888888888888888888888888'
                `8888888888888888888888888888888888888888888888'
                 `88888888888888888888888888888888888888888888'

Many other examples: http://www.fiikus.net/?asciiart
*/



/* Roll back transactions to clear things out: */
ROLLBACK
GO

/* We were working with Brent Ozar - update my age again: */
BEGIN TRAN
UPDATE dbo.Users
  SET Age = Age + 1
  WHERE DisplayName = 'Brent Ozar';
--ROLLBACK
GO


/* What kind of locks does it have? Run this in another window: */
sp_WhoIsActive @get_locks = 1;
GO



/* Roll it back: */
ROLLBACK
GO

/* Now it's time for my friend Alex's birthday: */
BEGIN TRAN
UPDATE dbo.Users
  SET Age = Age + 1
  WHERE DisplayName = 'Alex';
--ROLLBACK
GO



/* In another window, check out the leaderboard: */
SELECT TOP 36 DisplayName, Location, Reputation, Id
  FROM dbo.Users
  ORDER BY Reputation DESC;
GO





/* Huh. It's blocked. What kind of locks does it have? Run in another window: */
sp_WhoIsActive @get_locks = 1;
GO


/* 
Whoa - before, SQL Server was only locking a single row.
But now it's locking the entire table. Why? What's the difference between these?
*/

BEGIN TRAN
UPDATE dbo.Users
  SET Age = Age + 1
  WHERE DisplayName = 'Brent Ozar';

BEGIN TRAN
UPDATE dbo.Users
  SET Age = Age + 1
  WHERE DisplayName = 'Alex';
GO
ROLLBACK;
GO


/* 
The plans look the same - well, mostly the same, except for the number of rows
they're working with. Why are Brent Ozar and Alex different?
*/
SELECT TOP 100 DisplayName, COUNT(*) AS recs 
  FROM dbo.Users
  GROUP BY DisplayName 
  ORDER BY COUNT(*) DESC;
GO



/*
There's over 5,000 Alexes, but there's only one me. (Thank God.)

SQL Server handles locking differently when you're updating different numbers
of rows, but that's not obvious in the execution plans.

Symptoms:
* Queries that worked fine in the past suddenly start blocking today
* Wildly variable workloads like data warehouse loads, batch processes
* Production & development have different data sizes
* Varying execution plans for writes (narrow vs wide plans for different parameters)
* High LCK% waits in monitoring tools

Mitigations:
* Commit your transactions faster
* Work in small batches: http://michaeljswart.com/2014/09/take-care-when-scripting-batches/
* Doing deletes? Try fast ordered deletes: https://www.johnsansom.com/fast-sql-server-delete/ 
* Microsoft's lock escalation resources: https://support.microsoft.com/en-us/help/323630/ 
* Use the right isolation level for your app's needs, like RCSI: https://www.brentozar.com/go/rcsi

Let's bail out:
*/

ROLLBACK
GO


/* And try setting our default isolation level to RCSI: */

ALTER DATABASE [StackOverflow] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT;
GO



/* Now take out our lock again: */
USE StackOverflow;
GO

BEGIN TRAN
UPDATE dbo.Users
  SET Age = Age + 1
  WHERE DisplayName = 'Alex';
GO
--ROLLBACK


/* Then try our select in another window: */
SELECT TOP 36 DisplayName, Location, Reputation, Id
  FROM dbo.Users
  ORDER BY Reputation DESC;
GO


/* And check our locks in another window: */
sp_WhoIsActive @get_locks = 1;
GO


/*
The update still hit lock escalation, and has a table lock. But now, due to the
magic of optimistic concurrency (RCSI), writers don't block readers!
*/





/* 
   ****************************************************************************
   *** RECAP: What you pretended you already knew
   ****************************************************************************

RESOURCE_SEMAPHORE (unavailable query worker space) symptoms:

* Bad queries get huge memory grants, other queries can't get memory to run
* sp_BlitzWho, sp_WhoIsActive show queries waiting on RESOURCE_SEMAPHORE
* SQLServer: Memory Manager – Memory Grants Pending counter goes > 0
* Queries take an unpredictably long time to start
* Page life expectancy drops (sometimes without long-running-queries happening)
* Monitoring tools show RESOURCE_SEMAPHORE waits


THREADPOOL (worker thread starvation) symptoms:

* Apps, users can’t connect to the SQL Server
* Monitoring tools show gaps in history with no metrics
* CPU isn't high
* SQL Server error log doesn’t show anything unusual
* Monitoring tools show THREADPOOL waits


LOCK ESCALATION (data got bigger) symptoms:

* Queries that worked fine in the past suddenly start blocking today
* Wildly variable workloads like data warehouse loads, batch processes
* Production & development have different data sizes
* Varying execution plans for writes (narrow vs wide plans for different parameters)
* High LCK% waits in monitoring tools

Learn more, download this script, and watch a video of this: 
https://www.brentozar.com/go/3hardproblems

TIP YOUR SPEAKERS: Help us suck less. Fill in your survey forms.

GO TO THE COMMUNITY CORNER: let's help the SQLbits organizers bring more
people into this fun family. You can help - ask them how. Volunteer.

And thanks for hanging out with me today.
*/

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

641

642

643

644

645

646

647

648

649

650

651

"But It Worked In Development!" - 3 Hard Performance Problems

Brent Ozar, 2018/02/17

Come on in and have a seat - yes, you're in the right session. (I guess.)

No, there's no slides here today - just demos.

About Me:

* Microsoft Certified Master - really good at taking tests

* American - really bad at understanding cricket

* Created sp_Blitz, DBAreactions.com, SQLServerUpdates.com, GroupBy.org

Download this script, the database, and watch the session video:

https://www.brentozar.com/go/3hardproblems

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.

****************************************************************************

*** THE SETUP

****************************************************************************

My SQL Server VM is:

* 4 cores, 8GB RAM

* SQL Server 2017 CU3

* Has the StackOverflow.com database

* Focusing on the dbo.Users table - about 1GB in size

Let's make sure we're on the latest-and-greatest SQL 2017 compat level. I

distribute the Stack Overflow database in a SQL Server 2008 backup just so that

the most people possible can play around with it, but today, I'm using 2017.

Nothing in this demo requires 2017 - but I just want you to understand that the

behaviors I'm going to show you haven't been fixed or changed in 2017.

ALTER DATABASE [StackOverflow] SET COMPATIBILITY_LEVEL = 140;

This demo isn't about best practices, so I'm going to make a couple changes to

the export in order to cause some problems.

Let's make the DisplayName, Location, and WebsiteUrl fields ten times larger.

These schema changes will happen instantly - SQL Server doesn't have to rewrite

the data pages to pull this off.

USE StackOverflow;

ALTER TABLE dbo.Users

ALTER COLUMN DisplayName NVARCHAR(400);

ALTER TABLE dbo.Users

ALTER COLUMN Location NVARCHAR(1000);

ALTER TABLE dbo.Users

ALTER COLUMN WebsiteUrl NVARCHAR(2000);

Let's also create an index on DisplayName because our application frequently

looks up people by that:

CREATE INDEX IX_DisplayName ON dbo.Users(DisplayName);

Open a web browser and check out the Stack Overflow users list:

https://stackoverflow.com/users?tab=Reputation&filter=all

Let's build a not-so-good query to reproduce that page. Run this:

SELECT TOP 250 *

FROM dbo.Users

ORDER BY Reputation DESC;

It takes a second or two - not bad. That's good enough, right? Ship it.

****************************************************************************

*** PROBLEM 1: RESOURCE_SEMAPHORE

****************************************************************************

So now let's go live. Get SQLQueryStress, an open-source load test tool:

https://github.com/ErikEJ/SqlQueryStress

Open that, point it at your database server, and copy/paste in the query.

Run it for 100 iterations, 8 threads.

While it runs, let's look at SQL Server's wait stats and bottlenecks:

sp_BlitzFirst @ExpertMode = 1;

One of your waits is probably RESOURCE_SEMAPHORE, a poison wait.

This means queries are waiting to get memory grants before they can even start.

Symptoms:

* RESOURCE_SEMAPHORE waits present

* Page life expectancy drops (sometimes without long-running-queries happening)

* SQLServer: Memory Manager – Memory Grants Pending counter goes > 0

* Queries take an unpredictably long time to start

To understand why, look at the query plan's memory grants. Run the query with

the actual execution plan turned on:

SELECT TOP 250 *

FROM dbo.Users

ORDER BY Reputation DESC;

* What is the query doing?

* How much memory is it asking for to do that task?

* How much data is SQL Server expecting to come back?

* How big is the actual table?

sp_BlitzIndex @TableName = 'Users';

In our case, this is a really big problem because we defined our data types to

be wildly oversized - remember how we grew 'em larger at the beginning? SQL

Server uses those data type sizes to estimate how much data is actually getting

stored. Erik Darling blogged about why big data type sizes are such a problem:

https://www.brentozar.com/archive/2017/02/memory-grants-data-size/

Ouch. That query isn't going to scale. If we stop SQLQueryStress and change its

parameters, like increase the number of threads or remove the wait time between

queries, it's going to get even worse. Even a handful of users can't scale here!

So how might we tune the query specifically trying to reduce memory grants?

/* Before: */

SELECT TOP 250 *

FROM dbo.Users

ORDER BY Reputation DESC;

/* Do we really need all those rows and columns? How about this: */

SELECT TOP 36 DisplayName, Location, Reputation, Id

FROM dbo.Users

ORDER BY Reputation DESC;

Run both of those queries, get their actual execution plans, and compare:

* The shape of the plan

* The costs

* The memory grants

* The logical reads

Sometimes, queries can look really similar, but have dramatically different

performance effects. Try running that new and improved query in SQLQueryStress

for 100 iterations, 8 threads.

Now, how do your wait stats look? (Turn off actual plans for this)

sp_BlitzFirst @ExpertMode = 1;

Consider:

* What's the memory grant of each query?

* Are queries waiting for that grant?

* Is the server faster?

It's still not a great query, and our server isn't in great shape when 8 of

these are running at the same time. CPU is maxed out at 100%. But it's sure

better than what we had before, right?

Right?

**************************

******* *******

**************************

PROBLEM 2: THREADPOOL

It's coming up on my birthday. Time to update my age.

BEGIN TRAN

UPDATE dbo.Users

SET Age = Age + 1

WHERE DisplayName = 'Brent Ozar';

--ROLLBACK

/* Let's see what the leaderboard looks like now: */

SELECT TOP 36 DisplayName, Location, Reputation, Id

FROM dbo.Users

ORDER BY Reputation DESC;

My query can get through just fine - but only mine. If other people try to run

that same query in other sessions, they're going to get blocked because my

update transaction hasn't been committed.

Try it now - open another window, and run the select there.

Then, let's pretend this happens in production. Open SQLQueryStress, and run

the leaderboard query across 200 threads.

The server's still okay - you can run DMV queries:

sp_WhoIsActive

Open another window of SQLQueryStress, run another 200 threads of the

leaderboard query. Is the server still responsive?

sp_WhoIsActive

Keep throwing in more load - let's open yet another SQLQueryStress, and run yet

another 200 threads of the leaderboard. Then check your waits:

sp_WhoIsActive

Dang - that's not good. Queries fail, even simple DMV queries, and you have a

hard time even connecting to SQL Server.

Symptoms:

* Apps, users can’t connect to the SQL Server

* Monitoring tools show gaps in history with no metrics

* CPU isn't high

* SQL Server error log doesn’t show anything unusual

Common causes:

* Query takes a lock, blocked queries use the rest of the worker threads

* Trying to protect 100s of databases with Always On AGs or database mirroring

https://www.red-gate.com/simple-talk/sql/database-administration/alwayson-availability-groups-what-not-to-do-when-adding-databases/

Ways to fix it:

* Raise the max worker threads setting (usually a very bad idea)

* Commit your transactions faster

* Use the right isolation level for your app's needs

* Reduce unnecessary parallelism: set Cost Threshold and MAXDOP appropriately

* Tune queries to reduce parallelism requirements by doing less work

Stop all your SQLQueryStress threads, roll back your transactions:

ROLLBACK

How could we tune this query to make it do less work, and maybe avoid the

blocking altogether?

SELECT TOP 36 DisplayName, Location, Reputation, Id

FROM dbo.Users

ORDER BY Reputation DESC;

Kinda interesting how we don't get an index recommendation, eh?

Let's make our own covering index:

CREATE INDEX IX_Reputation ON dbo.Users(Reputation)

INCLUDE (DisplayName, Location, Id);

/* And then see how the execution plan looks: */

SELECT TOP 36 DisplayName, Location, Reputation, Id

FROM dbo.Users

ORDER BY Reputation DESC;

Now run our update again, taking out our locks:

BEGIN TRAN

UPDATE dbo.Users

SET Age = Age + 1

WHERE DisplayName = 'Brent Ozar';

--ROLLBACK

/* And run this in another window to see if it gets blocked: */

SELECT TOP 36 DisplayName, Location, Reputation, Id

FROM dbo.Users

ORDER BY Reputation DESC;

Ta-dah! The blocking is gone.

Now, we won't run into threadpool problems - because our query can actually

get through, without being blocked.

If you wanna see it, fire up SQLQueryStress, running 200 threads of the

leaderboard. They will run, but of course now you have a new problem: 100% CPU.

You can at least sustain a lot more load again, but of course it'll hurt.

When your server has been getting THREADPOOL waits:

* Enable & use the Dedicated Admin Connection:

https://www.mssqltips.com/sqlservertip/1801/enable-sql-server-dedicated-administrator-connection/

* When it hits, train someone to log in and save sp_BlitzWho or

sp_WhoIsActive results to a table

* Use sp_BlitzIndex to find “aggressive indexes” – indexes with long

lock waits, and tune those tables

****************************************************************************

*** PROBLEM TREE ***********************************************************

****************************************************************************

,nm.

O8O8b ,odb.

.d8b. `48P. .dbo. 48b.4b.,._

,o. _ 488P _..78OnO8b.,8O88O8b.d88888O88O8b.,db.

'8O8bnd8O888888O8888O888O88b d888O8d8O888"8P'"48O88'

`" 48O8P"'"88O8`8b__d8O888O88O8O88b`88O8bd8b._`887

.db.d8888._,dO88b.888888888O' `8888O88888O8O8888b8O8b.

8O888d888O888888O8888"488O888bnd888888O88P d8O88O888P

`48O8888O8888P'"88O88b.`8888O8888O888b`88O888b"88O"8.._

.d8d8888`48888O.,d8888O888O888888O8"_dO888O888O87b8' 4dO8Ob.

,db.88O8'd88b.888888O88O8888888P`78888888O88888O8P888O8b.d8848P

jO888P 788O888888O888'488b.888O8888d888O88888O888' 888P.8887"'

48P.O88888"'88P.d888b.88888O888888O88`"48O88888O8bd8P"8O88P

`'"++8OP .d8O88888O88O8O888P 4O88888b8O8888b `88O888nO88b.

`4888O8P+4888888"d88O88888O88O888b 4O888b8P"`48P"48OP

"++"' .d8bj888888888888+"`4888O888O88P' "'

`488OP+88888888P' `"'++"+"'

`'" `+488888b.

`+488888b.

`+88888b.

d8888888

.d8888888'

.d88888888'

_.d8888888888b._

888888888888888888888888888888888888888888888888888888888888

;888888888888888888888888888888888888888888888888888888'

88888888888888888888888888888888888888888888888888888'

`88888888888888888888888888888888888888888888888888'

`888888888888888888888888888888888888888888888888'

`8888888888888888888888888888888888888888888888'

`88888888888888888888888888888888888888888888'

Many other examples: http://www.fiikus.net/?asciiart

/* Roll back transactions to clear things out: */

ROLLBACK

/* We were working with Brent Ozar - update my age again: */

BEGIN TRAN

UPDATE dbo.Users

SET Age = Age + 1

WHERE DisplayName = 'Brent Ozar';

--ROLLBACK

/* What kind of locks does it have? Run this in another window: */

sp_WhoIsActive @get_locks = 1;

/* Roll it back: */

ROLLBACK

/* Now it's time for my friend Alex's birthday: */

BEGIN TRAN

UPDATE dbo.Users

SET Age = Age + 1

WHERE DisplayName = 'Alex';

--ROLLBACK

/* In another window, check out the leaderboard: */

SELECT TOP 36 DisplayName, Location, Reputation, Id

FROM dbo.Users

ORDER BY Reputation DESC;

/* Huh. It's blocked. What kind of locks does it have? Run in another window: */

sp_WhoIsActive @get_locks = 1;

Whoa - before, SQL Server was only locking a single row.

But now it's locking the entire table. Why? What's the difference between these?

BEGIN TRAN

UPDATE dbo.Users

SET Age = Age + 1

WHERE DisplayName = 'Brent Ozar';

BEGIN TRAN

UPDATE dbo.Users

SET Age = Age + 1

WHERE DisplayName = 'Alex';

ROLLBACK;

The plans look the same - well, mostly the same, except for the number of rows

they're working with. Why are Brent Ozar and Alex different?

SELECT TOP 100 DisplayName, COUNT(*) AS recs

FROM dbo.Users

GROUP BY DisplayName

ORDER BY COUNT(*) DESC;

There's over 5,000 Alexes, but there's only one me. (Thank God.)

SQL Server handles locking differently when you're updating different numbers

of rows, but that's not obvious in the execution plans.

Symptoms:

* Queries that worked fine in the past suddenly start blocking today

* Wildly variable workloads like data warehouse loads, batch processes

* Production & development have different data sizes

* Varying execution plans for writes (narrow vs wide plans for different parameters)

* High LCK% waits in monitoring tools

Mitigations:

* Commit your transactions faster

* Work in small batches: http://michaeljswart.com/2014/09/take-care-when-scripting-batches/

* Doing deletes? Try fast ordered deletes: https://www.johnsansom.com/fast-sql-server-delete/

* Microsoft's lock escalation resources: https://support.microsoft.com/en-us/help/323630/

* Use the right isolation level for your app's needs, like RCSI: https://www.brentozar.com/go/rcsi

Let's bail out:

ROLLBACK

/* And try setting our default isolation level to RCSI: */

ALTER DATABASE [StackOverflow] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT;

/* Now take out our lock again: */

USE StackOverflow;

BEGIN TRAN

UPDATE dbo.Users

SET Age = Age + 1

WHERE DisplayName = 'Alex';

--ROLLBACK

/* Then try our select in another window: */

SELECT TOP 36 DisplayName, Location, Reputation, Id

FROM dbo.Users

ORDER BY Reputation DESC;

/* And check our locks in another window: */

sp_WhoIsActive @get_locks = 1;

The update still hit lock escalation, and has a table lock. But now, due to the

magic of optimistic concurrency (RCSI), writers don't block readers!

****************************************************************************

*** RECAP: What you pretended you already knew

****************************************************************************

RESOURCE_SEMAPHORE (unavailable query worker space) symptoms:

* Bad queries get huge memory grants, other queries can't get memory to run

* sp_BlitzWho, sp_WhoIsActive show queries waiting on RESOURCE_SEMAPHORE

* SQLServer: Memory Manager – Memory Grants Pending counter goes > 0

* Queries take an unpredictably long time to start

* Page life expectancy drops (sometimes without long-running-queries happening)

* Monitoring tools show RESOURCE_SEMAPHORE waits

THREADPOOL (worker thread starvation) symptoms:

* Apps, users can’t connect to the SQL Server

* Monitoring tools show gaps in history with no metrics

* CPU isn't high

* SQL Server error log doesn’t show anything unusual

* Monitoring tools show THREADPOOL waits

LOCK ESCALATION (data got bigger) symptoms:

* Queries that worked fine in the past suddenly start blocking today

* Wildly variable workloads like data warehouse loads, batch processes

* Production & development have different data sizes

* Varying execution plans for writes (narrow vs wide plans for different parameters)

* High LCK% waits in monitoring tools

Learn more, download this script, and watch a video of this:

https://www.brentozar.com/go/3hardproblems

TIP YOUR SPEAKERS: Help us suck less. Fill in your survey forms.

GO TO THE COMMUNITY CORNER: let's help the SQLbits organizers bring more

people into this fun family. You can help - ask them how. Volunteer.

And thanks for hanging out with me today.

[Video] Office Hours 2018/1/10 (With Transcriptions)

When Does Index Size Change Index Choice?

“But It Worked in Development!” – 3 Hard Performance Problems

Open Source Demo Script

Leave a Reply Cancel reply

Hi! I’m Brent Ozar.