Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mfemenel
Professor Frink
1421 Posts |
 Posted - 2011-01-06 : 09:37:13
|
| Nightly we have a table that does a partition switch from our staging table into our production table. We then call an alter index statement:Alter index ALL ON tablename REBUILD WITH (Sort_in_tempdb = ON)Now it's my understanding that Rebuild ALL should be doing a full sampling of data for stats on all indexes in the table. Randomly (on varying nights) the alter index statements don't do a full sample.For exampleTuesday evening Table A is loaded with 1677473 rows and the rebuild samples 1677473 rows. Last night, same table is loaded with 1693811 rows but only 73466 rows are sampled. This then causes downstream processes to bog down because it sampled such a small percentage. There is no consistency as to when this happens. The same job runs at the same time every night. We've seen this on varying days of the week, never in any pattern we can identify. Also, Auto update and auto create stats are enabled on the db.Anyone have any thoughts on what would cause this behavior? Mike"oh, that monkey is going to pay" |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-01-06 : 10:26:00
|
| Are you looking at statistics on an index or column statistics?--Gail ShawSQL Server MVP |
 |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2011-01-06 : 10:46:47
|
| on an index via dbcc showstatistics tablename, indexnameMike"oh, that monkey is going to pay" |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2011-01-06 : 10:47:30
|
| and one more thing, these are clustered indexes where I'm seeing this issue.Mike"oh, that monkey is going to pay" |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-01-06 : 11:10:48
|
| There wasn't an auto update (or sp_updatestats) afterwards perhaps? Rebuilds will update index stats with full scan--Gail ShawSQL Server MVP |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2011-01-06 : 11:23:53
|
| The tables are loaded and then no further data is added until the next evening when the process runs again. So there isn't anything else that would cause the auto stats to fire. And I agree that update index stats "should" do a full scan. Once or twice a week though that doesn't happen and there's no pattern I can find to it.Mike"oh, that monkey is going to pay" |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-01-07 : 03:06:28
|
| Rebuild index does update stats with full scan, because it's already reading the entire index. Not happening same time of week? No pattern?Profiler trace? Make sure there's no UPDATE STATS or sp_updatestats run manually?--Gail ShawSQL Server MVP |
|
|
|
|
|
|
Alter index issue