The Twelve Days of SQLT: Day Four: Nobody Did It

Previous installment: Day Three: Just a Mess Without a Clue

I know a funny little man,
As quiet as a mouse,
Who does the mischief that is done
In everybody’s house!
There’s no one ever sees his face,
And yet we all agree
That every plate we break was cracked
By Mr. Nobody.

—Unknown author

The purpose of a SQLT report is to collect all the information that could possibly shed light on a problem with SQL query performance. I was recently asked to investigate why performance had suddenly changed for the worse on the 1st of the month. I was assured that “nothing had changed.” Of course bind variables are always changing and statistics are always changing (which could cause plans to change and performance to change), but I was assured that, even so, performance had been stable ever since the application had gone live, several years earlier. After poking around in the SQLT report for a while, I noticed a peculiarity about the statistics on the indexes. Here is a sanitized excerpt; I’ve placed the statistics for two indexes (I1 and I2) side by side:

Index
Name

Collection
Date

Rows

Index
Name

Collection
Date

Rows

I1

11/3/2015

0

I2

11/3/2015

0

I1

11/3/2015

0

I2

11/3/2015

0

I1

11/3/2015

0

I2

11/3/2015

0

I1

11/3/2015

0

I2

11/3/2015

0

I1

11/2/2015

0

I2

11/2/2015

0

I1

11/2/2015

0

I2

11/2/2015

0

I1

11/2/2015

0

I2

11/2/2015

0

I1

11/2/2015

0

I2

11/2/2015

0

I1

11/1/2015

894155

I2

11/1/2015

894155

I1

11/1/2015

0

I2

11/1/2015

0

I1

11/1/2015

922819

I2

11/1/2015

922819

I1

11/1/2015

922819

I2

11/1/2015

922819

I1

11/1/2015

0

I2

11/1/2015

0

I2

10/29/2015

0

I2

10/28/2015

0

I2

10/28/2015

0

I2

10/26/2015

0

I2

10/25/2015

0

I2

10/24/2015

0

I2

10/23/2015

0

I2

10/22/2015

0

I2

10/19/2015

0

I2

10/18/2015

0

I2

10/18/2015

0

I2

10/16/2015

0

I2

10/15/2015

0

I2

10/14/2015

0

I2

10/12/2015

0

I2

10/11/2015

0

I2

10/10/2015

0

I2

10/10/2015

0

I2

10/8/2015

0

I2

10/6/2015

0

I2

10/5/2015

0

I2

10/4/2015

1530219

I2

10/4/2015

378630

I2

10/4/2015

378630

I2

10/4/2015

0

I2

10/4/2015

0

I2

10/4/2015

258751

I2

10/4/2015

258751

I2

10/4/2015

1640826

I2

10/4/2015

1626387

I2

10/4/2015

1661260

I2

10/3/2015

1458493

I2

10/3/2015

1559868

I2

10/3/2015

0

I2

10/3/2015

0

I2

10/1/2015

0

Do you see what I see? Why do you think performance changed on the 1st of November? What is your recommendation? Click on the attachment to see my own analysis and recommendation.

The Twelve Days of SQL

The Twelve Days of NoSQL

Copyright © 2015 Iggy Fernandez