A Fine Slice Of SQL Server
Vlad Drumea tries out SQL Server Management Studio 22:
SQL Server Management Studio 22 just went GA this week, so I figured I’d document my new configuration in a blog post.
A while ago I made a post about my then SSMS 19 configuration, but a lot has changed in the latest version of SSMS.
Read on for a variety of configuration changes that Vlad recommends.
Leave a CommentKoen Verbeeck troubleshoots an issue:
While deploying a DACPAC (from a SQL Server Data Tools Database Project) through Azure Devops, I got the following error message:
The user attempting to perform this operation does not have permission as it is currently logged in as a member of an Azure Active Directory (AAD) group but does not have an associated database user account. A user account is necessary when creating an object to assign ownership of that object. To resolve this error, either create an Azure AD user from external provider, or alter the AAD group to assign the DEFAULT_SCHEMA as dbo, then rerun the statement.
Read on to see what happened in Koen’s case. This is an example of a generic error message hiding relevant information from us.
Leave a CommentVictor Rivas visualizes some sales data:
This use case demonstrates the powerful capability of Synoptic Panel to analyze and visualize spatial data at large venues like The Sphere in Las Vegas, which seats 9,205 people. The study addresses the challenge of visualizing over 1 million ticket sales records from 200 events, including concerts and conferences, to gain insights into revenue and average occupancy percentage across different seating categories, sectors, and individual seats.
The objective is to demonstrate how spatial data visualization helps stakeholders understand revenue distribution and audience behavior related to seating arrangements, enabling more informed decision-making.
Click through for the case study. H/T Marco Russo.
Leave a CommentLee Asher performs refactoring:
Over time the term “refactoring” has expanded and is sometimes used to mean code quality improvement in general, but here we are using it with its original meaning: condensing and eliminating redundant segments of code. Like factoring a number in math, we break the code into smaller blocks, identify any repeated elements, then replace them with a single reference.
I appreciate that Lee is sticking to the original meaning of the term here. Interestingly, Lee doesn’t cover T-SQL functions at all. On net, that’s probably a good thing, especially scalar functions. It’s easy to find cases where converting a function to an inline call can speed up query performance by 3x or more.
The mechanisms Lee does use could have an impact on query performance, especially lateral join/APPLY. But for some of these, as long as you do not overuse the technique, performance will be pretty similar.
Leave a CommentJoe Obbish looks at some execution plans:
Getting back to the query, it doesn’t look that offensive to me. The row mode sort is a parallel top N sort and the overall number of rows to return is low, so each thread can independently sort its rows and return 1000 locally sorted rows to the parent operator. This is about as good as it gets with parallel row mode sorting. This is a row mode only query so the operator times that you see are the sum of that operator’s work and its children. In terms of real work done by the query, the scan clocks in at 1.168 seconds and the sort clocks in at 0.84 seconds. The final accounting at the end by the parent Parallelism (Gather Streams) is misleading at best and an outright LIE at worst. There wasn’t 4 seconds of work done by this query. There was only 2 seconds.
Joe looks at two separate things in this post: first, a way of trying to optimize OFFSET/FETCH style paging queries; and second, how the gather streams parallel operator can report wrong information.
Leave a CommentGreg Low wants to know how things tie together:
This post describes how the object dependency tracking views provide more reliable insights into object dependencies than previous methods such as the use of the sp_depends system stored procedure.
During a recent consulting engagement, I was asked about the best way to determine which stored procedures and views made use of a particular table. In the past, the methods available from within SQL Server were not very reliable. Way back in SQL Server 2008, significant improvements were made in this area, yet I see so few people using them, at least not directly. Many will use them indirectly via SSMS.
In this post, let’s explore the problems with the previous mechanisms (that are still retained for backwards compatibility) and then see how the object dependency views improve the situation.
The dependency DMVs that Greg lands on are much better than sp_depends, for sure, but don’t expect them to know about cross-instance dependencies.
Amy Esselman explains how to signify that a point in a time series is incomplete:
When we’re reporting the latest information, it can be challenging to know how to handle data that is still in progress. For example, if we’re reporting annual performance trends with only three quarters completed in the latest year, the numbers can appear misleadingly low. If you exclude the latest data points, it could hide crucial details from stakeholders. Audiences often want timely updates, but partial data can cause confusion if not clearly communicated.
Amy includes several tactics that can clarify the situation.
Leave a CommentA typical task DBAs and Developers perform is optimizing query performance. The first step, after identifying troublesome queries using a tool like the pg_stat_statements view, is to look at the execution plan to determine what is happening and how to improve.
In PostgreSQL this can be done using EXPLAIN or using third-party tools which use the same process to gather query execution data.
Click through to see what an explain plan looks like in PostgreSQL and ways to visualize those plans.
Leave a CommentPhil Seamark explains why including primary keys in summarize statements can be a bad thing:
When writing DAX queries, performance tuning often comes down to small design decisions that have big consequences. One such decision is whether to include Primary Key columns from Dimension tables in your
SUMMARIZECOLUMNSstatements. This is particularly important when those Dimension tables use DUAL or IMPORT storage modes.This article explains why doing so can lead to inefficient query plans. It describes what happens under the hood. It also shows how to avoid this common pitfall.
Read on to learn more.
Leave a CommentReitse Eskens troubleshoots an issue:
Today was a very interesting day at a customer’s site, where I had some trouble determining why the OneLake security wasn’t functioning as I expected. Spoiler alert: a part was me, and a part had to do with setting up my testing account.
Click through to see what happened and for two Fabric Ideas to improve how column-level and row-level security work.
Leave a Comment