3 ms·

> It still amazes me how queries can go from absolutely great to scanning tables with a row added in the wrong place. I think that this is a bit of an oversimp

> It still amazes me how queries can go from absolutely great to scanning tables with a row added in the wrong place. I think that this is a bit of an oversimplification, though I understand the sentiment. Statistics skew is one of the primary reasons for such a dramatic shift in query planning and adding a row can create such a problem if you're already at some sort of skew tipping point and that one row pushes you over the edge. It would be nice to know more about where the edge is and how to plan to ensure you don't drive your application's/server's performance off the cliff, though.

protomyth 10y ago
> I think that this is a bit of an oversimplification, though I understand the sentiment. No, it really isn't. Cache / Memory isn't a sliding scale, and once you exceed it you fall off the cliff. Databases queries can fall off a cliff if you exceed your memory. I've watched a query that was executing in a couple of seconds drop into many minutes territory because it had to start paging. There is a real machine with limited memory running your queries and it has been my experience that warnings are rare.
- swasheck 10y ago
  I appreciate your response and I agree with you. However, you don't really address my point, which is that statistics skew can cause variations in query planning. Your scenario assumes a fixed data size with a homogeneous (or at least "known") distribution of keys. In your original comment, you noted that adding a row "in the wrong place" can cause seeks to go to scans. As I thought through it, I realized you can get a good sense of statistics skew by getting the counts and distinct counts of each value for your keys. If you have a lot of variation, then you may be able to reasonably expect query plan variations.
  - protomyth 10y ago
    I was looking at my original post you responded to. I was talking about catastrophic changes to speed based on data and machine considerations. They are not the only causes of problems but are often overlooked because people forget that this stuff runs on real hardware with definite limits beyond the query plan statistics.
- jve 10y ago
  Even if you have tons of RAM and the data IS there, performance is still disasterous if you have to perform seven, eight or nine digit logical reads. (millions or billions). Happens when SQL Server assumes it will use loop join because it thought there is only 1 row to join. Oops.
  - protomyth 10y ago
    Oh yeah, memory fails aren't the only cause, but they do tend to hit you very quickly. Nothing like doing production patches because you went from comfortably able to get done in the time period needed to "won't finish until next week". I can just see the developer face when seeing the loop join where it shouldn't be. Almost as bad as seeing FSM (full sort merge) on Ingress or full table scan on ASE or SQL Server. That's really why I recommend SQL writers really study the plan generated. Its the closest thing to studying assembly to teach what the optimizer is thinking. I still think the old Ingress plan viewer was pretty neat.
jve 10y ago
There is a nice post: SQLskills procs to analyze data skew and create filtered statistics [1] that links to a session where statistics skew is explained and some scripted solutions provided to kind of cover more rows with filtered stats. [1]: http://www.sqlskills.com/blogs/kimberly/sqlskills-procs-analyze-data-skew-create-filtered-statistics/ http://www.sqlskills.com/blogs/kimberly/sqlskills-procs-anal...

protomyth 10y ago

> I think that this is a bit of an oversimplification, though I understand the sentiment. No, it really isn't. Cache / Memory isn't a sliding scale, and once you exceed it you fall off the cliff. Databases queries can fall off a cliff if you exceed your memory. I've watched a query that was executing in a couple of seconds drop into many minutes territory because it had to start paging. There is a real machine with limited memory running your queries and it has been my experience that warnings are rare.

swasheck 10y ago
I appreciate your response and I agree with you. However, you don't really address my point, which is that statistics skew can cause variations in query planning. Your scenario assumes a fixed data size with a homogeneous (or at least "known") distribution of keys. In your original comment, you noted that adding a row "in the wrong place" can cause seeks to go to scans. As I thought through it, I realized you can get a good sense of statistics skew by getting the counts and distinct counts of each value for your keys. If you have a lot of variation, then you may be able to reasonably expect query plan variations.
- protomyth 10y ago
  I was looking at my original post you responded to. I was talking about catastrophic changes to speed based on data and machine considerations. They are not the only causes of problems but are often overlooked because people forget that this stuff runs on real hardware with definite limits beyond the query plan statistics.
jve 10y ago
Even if you have tons of RAM and the data IS there, performance is still disasterous if you have to perform seven, eight or nine digit logical reads. (millions or billions). Happens when SQL Server assumes it will use loop join because it thought there is only 1 row to join. Oops.
- protomyth 10y ago
  Oh yeah, memory fails aren't the only cause, but they do tend to hit you very quickly. Nothing like doing production patches because you went from comfortably able to get done in the time period needed to "won't finish until next week". I can just see the developer face when seeing the loop join where it shouldn't be. Almost as bad as seeing FSM (full sort merge) on Ingress or full table scan on ASE or SQL Server. That's really why I recommend SQL writers really study the plan generated. Its the closest thing to studying assembly to teach what the optimizer is thinking. I still think the old Ingress plan viewer was pretty neat.

jve 10y ago

There is a nice post: SQLskills procs to analyze data skew and create filtered statistics [1] that links to a session where statistics skew is explained and some scripted solutions provided to kind of cover more rows with filtered stats. [1]: http://www.sqlskills.com/blogs/kimberly/sqlskills-procs-analyze-data-skew-create-filtered-statistics/ http://www.sqlskills.com/blogs/kimberly/sqlskills-procs-anal...