3 ms·

The reality is that, even after more than 30 years of research, query optimizers are highly complex pieces of software which still face some technical challenge

The reality is that, even after more than 30 years of research, query optimizers are highly complex pieces of software which still face some technical challenges. Yep. It still amazes me how queries can go from absolutely great to scanning tables with a row added in the wrong place. WITH RECOMPILE still scares the heck out of me. It can really help when distributions change, but it can also kill your performance with a bad query plan choice. It addition to the hint show, you can also force certain indexes. This didn't seem to be as big a problem on SQL Server as its parent code Sybase ASE. Sybase made some really, really bad choices on big tables. No amount of optimization will fix a bad schema design. This is also a field where talking to people about how you built your machines and where the indexes are located can make a world of difference. If you cannot have the whole database on the fastest storage, then at lest put the indexes there.

swasheck 10y ago
> 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...

swasheck 10y ago

> 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...