Optimizing Azure SQL Database, DevOps Collabora tion, Invisible Indexes and more in Database We ekly (07/22/22)

Query Tuning, Why Bother?

There are simply tons of resources on query tuning. I’ve written, in whole or in part, several different books on the subject. There are other books too. Videos. Articles. Blog posts. Online classes. In-person classes. Probably millions, if not billions, of words on this topic. Further, there are more coming.

I’m just going to say it.

Why?

A few of us, an exceedingly tiny few, will be able to spend the time. First, the time to learn how to gather query metrics, understand T-SQL, read execution plans, and understand how the query optimizer works within the SQL Server engine. Second, the time to practice these skills because you can’t just read a book, watch a video or attend a class and be able to do this. You will have to practice to get the skills down. Finally, the time to actually do the work. It’s not easy. It is time consuming.

Here's the deal, most of your businesses, they don’t care. Oh, they want everything to run fast, of course. However, go off to training? No. You’re needed here. Spend time studying and practicing? No, we really need your time spent on building new servers or deploying new code or whatever else they’re going to say has priority. Make things faster? Absolutely. What’s that? How long? No. You can’t do that.

More than this, most query tuning involves two things, because this is where the problems are, changing structures and changing code. For many, maybe even most, organizations, that is absolutely a non-starter. Maybe they’re running third-party software, so they can’t change the code, can’t change the structures. Maybe it’s ancient code put together at the dawn of the PC age by someone who retired 25 years ago and if it’s not broke, don’t even breathe on it hard. Maybe your development team refuses to work with you and you’ve got bad ORM code on top of an object-relational database (when you build your relational database out of objects instead of tables, nightmare). Whatever it might be, you’re not changing the code or the structures.

With all this, what happens? You buy bigger hardware. You buy more hardware. You go up to the next service tier in the cloud. Heck, Microsoft comes to your rescue with adaptive and intelligent query processing (wonderful stuff, truly, but not an actual panacea). You’re simply not going to be given that time.

So, why do so many people spend so much time on query tuning? Why are they always the most well-attended sessions at events, all over the world? What is it about this thing that far too many of us will never be able to do that we’re all spending insane amounts of time learning? I’m truly curious. If you know, please share.

Grant Fritchey

Join the debate, and respond to the editorial on the forums

We created a new, short video to show how a DBA can use SQL Monitor in their day to day work, including sharing reports with senior leaders. Watch it now.

A brief history of the DevOps movement and a discussion of the pivotal role of a tool like Flyway in the DevOps toolchain, when developing and delivering database changes.

Database object documentation is essential for explaining to busy developers, and the wider business, the purpose of each object and how to use it. The solution presented in this article consists of a SQL script to allow developers to add comments to MySQL database objects, without affecting the database version, and a simple way to generate a documentation report, in JSON. The SQL script will execute automatically as a callback, during any Flyway Teams migration run, and the report will allow the team to spot any gaps quickly

Previously I’ve talked about why logging is required and the architecture and circular nature of the log, so now it’s time to look at the real heart of the logging system—the log records themselves.

Logging into multiples servers to make the same update to the command of a job step can be time consuming. Thankfully, there are better methods.

Asking questions is just as important as running network traces. By clarifying who is talking we can cut out noise, clarifying how we get there helps us avoid rabbit holes, clarifying the language helps us understand what the rules are for their conversation.

Snowflake has 3 important components. The Cloud services layer, centralised storage layer and the compute layer.

A technical perspective of Oracle's new database service for Microsoft Azure

Presenting a stored procedure that will create dynamically the external table and/or view or synonym that is calling external tables and therefore causing the error message.

Design considerations, checklists, and detailed configuration recommendations for deploying optimal Azure SQL workloads.

You can use a serverless SQL pool to create a CETAS which will materialize the query results. It means a heavy query in which, for example, the results would be part of future large join with other queries or aggregations that can be consolidated for reporting purposes.

How to create a database project for a database running in SQL Server 2022 which is the source for Azure Synapse Link

Query msdb, use the Last database Backup property in SSMS or check the contents of the backup directory!

Tick marks and gridlines may seem trivial, but even minor changes can have a major impact on the readability of your graph.

What does the growing “plug-and-play” analytical culture mean for data audits?

What if there were a way of reading from APIs that abstracted all the low-level grunt work and worked the same way everywhere? Good news! That is exactly what Steampipe does. It’s a tool that translates REST API calls directly into SQL tables.

How to use Flyway Teams to run basic tests whenever it successfully executes a migration, checking that all the business processes supported by our database always produce the expected results.

Measuring the wrong things is worse than not measuring anything. In this article, Mallika Gunturu explains the right things to measure for agile

Rolling averages are a very common calculation used to smooth out charts. This article shows how to compute a rolling average taking into account only the working days.

There’s more to security in MySQL than user account privileges. In this article, Lukas Vileikis explains the other components of MySQL security.

A few nice updates to Erik Darling's sp_pressuredetectorm including total physical memory in the server and additional CPU details.

In SQL Server 2022 we are introducing concurrent global allocation map (GAM) and shared global allocation map (SGAM) updates which will give SQL Server 2022 a big improvement for scalability of tempdb.

Erik Darling adds new search functionality to his sp_QuickieStore procedure, making it even easier to dig into Query Store data to find queries to tune,

How to store current index usage stats up to the midnight before a reboot of my SQL instances, for analysis.

End users that are not trained in data governance but are actively involved in maintaining a data set can easily make data entry mistakes or create inconsistent data types within columns.

Shortcuts are tempting and disguise themselves as cost-effective. If you’re a data analyst that doesn’t know or can’t afford any better, surely take the data source->dataset approach. Teo Lachev explains why this shortcut might not work out so well.

A really cool way of implementing Field Parameters in Power BI reports.

An introduction to the concept of SCDs and ways to handle them in Power BI.

When you create a new subscription, the record is stored in the ReportServer databases Subscription table with the login account that created the subscription. If the account does not have an email address associated with it or does not have permission to send an email, your subscription will fail.

A 12-point checklist of scripts to review your server’s current security posture.

Accessibility ensures that everyone has a great experience on your site. Bikkani explains how to achieve web accessibility.

Sometimes you need to delete data from multiple tables in a database. For example, you might have a multi-tenant database, and you need to delete all the data that belong to some tenant.

Kenneth Fisher discovers a handy SSMS option to keep pinned tabs separate

Brent Ozar sets a challenge: get a key lookup to show up in an execution plan without having an index seek or index scan operation on the same table

Every now and then, a client says, “We want to add indexes to this third party application, but if we do, we’ll lose support.” No problem – enter indexed views.

Why the SELECT...WINDOW Clause should help make the WINDOW Operations more user-friendly

If you’ve worked with reporting, you’ve probably come across the following problem. You have a list of values, say “A, B, C, D, K, L, M, N, R, S, T, U, Z” that you want to display in a more user-friendly, condensed manner, “A-D, K-N, R-U, Z”.