Update Statistics..

Question

Post reply

Update Statistics..

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Arsh SSCertifiable Points: 6354 More actions · Answer 1

Arsh

SSCertifiable

Points: 6354

September 4, 2017 at 7:38 am

#1958011

Its an Insert-Centric table.

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 2

Arsh - Monday, September 4, 2017 7:18 AM
Jeff Moden - Friday, September 1, 2017 6:55 AM
Arsh - Friday, September 1, 2017 5:40 AM
GilaMonster - Thursday, August 31, 2017 1:19 PM
Threshold for triggering auto update != sampling rate.
The threshold is 20%. Jeff's talking about the sample rate.
Thanks Gail. So the higher the sampling rate the better are the stats, I suppose ?
Like everything else, "It Depends". In the case of statistics, it depends on the distribution of data.
As a reminder, the purpose of the "FULL SCAN" experiment I suggested is to prove whether or not statistics on the large table are a part of the blocking problem or not. My hunch is that it's not a part of the problem, but I could be wrong. "One good experiment is worth a thousand expert opinions". 😉
Have you done the experiment, yet?
Hi Jeff . Thanks. Actually there's no end-to-end test environment here in this place (Anti ITIL environment 🙂 ) . Could manage setting up one test DB server though with a production restore just over a month ago for another exercise. Nonetheless, I'll run an update stats with full scan on this table and see if I can simulate a part of it running some query at the database directly. Doing this on production looks very difficult as the technical project lead has no knowledge of the databases and feels good opinionating on advance topics on databases...and a DB dev lead feels that statistics are actually not important ....sic. Hence I put it as a Take-it-or-Leave it statement after some words about statistics . So it might take time till the same people ask me to help in the statistics area . Thank you for the valuable help . really appreciate.

I wasn't suggesting to do the experiment on a test box. I was suggesting that you do it on the prod box. It's a simple, one time experiment to demonstrate whether you have a stale statistic or poorly sampled statistic problem or not.

Arsh - Monday, September 4, 2017 7:29 AM
GilaMonster - Friday, September 1, 2017 1:52 PM
Arsh - Friday, September 1, 2017 5:40 AM
GilaMonster - Thursday, August 31, 2017 1:19 PM
Threshold for triggering auto update != sampling rate.
The threshold is 20%. Jeff's talking about the sample rate.
Thanks Gail. So the higher the sampling rate the better are the stats, I suppose ?
Not necessarily. If the data is evenly distributed, then even a 1% sample is probably sufficient.
Hi Gail, the sampled rows are only 0.35% of a total of close to 140 million rows. The density-vector shows 'All Density' as 0.0002 . Average Length is 8 . I'm looking at a column that has some transaction amount. Thank you.

Density-vector for what? I'm thinking that it's likely that there are more than a single statistic on the table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Jeff Moden - Monday, September 4, 2017 10:34 AM
Density-vector for what? I'm thinking that it's likely that there are more than a single statistic on the table.

And besides, all a density vector tells you is the average uniqueness of the column or set of columns (it's 1/(unique values) ). It doesn't give any information about whether a stat is out of date (nothing in the stats can), nor does it say anything about the sampling rate, whether it's sufficient or not.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Arsh SSCertifiable Points: 6354 More actions · Answer 4

I understand Gail. So I've to hunt for the 'Actual' vs 'Estimated' row count in the plan iterations. Thank you.

Arsh SSCertifiable Points: 6354 More actions · Answer 5

Jeff , yes the table has many statistics defined; I mentioned only for one column.Thank u .

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 6

So have you run the experiment to see if it's actually out of date statistics that's causing the problem?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Arsh SSCertifiable Points: 6354 More actions · Answer 7

Hi Jeff. Couldn't convince the team I am working with. There's no DBA except me. There's no end to end test environment as well.The production env is hosted by some other company remotely.Thank you.

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 8

Arsh - Tuesday, September 5, 2017 10:38 PM
Hi Jeff. Couldn't convince the team I am working with. There's no DBA except me. There's no end to end test environment as well.The production env is hosted by some other company remotely.Thank you.

You should show them the last part of that Adam Machanic video you posted a link for yesterday. It really makes the point about statistics and Adam also mentions the futility of ending up with a 20% sample rate. He also clearly demonstrates the horrible performance if you do a query based on an ever increasing key with as little as a 13% addition of data.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Arsh SSCertifiable Points: 6354 More actions · Answer 9

Jeff Moden - Wednesday, September 6, 2017 7:42 PM
Arsh - Tuesday, September 5, 2017 10:38 PM
Hi Jeff. Couldn't convince the team I am working with. There's no DBA except me. There's no end to end test environment as well.The production env is hosted by some other company remotely.Thank you.
You should show them the last part of that Adam Machanic video you posted a link for yesterday. It really makes the point about statistics and Adam also mentions the futility of ending up with a 20% sample rate. He also clearly demonstrates the horrible performance if you do a query based on an ever increasing key with as little as a 13% addition of data.

Yeah , I'll try to. For my own assessment of the time required , I ran the UPDATE STATISTICS WITH FULLSCAN on the biggest table ,as per your suggestion on a production database backup copy taken 4 months ago (70% of current DB size of 700 GB) . It finished in 57 minutes. the company has no end-end test environment, so have to wait for the migration of this database to a new data center , after which , can try this if agreed by others, so a months time I suppose. But thank you very very much for the guidance on this topic as well.