March 25, 2015 at 2:43 am
Hi, just looking for some confirmation that what I'm observing is reasonable.
I took advantage of some system downtime following a scheduled power outage at the weekend to update the statistics in our database using sp_updatestats.
This is not something I've done before explicitly, instead relying on auto-update.
Following this, I noted that the disk space usage reported by sp_spaceused had reduced significantly.
In particular, Data usage was down by 1100MB (~9GB to ~8GB). Index usage was only down by 40MB.
Is this consistent with what I imagine to have happened, i.e. an existing set of statistics derived through FULLSCAN heuristics has been replaced by a set derived through sampling?
Would the sampled set be so much smaller than the original?
Thanks
Gary
March 25, 2015 at 4:28 am
Yeah, it can be. sp_updatestats is an OK way to update statistics on most systems for most statistics. But some systems and some statistics are going to need a more direct control on the update. In that case, use the UPDATE STATISTICS command directly. You can control sample size or even set FULL SCAN as needed. Also, for most systems, I wouldn't suggest just letting the auto update handle everything. As the number of rows grows, auto update fires less and less frequently. I'd also suggest setting traceflag 8371 to on (if you're 2008r2 sp1 or greater). That will help auto stats.
"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
March 25, 2015 at 5:57 am
sp_spaceused is unrelated to statistics.
sp_spaceused uses the metadata that the storage engine knows about tables to calculate results, it doesn't use the distribution statistics which sp_updatestats (or UPDATE STATISTICS) affect
The statistics blobs are tiny, they won't contribute significantly to the size of the database. No matter whether the update is sampled or full scan the stats blobs don't contain more than 200 different values in them.
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
March 25, 2015 at 6:28 am
I guess I misunderstood the question. I'm with Gail.
"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
March 25, 2015 at 6:29 am
Thanks Grant, Gail
Unfortunately we're stuck on 2008 SP3 (not R2) so that trace flag (2371?) isn't available.
I think I'll have to schedule some UPDATE STATISTICS alongside our regular index reorg.
All of the user tables' row counts are in line with what they were before the statistics were updated.
I'm still at a loss to explain where the extra 1GB of free space has come from, if not the stats blobs.
Guess I'll have to dig a bit deeper...
March 25, 2015 at 6:32 am
Run DBCC UPDATEUSAGE, make sure that the metadata around space usage is correct. The bugs in the usage algorithms should all have been fixed on SQL 2008, but...
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply