The Importance of CPU Performance for SQL Server

When it comes to optimizing performance for SQL Server, many people focus solely on I/O performance, but neglect the impact of CPU performance. However, as we will discuss, single-threaded CPU performance is often the final bottleneck in achieving optimal performance.

The latest generation of CPUs has made significant strides in addressing this issue. The introduction of boost performance baked into the architecture has greatly improved overall system performance. In fact, many database administrators who once disabled boost to achieve consistency have now found it no longer necessary. With newer generations of CPUs, power management is handled more efficiently, allowing for faster throttle-ups and reducing the need for manual intervention.

One key takeaway from this shift in CPU technology is that it's no longer a necessity to disable power management features like Intel's Turbo Boost. Instead, these features are now integrated into the system, making them easier to manage. This change has significant implications for SQL Server performance, as it allows for more efficient use of resources and reduces the need for manual optimization.

Furthermore, advancements in CPU technology have also led to a decrease in the importance of power management when it comes to achieving optimal performance. As we've seen with newer generations of CPUs, boost performance is no longer a bottleneck, making it easier for database administrators to achieve consistent results without having to manually intervene.

However, there are still some best practices that can help optimize CPU performance for SQL Server. One key strategy is to implement striped backups, which can greatly improve performance even with relatively slow I/O subsystems. This approach allows data to be written across multiple devices, significantly reducing the time it takes to complete a backup. With the increasing power of modern systems, this approach has become even more effective.

In fact, there are already tools available that support striped backups, including Ola Hallengren's free utility, which has been around for quite some time and works well. By leveraging these tools, database administrators can achieve significant improvements in backup performance without having to manually optimize system settings.

For home lab enthusiasts working with SQL Server, it's essential to understand that a 16-core Threadripper system with commodity DDR4 memory is more than capable of matching the performance of larger, more expensive systems. In fact, these smaller systems are often far less expensive and can be built with relative ease.

When building a system for SQL Server, there's no need to go all out with a massive number of cores or expensive hardware components. Instead, focusing on high-quality, commodity-grade components can result in surprisingly fast performance. With the right configuration, even smaller systems can leave larger, more expensive setups in the dust.

In conclusion, CPU performance is an often-overlooked aspect of SQL Server optimization, but it's crucial for achieving optimal results. The latest generation of CPUs has made significant strides in addressing this issue, reducing the need for manual intervention and making striped backups a viable option. By understanding how to optimize CPU performance and leveraging available tools, database administrators can achieve consistent results and improve overall system efficiency.

