Press "Enter" to skip to content

Day: November 15, 2023

Updates to Azure Well-Architected Review Assessments

Published 2023-11-15 by Kevin Feasel

Stephen Sumner shows off some changes:

Microsoft is excited to announce a significant update to the Azure Well-Architected Review assessment helps you build and optimize workloads. It walks you through a series of questions about your workload. Based on your responses, it generates tailored and prioritized recommendations to improve your workload design. The guidance is actionable and applicable to nearly every workload. It aligns with the latest best practices across the five key pillars of reliability, security, cost optimization, operational excellence, and performance efficiency (see figure 1).

I’m a big fan of the Well-Architected Framework and the assessments Microsoft has put together. An assessment can take teams within a company days to complete because the questions are so thorough, but once you do get through the list, you’ll get some great practical insights on your setup and what you can do to improve performance and save money.

Comments closed

Using the WINDOW Operator in SQL Server 2022

Published 2023-11-15 by Kevin Feasel

Steve Jones cuts out the duplication:

I am the host for T-SQL Tuesday this month, and I hope that a lot of people like the topic. This idea actually came to me earlier this year when I happened to see someone ask about a T-SQL problem and get an answer using a Window function. This person mentioned they hadn’t used the window function before, and I wondered how many people haven’t even tried using the OVER() clause with a window function.

Read on to see how you can use WINDOW to replicate window frames, which can be quite helpful for larger queries. It doesn’t change the performance profile but does make the code more readable.

Comments closed

Operations Auditing with LAG() and LEAD()

Published 2023-11-15 by Kevin Feasel

Deborah Melkin builds a report query:

I’ve been doing a lot of work recently where I have long running processes where I need to have visibility on where we are during the process as well as being able to provide a report we can use to find out if there were any errors, how long each step took, and other useful information that we can use later. My solution was to create an audit log table where I simply added a row before and after each step, recording the information I had available. When the processes are done, I create a query to run over the table returning the values I want to see.

Deb also throws in the only ordered set function in SQL Server, STRING_AGG().

Comments closed

The Power of LAG and LEAD

Published 2023-11-15 by Kevin Feasel

Rod Edwards shows off a great use case for LAG():

I often find myself using windows functions in order to group data in wierd and wonderful ways that a simple GROUP BY can’t do… however, the example below is one I came across quite recently on my travels, I was asked to have a look at some code for optimization opportunities. And in this case, thankfully there were some.

Read on for a lengthy cursor, followed by a much less lengthy (and much faster) window function.

Comments closed

Good Use Cases for Window Functions

Published 2023-11-15 by Kevin Feasel

Aaron Bertrand build a list:

When I first used window functions back in SQL Server 2005, I was in awe. I had always used inefficient self-joins to calculate things like running totals, and these really didn’t scale well with size-of-data. I quickly realized you could also use them for ranks and moving averages without those cumbersome self-joins, elaborate sub-queries, or #temp tables. Those all have their place, but window functions can make them feel old-school and dirty.

I’d also recommend learning more about the APPLY operator as well, as it can, depending on the circumstances, be even more effective than window functions (combined with common table expressions) for some of the use cases.

Comments closed