Press "Enter" to skip to content

Month: October 2021

Adding Debug Logic to T-SQL Procedures

Deborah Melkin does not take kindly to bugs:

I often find that I have to write complicated stored procedures where I need to check things as I go along. My go-to for using this snippet is when I write stored procedures that use dynamic SQL. You’d be surprised (or not) at how often I have had to do this over the years. There’s been functionality where the user gets to choose the columns being used, rewriting ORM data layer “catch-all” queries to improve performance, and cross database queries where the name of the database may not be the standard name (think development and QA databases living on the same SQL instance.)

Click through for an example of where the @Debug parameter pays off. My recollection was that, for really long NVARCHAR(MAX) strings, running PRINT by itself might cut off the code after ~4000 characters, but that could be a historical recollection.

1 Comment

Short Query Store Queries

Mala Mahadevan has a few short-ish Query Store scripts for us:

I use Query Store a lot where I work – and I’d like to share queries I use on Query Store DMVs that I find incredibly useful.

My favorite is one below, which I use to see stored procedure duration. It comes with other information including plan id, start and end time – all of us help me see red flags right away if there is any query not performing as well as it should.

Click through for that script as well as two more.

Leave a Comment

Performing a Restore to SQL Managed Instance

Arun Sirpal shows us how to perform a backup and restoration from an on-premises SQL Server to Azure SQL Managed Instance:

So in the last blog we confirmed that we could move to SQL MI via some analysis, this is now time to actually do a backup and restore via URLs to move data.

Quite simply you need to BACKUP to URL (Azure Storage container) and the setup requirement is that you need to create a SQL credential that holds the SAS token – this is what allows authentication to the container to take place. 

Click through for the process.

Leave a Comment

Building a DBA Database

Andy Mallon wants a database all of his own:

I’ve written about my DBA Database quite a few times. Some of the code in that database is written recently, and some of it was originally written a decade ago. Regardless of how old it is, or how good of a DBA I was when I wrote it, these little scripts are life-savers, and they are always at my fingertips.

And Andy has made them available to your fingertips as well. And the rest of your hands, I suppose.

Leave a Comment

Detecting Hard-to-Classify Data

Kaushal Mukherjee takes us through a new Python package:

The article explains the algorithm behind the recently introduced Python package named PyHard, based on the concept of Instance Space Analysis. It helps in assessing the quality of a dataset and identifying what are the instances which are hard/easy to classify. With the help of this algorithm we can separate out noisy instances. It also provides an interactive visualization tool to deep dive into the instance space.

Click through for the details. I’m going to wait for PyHard 2: PyHarder. Or maybe PyHardWithAVengeance. But it’ll all go downhill by the time we get to PyHard 5.

Leave a Comment

Scaling Out vs Scaling Up

Jordan Braiuka compares two models for scaling:

We often get questions from customers about the best way to add capacity to their cluster. Is it better to add nodes, or simply to increase the capacity in their nodes? Unfortunately, the truth is there is no best way—like all complex issues in distributed systems, there are benefits and drawbacks to each scaling approach. 

While each of our highly distributed systems (Apache CassandraApache Kafka, etc.) have slightly different implementations of scaling, the concepts remain consistent across most distributed systems. 

Click through for a comparison between the two approaches. As the article indicates, both are meaningful strategies and your choice might come down to a combination of the technology stack and the problem at hand.

Leave a Comment

SSMS Tools Pack 5.7

Mladen Prajdic announces SSMS Tools Pack 5.7.0.0:

Run script on multiple targets
Improved: Show databases that are not in normal state and are not accessible.
SQL History
Improved: Enable showing confirmation dialog when restoring the last session.
Search Results in Grid Mode
Improved: Added warning that searching through large result sets will block SSMS.

These are the improvements; click through for the list of what’s new.

Leave a Comment

Testing sp_ineachdb

Aaron Bertrand takes us to the Island of Misfit Databases:

The only database that requires extra handling is the one that contains a tab, because SQL Server doesn’t know how to generate file names when that character is present. I am sure there are a bunch of other less common but equally exotic characters that may cause the same problem.

This is how I actually tested sp_ineachdb, to make sure it was ready for just about any bad idea anyone used to name a database, and could handle various possible database states (for a lot more background on this procedure, and why it is better than the undocumented, unsupported, and buggy sp_msforeachdb, see this and this). Here you can see that the procedure works against all these poorly-named databases, and skips databases that are inaccessible (rather than raise an exception).

Click through to see the list of databases Aaron uses. Technically, I think Aaron’s blog post also counts as a Halloween post because some of those databases are spooky.

Leave a Comment

Star Schemas versus Header-Detail Tables in Power BI

Marco Russo and Alberto Ferrari lay out another proof that the star schema is the right schema for Power BI:

We already shown in a previous article (Power BI – Star schema or single table – SQLBI) how the star schema proves to be the best option when compared with a single table model. Single-table models are the evil: do not be tempted by them, choose a star schema.

In this article, I want to show you an example in the opposite direction. A single table model denormalizes everything in one table, and we already learned that it is bad. But what if we keep a more normalized structure, as it often happens in header/detail models (like orders and order lines)? Is a header/detail model better than a star schema? The quick answer is: “No. Nope. No way. Not at all. Are you kidding me? No.”. Nonetheless, this might be just our personal opinion. The goal of the article is to provide you with some numbers and considerations to prove the previous statement.

Read on and you make the call.

Leave a Comment