"WEBVTTKind: captionsLanguage: enit's not every day that you trip over bugs and find problems even if somebody was going to Shell out the 1.6 million dollars it would take to license Microsoft SQL server for this super micro system this is probably the fastest system on the planet running Microsoft SQL server and oh boy Glenn Berry and I we found some bugs and we had some Adventures well we got something very special for you today I'm joined by Mr Glenn Berry MVP SQL expert and uh I needed him to take apart some things as we wanted to do a bunch of testing across a bunch of different servers Intel like it was faster Genoa Genoa however you want to pronounce it uh the new sp5 platform from AMD Intel Sapphire Rapids with their accelerators q80 puts up a good pretty good fight and he's also committed to a threadripper system which is desktop which is a little different class and maybe like we had a whole different class of problems than we were expecting but I'm getting ahead of myself say hello let's let's talk about it hey well I'm really happy to be working with you on this and I had a lot of fun on these three systems and basically we we built a two terabyte database with 24 data files in SQL Server 2022 and we played with several different systems to see how fast we could get it to back up that database using different methods so that's kind of a high level of what we try to do and there was a lot of challenges and bottlenecks we ran into as we were doing that at the end of the day though backing up a two terabyte database in production most people expect that to take 45 minutes an hour yeah on these test systems we can do it in under a minute oh yeah yeah definitely I mean we got it down to 34 seconds so and that's a actual backup with no compression using a backup to know we got it down to 14 seconds so that's kind of mind-blowing 14 seconds to back up a two terabyte database yeah yeah that's crazy it doesn't necessarily even require that you have 1.5 terabytes of memory it's like oh we'll just cache it all into memory it's like no this is these were committed rights they made it to a block device yeah definitely you know so we ran several different tests we did a backup to null which is basically just reading all the data in the database and throwing it away and then we did a backup with no compression which is a lot of people do and then we did two different forms of software compression we did Legacy Microsoft Express compression that's been around since 2008 and then we tried the new Intel software-based compression qat compression and then we did Hardware assisted qat compression so we did those tests on this database with lots of different configuration settings and lots of messing around with the drives to try to maximize our throughput that's basically our bottleneck here is read sequential throughput from The Sword subsystem and then memory throughput and then finally write sequential throughput writing to the backup files using consumer ssds in place of enterprise that is a recipe for disaster in the Enterprise but in our case we wanted to see what the system would do making it go as fast as possible and we very quickly encountered some very obvious bugs one it's pretty clear that Microsoft is not really doing a lot of testing around 128 core SQL servers I mean sure the license is wildly expensive but our dual 96 core Genoa system here was definitely not behaving as it should it could be handily outperformed by a dual 32 core system in a lot of cases now for me that was counter-intuitive because in the preliminary testing and looking at how SQL servers used real world almost always it's more of a CPU bottleneck and I think that's what you were encountering on your own storage Beast system yeah and when you've got fewer cores and you do a striped backup what happens is you get lots of CPU pressure that more the more stripes you add and a striped backup by the way is just instead of writing to one backup file you have multiple files and they can be on the same drive or they can be striped across different physical drives like we did here so doing that puts a lot more pressure on the CPU so that's why I was pegging my threadripper system at 100 doing that for this testing and we didn't have that problem on the Genoa or Sapphire rapid systems when they had a lot more cores available but one interesting thing though that we ran into which is probably a SQL Server bug I'm going to go ahead and say it definitely is but I don't think you're quite as sure is uh we have so many cores that it doesn't seem like SQL server was recognizing all of the cores and using them appropriately because even though we had more cores available the software-based backup didn't seem like it took advantage of that no it didn't hear it you know SQL Server when it starts up writes an entry to the SQL server error log that tells you how many cores it can see and that was picking up all the cores and the sapphire Rapids and Genoa systems but there's a different query you can run that looks at what the Newman nodes inside of SQL Server can see and that was only picking up 64 logical cores per pneuma node and that's way below what we had available in those two systems so I think that was part of the problem we were running into running the qat software compression uh just to give the the audience an idea of the these systems these are pretty much top of stack for from both Intel and AMD both of them were Super Micro Systems our super micro system with Genoa had dual 96 core CPUs these are really not super realistic for what you'd be running on a on a bare metal SQL Server workload maybe you would be running a bunch of SQL Server VMS but I don't think that there's there's probably what five six customers worldwide running a system like that yeah because SQL server licensing costs for Enterprise Edition with that many cores is really going to be very expensive so most people are running on much smaller systems whether it's bare metal or virtualized so you're absolutely right there our other system which was Sapphire rapids-based also super micro because we tried to be as like for like as we could was uh based around the 48 core I can't remember the designation I think it was the 44 core 48 core Sapphire Rapids 8368 no it was yeah it was a 44 core and then it was an Intel Xeon Platinum 8458p is what it was so yeah one of the top of the line Sapphire rapid skus I was hoping that the extra clock speeds on that because usually if you have a little bit fewer cores you get a little bit better boost performance but it didn't really seem to help us much here no it didn't and interesting I ran cpu-z and geekbench and they were pretty close between the Genoa and the sapphire Rapids on those two benchmarks so they're fairly evenly matched for single threaded performance so part of our goal here was one to use all the pcie lanes and two to make this thing go as fast as we possibly could we had to get out our u.2 cheat codes and we used 24 icdoc m.2 to u.2 adapters along with solidon p44 Pro ssds because they were the fastest ssds we could get for this job even faster than Samsung ssds well for these five tests the storage Beast one on two of the tests so it won on no compression because it had slightly better I O performance and the Genoa system did because I had a lot of Samsung 990 drives in there where you had a lot of 980s and it's kind of funny to say that a Samsung 980 is slow but it was it was in this test so we won no compression on storage bees and then qat software also the storage piece one because it has faster single threaded performance than either one of the server systems and then the Genoa system one on backup to null because it had more memory throughput than my threadripper did and remember all we're doing is reading everything and throwing it away so having lots of read sequential throughput on the storage subsystem and then memory throughput is all you care about there and then also it won on the Microsoft Express Legacy software compression the Genoa system one and then finally the sapphire rapid system one on the qat hard work impression because it was using the built-in qat accelerator inside of the CPU so even though it had less storage performance it actually won that test and that's what that feature is designed for so it worked as advertised in that test yeah then the nice thing about the qat uh acceleration is that it really does work as advertised and to help everybody understand it's not a feature in a CPU core it's actually more like a pcie peripheral that's built into the CPU it's a sort of a memory based accelerator and we're fortunate because we also have numbers because you mailed me a pcie 3.0 quick assist accelerator you know in the before times intel was doing these kinds of accelerators on pcie cards which are actually pretty good and even if you have a SQL Server that's fully CPU pegged you use this accelerator whether built in or on a pcie card and it does not add really any CPU overhead so it doesn't you know when you're running a backup your users can't really feel that because the system's not busy dealing with that that might be IO yeah you know that car that I sent you is an older 2018 vintage uh qat 8970 card and it works as advertised also it offloads that backup compression workload from your regular cores to that card and it lets you run your regular workload with less of an effect from the backup compression and with that it was still holding its own even in you know 2023 it's you know I know 2018 is basically the 1970s in computer years but yeah still doing pretty good yeah I wish that Intel would come out with a newer version of that so if you wanted to run that on a generous system you could but they don't have a whole lot of incentive to do that so there hasn't been any announcements about it yet now I have picked up some more Sapphire rapid CPUs workstation versions and I found that the qat accelerator is disabled but there may be a way to to get it turned back on I don't know yet we're gonna have to save that for a future video yeah that'll be fun to play with if we can get that turned on otherwise they're going to be shoving pcie 3 peripherals and a pcie5 slot just it's like why why yeah such a waste it's fun I think um I think also it's probably worth setting up a single socket journalist system that's more akin to what people would probably use with with SQL server and see how that goes because the CPU for your threadripper system to be as CPU pegged as it was the Genoa system was disproportionately not pegged it's wasting Cycles somewhere beyond not just not using the cores and the single thread performance but I it was it was not obvious where or what was going on yeah well it'd be nice if we can get our hands on some pcie Gen 5 drives to use for this testing you know we've been just using Gen 4 drives so that's kind of our bottleneck on some of these tests yeah yeah it's uh it's a lot of fun we're also we're going a little off label here in the Enterprise drives especially even Enterprise pcie5 drives um are architected differently than consumer drives and so in order for us to to pull out what we have here you with your 990s and me with my 980s and later solidime ssds um they perform way differently than Enterprise drives and Enterprise drives are perform worse but you can count on an Enterprise Drive to perform consistently over the lifetime of the drive and no matter what the capacity of the drive is with the drives that we're using in these servers it's really not something anybody should ever actually do with their systems because as the as their database gets larger the performance is going to tank substantially and that's why no one uses these kinds of drives in an actual real server oh yeah that's that's the trade-off you know you get consistency and endurance with Enterprise drives but you don't get the absolute Peak Performance that a client Drive gets and and one thing that I had to be careful about with this testing is that most uh consumer drives or TLC and they have a small SLC right cache or relatively small and you've got to be careful that you don't exhaust that or when you do exhaust that you'll see a huge drop off in your sequential right performance so depending on what a test you're doing here you can run into that when you're doing this sort of Benchmark testing not just TLC also qlc especially the important ones like the 990s technically I think are qlc but they mix TLC and SLC depending on on what they're doing I may be wrong about that on the 990. yeah so yeah if we can get pcie Gen 5 drives at some point in the future that'll just give us close to double the bandwidth you know and like as you've said before the Gen 5 drives are still a little bit immature although they're getting slightly better I just got a a crucial t700 a couple days ago to play with so that's another fast consumer Drive Crystal are you listening send us a bunch although adapting the m.2 into u.2 is a little tricky I I reached out to icdoc and they sent me 16 I think and I bought eight more or nine more so that we could take m.2 and shove them into u.2 and fun fact even if you do that that doesn't mean that m.2 are hot plug you can put them in the u.2 slot but they're still not hot plug if you just shove them in there they don't work you have to reboot and it's probably hard on the m.2 and I'm probably lucky I didn't murder one of them shoving it into something that was not actually hot plugged yeah well I think we had a lot of fun playing with these server systems and making them do things they weren't really designed to do so impressive performance though for Jan on like from your perspective how is it in terms of we've seen two or three generations from both team red and team blue uh it seems to me like the performance Jan on Jan is moving up a lot which people running SQL workloads can benefit from but you know what are what are your thoughts well you know a lot of times your CPU performance is extremely important to SQL server and a lot of people don't seem to realize that they just focus on i o performance but if you don't have any other bottlenecks your single threaded CPU performance is your final bottleneck so the latest generation CPU releases are making a big difference for a lot of workloads you know some of you don't have any other bottlenecks to run into so there have been some pretty big jumps and I'm really anxious to see more and more people start to use Sapphire rap Zan Genoa for SQL Server do you think the fact that the last couple generations of CPU pretty much have Boost performance baked in I mean I know Intel xeons have been doing boost for a long time but certainly I've worked with other database administrators who disabled it's like oh we're going to disable all power management and we're going to disable everything and the chords will always run at the same speed for consistency and now that seems like you're shooting yourself in the foot yeah that used to be a really big deal power management make a huge difference for SQL server on older generation CPUs but the newer ones handle that a lot better and they throttle up a lot more quickly so you don't necessarily have to go in and do that like you did or if you do it it doesn't make as much of a difference as it did in the past so you're absolutely right there fun times can you think of any other tips or tricks or anything else you want to share well not really I mean just the one thing I think people should take away from this is you want to do striped backups to get more performance even if you've got a fairly slow i o subsystem that makes a huge difference so yeah that's probably the biggest easy tick away from this it would be pretty easy with the uh with the Powershell facilities in modern SQL Server you could do a Powershell command that does the striped backup and then collects the files and deposits them on a remote system or forwards them or or linearizes the fact that now your backups are spread across a whole bunch of different devices yeah well I mean there's existing tools there's a guy named Ola allengren who's another SQL Server MVP who has a tool that's been around for quite a few years that already supports striped backups and sort of a free utility and it works really well so yeah nice so this was a lot of fun I learned a lot I learned a lot about how acceleration works and how acceleration works specifically with SQL Server because Microsoft's actually done a really good job making sure that the way that it uses the hardware acceleration doesn't impinge on things that are running on the CPU because that could negatively impact the actual thing it's trying to do as far as be a database server so there's a lot of really interesting stuff you can learn here and you can learn all that from from Glenn's blog and also his YouTube channel so you should definitely check that out you want to gonna say anything about it the last thing you blogged about or your articles or you know fun tips and tricks I mean if you have to live this daily you really should check it out uh well that sort of caught me by surprise but I mean I I blog fairly regularly and I'm trying to build my YouTube channel so there'll be more content coming soon so yeah you know I focus on SQL server and from a hardware perspective which is sort of unusual so well certainly there needs to be more of that because there have been some messes that I've cleaned up not with not on the Microsoft side but just with databases in general where it's like oh look at that there's no keys wow how is this working at all oh yeah no that's that keeps me employed so you know and if you're you know a home lab Enthusiast working on SQL Server you don't need a 96 core Genoa system to do it you can do it with a 16 core threadripper with its you know commodity ddr4 memory I mean look at these numbers in this spreadsheet this tiny little 16 core system is within spitting distance of these monster systems so yeah it's pretty good really nope you can build a pretty fast system for a lot less money if you're willing to get your hands dirty so and I guarantee you that system runs circles around a lot of systems that people work with every day in the Enterprise oh absolutely no doubt about that I could detect the horror in your chuckle like the suffering of day job oh yeah all right well thank you very much for joining me well it was great to be here I really appreciated working with you and having you be my server monkey so that was fun no problem and we're gonna do more of it I'm sure because uh we need to know inquiring minds want to know isn't it all right foreignit's not every day that you trip over bugs and find problems even if somebody was going to Shell out the 1.6 million dollars it would take to license Microsoft SQL server for this super micro system this is probably the fastest system on the planet running Microsoft SQL server and oh boy Glenn Berry and I we found some bugs and we had some Adventures well we got something very special for you today I'm joined by Mr Glenn Berry MVP SQL expert and uh I needed him to take apart some things as we wanted to do a bunch of testing across a bunch of different servers Intel like it was faster Genoa Genoa however you want to pronounce it uh the new sp5 platform from AMD Intel Sapphire Rapids with their accelerators q80 puts up a good pretty good fight and he's also committed to a threadripper system which is desktop which is a little different class and maybe like we had a whole different class of problems than we were expecting but I'm getting ahead of myself say hello let's let's talk about it hey well I'm really happy to be working with you on this and I had a lot of fun on these three systems and basically we we built a two terabyte database with 24 data files in SQL Server 2022 and we played with several different systems to see how fast we could get it to back up that database using different methods so that's kind of a high level of what we try to do and there was a lot of challenges and bottlenecks we ran into as we were doing that at the end of the day though backing up a two terabyte database in production most people expect that to take 45 minutes an hour yeah on these test systems we can do it in under a minute oh yeah yeah definitely I mean we got it down to 34 seconds so and that's a actual backup with no compression using a backup to know we got it down to 14 seconds so that's kind of mind-blowing 14 seconds to back up a two terabyte database yeah yeah that's crazy it doesn't necessarily even require that you have 1.5 terabytes of memory it's like oh we'll just cache it all into memory it's like no this is these were committed rights they made it to a block device yeah definitely you know so we ran several different tests we did a backup to null which is basically just reading all the data in the database and throwing it away and then we did a backup with no compression which is a lot of people do and then we did two different forms of software compression we did Legacy Microsoft Express compression that's been around since 2008 and then we tried the new Intel software-based compression qat compression and then we did Hardware assisted qat compression so we did those tests on this database with lots of different configuration settings and lots of messing around with the drives to try to maximize our throughput that's basically our bottleneck here is read sequential throughput from The Sword subsystem and then memory throughput and then finally write sequential throughput writing to the backup files using consumer ssds in place of enterprise that is a recipe for disaster in the Enterprise but in our case we wanted to see what the system would do making it go as fast as possible and we very quickly encountered some very obvious bugs one it's pretty clear that Microsoft is not really doing a lot of testing around 128 core SQL servers I mean sure the license is wildly expensive but our dual 96 core Genoa system here was definitely not behaving as it should it could be handily outperformed by a dual 32 core system in a lot of cases now for me that was counter-intuitive because in the preliminary testing and looking at how SQL servers used real world almost always it's more of a CPU bottleneck and I think that's what you were encountering on your own storage Beast system yeah and when you've got fewer cores and you do a striped backup what happens is you get lots of CPU pressure that more the more stripes you add and a striped backup by the way is just instead of writing to one backup file you have multiple files and they can be on the same drive or they can be striped across different physical drives like we did here so doing that puts a lot more pressure on the CPU so that's why I was pegging my threadripper system at 100 doing that for this testing and we didn't have that problem on the Genoa or Sapphire rapid systems when they had a lot more cores available but one interesting thing though that we ran into which is probably a SQL Server bug I'm going to go ahead and say it definitely is but I don't think you're quite as sure is uh we have so many cores that it doesn't seem like SQL server was recognizing all of the cores and using them appropriately because even though we had more cores available the software-based backup didn't seem like it took advantage of that no it didn't hear it you know SQL Server when it starts up writes an entry to the SQL server error log that tells you how many cores it can see and that was picking up all the cores and the sapphire Rapids and Genoa systems but there's a different query you can run that looks at what the Newman nodes inside of SQL Server can see and that was only picking up 64 logical cores per pneuma node and that's way below what we had available in those two systems so I think that was part of the problem we were running into running the qat software compression uh just to give the the audience an idea of the these systems these are pretty much top of stack for from both Intel and AMD both of them were Super Micro Systems our super micro system with Genoa had dual 96 core CPUs these are really not super realistic for what you'd be running on a on a bare metal SQL Server workload maybe you would be running a bunch of SQL Server VMS but I don't think that there's there's probably what five six customers worldwide running a system like that yeah because SQL server licensing costs for Enterprise Edition with that many cores is really going to be very expensive so most people are running on much smaller systems whether it's bare metal or virtualized so you're absolutely right there our other system which was Sapphire rapids-based also super micro because we tried to be as like for like as we could was uh based around the 48 core I can't remember the designation I think it was the 44 core 48 core Sapphire Rapids 8368 no it was yeah it was a 44 core and then it was an Intel Xeon Platinum 8458p is what it was so yeah one of the top of the line Sapphire rapid skus I was hoping that the extra clock speeds on that because usually if you have a little bit fewer cores you get a little bit better boost performance but it didn't really seem to help us much here no it didn't and interesting I ran cpu-z and geekbench and they were pretty close between the Genoa and the sapphire Rapids on those two benchmarks so they're fairly evenly matched for single threaded performance so part of our goal here was one to use all the pcie lanes and two to make this thing go as fast as we possibly could we had to get out our u.2 cheat codes and we used 24 icdoc m.2 to u.2 adapters along with solidon p44 Pro ssds because they were the fastest ssds we could get for this job even faster than Samsung ssds well for these five tests the storage Beast one on two of the tests so it won on no compression because it had slightly better I O performance and the Genoa system did because I had a lot of Samsung 990 drives in there where you had a lot of 980s and it's kind of funny to say that a Samsung 980 is slow but it was it was in this test so we won no compression on storage bees and then qat software also the storage piece one because it has faster single threaded performance than either one of the server systems and then the Genoa system one on backup to null because it had more memory throughput than my threadripper did and remember all we're doing is reading everything and throwing it away so having lots of read sequential throughput on the storage subsystem and then memory throughput is all you care about there and then also it won on the Microsoft Express Legacy software compression the Genoa system one and then finally the sapphire rapid system one on the qat hard work impression because it was using the built-in qat accelerator inside of the CPU so even though it had less storage performance it actually won that test and that's what that feature is designed for so it worked as advertised in that test yeah then the nice thing about the qat uh acceleration is that it really does work as advertised and to help everybody understand it's not a feature in a CPU core it's actually more like a pcie peripheral that's built into the CPU it's a sort of a memory based accelerator and we're fortunate because we also have numbers because you mailed me a pcie 3.0 quick assist accelerator you know in the before times intel was doing these kinds of accelerators on pcie cards which are actually pretty good and even if you have a SQL Server that's fully CPU pegged you use this accelerator whether built in or on a pcie card and it does not add really any CPU overhead so it doesn't you know when you're running a backup your users can't really feel that because the system's not busy dealing with that that might be IO yeah you know that car that I sent you is an older 2018 vintage uh qat 8970 card and it works as advertised also it offloads that backup compression workload from your regular cores to that card and it lets you run your regular workload with less of an effect from the backup compression and with that it was still holding its own even in you know 2023 it's you know I know 2018 is basically the 1970s in computer years but yeah still doing pretty good yeah I wish that Intel would come out with a newer version of that so if you wanted to run that on a generous system you could but they don't have a whole lot of incentive to do that so there hasn't been any announcements about it yet now I have picked up some more Sapphire rapid CPUs workstation versions and I found that the qat accelerator is disabled but there may be a way to to get it turned back on I don't know yet we're gonna have to save that for a future video yeah that'll be fun to play with if we can get that turned on otherwise they're going to be shoving pcie 3 peripherals and a pcie5 slot just it's like why why yeah such a waste it's fun I think um I think also it's probably worth setting up a single socket journalist system that's more akin to what people would probably use with with SQL server and see how that goes because the CPU for your threadripper system to be as CPU pegged as it was the Genoa system was disproportionately not pegged it's wasting Cycles somewhere beyond not just not using the cores and the single thread performance but I it was it was not obvious where or what was going on yeah well it'd be nice if we can get our hands on some pcie Gen 5 drives to use for this testing you know we've been just using Gen 4 drives so that's kind of our bottleneck on some of these tests yeah yeah it's uh it's a lot of fun we're also we're going a little off label here in the Enterprise drives especially even Enterprise pcie5 drives um are architected differently than consumer drives and so in order for us to to pull out what we have here you with your 990s and me with my 980s and later solidime ssds um they perform way differently than Enterprise drives and Enterprise drives are perform worse but you can count on an Enterprise Drive to perform consistently over the lifetime of the drive and no matter what the capacity of the drive is with the drives that we're using in these servers it's really not something anybody should ever actually do with their systems because as the as their database gets larger the performance is going to tank substantially and that's why no one uses these kinds of drives in an actual real server oh yeah that's that's the trade-off you know you get consistency and endurance with Enterprise drives but you don't get the absolute Peak Performance that a client Drive gets and and one thing that I had to be careful about with this testing is that most uh consumer drives or TLC and they have a small SLC right cache or relatively small and you've got to be careful that you don't exhaust that or when you do exhaust that you'll see a huge drop off in your sequential right performance so depending on what a test you're doing here you can run into that when you're doing this sort of Benchmark testing not just TLC also qlc especially the important ones like the 990s technically I think are qlc but they mix TLC and SLC depending on on what they're doing I may be wrong about that on the 990. yeah so yeah if we can get pcie Gen 5 drives at some point in the future that'll just give us close to double the bandwidth you know and like as you've said before the Gen 5 drives are still a little bit immature although they're getting slightly better I just got a a crucial t700 a couple days ago to play with so that's another fast consumer Drive Crystal are you listening send us a bunch although adapting the m.2 into u.2 is a little tricky I I reached out to icdoc and they sent me 16 I think and I bought eight more or nine more so that we could take m.2 and shove them into u.2 and fun fact even if you do that that doesn't mean that m.2 are hot plug you can put them in the u.2 slot but they're still not hot plug if you just shove them in there they don't work you have to reboot and it's probably hard on the m.2 and I'm probably lucky I didn't murder one of them shoving it into something that was not actually hot plugged yeah well I think we had a lot of fun playing with these server systems and making them do things they weren't really designed to do so impressive performance though for Jan on like from your perspective how is it in terms of we've seen two or three generations from both team red and team blue uh it seems to me like the performance Jan on Jan is moving up a lot which people running SQL workloads can benefit from but you know what are what are your thoughts well you know a lot of times your CPU performance is extremely important to SQL server and a lot of people don't seem to realize that they just focus on i o performance but if you don't have any other bottlenecks your single threaded CPU performance is your final bottleneck so the latest generation CPU releases are making a big difference for a lot of workloads you know some of you don't have any other bottlenecks to run into so there have been some pretty big jumps and I'm really anxious to see more and more people start to use Sapphire rap Zan Genoa for SQL Server do you think the fact that the last couple generations of CPU pretty much have Boost performance baked in I mean I know Intel xeons have been doing boost for a long time but certainly I've worked with other database administrators who disabled it's like oh we're going to disable all power management and we're going to disable everything and the chords will always run at the same speed for consistency and now that seems like you're shooting yourself in the foot yeah that used to be a really big deal power management make a huge difference for SQL server on older generation CPUs but the newer ones handle that a lot better and they throttle up a lot more quickly so you don't necessarily have to go in and do that like you did or if you do it it doesn't make as much of a difference as it did in the past so you're absolutely right there fun times can you think of any other tips or tricks or anything else you want to share well not really I mean just the one thing I think people should take away from this is you want to do striped backups to get more performance even if you've got a fairly slow i o subsystem that makes a huge difference so yeah that's probably the biggest easy tick away from this it would be pretty easy with the uh with the Powershell facilities in modern SQL Server you could do a Powershell command that does the striped backup and then collects the files and deposits them on a remote system or forwards them or or linearizes the fact that now your backups are spread across a whole bunch of different devices yeah well I mean there's existing tools there's a guy named Ola allengren who's another SQL Server MVP who has a tool that's been around for quite a few years that already supports striped backups and sort of a free utility and it works really well so yeah nice so this was a lot of fun I learned a lot I learned a lot about how acceleration works and how acceleration works specifically with SQL Server because Microsoft's actually done a really good job making sure that the way that it uses the hardware acceleration doesn't impinge on things that are running on the CPU because that could negatively impact the actual thing it's trying to do as far as be a database server so there's a lot of really interesting stuff you can learn here and you can learn all that from from Glenn's blog and also his YouTube channel so you should definitely check that out you want to gonna say anything about it the last thing you blogged about or your articles or you know fun tips and tricks I mean if you have to live this daily you really should check it out uh well that sort of caught me by surprise but I mean I I blog fairly regularly and I'm trying to build my YouTube channel so there'll be more content coming soon so yeah you know I focus on SQL server and from a hardware perspective which is sort of unusual so well certainly there needs to be more of that because there have been some messes that I've cleaned up not with not on the Microsoft side but just with databases in general where it's like oh look at that there's no keys wow how is this working at all oh yeah no that's that keeps me employed so you know and if you're you know a home lab Enthusiast working on SQL Server you don't need a 96 core Genoa system to do it you can do it with a 16 core threadripper with its you know commodity ddr4 memory I mean look at these numbers in this spreadsheet this tiny little 16 core system is within spitting distance of these monster systems so yeah it's pretty good really nope you can build a pretty fast system for a lot less money if you're willing to get your hands dirty so and I guarantee you that system runs circles around a lot of systems that people work with every day in the Enterprise oh absolutely no doubt about that I could detect the horror in your chuckle like the suffering of day job oh yeah all right well thank you very much for joining me well it was great to be here I really appreciated working with you and having you be my server monkey so that was fun no problem and we're gonna do more of it I'm sure because uh we need to know inquiring minds want to know isn't it all right foreign\n"