Voice of the DBA

Tag: sql server

  • CPU Upgrades

    I expected this to be an article from Glenn Berry. He often tries to convince me to upgrade. I think so he can justify buying his own new hardware.

    In this case, however, this is a note from Tom’s Hardware that it’s a great time to upgrade your CPU. The article is a few weeks old, referencing Cyber Monday deals, and it notes that CPU sales are at a 30-year low. This means there’s a lot of supply and retailers are trying to clear out stock.

    I don’t know how many of you find your CPU slow, or if you have access to change the CPU (often you can’t in a laptop). If you have a desktop, then you might not have a motherboard that supports the latest CPUs. Glenn would say upgrade your motherboard, but as I’ve seen in the past, this sometimes means memory upgrades as well.

    Many of us work with server machines, but we aren’t responsible for the hardware. We often can’t even request different hardware. Glenn has written about which CPUs work for SQL Server, though not for a few years. Maybe this piece will get him to update that article.

    SQL Server 2022 is the latest version, and the price as increased. The performance per core is more important than ever, so choosing the best CPU could have a big impact on the ROI of your database server. Even if you use the cloud, often there are choices in hardware, and expressing your reasoning for one over the other to the groups responsible for infrastructure can help ensure your organization gets the most performance for their billing.

    And if you’re the person specifying hardware, you definitely ought to understand the differences between the CPU choices and make the best choice for your budget.

    Steve Jones

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

  • The SPU

    Ever since I heard about the SQLOS and all the work that went into producing an operating system for SQL Server inside of the host OS, I’ve thought that perhaps there might be value in specializing the environment for a database platform. Given the importance of databases, especially relational ones, and the need to get every bit of performance out of hardware, I wondered if we wouldn’t see a version of SQL Server that is installed directly on the hardware, without a host OS.

    In some sense, I guess that’s what a PaaS database is in Azure, but I thought we might see that for the download-and-install version. I doubt that will ever happen now with the growth of the cloud.

    Instead, maybe there’s a better way to improve performance without requiring SQL Server (or Oracle, PostgreSQL, etc.) to implement some of the OS features they’d need. Perhaps we could change the hardware around and use a SQL Processing Unit (SPU). The SPU is another specialized chip. Like a GPU for graphics, or even some of the other chips that are made specifically for mobiles, storage, or networking, this one would be focused on database needs. Apparently, there are a few companies that are researching how they might build chips that focus specifically on the types of computations that data analysis requires.

    I don’t know how practical this is, or whether we’d see any major database vendor attempt to port to a new chip. Perhaps they’d add instructions that could use one of these chips inside a regular system, similar to how a GPU can offload work. That might seriously improve performance, something that a lot of our customers would appreciate. Especially if this happens without spending a lot of developer time rewriting old code.

    That’s not to say that developers shouldn’t learn to write better code. They ought to, mostly so that their initial attempt at producing reports or batches of data handles a wide variety of data values and workloads without stressing the hardware. More efficient code is always the best way to attack any problem. Even if you had a very efficient SPU, chances are that your workload will still grow to overwhelm the hardware at some point, especially as the number of users grows.

    Monitoring helps find problem queries, but it’s up to developers to change their habits, grow their knowledge, and produce better code sooner. Then we can use hardware to deal with the large numbers of users that need to access the system.

    Steve Jones

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

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

  • T-SQL Tuesday #154–Thinking about SQL Server 2022

    This month is an interesting T-SQL Tuesday party, as Glenn Berry hosts and asks us to think about the upcoming new release of SQL Server. Sometime later this year, I expect SQL Server 2022 to be released and Glenn is asking us to talk about our experiences.

    If you haven’t looked at this new version, which is in RC0, you might take a few minutes to play with it and see if the language (or other) changes might be useful in your organization.

    If you want to host a T-SQL Tuesday yourself, ping me on Twitter.

    My Work with SQL Server 2022

    I first saw some demos of SQL Server 2022 in 2021, at various conferences. Microsoft showed off some new capabilities, some of which were very interesting. I was lucky as an MVP to get some access to private, pre-CTP builds and experiment a bit with new features.

    This spring Microsoft publicly released CTP 2.0, then 2.1, and now RC0. I’ve upgraded to 2.1 on my desktop, and was waiting the docker container to update before moving to RC0. just after Glenn’s invitation, I saw the container was up to date, so I pulled a new one.

    I’ve run some of my old demos on the platform, just to check that they work. That’s really work to see if there are any regression bugs. I have rarely found this to be the case, but it has been interesting to do this with 2017, with the linux version, and more. I’ve only been lightly interested in this release, as a few of the changes aren’t applicable for the work I do with Redgate. Others might be interesting to the community, and I need to spend more time on them.

    Really, I got mostly interested in the T-SQL language changes. I had been using Window functions and the OVER() clause quite a bit and find writing them cumbersome, but I was excited to see the SELECT..WINDOW clause. I’d also liked the STRING_SPLIT enhancements with an ordinal.

    There was an article sent to SQL Server Central that got me to look at more of the features, and I think that while most of these aren’t changes I’ve been needing, they do improve and round out the language. I look forward to experimenting with them a bit.

    I’ve reproduced a few performance demos, looking at the query optimization features, and those seem good, but for me and many other developers, these will just be things that should work. Not something to get too excited about. Unless you’re on call, then you might really want to upgrade and hope these fix some of your problems without creating other ones. Maybe the thing I’m most excited about is the granular UNMASK permission, which has been overdue for a couple of versions.

    I don’t quite know what to think of this new version. While there are more changes than I expected, it feels like a lot of small changes and not much of a fundamental shift in the product. I suppose it’s a major release, but kind of like SQL Server 2014, this feels more evolutionary than revolutionary.