A Fine Slice Of SQL Server
Monica Rathbun looks at tempdb:
Tempdb is always a topic for me whether it’s in my sessions or blogs I have written. However, I’ve never been so excited about it then I am when it comes to the dramatic performance changes introduced in SQL Server 2022. THEY HAVE SOLVED ONE OF OUR BIGGEST PERFORMANCE BOTTLE NECKS, System page latch concurrency.
Click through for the details.
Comments closedErin Stellato has a new version of Management Studio for us:
In addition, we received a lot of feedback about the dialog box, “Querying transaction count”, we introduced back in SSMS 18.11. In 18.12, we added an option to cancel the check if it took more than two seconds, but user comments indicated that was not enough. Therefore, in SSMS 19.0 you have the ability to disable to dialog box completely. Within Tools > Options > Query Execution, simply uncheck the option “Check for open transactions before closing T-SQL query windows”.
That alone might be worth it, especially if you work via VPN and have a bunch of SSMS tabs open.
Comments closedJared Poche looks at the recent past:
Tempdb contention has long been an issue in SQL Server, and there are many blogs on the issue already. But I wanted to add one more mainly to highlight the improvements in recent versions of SQL Server
Tempdb contention is most often discussed in as relating to the creation of temp tables (and other objects) in tempdb. If you are experiencing this you will see PAGELATCH_EX or PAGELATCH_SH waits, frequently with wait resources like 2:1:1 or 2:1:3. This indicates contention in database 2 (tempdb), page 1 (the first data file in tempdb), and one of the PFS, GAM, or SGAM pages (which are pages 1, 2, and 3 respectively). Tempdb files of sufficient size will have additional PFS, GAM, and SGAM pages at higher page numbers, but 1 and 3 are the pages most often referenced.
Read on to see what the SQL Server team has done over three of the past four versions of SQL Server to limit tempdb contention.
Comments closedErin Stellato has a bundle of updates for us:
The query results window got a fair bit of attention this release as we work through the backlog of open issues. First, we introduced a new configuration option to show or hide the action bar in the query results view. The Query Editor > Results: Show Action Bar option can be found in the command palette (CTRL + , ) if you type Show Action Bar. By default, the action bar is shown in the query results pane, as seen in the screenshot below:
Check out the full set of changes in the article.
Comments closedWarwick Rudd continues a series on Azure Arc-Enabled Data Services. Part 9 covers database restoration:
Now that we have our Azure Arc-enabled SQL Managed Instance deployed, potentially one of the most common tasks you will need to undertake is being able to perform a database restore of an existing database to your newly provisioned Azure Arc-enabled SQL Managed Instance.
In our previous post, we deployed our Azure Arc-enabled SQL Managed Instance – “ae-sqlmi-demo” that we will continue to investigate and learn about how we can utilize in our data environments. In this post, we are having a look at restoring a database.
Part 10 looks at upgrade management:
Comments closedManaging your Data Controller through Azure Data Studio provides an easy approach for controlling when you wish to apply upgrades supplied by Microsoft. This is something you need to stay on top of as the upgrade path is an incremental approach and only allows you to upgrade to the next version. If you leave it too long, you could be faced with multiple upgrades to be undertaken. This could lead to extended outages while you get up to date.
Brent Ozar looks at the ConstantCare customer base:
Companies are leapfrogging right past SQL Server 2017. I’m going to hazard a guess that SQL Server 2017 came out too quickly after 2016, and didn’t offer enough features to justify upgrades from 2016.
Does that offer us any lessons for SQL Server 2022? Is 2022 going to be a 2017-style release that people just leapfrog over? Well, as I write this, it’s late December 2022, and I’m not seeing the widespread early adoption that I saw for 2019 where people had it in development environments ahead of the release, learning how to use it.
It’s an interesting analysis of a particular market segment: users of the ConstantCare service.
1 CommentAaron Bertrand gets into the whats and wherefores:
Like many companies, Stack Overflow is trying to get out of the business of running our architecture in our own data centers; instead, we want to offload some of the more mundane parts of system administration to a cloud service offering like Azure.
I’m going to cut to the chase for the purpose of this article and concede we’ve already decided on Azure for the majority of our infrastructure and, most importantly to me, our databases.
Click through to learn what their plan is and why Aaron & co went that particular route.
Comments closedThere was a time when a new release of SQL Server – and therefore a new release of SQL Server Analysis Services – was the most exciting thing in the world for me. New functionality! New things to blog about! Not so now that my focus, and Microsoft’s, is on Power BI and we get cool new functionality there every month. All the same there are still a lot of people running SSAS on-premises and SQL Server 2022 has just been released, so what’s new and is it worth upgrading?
Read on for Chris’s thoughts. MDX even makes an appearance.
Comments closedTimi Oshin and Erin Stellato have an update for us:
In this release of Azure Data Studio, we have exciting news to share across several of our core features and extensions. The first is the announcement of the general availability of Table Designer and Query Plan Viewer. We would like to extend a huge thank you to our engineering teams who have worked tirelessly over the past few months on improvements to these features. We would also like to thank the MVPs and community members who provided feedback on these features. We are grateful for continued engagement from users as we work to make Azure Data Studio the tool of choice for cloud database management across multiple platforms.
There’s a lot in this release, so check out the full changelog.
Comments closedToday, we announced the general availability of SQL Server 2022, the most Azure-enabled release of SQL Server yet, with continued innovation across performance, security, and availability. This marks the latest milestone in the more than 30-year history of SQL Server.
Click through for the high-level overview of what’s in SQL Server 2022 and grab a copy to mess around with.
Comments closed