SQL only sampled 1 row for 47,000,000 table for a user created statistic

Question

SQL only sampled 1 row for 47,000,000 table for a user created statistic

Paul Treston

Mr or Mrs. 500

Points: 590
More actions
August 30, 2013 at 5:42 am

#277455

I have an unusual problem which cause as major performance issues with our Application. A user created statistic on our main table in our database hit the 20% modification threshold so SQL refreshed the statistics. It only sampled 1 row.
Before:
StatisticNameStatisticTypeNoRecomputeLastUpdatedRowsRowsSampledUnfilteredRowsRowModificationsModificationPercentageSteps
_dta_stat_244664415_1_2_15_7_3_8User Created02013-03-17 16:31:4739176591343917659178344252028
After:
StatisticNameStatisticTypeNoRecomputeLastUpdatedRowsRowsSampledUnfilteredRowsRowModificationsModificationPercentageSteps
_dta_stat_244664415_1_2_15_7_3_8User Created02013-08-29 17:02:2546999958146999958001
Once the issue was identified, an UPDATE STATISTIC WITH FULLSCAN fixed the problem.
Does anyone know a valid reason why SQL would have only sampled 1 row ? Personally I think this is a bug in the SQL Engine.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 1

Is it possible that it's a filtered statistic? I mean, the "good" example only sampled 38 rows. That seems pretty radically off unless it's filtered down to only a few rows. In which case, it's possible that the sampled scan, normally what's done for automatic statistics updates, was accurate, as far as it goes. Full knowledge that "accurate" and "good" in this case are two different things.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Paul Treston Mr or Mrs. 500 Points: 590 More actions · Answer 2

Hi Grant, thanks for the reply. I have just double checked and it is not a filtered index.

NameUpdatedRowsRows SampledStepsDensityAverage key lengthString IndexFilter ExpressionUnfiltered Rows

_dta_stat_244664415_1_2_15_7_3_8Aug 29 2013 5:02PM4699995811024NO NULL46999958

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 3

I am not sure. This is not something I've seen before. Do you have all the service packs applied to the server?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Paul Treston Mr or Mrs. 500 Points: 590 More actions · Answer 4

Yes. We are running SQL2008R2 SP2 Enterprise Edition. We had applied SP2 on Thursday 22nd. Performance ground to a halt on Monday 26th. We rebuilt all statistics on this key table and the performance returned to normal. The root cause analysis I conducted identified this very obscure stat which contains pretty much all of the key columns that would be used to JOIN to this table.