August 2, 2008 at 5:48 am
Marios Philippopoulos (8/2/2008)
FULL SCAN, though it works to alleviate production issues, is a maintenance nightmare. One cannot run it on the whole database all the time because it will block other processes running on the instance.
It shouldn't do. Stats updates don't take any locks other than a schema-stability lock. If you're seeing processes blocked by a stats update, then there is something strange going on.
I do agree that you don't want to do full scan on an entire DB. The way I've got the manual stats running (admittedly just sampled)is to do the update in the job that adds new data into the table. That works, because the DB in question is write-heavy overnight and read-heavy during the day.
Data skew causes more problems than just inadequate statistics. It's also the major factor in parameter sniffing.
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
August 2, 2008 at 5:52 am
GilaMonster (8/2/2008)
Marios Philippopoulos (8/2/2008)
FULL SCAN, though it works to alleviate production issues, is a maintenance nightmare. One cannot run it on the whole database all the time because it will block other processes running on the instance.It shouldn't do. Stats updates don't take any locks other than a schema-stability lock. If you're seeing processes blocked by a stats update, then there is something strange going on.
I do agree that you don't want to do full scan on an entire DB. The way I've got the manual stats running (admittedly just sampled)is to do the update in the job that adds new data into the table. That works, because the DB in question is write-heavy overnight and read-heavy during the day.
Data skew causes more problems than just inadequate statistics. It's also the major factor in parameter sniffing.
Thanks, good to know.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 2, 2008 at 11:15 am
Marios Philippopoulos (8/2/2008)
Grant Fritchey (8/2/2008)
And that's why we've only had to do the FULL SCAN in a few places and on a somewhat irregular basis. Most indexes, most of the time, if they were designed properly, have an even enough distribution for the sampling mechanism to work very well.So is this a question of design? I must admit I've never thought about it this way.
Are there any strategies to minimize the possibility of uneven data distributions through proper indexing? Now that would be an interesting article for SSC.
FULL SCAN, though it works to alleviate production issues, is a maintenance nightmare. One cannot run it on the whole database all the time because it will block other processes running on the instance. Rather, a few arbitrarily chosen tables are usually chosen. It's messy, it's dirty and I don't like it. If there is a way to minimize its use through proper indexing I want to know about it. 🙂
I don't know any tricks. We've just developed a method of testing all our designs and only indexing where it's really needed and trying to evaluate the columns... Hard work, care, repitition... you know, the boring sutff.
"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
August 2, 2008 at 11:43 am
Hmmm, I wonder how filtered indexes (SQL 2008) would work for a column with severe data skew. Create one index for the 'common' data values and another for the 'rare' values. I'm not sure if it would solve the parameter sniffing problem, the plan would be compiled for the index applicable at first run, though it might force a recompile instead of using the non-optimal plan, however it should solve the inaccurate statistics problem.
I'll give it a try out when I get back from TechEd.
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 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply