Voice of the DBA

Tag: Performance

  • The Growth of IQP

    When Microsoft started to talk about Intelligent Query Processing (IQP) before SQL Server 2017, I wasn’t sure what to think. There was a diagram with 20 things on it, and only 5 were highlighted (you can see that in the article linked). That felt like something, but barely something. Over time, as we’ve gone through SQL Server 2019 and now 2022, the diagram now looks more filled in. In fact, now there are a lot of things filled in.

    When I think about new features of SQL Server, I don’t often spend a lot of time on the IQP stuff. First, I don’t have to live with people yelling at me every day about slow performance. If I did, I might be more anxious to test and evaluate the changes. Second, these are mostly just hidden things that come with the product and (fingers crossed) improve my workload.

    There’s an article on the 2022 changes, which improve upon some of the older IQP work as well as add some new items. It seems the feedback loops are getting better, though not perfect. I’m sure all the performance tuning experts will write posts about where these things fail, which is good. We want to know where we might have problems. Hopefully, we’ll also see lots of demo code that shows where these things work.

    One of the more interesting things is the Parameter Sensitive Plan Optimization. This should help with parameter sniffing issues, though it will be interesting to see what percentage of a variable workload is improved by this. I also wonder if loaded systems that struggle here might have other plan issues. I already know some people have an overloaded plan cache now. If they could have even more plans for each type of query, there might be other issues to contend with.

    For the most part, SQL Server works well for a wide variety of situations. Many people seem to be quite pleased with how well it performs. I don’t know if that means customers look forward to upgrades or are happy where they are, but I am glad that the product doesn’t just add developer features or complex technology for administrators to learn. Getting investments in better query performance is important, especially as it can be hard to get developers to write better code on their own.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.

  • The Need for 256GB

    I have seen a few people call for raising the RAM limit in the Standard Edition of SQL Server. In 2016, Aaron Bertrand voiced this, and for 2019, Glenn Berry asked that the limit be raised to 256GB. In the last newsletter of the year, Brent Ozar asked Santa for a 256GB limit.

    I wonder how many of you would really take advantage of that. In the Azure SQL Database pricing table, to get beyond 128GB of RAM, you need to go to 32 cores. For Azure VMs, you need to purchase even more cores.  AWS EC2 VMs require 32 cores to get to 256GB.

    How many of you use this many cores for your SQL Server Standard Edition instances? I’m sure some of you do, but is it many instances that require this many cores and RAM without Enterprise Edition? If you do run EE, then is it because you need more resources or because you need some other EE feature?

    Certainly, the use of lots of resources is likely something Microsoft considers to be a feature. They want more for licensing when you have a large workload. I don’t know if I think this is morally fair, after all the bits are really the same and there’s an artificial limit that doesn’t allow the use of them with more underlying resources.

    As a side note, this has made it into other areas. My Tesla offers me the option for more acceleration if I pay them US$2000. The hardware will already support this, but it’s a software unlock for a price. That feels strange.

    Across all the instances you have, how many of them have the need for more RAM? Perhaps a better question is whether your organization would allocate more RAM given the cost involved. I still see too many organizations that underspend for hardware when it would make a difference for customers. Of course, many of you might also get better performance if you learned to write better code that efficiently solves query problems.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.

  • The PASS Pro Videos are Live

    The PASS Pro program was an interesting educational opportunity that came out last year. A number of people subscribed, including me. I did this more to support the org than for the content, but I did want to watch the videos and learn some new skills.

    When Redgate Software purchased the assets, this was one of the first things we wanted to release. The process took a couple weeks, but you can now access the materials for free.

    All the courses were added to the Redgate University, as this was the easiest way to organize them on our side. You watch each of these for free:

    You can now work through these courses, without the quiz gates, and just view the material.

    I do hope we get the quizzes live, but for now, you can access these for free.

  • The Database Server Upgrade for Let’s Encrypt

    Let’s Encrypt is a service the provides free SSL certificates for web servers. They have grown from a small project to a critical service on the web that serves over 235mm clients each year, and often issues over 1.5mm certificates each day. They depend on a MariaDB database to support this, and recently, they released a post on the database server upgrade they made. They run physical hardware on a managed services provider (Datto), rather than using a cloud platform service, which is fine. While cloud services are great, they aren’t always the best choice. I’d like to see them talk about any evaluation as to what Datto is better than, say, AWS.

    In any case, I like seeing how people upgrade hardware, as well as the impact of changes. I haven’t always done this when I’ve upgraded my home systems, though I do notice the new one always runs faster. Good for me, but for production systems, we should have better metrics, if for no other reason than to better learn how to size the next upgrade.

    In this case, Let’s Encrypt has a great post that shows how the hardware changed. They moved from Intel to AMD, 48 threads to 128 threads, doubled RAM, and went from mid 500MB/s read/writes to 3200MB/s. Their response metrics also improved, with API requests going from 90ms to 9ms. CPU dropped from 90% to 25% and latency is a third of previous values. Quite an impressive change.

    I like seeing the details of their change, and I think this is a nice pattern for others to think about. Whether you spec out machines, VMs in AWS, or DTUs in Azure. You ought to have some metrics that matter for your customers, and then compare the before and after. Prove your upgrade worked, or maybe prove that you need to alter your specs after the upgrade. One advantage with a cloud provider is that you can often scale up or down as needed. With an MSP, it might be harder, but I bet you can still change hardware fairly quickly if you suspect a problem.

    While we all want to test a new configuration before we turn it on, simulating a workload can be challenging. Clients somehow always seem to find ways to stress systems we hadn’t anticipated. When spec’ing out a new system, make sure you have room to grow if you’ve underestimated how the new system performs. You also might set specific targets that aim to improve your metrics to some level. Even if you don’t hit the estimate, you will learn something about your methodology that can help you improve your guess for the future.

    Above all, publish something like Let’s Encrypt. Share some data and help our industry move forward. We’ve love to have you write something for SQLServerCentral.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.