A Fine Slice Of SQL Server
Deborah Melkin continues a series on Power Apps:
As a developer, I have two things I have to do: 1. Check code into source control and 2. Make sure I can use that code to deploy to any and all environments repeatably and successfully. The question then becomes, how do you do this when the development environment is in a portal?
Read on for the answer, as well as a tricky situation you might run into along the way.
Comments closedKendra Little has a public service announcement:
I’m a huge fan of SQL Server’s Query Store feature. Query Store collects query execution plans and aggregate query performance metrics, including wait stats. Having Query Store enabled makes troubleshooting performance issues such as bad parameter sniffing, much, much easier. Because Query Store is integrated into SQL Server itself, it also can catch query plans in a lightweight way that an external monitoring system will often miss.
When performance matters, it’s important to ensure that you’re managing Query Store so that Query Store cleanup does not run during high volume times. Query Store cleanup could slow your workload down significantly.
Read on for more information. I’d also like to plug qdstoolbox, an open-source solution some of my former colleagues worked on. This includes QDSCacheCleanup, which works considerably better than the built-in cleanup process.
Comments closedAndy Brownsword digs into SSISDB:
I’ve recently been reviewing SSIS packages to make some performance fixes and needed a way to validate the results of those changes. I thought I’d share the scripts as they may be useful for others.
Rather than relying on run times from the SQL Agent running the packages I wanted to dive deeper into the packages so took another approach.
Read on for a query that gets the information. You can also eyeball it in the SSIS reports if you have a small or consistent set of packages to run.
Comments closedSteven Sanderson wants to check for row existence:
Hello, fellow R users! Today, we’re going to explore a common scenario you might encounter when working with data frames: checking if a row from one data frame exists in another. This is a handy skill that can help you compare datasets and verify data integrity.
There are several ways to do it. Click through for two of those ways.
Comments closedKurt Buhler performs comparative analytics:
In a previous article about format strings, we showed an example of how format strings can improve visualizations. The visualizations in that article compared the performance of a company’s marketing videos on a streaming platform to the average of all their videos released that year. In this article, we explain how to conduct this analysis yourself in DAX, where you compare series that occur in different periods.
Click through for an explanation, both in video and in blog form.
Comments closedSam Garth troubleshoots an issue:
On a recent case, a customer was trying to restore a database from a storage account using a SAS token when they received the below error.
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)Additional information:
Cannot open backup device
‘https://storage.blob.core.windows.net/container/dbbackup_2024_03_21_121901.bak‘
Operating system error 86(The specified network password is not correct.).
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3201)
Read on for the troubleshooting steps Sam followed to solve the problem.
Comments closedCary Huang explains how row visibility works in Postgres:
Simply put, the visibility refers to whether a row of data (Heap Tuple by default) should be displayed to the user in certain states, backend processes, or transactions.
For example,
- A user changes a data record from ‘A’ to ‘B’ via UPDATE
- PostgreSQL handles this by INSERTing a new record call ‘B’ while marking record ‘A’ as ‘invisible’
- Both records exist in the system, but ‘B’ is visible, while ‘A’ is not.
- A deleted or invisible record is also referred as a ‘dead’ tuple
- One of the responsibilities of VACUUM process is to clear these ‘dead’ tuples to free up space.
Read on to learn more about various visibility indicators and how they ultimately tell us whether a row should be visible or not.
Comments closedRyan Booz explains how recursive common table expressions work:
The first two articles in this series demonstrated how PostgreSQL is a capable tool for ELT – taking raw input and transforming it into usable data for querying and analyzing. We used sample data from the Advent of Code 2023 to demonstrate some of the ELT techniques in PostgreSQL.
In the first article, we discussed functions and features of SQL in PostgreSQL that can be used together to transform and analyze data. In the second article, we introduced Common Table Expressions (CTE) as a method to build a transformation query one step at a time to improve readability and debugging.
In this final article, I’ll tackle one last feature of SQL that allows us to process data in a loop, referencing previous iterations to perform complex calculations: Recursive CTE’s.
Given that Postgres allows for materialized common table expressions, I’m a bit curious about how recursive common table expressions perform compared to SQL Server.
Comments closedNicky van Vroenhoven performs a search:
You probably also use the same method as I did to search through the Admin portal and tenant settings: CTRL + F from your browser. It does the trick, but not very well.
For example, it only searches the titles of the settings, not the descriptions.
Next to that, you also can get a lof matches that you have to scroll or loop through, which makes it not very clear because more often than not, you don’t know in what section of the tenant settings you ended up.
Read on for an alternative method of searching. Or, I guess, two of them because without Nicky’s post, it can be easy to confuse the two search boxes.
Comments closedSteven Sanderson takes a slice:
Explanation: – We use
nrow(my_df)to get the total number of rows in the data frame. – Then, we use indexing ([nrow(my_df), ]) to extract the last row.
Read on for a simple example of getting the last row using base R, dplyr, and data.table. Then, we kick it up a notch to get the second-to-last row, with the idea being that you could substitute “second-to-last” with any arbitrary number.
Comments closed