July 5, 2011 at 11:32 am
I'm being hammered by management for an explanation of how improving queries ( eliminate table scans and excessive logical reads, etc ) will result in our ability to do statistics maintenance at lower sampling rates.
We're pretty much driven by the development team who designed the database and the ORM that generates the sql. A few years ago I was asked to begin updating stats at fullscan. Now that we're at 40 million records in the database, maintenance runs too long at fullscan.
Improvements are being made to some of our worst-performing select statements, but management really wants specific information like " improvements to these queries means we can run stats at 50% sampling on those tables, 20% on those tables and at sql server's default sampling on those tables."
We do use a common stored procedure for stats which starts with those having the highest ratio of data changes to table records.
We've been testing 90% sampling in recent weeks. Auto create/update stats is on.
Since my test environment does not match production at all, the testing I've done is of questionable value. Some queries show longer run times at lower sampling, some don't.
July 5, 2011 at 11:40 am
Indianrock (7/5/2011)
Improvements are being made to some of our worst-performing select statements, but management really wants specific information like " improvements to these queries means we can run stats at 50% sampling on those tables, 20% on those tables and at sql server's default sampling on those tables."
You can't say anything like that.
Some stats will need updates with fullscan, some need sampled, some you can get away with no updates at all. Has to do with the data and the queries. No easy way to tell other than testing. Yes, it's a pain, that's why most people just got for fullscan everywhere if they can
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
July 5, 2011 at 11:42 am
You need a better test env. No ifs or buts about that one.
A couple options assuming you can't restore the full prod db to another server.
1 - red-gate virtual-restore. Keep in mind that it might skew the perf results because of the overhead to read / write the data.
2 - You can script the stats from prod to redeploy on test which should give you accurate plans even if you don't have all the data.
ssms / your db / right-click / task / script
In the option use script stats with histogram. Now this will take a heck of a while to script this out completely (runs update stats + histogram for all stats for all the tables you want AFAIK). But if you do it 10 - 20 tables at a time maybe you can get away with it!
July 5, 2011 at 11:52 am
We do have a copy of the production database, which barely fits on the DEV systems. But the DEV system I'm testing on only has 16GB of memory versus 128, isn't 64-bit and just has one fairly large locally attached drive ( no SAN - Netapp ).
My general answer to management's question is that we probably have to update stats at some higher rate on larger/busier tables, but outside of testing on a similar system, I don't know of any formula for that.
July 5, 2011 at 12:02 pm
Indianrock (7/5/2011)
We do have a copy of the production database, which barely fits on the DEV systems. But the DEV system I'm testing on only has 16GB of memory versus 128, isn't 64-bit and just has one fairly large locally attached drive ( no SAN - Netapp ).My general answer to management's question is that we probably have to update stats at some higher rate on larger/busier tables, but outside of testing on a similar system, I don't know of any formula for that.
I'm working on a project that might help you out but it's not a 1 size fits all type of problem. You'll really have to set aside a lot of days to do tests. It should get much better all the time... but you can't expect an overnight fix for this!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply